GIN fastupdate and Pending List Tuning #

A GIN index maps each element inside a composite value — every lexeme in a tsvector, every key in a jsonb document, every member of an array — to the rows containing it. Inserting one row therefore means inserting many index entries, which would make writes painfully slow. PostgreSQL’s answer is fastupdate: append the new entries to an unsorted pending list and merge them into the tree later, in bulk.

The cost is paid by readers. Every search must scan the pending list linearly on top of the ordinary tree lookup, so query latency creeps upward between flushes. The index types themselves are covered in specialized index types (GIN/GiST).

What the Pending List Does #

With fastupdate = on (the default), an insert writes its entries to a small append-only area attached to the index. Nothing is sorted and nothing is merged. A search then does two things:

  1. Search the main GIN tree normally.
  2. Scan the entire pending list sequentially, testing each entry.

Step 2 is O(list size), so a query that takes 3 ms against a clean index can take 40 ms once the list holds tens of thousands of entries. Three events empty it: the list exceeding gin_pending_list_limit (default 4 MB), a vacuum on the table, or an explicit gin_clean_pending_list() call. Whichever happens first pays the merge cost for everyone waiting on it.

The pending list sits between writers and readers Inserts append cheaply to an unsorted pending list. Searches must consult both the sorted GIN tree and the entire pending list. A flush, triggered by the size limit, vacuum, or a manual call, merges the list into the tree. INSERT / UPDATE many entries per row pending list (unsorted) cheap append · grows to gin_pending_list_limit flush: size limit · VACUUM · manual clean main GIN tree (sorted) posting lists per key every search reads BOTH tree lookup + full list scan write cost falls, read cost rises with list length — the whole trade in one picture

Annotated Evidence #

The symptom is latency variance on an otherwise stable query:

-- Immediately after a flush
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM documents WHERE body_tsv @@ to_tsquery('english', 'invoice & overdue');

Bitmap Heap Scan on documents  (actual time=2.81..3.42 rows=1204 loops=1)
  Recheck Cond: (body_tsv @@ '''invoic'' & ''overdu'''::tsquery)
  ->  Bitmap Index Scan on documents_body_tsv_idx  (actual time=2.60..2.60 rows=1204 loops=1)
        Buffers: shared hit=142
Execution Time: 3.51 ms

-- After 90 minutes of ingestion, same query, same rows
  ->  Bitmap Index Scan on documents_body_tsv_idx  (actual time=38.92..38.92 rows=1204 loops=1)
        Buffers: shared hit=142 read=2891        -- the extra pages are the pending list
Execution Time: 40.06 ms

Identical plan and identical row counts, with an eleven-fold increase in index-scan time and thousands of extra page reads. Confirm it directly:

CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT version, pending_pages, pending_tuples
  FROM pgstatginindex('documents_body_tsv_idx');
-- version | pending_pages | pending_tuples
--       2 |          2891 |        1841022

1.8 million unsorted entries being scanned by every single search.

Step-by-Step Tuning #

  1. Establish that latency varies rather than being uniformly high. A GIN index that is always slow has a different problem — usually a query matching very common terms.

  2. Read the pending list size with pgstatginindex as above, sampled a few times across a working day.

  3. Flush manually and re-measure to prove causation:

    SELECT gin_clean_pending_list('documents_body_tsv_idx');

  4. Size the limit per index rather than globally. A smaller limit means more frequent, smaller merges and steadier read latency:

    ALTER INDEX documents_body_tsv_idx SET (gin_pending_list_limit = 512);   -- kilobytes

  5. Disable fastupdate where reads dominate and write volume is modest:

    ALTER INDEX documents_body_tsv_idx SET (fastupdate = off);

    Every insert then merges directly into the tree — slower writes, but flat read latency.

  6. Keep it on for bulk loads, and flush deliberately afterwards:

    -- ingestion job
ALTER INDEX documents_body_tsv_idx SET (fastupdate = on);
COPY documents FROM '/data/docs.csv';
SELECT gin_clean_pending_list('documents_body_tsv_idx');
VACUUM ANALYZE documents;

  7. Vacuum often enough that flushes are not left to chance, since a vacuum pass empties the list as a side effect.

