Re: COPY and heap_sync

On Sat, Aug 30, 2014 at 5:05 AM, Atri Sharma <atri(dot)jiit(at)gmail(dot)com> wrote:
>
>
> On Saturday, August 30, 2014, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>>
>> On Sat, Aug 30, 2014 at 11:56 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
wrote:
>> >
>> > If you insert tuples with COPY into a table created or truncated in
the same transaction, at the end of the COPY it calls heap_sync.
>> >
>> > But there cases were people use COPY in a loop with a small amount of
data in each statement. Now it is calling heap_sync many times, and if
NBuffers is large doing that gets very slow.
>> >
>> > Could the heap_sync be safely delayed until the end of the
transaction, rather than the end of the COPY?
>>
>> Wouldn't unconditionally delaying sync until end of transaction
>> can lead to burst of I/O at that time especially if there are many
>> such copy commands in a transaction, leading to delay in some
>> other operation's that might be happening concurrently in the
>> system.
>>
>>
>>
>
> I agree with that but then, it can provide us the same benefits like
group commit,especially when most of the copy commands touch pages which
are nearby,hence reducing the seek time overhead.
>
> We could look at making it optional through a GUC, since it is useful
albeit for some specific usecases.
>

It's interesting... maybe something analogous to "SET CONSTRAINTS
DEFERRED"...

SET COPY COMMIT { IMMEDIATE | DEFERRED }

or

SET COPY MODE { IMMEDIATE | DEFERRED }

Just some thoughts!

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog: http://fabriziomello.github.io
>> Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
>> Github: http://github.com/fabriziomello