Postgres Full-Text Search: The Setup You Should Ship

Postgres full-text search works out of the box for most apps, and you probably don't need Elasticsearch to get good results. I've built app search on Postgres four times and torn it out for Elasticsearch exactly once. That one time was justified — the product grew a facet-heavy search UI that Postgres was never meant to serve. That distinction matters more than row count, and it's the whole thesis of this article. The other three times, moving would have cost a team a cluster, a sync pipeline, and a permanent class of "the search index is stale" bugs.

📖 Read the full guide: Postgres Full-Text Search: tsvector, GIN, and Real Limits

The companion video walks the mental model at a whiteboard pace: watch it here. This article is the copy-paste version with the edge cases that bite in production.

TL;DR

• Store a tsvector in a stored generated column, index it with GIN. That's the default shape.
• Parse user input with websearch_to_tsquery. I default to it and have never regretted it.
• Order with ts_rank_cd, blended with recency or popularity. Raw rank alone is a weak signal.
• Snippet with ts_headline, applied only to the page you're about to render.
• Typos and accents are pg_trgm plus unaccent, not core FTS.

If you're under roughly ten million documents in one language, you probably don't need Elasticsearch. Treat that number as a smell test, not a law — I've seen twenty million rows behave fine on decent hardware, and I've seen three million rows struggle because of pathological ts_rank_cd over huge documents.

The primitives, in 90 seconds

tsvector is a sorted list of distinct normalized lexemes, optionally carrying positions and weight labels. tsquery holds search terms joined by &, |, !, and the phrase operator <->. The @@ operator asks whether one matches the other.

SELECT to_tsvector('english', 'The quick brown foxes jumped over the lazy dogs');

                        to_tsvector
------------------------------------------------------------
 'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2
(1 row)

Note what happened. the and over vanished as stop words. foxes became fox, jumped became jump, lazy became lazi. The integers are token positions in the original stream, which is why position 1 is missing.

That stemming is why a search for runs finds a document containing running. It's dictionary normalization at index time, not a LIKE '%run%' scan pretending to be search. And those position integers aren't decoration — they're what makes phrase search and ts_rank_cd possible.

tsquery syntax: & | ! <-> and prefixes

SELECT to_tsquery('english', 'index & !gist'),
       to_tsquery('english', 'bitmap <-> heap'),
       to_tsquery('english', 'index <2> scan'),
       to_tsquery('english', 'run:*');

`means "N tokens apart."run:*is a prefix match. All useful. All dangerous if you wire them to a text box, becauseto_tsquery` demands syntactically valid input and throws a syntax error on anything else:

`sql
SELECT to_tsquery('english', 'fast "index scan" or bitmap -gist');
ERROR: syntax error in tsquery: "fast "index scan" or bitmap -gist"
`

Here's the same input through the three safe parsers:

`sql
SELECT plainto_tsquery('english', 'fast "index scan" or bitmap -gist') AS plain,
phraseto_tsquery('english', 'fast "index scan" or bitmap -gist') AS phrase,
websearch_to_tsquery('english','fast "index scan" or bitmap -gist') AS websearch \gx
`

`
-[ RECORD 1 ]------------------------------------------------------
plain | 'fast' & 'index' & 'scan' & 'bitmap' & 'gist'
phrase | 'fast' <-> 'index' <-> 'scan' <2> 'bitmap' <-> 'gist'
websearch | 'fast' & 'index' <-> 'scan' | 'bitmap' & !'gist'
`

plainto_tsquery ANDs everything. phraseto_tsquery forces one rigid phrase (the <2> is the stop word or leaving a positional hole). websearch_to_tsquery honors quotes, or, and a leading minus — exactly what users already expect from a search box. It landed in PostgreSQL 11. Use it.

Making it fast: generated column + GIN index

`sql
CREATE TABLE article (
id bigserial PRIMARY KEY,
title text NOT NULL,
body text NOT NULL,
published_at timestamptz NOT NULL DEFAULT now(),
search_vec tsvector GENERATED ALWAYS AS (
setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
setweight(to_tsvector('english', coalesce(body, '')), 'B')
) STORED
);

CREATE INDEX article_search_vec_gin ON article USING GIN (search_vec);
`

Read that generated expression carefully, because this is the most common mistake I see: you must pass the configuration as a literal. The one-argument to_tsvector(text) is only STABLE, since it reads the default_text_search_config GUC. Generated columns and expression indexes require IMMUTABLE. Write to_tsvector('english', body) or Postgres rejects the DDL.

setweight labels every lexeme A/B/C/D. Title gets A, body gets B, and the ranking functions weight them {D, C, B, A} = {0.1, 0.2, 0.4, 1.0} by default — a title hit ends up outweighing a body hit by 2.5x in ts_rank_cd. Also note coalesce: a NULL anywhere in the expression nulls the whole vector, and NULL never matches @@.

Stored generated columns arrived in PG12. On PG11, or when you need per-row language (a lang column feeding a regconfig), you still need the old BEFORE INSERT OR UPDATE trigger calling tsvector_update_trigger. It works fine. It's just more moving parts.

