Thread: Continual uptime while loading data ... COPY vs INSERTS within a transaction.
Continual uptime while loading data ... COPY vs INSERTS within a transaction.
From
Benjamin Arai
Date:
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? Benjamin
Re: Continual uptime while loading data ... COPY vs INSERTS within a transaction.
From
"Christopher Browne"
Date:
On Feb 9, 2008 6:30 PM, Benjamin Arai <me@benjaminarai.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
Re: Continual uptime while loading data ... COPY vs INSERTS within a transaction.
From
Tom Lane
Date:
Benjamin Arai <me@benjaminarai.com> writes:
> 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, and no. Use COPY.
regards, tom lane
Re: Continual uptime while loading data ... COPY vs INSERTS within a transaction.
From
Gerald Timothy Quimpo
Date:
On Sat, 2008-02-09 at 19:27 -0500, Tom Lane wrote: > Benjamin Arai <me@benjaminarai.com> writes: > > 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, and no. Use COPY. Unless inserting into a table that has rules and those rules need to fire. I think I saw a post (by you, Tom) that said COPY doesn't fire rules (haven't read the 8.3 release notes yet though, if COPY fires rules in 8.3 that'd be great, I'd love to use copy for pushing rows into the base table and having the rules fire so the right data goes into the right "inherits" descendant tables). tiger