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: 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 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. 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. 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 ... (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