Re: Continual uptime while loading data ... COPY vs INSERTS within a transaction.

On Feb 9, 2008 6:30 PM, Benjamin Arai <me(at)benjaminarai(dot)com> wrote:
> Hello,
>
> We are running a system which requires continual uptime while loading
> data. Currently one particular table receives a large number of inserts
> per commit (about 10000 inserts). This process works well allowing both
> end users to access the data as well as loading reasonably quickly.
>
> We are thinking of modifying our system to use COPY to replace these
> large INSERT transactions but we are concerned that it will greatly
> impact the user experience (i.e., exclusively lock the table during the
> copy process). First, does COPY grab an exclusive lock? Second, is
> there a better way to load data?

No, COPY does not take an exclusive lock, so this optimization should
be a helpful one.

COPY has been fairly regularly enhanced over the last few years to
make it faster, and there is no reason to think that this progression
is ending at PG 8.3, so this should indeed be a near-optimal way to
load data.
--
http://linuxfinances.info/info/linuxdistributions.html
"The definition of insanity is doing the same thing over and over and
expecting different results." -- assortedly attributed to Albert
Einstein, Benjamin Franklin, Rita Mae Brown, and Rudyard Kipling