Re: COPY and heap_sync

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
> <javascript:_e(%7B%7D,'cvml','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.

Regards,

Atri

--
Regards,

Atri
*l'apprenant*