Re: COPY and heap_sync

<div dir="ltr"><div class="gmail_extra"><br />On Sat, Aug 30, 2014 at 5:05 AM, Atri Sharma <<a
href="mailto:atri.jiit@gmail.com">atri.jiit@gmail.com</a>>wrote:<br />><br />><br />> On Saturday, August
30,2014, Amit Kapila <<a href="mailto:amit.kapila16@gmail.com">amit.kapila16@gmail.com</a>> wrote:<br />
>><br/>>> On Sat, Aug 30, 2014 at 11:56 AM, Jeff Janes <<a
href="mailto:jeff.janes@gmail.com">jeff.janes@gmail.com</a>>wrote:<br />>> ><br />>> > If you
inserttuples with COPY into a table created or truncated in the same transaction, at the end of the COPY it calls
heap_sync.<br/> >> ><br />>> > But there cases were people use COPY in a loop with a small amount of
datain each statement.  Now it is calling heap_sync many times, and if NBuffers is large doing that gets very slow.<br
/>>> ><br />>> > Could the heap_sync be safely delayed until the end of the transaction, rather than
theend of the COPY?<br />>><br />>> Wouldn't unconditionally delaying sync until end of transaction<br />
>>can lead to burst of I/O at that time especially if there are many<br />>> such copy commands in a
transaction,leading to delay in some<br />>> other operation's that might be happening concurrently in the<br />
>>system.<br />>><br />>><br />>><br />><br />> I agree with that but then, it can
provideus the same benefits like group commit,especially when most of the copy commands touch pages which are
nearby,hencereducing the seek time overhead.<br /> ><br />> We could look at making it optional through a GUC,
sinceit is useful albeit for some specific usecases.<br />><br /><br /></div><div class="gmail_extra">It's
interesting...maybe something analogous to "SET CONSTRAINTS DEFERRED"... <br /><br /></div><div class="gmail_extra">SET
COPYCOMMIT { IMMEDIATE | DEFERRED }<br /></div><div class="gmail_extra"><br /></div><div class="gmail_extra">or<br
/><br/>SET COPY MODE { IMMEDIATE | DEFERRED }<br /><br /></div><div class="gmail_extra"> Just some thoughts!<br
/></div><divclass="gmail_extra"><br /></div><div class="gmail_extra">Regards,<br /><br /></div><div
class="gmail_extra">--<br/>Fabrízio de Royes Mello<br />Consultoria/Coaching PostgreSQL<br />>> Timbira: <a
href="http://www.timbira.com.br">http://www.timbira.com.br</a><br/> >> Blog: <a
href="http://fabriziomello.github.io">http://fabriziomello.github.io</a><br/>>> Linkedin: <a
href="http://br.linkedin.com/in/fabriziomello">http://br.linkedin.com/in/fabriziomello</a><br/>>> Twitter: <a
href="http://twitter.com/fabriziomello">http://twitter.com/fabriziomello</a><br/> >> Github: <a
href="http://github.com/fabriziomello">http://github.com/fabriziomello</a></div></div>