Re: Periodically slow inserts

Gael Le Mignot <gael(at)pilotsystems(dot)net> writes:
> The problem is when we index objects into the full-text search part of
> the database (which a DELETE and then an INSERT into a specific table),
> the INSERT sometimes take a long time (from 10s to 20s), but the same
> insert (and many other similar ones) are fast (below 0.2s).

> This slowness comes regularly, about every 200 objects indexed,
> regardless of the frequency of the inserts.

Hm. You didn't say which PG version you're using, but if it's >= 8.4,
I think this may be caused by GIN's habit of queuing index insertions
until it's accumulated a reasonable-size batch:
http://www.postgresql.org/docs/9.0/static/gin-implementation.html#GIN-FAST-UPDATE

While you can turn that off, I think that doing so will reduce the
index's search efficiency over time. It might be better to schedule
regular vacuums on the table so that the work is done by vacuum rather
than foreground queries.

regards, tom lane