Postgres Full-Text Search: tsvector, GIN, and Real Limits

2026-08-06

Postgres full-text search handles most application search workloads on its own — product catalogues, docs sites, ticket systems, CRM notes, anything roughly under ten million rows with normal concurrency — without a second datastore, a sync pipeline, or a JVM heap to babysit. It runs transactionally, in the same query as your joins and permission filters, because there's nothing to sync in the first place.

▶ Watch on YouTube: Full-text search: when Postgres replaces Elasticsearch

I've watched three different teams reach for Elasticsearch the moment someone says "we need search" in standup. What arrives with that decision is rarely priced up front: cluster health to alert on, storage models to reconcile, and a new line in the on-call rotation for a team that's already behind on autovacuum. This piece walks through the actual DDL, index sizing behaviour, real plan shapes, and the failure modes that show up six months after launch — plus exactly where Elasticsearch genuinely starts winning, so you can decide with evidence instead of reflex.

The primitives: tsvector, tsquery, and @@

tsvector is a Postgres data type holding a sorted list of distinct lexemes, optionally with integer positions. tsquery is the type representing a search expression over those lexemes. Everything else in Postgres full-text search is machinery around those two.

to_tsvector parses text into tokens, runs them through the dictionaries of a text search configuration, drops stop words, and reduces what's left to lexemes:

SELECT to_tsvector('english', 'The quick brown foxes are jumping quickly');

             to_tsvector
--------------------------------------
 'brown':3 'fox':4 'jump':6 'quick':2,7

Read that output carefully — it explains most of the behaviour you'll meet later. "The" and "are" are gone (English stop words), but their token slots aren't reused: "brown" keeps position 3, not 2. "foxes" became fox, "jumping" became jump, and both "quick" (position 2) and "quickly" (position 7) collapsed onto the single lexeme quick, which now carries two positions. Those position integers aren't decoration — ts_rank_cd and the phrase operators depend on them.

The match operator @@ returns true when a tsvector satisfies a tsquery. Both operands must come from the same text search configuration or you get silent wrong answers, not errors. A simple-config vector will never match an english-config query for "running," because one side stemmed and the other didn't. This mismatch is the most common "search is broken" bug report you'll get, and it never shows up in the logs — it just quietly fails to match.

Four ways to build a tsquery

| Constructor | Input handling | Errors on junk? | Use when | |---|---|---|---| | to_tsquery | Requires explicit operator syntax (fox & jump) | Yes | You build the expression programmatically | | plainto_tsquery | Free text, ANDs every term | No | Simple "all words must appear" search | | phraseto_tsquery | Free text, joins terms with <-> | No | Exact phrase matching | | websearch_to_tsquery | Google-style: quoted phrases, OR, leading - for negation | No, never errors | Anything typed by a human into a box |

websearch_to_tsquery arrived in PostgreSQL 11 and it's the right default for user-facing search. Users type apostrophes, unbalanced quotes, and stray ampersands. to_tsquery throws a 500 at them; websearch_to_tsquery doesn't.

Stop putting to_tsvector in the WHERE clause

This is the query everyone writes first:

SELECT id, title
FROM documents
WHERE to_tsvector('english', body) @@ plainto_tsquery('english', 'vacuum bloat');

It works, right up until the table is big enough to matter. There's no index on that expression, so Postgres sequentially scans the table and re-tokenises every row's body text on every query.

Use a generated column instead

Two correct fixes: an expression index matching that exact expression, or — on PostgreSQL 12+ — a stored generated column, which is what I'd default to:

ALTER TABLE documents
  ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body,  '')), 'B')
  ) STORED;

Three details that trip people up. coalesce is mandatory — a NULL anywhere in the concatenation makes the whole vector NULL. The two-argument form of to_tsvector with the configuration spelled out explicitly is required, because a generated column expression must be immutable, and the one-argument form depends on default_text_search_config, which is merely stable. And the column is materialised on disk, so it costs storage and gets rewritten on every UPDATE to title or body.