Read latency with and without pending-list tuning A sawtooth line shows query latency climbing steeply between rare flushes with a large pending list limit. A nearly flat line shows latency with a small limit or fastupdate disabled. time under continuous ingestion → query latency gin_pending_list_limit = 4 MB (default) limit = 512 kB, or fastupdate = off flush

Before and After #

-- BEFORE: default 4 MB limit, 1.84M pending tuples
Bitmap Index Scan  (actual time=38.92..38.92 rows=1204)  Buffers: shared hit=142 read=2891
Execution Time: 40.06 ms

-- AFTER: gin_pending_list_limit = 512 kB, list flushed
Bitmap Index Scan  (actual time=2.98..2.98 rows=1204)   Buffers: shared hit=148
Execution Time: 3.62 ms

The row counts and the plan are unchanged; only the pages read by the index scan moved, and they moved because the pending list was no longer part of every search.

Common Pitfalls #

Blaming the query when latency drifts. The plan is identical throughout, so it looks like nothing changed. Diagnostic signal: rising Bitmap Index Scan time with a constant row count. Fix: check pending_tuples.

Disabling fastupdate during bulk ingestion. Every insert then merges individually, and load throughput collapses. Diagnostic signal: a COPY that suddenly takes hours. Fix: keep it on for the load, flush afterwards.

Setting gin_pending_list_limit globally. Different GIN indexes serve different workloads. Diagnostic signal: one index flushing constantly while another still grows unbounded. Fix: set the storage parameter per index.

Assuming vacuum will keep up. On a table below its autovacuum threshold, no flush happens for hours. Diagnostic signal: last_autovacuum old with a large pending list. Fix: schedule gin_clean_pending_list(), or tighten the vacuum threshold as described in autovacuum thresholds and dead tuple drift.

Confusing pending-list latency with a bad query. A search matching very common terms is slow for a different reason: its posting lists are enormous and the bitmap it produces covers much of the table. Diagnostic signal: consistently slow queries on frequent terms with pending_tuples near zero. Fix: restrict the search — add a selective secondary predicate, or use ranking with a LIMIT over a narrower candidate set.

Rebuilding the index to clear the list. A full REINDEX does empty it, but at the cost of rewriting the entire structure. Diagnostic signal: a maintenance job that reindexes a large GIN index nightly purely for latency. Fix: gin_clean_pending_list() performs the merge alone and finishes in a fraction of the time.

Setting the limit so low that writes suffer. Every flush is a merge, and merging on nearly every insert reproduces the cost fastupdate exists to avoid. Diagnostic signal: ingestion throughput falling as read latency improves. Fix: treat the limit as a dial between the two, measure both sides, and settle where the workload’s latency budget and its write budget are both met.

Leaving the setting uniform across environments. A staging system with light ingestion never reproduces the production pending list, so the regression only appears after deployment. Diagnostic signal: a GIN-backed feature that is fast in testing and erratic live. Fix: check pgstatginindex on production directly rather than inferring behaviour from a quiet environment.

The pending list limit is a dial, not a switch A horizontal dial from fastupdate off at one end, favouring read latency, to a large pending list limit at the other, favouring write throughput, with a marked practical range in between. fastupdate = off flat read latency slowest writes limit = 4 MB+ fastest writes spiky reads limit 256 kB – 1 MB most latency-sensitive search workloads settle here

Frequently Asked Questions #

Why do GIN queries slow down as writes accumulate? Because fastupdate appends new entries to an unsorted pending list instead of merging them into the tree, and every search scans that list in full. Read latency therefore grows with the list until a flush empties it.

Should I turn fastupdate off? Turn it off when predictable read latency matters more than write throughput and write volume is moderate. Keep it on for ingestion-heavy workloads, where batching the merges is a substantial win.

What flushes the pending list? Exceeding gin_pending_list_limit, any vacuum of the table, or an explicit gin_clean_pending_list() call. Only the last is under your control, which is why latency-sensitive systems schedule it.