GIN is the right index type here. It builds around three times slower than GiST and it's bigger, but it searches faster and suits read-heavy data — which is what a search table is.

Proving the GIN index is used

`sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title
FROM article
WHERE search_vec @@ websearch_to_tsquery('english', 'bitmap heap scan');
`

`
Bitmap Heap Scan on article (cost=44.29..1893.55 rows=489 width=42)
(actual time=0.612..3.887 rows=471 loops=1)
Recheck Cond: (search_vec @@ websearch_to_tsquery('english'::text, 'bitmap heap scan'::text))
Heap Blocks: exact=433
Buffers: shared hit=449
-> Bitmap Index Scan on article_search_vec_gin (cost=0.00..44.17 rows=489 width=0)
(actual time=0.531..0.531 rows=471 loops=1)
Index Cond: (search_vec @@ websearch_to_tsquery('english'::text, 'bitmap heap scan'::text))
Buffers: shared hit=16
Planning Time: 0.214 ms
Execution Time: 4.019 ms
`

A GIN scan always produces a bitmap, so you get Bitmap Index Scan feeding Bitmap Heap Scan. The Recheck Cond line isn't a failure. GIN hands back candidate rows and the heap confirms them; if the bitmap goes lossy it degrades to page granularity and rechecks every tuple on those pages. Skip the GIN index entirely and you get a Seq Scan that scales linearly with table size and gets ugly fast.

One gotcha for freshly loaded tables: GIN's fastupdate buffers new entries in a pending list sized by gin_pending_list_limit. Until vacuum or a limit overflow flushes it, scans have to read that unsorted list and timings look erratic. Run VACUUM ANALYZE after a bulk load before you benchmark anything.

Ranking with ts_rank and ts_rank_cd

`sql
SELECT id, title,
ts_rank_cd('{0.1, 0.2, 0.4, 1.0}', search_vec, q, 32) AS rank
FROM article, websearch_to_tsquery('english', '"index scan" or bitmap') q
WHERE search_vec @@ q
ORDER BY rank DESC, published_at DESC
LIMIT 10;
`

ts_rank scores on term frequency. ts_rank_cd does cover density, which accounts for how close the matched lexemes sit, and therefore needs the positional data in your vector. I use ts_rank_cd for prose and ts_rank for short catalog fields.

That trailing 32 is the normalization bitmask: rank/(rank+1), squashing scores into 0..1 so I can blend them. 1 divides by 1 + log(length), 2 by raw length. Pick a normalization on purpose or long documents win everything.

Raw rank is a weak relevance signal on its own. Real ordering looks more like ORDER BY (0.7 * rank + 0.3 * popularity_score) DESC.

The cost you must know: ranking touches the tsvector of every matching row, so ORDER BY rank LIMIT 10 on a query matching 400k rows ranks 400k rows before truncating. Narrow the match set first with filters, or look at the RUM extension, which stores positions inside the index and can return ranked results without sorting the whole match set.

Highlighting with ts_headline

`sql
WITH hits AS (
SELECT id, title, body,
ts_rank_cd(search_vec, q, 32) AS rank
FROM article, websearch_to_tsquery('english', 'bitmap heap scan') q
WHERE search_vec @@ q
ORDER BY rank DESC
LIMIT 10
)
SELECT id, title, rank,
ts_headline('english', body,
websearch_to_tsquery('english', 'bitmap heap scan'),
'StartSel=, StopSel=, MaxWords=35, MinWords=15,
MaxFragments=2, FragmentDelimiter= … ') AS snippet
FROM hits;
`

The CTE exists for one reason. ts_headline re-parses the original document text and cannot use the index at all. The docs say plainly to use it only on the rows you're displaying. Apply it to an unbounded match set and you'll parse a million bodies to render ten.

Typos, autocomplete, and pg_trgm fuzzy search

Core full-text search has no fuzzy matching inside tsquery. None. If a user types "fxo" instead of "fox," websearch_to_tsquery won't save you. That's pg_trgm's job:

`sql
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX article_title_trgm ON article USING GIN (title gin_trgm_ops);

SELECT title, similarity(title, 'postgers vaccum') AS sim
FROM article
WHERE title % 'postgers vaccum'
ORDER BY sim DESC LIMIT 5;
`

Trigrams work on raw text independently of your text search config, which makes them a clean fallback: run FTS, and if it returns nothing, retry with %.

For accents, unaccent strips diacritics, but unaccent(text) is STABLE because it depends on the default dictionary. To index it you need a wrapper:

`sql
CREATE FUNCTION f_unaccent(text) RETURNS text
LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS
$$ SELECT public.unaccent('public.unaccent', $1) $$;
`

Autocomplete: prefix tsquery ('foo:*') is cheaper for a single leading token; trigram similarity handles mid-word and misspelled prefixes, since it doesn't care about word boundaries the way FTS prefix matching does.

Postgres vs Elasticsearch: the honest line

