Re: postgres 8.4, COPY, and high concurrency

On Wed, Nov 14, 2012 at 12:04 PM, Jon Nelson <jnelson+pgsql@jamponi.net> wrote:
> On Wed, Nov 14, 2012 at 1:01 PM, Jeff Janes <jeff.janes@gmail.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