Hi all,
I'm running PostgreSQL 9.1 on a fairly beefy server with a lot of RAM,
so I generally want work_mem set pretty high. One of my tables has a
GIN index, and, as a consequence of the high work_mem setting, its
fastupdate pending list can grow very large. This leads to the
occasional INSERT or UPDATE taking inordinately long as the pending list
is flushed.
My thinking was that I could prevent the list from growing too large by
setting the autovacuum storage parameters on the table such that the
autovacuum process will run after a reasonable number of INSERTs or
UPDATEs. However, the table is mostly INSERT-only. Therefore, only the
autovacuum_analyze_* parameters will actually do anything to affect when
the autovacuumer will run, but when it does, it will do a VACUUM ANALYZE
instead of a plain VACUUM. I don't particularly need the table to be
re-analyzed that often.
Are there any other good solutions for preventing the fastupdate
mechanism from impacting normal queries? I would also consider just
turning fastupdate off, but my understanding is that can lead to the
index becoming less efficient. Is there a way to set work_mem low just
for the purpose of sizing the pending list?
Thanks,
Zev