Postgres Full-Text Search in Production: How to Load-Test the Index and Pin Down Relevance

Adding full-text search to Postgres is a two-line migration. Running it in production without surprises is not. The GIN index that makes search fast also adds write amplification and a background cleanup process that can quietly fall behind under a heavy insert rate, and "relevance" that looked fine on your laptop can drift the day someone changes a dictionary. Before you decide Postgres search is or isn't enough, you need evidence — a load test against your real write rate and a relevance contract you can measure — not just a row count.

I wrote earlier about why Postgres full-text search is usually the right first stop instead of standing up Elasticsearch. A reader pushed back with a sharp point: "one fewer service" doesn't mean zero search-specific operations. That's correct, and it's the part most tutorials skip. This post is the operations half of the story.

Why does the GIN index slow down my writes?

A GIN (Generalized Inverted Index) maps each lexeme to the list of rows that contain it. When you insert or update a row, every lexeme in its tsvector has to be threaded into the index — a document with 200 distinct word roots touches 200 posting lists. That is the write amplification the reader flagged: one row write becomes many index writes.

Postgres softens this with the fastupdate mechanism. New entries land in an unsorted pending list first, and are merged into the main index structure in bulk later — during autovacuum, or when the pending list exceeds gin_pending_list_limit (4 MB by default). This keeps individual inserts cheap, but it moves the cost, it doesn't remove it. Two failure modes follow:

• Under a sustained high insert rate, the pending list grows faster than it's flushed. Searches now scan a large unsorted pending list on top of the main index, and query latency climbs.
• The eventual flush is a burst of I/O that competes with your transactional workload.

You can tune this per index:

-- Flush the pending list more often (smaller bursts, steadier latency)
ALTER INDEX articles_search_idx SET (gin_pending_list_limit = '512kB');

-- Or, for a write-heavy table where you'd rather pay the cost inline
ALTER INDEX articles_search_idx SET (fastupdate = off);

-- Force a flush on demand (e.g. before a read-heavy window)
SELECT gin_clean_pending_list('articles_search_idx');

The takeaway: the GIN index doesn't make writes free, it defers them — so your capacity question is "can autovacuum keep the pending list drained at my peak write rate?", and that only has an answer under load.

How do I actually load-test it?

Test the transactional workload, not isolated search latency. A benchmark that only fires SELECT queries will tell you search is fast while hiding the fact that your inserts have doubled in latency. Drive writes at your real production rate and watch the write path.

The four things to record over a sustained run:

-- 1. Index size growth over the run
SELECT pg_size_pretty(pg_relation_size('articles_search_idx'));

-- 2. Pending list pages (needs the pageinspect extension)
SELECT pending_pages, pending_tuples
FROM gin_metapage_info(get_raw_page('articles_search_idx', 0));

-- 3. Autovacuum activity and last run per table
SELECT relname, last_autovacuum, autovacuum_count, n_dead_tup
FROM pg_stat_user_tables WHERE relname = 'articles';

-- 4. p95 latency of the TRANSACTIONAL statements, from your app or pg_stat_statements
SELECT query, calls, mean_exec_time, max_exec_time
FROM pg_stat_statements
WHERE query LIKE 'INSERT INTO articles%' OR query LIKE 'UPDATE articles%';

Run it long enough to cross at least a few autovacuum cycles — a 30-second smoke test will never surface a pending-list problem that shows up after an hour of steady inserts. If pending_pages trends upward and never comes back down, autovacuum is losing the race: raise autovacuum_vacuum_cost_limit, lower the scale factor for that table, or turn fastupdate off and accept inline cost.

The takeaway: a search load test that doesn't measure insert/update p95 and pending-list drain is measuring the wrong half of the system.

What is a relevance contract, and why do I need one?

Performance is only half of "is Postgres enough." The other half is relevance — and relevance is the part that silently regresses. The day someone changes the text-search configuration from english to simple, or reweights title vs. body, or adjusts a trigram threshold, your results change and no test fails, because there was never a test.

A relevance contract makes those changes measurable. It has four parts:

1. Pin the configuration per field and language. Never rely on the default_text_search_config session setting — name it explicitly in both the generated column and the query, so indexing and querying always agree. A mismatch here produces results that are wrong in ways that are maddening to debug.

   -- Explicit config in BOTH places, always
   ... to_tsvector('english', coalesce(body, ''))         -- index side
   WHERE search_vector @@ websearch_to_tsquery('english', :q)  -- query side

1. Build a small judged query set. Twenty to fifty real queries with the row IDs a human agrees are correct answers. This is your regression suite for search quality.

2. Track a couple of blunt metrics. Zero-result rate (queries returning nothing) and a ranking metric like precision@10 against your judged set. You don't need information-retrieval sophistication; you need a number that moves when quality moves.