Before PostgreSQL 12 this was done with tsvector_update_trigger. You'll still find it in legacy schemas — no urgency to change it, but new work should use the generated column.

Weighting so titles beat body text

setweight labels every lexeme in a tsvector with A, B, C, or D — that's what the DDL above does, giving title lexemes A and body lexemes B.

Ranking functions take an optional weights array, given in the order {D, C, B, A}, defaulting to {0.1, 0.2, 0.4, 1.0}. Yes, the array is backwards relative to how you think about the labels. Get it wrong and your titles rank tenth.

ORDER BY ts_rank_cd('{0.05, 0.1, 0.3, 1.0}', d.search_vector, q) DESC

That array is where "it matches" becomes "it ranks sensibly," and it's the cheapest tuning knob you have.

GIN vs GiST: what the index really costs

CREATE INDEX CONCURRENTLY idx_documents_search
  ON documents USING GIN (search_vector);

CONCURRENTLY isn't optional on a live table — it costs two table scans and a longer build in exchange for not blocking writes.

GIN is an inverted index: each lexeme stored once, with a posting list of row locations — structurally the same idea Lucene uses, which is why the performance story is closer than the conference-talk consensus suggests. GiST for tsvector is lossy, produces false positives that must be rechecked against the heap row, builds faster, and stays smaller. Per the docs, GIN is slower to build, larger, and roughly three times faster to search. For read-heavy search, GIN is the default and GiST is the exception (volatile data, or composite indexes mixing tsvector with another type).

Here's the part tutorials leave out. GIN has a fastupdate mechanism: new entries land in an unsorted pending list and get merged into the main structure later. Inserts feel fast because of it. The merge fires when the pending list exceeds gin_pending_list_limit (default 4MB) or when autovacuum gets to it. Between merges, a query may have to scan that unsorted pending list, which is why bulk-loading a million rows and then running a search can produce one baffling slow query followed by normal ones. On a high-churn text column, if autovacuum isn't keeping up, the pending list keeps growing and both write and read latency degrade together. If your workload can't tolerate that variance, turn fastupdate off and accept slower inserts.

The first time I saw a GIN index larger than the table it indexed, I assumed bloat and scheduled a reindex. It wasn't bloat — long documents with high vocabulary diversity genuinely produce a lot of distinct lexemes, and the inverted structure has to store all of them. Measure it before you panic, then measure again after mass updates, because then it probably is bloat.

Config card: GIN build and maintenance

| Setting | Where | Why | |---|---|---| | maintenance_work_mem | SET for the build session, 1–4GB | GIN builds are strongly sensitive to it; materially shortens build time on large corpora | | gin_pending_list_limit | Per-index storage parameter or GUC, default 4MB | Caps pending-list scan cost paid by readers | | fastupdate | Per-index storage parameter, default on | Off gives predictable read latency, slower writes | | work_mem | Session or role for search endpoints | The Top-N sort for ORDER BY rank LIMIT n spills to disk without it |

Ranking: ts_rank vs ts_rank_cd and the normalization flags

ts_rank ranks on frequency of matching lexemes. ts_rank_cd computes a cover density ranking, using lexeme positions to reward documents where query terms appear close together, and it requires positions to be present in the vector. For most prose corpora, ts_rank_cd produces results humans agree with more often.

Both take a normalization bitmask, and the default of 0 means no normalization at all — which is how long documents end up winning everything:

| Value | Effect | |---|---| | 0 | No normalization (the default) | | 1 | Divide rank by 1 + log of document length | | 2 | Divide rank by document length | | 4 | Divide by mean harmonic distance between extents (ts_rank_cd only) | | 8 | Divide by number of unique words | | 16 | Divide by 1 + log of number of unique words | | 32 | Divide rank by itself + 1 (maps into 0..1) |

Combine values with |. Something like 32|1 is a reasonable starting point for mixed-length documents, but the right value depends entirely on your corpus — test it against a list of queries where you know what the top result should be.