Dimension	Postgres FTS	Elasticsearch / OpenSearch
Relevance tuning	setweight A–D, normalization bitmask, hand-blended SQL	BM25 by default, per-field boosts, iterated by non-DBAs
Typo tolerance	pg_trgm bolted alongside, not inside tsquery	Fuzzy queries built in
Facets / aggregations	GROUP BY over the match set, fine until it isn't	Aggregations designed for it
Scale	Single-digit millions is comfortable	Horizontal sharding across nodes
Ops burden	Zero new infrastructure	A cluster, a sync pipeline, its own on-call
Consistency with source data	Same transaction, always current	Eventually consistent by construction

The breaking point is feature demand, not row count. Dozens of language analyzers, index-time A/B testing of analyzers, search-as-you-type at massive scale, product managers who want to tune boosts weekly — that's when you move.

Middle ground before you run two datastores: ParadeDB's pg_search embeds a Tantivy BM25 index inside Postgres, ZomboDB bridges to Elasticsearch from SQL, and logical decoding into OpenSearch is the well-trodden replication path.

Gotchas nobody mentions until 2am

• GIN bloat. REINDEX INDEX CONCURRENTLY article_search_vec_gin; (PG12+) rebuilds without blocking writes. Schedule it proactively on high-churn tables rather than waiting for symptoms.
• Size and WAL. A stored tsvector roughly doubles a text-heavy table and is replicated like any other column. Expect more disk and more replication lag under load; your standbys will notice.
• Config changes are migrations. Switching 'english' to 'simple' means regenerating every vector and rebuilding the index. There's no in-place migration.
• maintenance_work_mem dominates GIN build time. Raise it in the session doing the build or a multi-million-row build will crawl.
• 1 MB limit on a tsvector, with 16383 as the maximum position. Truncate huge documents deliberately.
• default_text_search_config is a session setting. Which is precisely why your generated column hardcodes 'english'.

The copy-paste starter

`sql
CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE TABLE article (
id bigserial PRIMARY KEY,
title text NOT NULL,
body text NOT NULL,
published_at timestamptz NOT NULL DEFAULT now(),
search_vec tsvector GENERATED ALWAYS AS (
setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
setweight(to_tsvector('english', coalesce(body, '')), 'B')
) STORED
);

CREATE INDEX article_search_vec_gin ON article USING GIN (search_vec);
CREATE INDEX article_title_trgm ON article USING GIN (title gin_trgm_ops);

INSERT INTO article (title, body) VALUES
('Bitmap heap scans explained', 'A GIN index scan builds a bitmap, then the heap rechecks each candidate row.'),
('Tuning maintenance_work_mem', 'Raising maintenance_work_mem substantially reduces GIN index build time.'),
('Phrase search with tsquery', 'Positional information enables the <-> operator and cover density ranking.');

VACUUM ANALYZE article;

WITH q AS (SELECT websearch_to_tsquery('english', 'gin "index scan"') AS tsq),
hits AS (
SELECT a.id, a.title, a.body,
ts_rank_cd(a.search_vec, q.tsq, 32) AS rank
FROM article a, q
WHERE a.search_vec @@ q.tsq
ORDER BY rank DESC, a.published_at DESC
LIMIT 10
)
SELECT h.id, h.title, round(h.rank::numeric, 4) AS rank,
ts_headline('english', h.body, q.tsq,
'StartSel=, StopSel=, MaxWords=30, MinWords=10') AS snippet
FROM hits h, q;
`

Then run EXPLAIN (ANALYZE, BUFFERS) on the inner query and confirm you see Bitmap Index Scan on article_search_vec_gin. If you see a Seq Scan, your tsquery config literal doesn't match the column's, or the table is too small for the planner to care.

That's the whole thing. Ship it, watch p95, and revisit when a product requirement — not a row count — forces your hand.

---

Tags: postgres, database, sql, performance ## Keep an eye on it after you ship

The starter above gets you a correct, fast implementation on day one, but full-text search tends to degrade quietly. GIN indexes bloat under heavy update churn, search_vec recalculates on every write to title or body, and nobody notices until a dashboard shows p95 creeping from 4ms to 400ms over a few months. Whatever you use to watch autovacuum lag, index bloat, and slow queries on the rest of your database should be watching this table too — it's not a special case, it's just another write-heavy index that needs the same attention as your primary keys. I run MyDBA alongside the apps I build this way specifically because bloat and autovacuum drift are the two things that silently wreck FTS performance, and catching them before they show up in query latency is a lot cheaper than debugging a "why did search get slow" ticket three months from now.

Ship the boring version first

Postgres full-text search isn't a compromise you make until you can afford Elasticsearch — for the overwhelming majority of apps, it's the correct final answer. You get relevance ranking, phrase search, typo tolerance via pg_trgm, and results that are always transactionally consistent with the row you just wrote, all without a second datastore, a sync pipeline, or a new class of on-call pages. Reach for something heavier only when a real product requirement demands it: deep facets, dozens of language analyzers, or search-as-you-type at a scale Postgres genuinely can't hold. Until then, the setup in this article is the version worth shipping.

pgdba Editorial builds MyDBA, a Postgres monitoring and health-check tool — https://mydba.dev/?utm_source=devto&amp;utm_medium=platform&amp;utm_campaign=postgres-full-text-search-guide

If you're running Postgres in production, it's worth five minutes to point MyDBA at it and see what your indexes and autovacuum settings are actually doing.