Re: postgres 8.4, COPY, and high concurrency

If you are inserting a lot of data into the same table, table extension locks are a problem, and will be extended in only 8k increments which if you have a lot of clients hitting/expanding the same table you are going to have a lot of overhead.

-----Original Message-----
From: pgsql-performance-owner(at)postgresql(dot)org [mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Jeff Janes
Sent: Wednesday, November 14, 2012 3:26 PM
To: Jon Nelson
Cc: Heikki Linnakangas; pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] postgres 8.4, COPY, and high concurrency

On Wed, Nov 14, 2012 at 12:04 PM, Jon Nelson <jnelson+pgsql(at)jamponi(dot)net> wrote:
> On Wed, Nov 14, 2012 at 1:01 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>>
>> While the WAL is suppressed for the table inserts, it is not
>> suppressed for the index inserts, and the index WAL traffic is enough
>> to lead to contention.
>
> Aha!
>
>> I don't know why that is the case, it seems like the same method that
>> allows us to bypass WAL for the table would work for the indices as
>> well. Maybe it is just that no one bothered to implement it. After
>> all, building the index after the copy will be even more efficient
>> than building it before but by-passing WAL.
>
>> But it does seem like the docs could at least be clarified here.
>
> In general, then, would it be safe to say that concurrent (parallel)
> index creation may be a source of significant WAL contention?

No, that shouldn't lead to WAL contention. The creation of an index on an already-populated table bypasses most WAL when you are not using archiving. It is the maintenance of an already existing index that generates WAL.

"begin; truncate; copy; create index" generates little WAL.

"begin; truncate; create index; copy" generates a lot of WAL, and is slower for other reason as well.

Cheers,

Jeff

--
Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
This email is confidential and subject to important disclaimers and
conditions including on offers for the purchase or sale of
securities, accuracy and completeness of information, viruses,
confidentiality, legal privilege, and legal entity disclaimers,
available at http://www.jpmorgan.com/pages/disclosures/email.