3. Gate dictionary, weight, and threshold changes on it. Any change to config, setweight values, or pg_trgm similarity thresholds re-runs the judged set before it ships.

The takeaway: relevance you can't measure is relevance you can't safely change — a judged query set turns "the search feels worse now" into a failing check.

How do I keep pagination stable and headlines safe?

Two smaller footguns from the same discussion, both real in production.

Deterministic ordering. ORDER BY ts_rank(...) DESC alone is not a total order — rows with equal rank can come back in any physical order, so page 2 can repeat a row from page 1 or skip one. Add a stable tie-breaker:

SELECT id, title
FROM articles
WHERE search_vector @@ websearch_to_tsquery('english', :q)
ORDER BY ts_rank(search_vector, websearch_to_tsquery('english', :q)) DESC, id
LIMIT 20 OFFSET :offset;

The trailing , id costs nothing and makes pagination reproducible.

Sanitize ts_headline output. ts_headline returns snippets cut from the original document and wraps matches in <b>...</b> (or whatever you configure). If that document can contain user-authored markup, those fragments are an XSS vector the moment you render them as HTML. The fix is to escape the source text in a controlled layer, then add your highlight tags — don't hand raw ts_headline output straight to innerHTML. Treat search snippets with the same suspicion as any other user-generated content.

The takeaway: ranking without a tie-breaker breaks pagination, and ts_headline without escaping breaks security — both are cheap to fix and easy to forget.

Postgres FTS: operate-it checklist vs. migrate signal

Concern	Operate Postgres FTS	Signal to consider a dedicated engine
Write rate	Pending list drains; insert p95 stable under load	Pending list grows unbounded even with fastupdate=off
Index size	Grows then plateaus per data volume	GIN index outgrows RAM and every search hits disk
Relevance	Judged set passes; zero-result rate steady	You need typo tolerance, synonyms, per-user ranking that FTS can't express
Pagination	ORDER BY rank DESC, id is stable	—
Ops load	One extra index and autovacuum tuning	Cross-field faceting/aggregations become the primary workload

Bottom line

Postgres full-text search stays the right call far longer than most teams assume — but "one fewer service" is a promise you have to earn with load testing and a relevance contract, not an assumption you get for free. Before you either commit to it or migrate off it, run a sustained load test at your real write rate and watch the pending list, autovacuum, and transactional p95; stand up a judged query set so you can measure relevance regressions; and add the two-line fixes for stable pagination and safe headlines. Make the stay-or-migrate decision on that evidence. If those checks pass at your scale, you don't need Elasticsearch — and now you can prove it.

Related reading

• Add Full-Text Search to Your App Before Reaching for Elasticsearch
• GitHub Actions vs CircleCI vs Buildkite: The Real Cost of "Free" CI Minutes
• Vercel Pros and Cons: When It's the Right Host, and When You'll Regret It

Comments

[Alex Shev]

Relevance testing is the part teams often skip with full-text search. Latency can be load-tested, but quality needs query sets, expected result buckets, bad-query examples, and a way to compare changes before shipping a new ranking rule.

[Libme]

You nailed the part that bites teams most: latency has a clean number to chase, but relevance doesn't, so the "compare changes before shipping a ranking rule" step is where regressions sneak in without a fixed yardstick. One thing I'd add to your list is pinning the corpus snapshot when you run those comparisons — otherwise a score can move because content changed rather than because your ts_rank weights did, and you end up chasing ghosts. It also helps to grade relevance rather than treat it as pass/fail buckets; a metric like NDCG over a small golden set catches subtle reordering that binary "did the right doc show up" checks miss. Curious how you source your bad-query examples — do you mine them from production logs, or hand-build adversarial cases?

[Alex Shev]

Pinning the corpus snapshot is a great addition. Otherwise you are not comparing ranking rules, you are comparing two moving systems at once. I also like the idea of grading relevance before tuning weights, because it turns a subjective search complaint into a repeatable regression check.

[Libme]

Pinning the corpus snapshot really is the load-bearing point, and I'd add that the same discipline pays off on the query side too: keep a fixed, versioned set of judged queries alongside the frozen corpus, since a "regression" is meaningless if the test queries drift between runs. One thing worth folding in is capturing your ts_rank inputs explicitly in the graded set — normalization flag, weight labels (A/B/C/D), and whether you're ranking on ts_rank vs ts_rank_cd — because a silent change there moves scores without touching a single weight. That makes the regression check reproducible by anyone, not just the person who tuned it last. How are you thinking about grading scale, binary relevant/not or graded like NDCG? Curious whether you found the extra granularity worth the labeling cost.

[Alex Shev]

Exactly. I would treat the judged query set like a fixture, not a loose spreadsheet: query text, expected top results, acceptable alternates, rank metric, ts_rank inputs, and corpus snapshot id. That makes relevance changes reviewable the same way schema changes are reviewable, instead of being "search feels worse" after deploy.