One structural point: ranking functions aren't index-accelerated. Postgres has to fetch each candidate row and evaluate the rank. That's why a selective @@ predicate is what keeps search fast, and why "search for the letter a" is a load test.

Rank isn't relevance. Blend it:

ORDER BY ts_rank_cd(w, d.search_vector, q, 32|1) * 0.7
       + d.recency_score * 0.3 DESC

Hand-tuning those coefficients in SQL is exactly where you start missing a real relevance engine. Note the feeling when it happens.

ts_headline: the mistake almost everyone makes

ts_headline works on the original document text, not on the tsvector. It can't use an index and re-parses every document you hand it. The docs say plainly: apply it only to the rows you're displaying.

WITH ranked AS (
  SELECT d.id, d.title, d.body,
         ts_rank_cd(d.search_vector, q, 32|1) AS rank
  FROM documents d, websearch_to_tsquery('english', $1) q
  WHERE d.search_vector @@ q
  ORDER BY rank DESC
  LIMIT 10 OFFSET $2
)
SELECT r.id, r.title, r.rank,
       ts_headline('english', r.body,
                   websearch_to_tsquery('english', $1),
                   'StartSel=<mark>, StopSel=</mark>, MaxWords=30, MinWords=12,
                    ShortWord=3, HighlightAll=FALSE, MaxFragments=2,
                    FragmentDelimiter= ... ') AS snippet
FROM ranked r
ORDER BY r.rank DESC;

Rank and limit first, headline the ten survivors. Put ts_headline in the same SELECT list as the search predicate and Postgres may compute it for every matching row before the LIMIT applies. On a query matching 200,000 documents, that's 200,000 re-parses to display ten snippets. This is the single most common performance bug in Postgres search implementations, and pg_stat_statements will show it as one query with an enormous mean time and no obvious index problem.

Reading the query plan

Limit  (cost=... rows=10) (actual time=... rows=10)
  ->  Sort  (actual rows=10)
        Sort Method: top-N heapsort  Memory: 41kB
        ->  Bitmap Heap Scan on documents  (actual rows=4211)
              Recheck Cond: (search_vector @@ '''vacuum'' & ''bloat'''::tsquery)
              Heap Blocks: exact=3980
              Buffers: shared hit=3812 read=180
              ->  Bitmap Index Scan on idx_documents_search  (actual rows=4211)
                    Index Cond: (search_vector @@ ...)

Bitmap Index Scan on the GIN index, feeding a Bitmap Heap Scan, then a Top-N heapsort — that shape is healthy. (Numbers here are illustrative; yours depend on your corpus.)

Two smells to watch for. A Seq Scan means either the index is missing, or your query's text search configuration doesn't match the column's, so the operator isn't index-compatible — check pg_stat_user_indexes.idx_scan on the GIN index to confirm it's being hit at all. A huge actual rows count on the Bitmap Heap Scan relative to your LIMIT means you're fetching tens of thousands of rows to sort down to ten, and heap fetches are your real cost. Fix that with a more selective predicate, not a bigger machine.

One more thing: exact COUNT() over a large result set means visiting every matching row. Most search UIs are better served by an estimate, or a capped count (count() FILTER (...) LIMIT 1000) with a "load more" button. Past the first few pages, prefer keyset pagination over OFFSET, which gets linearly more expensive the deeper you page.

Typos, accents, and autocomplete with pg_trgm

Full-text search is not fuzzy search. Stemming is not spell correction. pg_trgm gives you trigram similarity (%, similarity()) with GIN or GiST support via gin_trgm_ops / gist_trgm_ops, and it's also how you make ILIKE '%term%' indexable. The usual pattern is FTS for recall and trigrams for "did you mean," run only when FTS returns nothing.

unaccent strips diacritics. To use it inside to_tsvector you have to build a custom text search configuration on the server, and unaccent() itself isn't immutable by default, which constrains its use in generated columns and index expressions. Plan for that before you promise accent-insensitive search to a French-language product team.

For prefix autocomplete you have three options: tsquery prefix matching with :*, a trigram index, or a dedicated materialised suggestions table. On anything with real traffic, the suggestions table wins.

pgvector means hybrid retrieval — keyword recall fused with embedding similarity — is now a realistic Postgres-only architecture rather than a reason to add a vector database.

Postgres vs Elasticsearch: where Elasticsearch actually wins

No hedging. These are real.

1. Analyzer sophistication. Postgres ships Snowball stemmers for a fixed language set. Lucene ships far richer analysis chains — CJK tokenisation, synonym graphs, edge n-grams. Worse, Postgres synonym, thesaurus, and ispell dictionaries are configured from files in the server's $SHAREDIR/tsearch_data, which you generally can't touch on managed cloud Postgres. If your product needs a maintained synonym list, that's a hard blocker on RDS. 2. Relevance engineering. Elasticsearch scores with BM25 by default and gives you per-field boosting, function scoring, rescoring, learning-to-rank. Postgres core gives you two rank functions, weight labels, and an ORDER BY. 3. Aggregations and faceting at speed over high-cardinality fields. Postgres can compute facets, but at large result-set sizes you're paying heap access for work Elasticsearch does in a columnar structure built for it. 4. Scale-out. Sharding a search corpus across nodes and fanning out read-heavy traffic is native there and manual here. 5. Index-time churn. Reindexing a hot corpus without degrading OLTP writes. In Postgres, every tsvector rewrite is an OLTP write. 6. Search-specific ecosystem. Percolators, suggesters, cross-cluster search — there's no Postgres equivalent.

The gap is narrowing. ParadeDB's pg_search extension brings a BM25 index type into Postgres and closes part of item 2, with the same managed-platform caveat: you have to be able to install it.

| Workload shape | Verdict | |---|---| | < 10M docs, one language, light faceting, small team | Postgres, comfortably | | 10M–100M docs, read-heavy, few writes | Postgres, with careful GIN and vacuum operations | | Multi-language incl. CJK, maintained synonyms | Elasticsearch | | Faceting over many high-cardinality fields | Elasticsearch | | Thousands of doc updates/sec on the searchable text | Elasticsearch | | Relevance is the product (marketplace, job board) | Elasticsearch, or pg_search if you can install it | | Search is a feature, not the product | Postgres |

Operating it: the checklist nobody writes down

Once search ships, these are the things that break.

GIN index size versus table size. Track it. It can legitimately exceed the table — what matters is the trend, not the ratio.

Bloat after mass updates. A backfill that rewrites every tsvector will inflate the GIN index.

Autovacuum on the search table. Every UPDATE to the indexed text produces a new row version, a dead tuple, and GIN index maintenance. Autovacuum also drains the pending list. Default per-table thresholds are usually too lax here.

work_mem sized so the Top-N sort stays in memory.

Statement timeout on search endpoints. A pathological query should die, not queue.

The reindex-after-config-change trap. Changing a text search configuration or upgrading a dictionary does not retroactively update stored tsvectors. Stored generated columns aren't recomputed on a config change — you have to force a rewrite of the column and rebuild the index. I've seen a team spend a week debugging "search stopped finding German words" after a dictionary upgrade, because the new config was live and every stored vector was still the old one.

pg_stat_user_indexes.idx_scan to confirm the index is used at all, and pg_stat_statements to catch the ts_headline-on-everything pattern before a user reports it.

Most of that is exactly what a continuous health check should be watching for you rather than the week after an incident — which is the gap tools like MyDBA fill, flagging index bloat, unused indexes, and autovacuum coverage before they become a search outage.

The verdict

Start in Postgres. Build the generated column, the GIN index, the ranked query with headlines applied only to the final page. Instrument it: index size, plan shape, p99 on the search endpoint. Then let a measured limit push you to a dedicated engine, rather than a conference talk you half-remember.

The migration runs easily in that direction. Your tsvector work becomes a well-understood corpus definition and your ranking experiments become a BM25 baseline. Going the other way — ripping out a search cluster after discovering you never needed it — means unwinding a sync pipeline that half your application now depends on. Choose the order that leaves you the cheaper mistake.