On Wed, Oct 30, 2013 at 9:54 AM, Leonardo Francalanci <m_lists@yahoo.it> wrote:
Jeff Janes wrote
> The index insertions should be fast until the size of the active part of > the indexes being inserted into exceeds shared_buffers by some amount > (what > that amount is would depend on how much dirty data the kernel is willing > to > allow in the page cache before it starts suffering anxiety about it). If > you have enough shared_buffers to make that last for 15 minutes, then you > shouldn't have a problem inserting with live indexes.
Sooner or later you'll have to checkpoint those shared_buffers...
True, but that is also true of indexes created in bulk. It all has to reach disk eventually--either the checkpointer writes it out and fsyncs it, or the background writer or user backends writes it out and the checkpoint fsyncs it. If bulk creation uses a ring buffer strategy (I don't know if it does), then it might kick the buffers to kernel in more or less physical order, which would help the kernel get them to disk in long sequential writes. Or not. I think that this is where sorted checkpoint could really help.
> and we are > talking about GB of data (my understanding is that we change basically every > btree page, resulting in re-writing of the whole index).
If the checkpoint interval is as long as the partitioning period, then hopefully the active index buffers get re-dirtied while protected in shared_buffers, and only get written to disk once. If the buffers get read, dirtied, and evicted from a small shared_buffers over and over again then you are almost guaranteed that will get written to disk multiple times while they are still hot, unless your kernel is very aggressive about caching dirty data (which will cause other problems).