Currently, we are monitoring pending_pages (pgstatginindex works on 9.4.4), and run a vacuum every night. We hope it will solve the problem, without disabling fast update.
We have got big slow down on our production plateform (PG 9.4.4).
What is it slow compared to? Did your version change, or your workload/usage change?
After analyzing wals with pg_xlogdump, we see lot of writing in Gin Indexes.
We suspect slow down are related to the write of pending update on the index.
So, is there any method to see
- what is the current config of gin_pending_list_limit on a given index ?
gin_pending_list_limit will be introduced in 9.5. In 9.4 and before, there is no such parameter. Instead, the limit is tied to the setting of work_mem in those versions.
- the current size of pending list on a given index ?
You can use this from the pgstattuple contrib module:
SELECT * FROM pgstatginindex('test_gin_index');
Your best bet may be to turn off fastupdate. It will slow down most inserts/updates, but you will not have the huge latency spikes you get with fastupdate turned on.
Also, you might (or might not) have a higher overall throughput with fastupdate turned off, depending on a lot of things like the size of the index, the size of ram and shared_buffers, the number of spindles in your RAID, the amount of parallelization in your insert/update activity, and the distribution of "keys" among the data you are inserting/updating.