Re: How to improve insert speed with index on text column

On Tue, Jan 31, 2012 at 10:46 AM, Josh Berkus <josh@agliodbs.com> wrote:
>
>> Shared buffers is the cache maintained by PostgreSQL. All all the data
>> that you read/write need to go through shared buffers.
>
> While this is technically true, I need to point out that you generally
> increase shared_buffers for high concurrency, and for reads, not for
> writes, especially for row-at-a-time inserts.  There's just not that
> much memory required (although more than the out-of-the-box defaults).

When inserting rows in bulk (even just with inserts in a tight loop)
into indexed tables, I often see the performance collapse soon after
the active index size exceeds shared_buffers.  Or at least,
shared_buffers + however much dirty data the kernel is willing to
tolerate.  But that later value is hard to predict.  Increasing the
shared_buffers can really help a lot here.  I'm sure this behavior
depends greatly on your IO subsystem.

Cheers,

Jeff