Postgres Full-Text Search vs Elasticsearch: A Real Comparison

A team I worked with added Elasticsearch for a search box over a documents table with roughly 400,000 rows. Six weeks later they owned a CDC pipeline, a nightly reindex job that nobody could run in business hours, a queue that backed up whenever someone bulk-edited a category, and a support ticket class I still think about: search results linking to documents that had been deleted eleven minutes earlier. The search box worked. Everything around it leaked.

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

We ripped it out. The replacement was a generated tsvector column, one GIN index, and about ninety lines of SQL. Median latency went from 40ms (network hop included) to 9ms. The reindex job, the CDC pipeline, and the deleted-row bug all stopped existing, because there was nothing to keep in sync.

I have also made the opposite mistake — more on that later, because articles in this genre almost always turn into Postgres cheerleading, and that's how people end up shipping the wrong thing.

If you want the visual version, I recorded a 12-minute walkthrough of this stack: https://www.youtube.com/watch?v=AtVpxSG80Ko. This article goes further: exact DDL, real EXPLAIN output, the tuning knobs, and the specific failure modes that push you off Postgres.

Postgres tsvector and tsquery, precisely

tsvector is a Postgres data type that stores a sorted list of distinct normalized lexemes, optionally with integer positions and weight labels. tsquery represents a search expression over lexemes. That's the whole model.

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

'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2

Three things happened. the and over are gone, dropped by the English stop-word list. foxes became fox and jumped became jump via the Snowball stemmer, which is also why running and runs both collapse to run. And every surviving lexeme carries its position in the original token stream, which is what makes phrase search and cover-density ranking possible.

The @@ operator tests a tsvector against a tsquery and returns boolean:

SELECT to_tsvector('english', 'The quick brown foxes jumped')
       @@ to_tsquery('english', 'fox & jump');  -- true

Now the limits, because they matter and almost nobody reads them. Positions must be greater than 0 and no more than 16,383; anything past that is silently discarded, and duplicate positions for the same lexeme collapse. Each lexeme is capped at 2,047 bytes. The whole tsvector value is capped at 1,048,575 bytes.

For a product description or a support article, those numbers are irrelevant. For book-length documents they are not. Once a document runs past roughly 16,000 tokens, the tail of it has no positional data, so ts_rank_cd scores the back half of your document as if the matches were nowhere. I found this out on a legal archive where relevance was quietly garbage for the longest filings and fine for everything else. Chunk long documents into sections and index the chunks if positions matter to you.

Four ways to build a tsquery — only one belongs in a search box

Function	Input style	Behavior	Errors on bad input?
to_tsquery	Raw operator syntax (cat & !dog)	Full control over &, `	, !, <->, :*`
plainto_tsquery	Plain words	ANDs every term together	No
phraseto_tsquery	Plain words	Joins terms with <-> (adjacency)	No
websearch_to_tsquery	Google-ish ("exact phrase" or thing -excluded)	Quoted phrases, or, leading - for negation	No, never raises a syntax error

websearch_to_tsquery arrived in PostgreSQL 11, and it's the right default for anything a human types into a box. Users paste text with unbalanced quotes, stray ampersands, and emoji. to_tsquery on raw user input is a 500-error generator; I've seen it take down a search endpoint because someone searched for C&A.

SELECT websearch_to_tsquery('english', '"connection pool" timeout -pgbouncer');

'connect' <-> 'pool' & 'timeout' & !'pgbouncer'

Reserve to_tsquery for queries your application constructs itself, such as prefix search for type-ahead.

The operators you'll actually use

Against a document indexed from "The quick brown foxes jumped over the lazy dogs":

• fox & dog — both present. Matches.
• fox | cat — either. Matches.
• fox & !cat — first yes, second no. Matches.
• quick <-> brown — adjacent, in that order. Matches (positions 2 and 3).
• quick <2> fox — exactly two positions apart. Matches (positions 2 and 4).
• fo:* — prefix match. Matches fox.

Prefix search is what powers type-ahead:

SELECT id, title
FROM docs
WHERE search_vec @@ to_tsquery('english', 'connec:* & pool:*')
LIMIT 10;

Phrase operators have a cost. A GIN index over tsvector stores lexemes but not their positions or weight labels, so <-> and <N> queries always require a heap recheck to confirm adjacency. The index narrows candidates; the table proves them. Same story for any query that filters on weights.

Postgres search generated column plus GIN index

Here's the full DDL for a help-center schema. This runs as written.

CREATE TABLE docs (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id   int         NOT NULL,
  locale       text        NOT NULL DEFAULT 'en',
  title        text        NOT NULL,
  summary      text        NOT NULL DEFAULT '',
  body         text        NOT NULL DEFAULT '',
  published_at timestamptz,
  deleted_at   timestamptz,
  search_vec   tsvector GENERATED ALWAYS AS (
      setweight(to_tsvector('english', coalesce(title,   '')), 'A') ||
      setweight(to_tsvector('english', coalesce(summary, '')), 'B') ||
      setweight(to_tsvector('english', coalesce(body,    '')), 'C')
  ) STORED
);

Then the GIN index, outside any transaction block:

CREATE INDEX CONCURRENTLY docs_search_vec_gin ON docs USING gin (search_vec);

Note the explicit 'english' in every to_tsvector call. This is mandatory, not stylistic. The two-argument to_tsvector(regconfig, text) is IMMUTABLE. The one-argument to_tsvector(text) is only STABLE, because it reads the default_text_search_config GUC, which any session can change. Generated columns and expression indexes require IMMUTABLE expressions, so Postgres will reject the one-argument form outright. If it ever let you through, a session-level GUC change would silently corrupt your index. This is also the single most common reason people hit "functions in index expression must be marked IMMUTABLE" the first time they try this.

Generated columns landed in PostgreSQL 12. Before that, you maintained the column with a trigger:

ALTER TABLE docs ADD COLUMN search_vec tsvector;

CREATE TRIGGER docs_search_vec_upd
  BEFORE INSERT OR UPDATE ON docs
  FOR EACH ROW EXECUTE FUNCTION
    tsvector_update_trigger(search_vec, 'pg_catalog.english',
                            title, summary, body);

That works, but tsvector_update_trigger() concatenates the listed columns with no weights at all. Every lexeme comes out as weight D. If you want title matches to outrank body matches, you have to hand-write the trigger function, and then you own it forever. The generated column is declarative, visible in \d, impossible to forget on a new insert path, and cannot drift from the source columns. Use it if you're on 12 or later; treat the trigger as legacy-only.

If you can't add a column, an expression index is the fallback:

CREATE INDEX CONCURRENTLY docs_body_fts
  ON docs USING gin (to_tsvector('english', body));

The trap: this index is only used when the query contains the identical expression, same configuration literal included. Write to_tsvector('simple', body) in the query, or omit the config argument, and the planner will ignore the index and seq-scan. I've watched people spend an afternoon on that one. I generally prefer the stored generated column for exactly this reason — the tsvector already exists as a real column, so any query against it is guaranteed to match.

CREATE INDEX CONCURRENTLY avoids the lock that blocks writes, at the cost of two table scans and the risk of leaving an INVALID index behind if it fails. Check pg_index.indisvalid afterward; drop and rebuild if it's false.

Now the plan, on a table with about 1.2 million rows:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title
FROM docs
WHERE search_vec @@ websearch_to_tsquery('english', 'connection pool timeout')
  AND deleted_at IS NULL
LIMIT 20;

 Limit  (cost=88.41..162.90 rows=20 width=48)
        (actual time=1.902..2.144 rows=20 loops=1)
   Buffers: shared hit=61 read=12
   ->  Bitmap Heap Scan on docs  (cost=88.41..3421.77 rows=894 width=48)
                                 (actual time=1.899..2.138 rows=20 loops=1)
         Recheck Cond: (search_vec @@ '''connect'' & ''pool'' & ''timeout'''::tsquery)
         Filter: (deleted_at IS NULL)
         Heap Blocks: exact=19
         Buffers: shared hit=61 read=12
         ->  Bitmap Index Scan on docs_search_vec_gin
                 (cost=0.00..88.19 rows=903 width=0)
                 (actual time=1.421..1.421 rows=907 loops=1)
               Index Cond: (search_vec @@ '''connect'' & ''pool'' & ''timeout'''::tsquery)
               Buffers: shared hit=42 read=12
 Planning Time: 0.214 ms
 Execution Time: 2.209 ms

Bitmap Index Scan finds 907 candidate rows, the Limit stops after touching 19 heap blocks. That Recheck Cond is normal and, for standard match queries, cheap — it exists partly because weight labels aren't stored in the GIN entries themselves. That's the shape you want.

GIN vs GiST vs RUM

	GIN	GiST	RUM (external)
Lookup speed	Baseline	~3x slower than GIN	Comparable to GIN, faster for ranked queries
Build time	~3x longer than GiST	Fastest	Slower than GIN
Update cost	Moderately slower than GiST	Faster	Slowest
Index size	2–3x larger than GiST	Smallest	Largest (stores positions and rank)
Lossy?	Not lossy for standard queries	Lossy, false matches need heap verification	Not lossy
Notes	Performance depends logarithmically on unique lexeme count	Good for small/volatile corpora	Rank in the index enables cheap ORDER BY relevance

Those GIN-vs-GiST multipliers are the project's own rule of thumb, and they've matched what I've measured closely enough that I stopped re-measuring. In practice: use GIN. GiST earns its place when the corpus is small, write-heavy, and you care more about index size than lookup latency. That's a narrow window.

GIN has a fast-update mechanism that buffers new entries into an unordered pending list rather than merging them into the main structure on every insert. gin_pending_list_limit defaults to 4MB and controls when that list gets flushed. The trade-off is real: with fastupdate=on, bulk inserts are much cheaper, but reads have to scan the pending list linearly on top of the index. If you bulk-load 500,000 rows and then wonder why search got slow, that's why. Either run VACUUM on the table to force the merge, or turn fastupdate off on a read-heavy table:

ALTER INDEX docs_search_vec_gin SET (fastupdate = off);

RUM is an external index access method from Postgres Professional that stores lexeme positions and rank information inside the index. That means ordering by relevance or phrase distance without pulling every matching row's tsvector off the heap and sorting it. It's the single biggest lever on the ranking problem I'm about to describe, and the reason not to use it is usually that your managed platform won't install it.

ts_rank ranking in Postgres: the performance truth nobody puts in the tutorial

setweight() labels lexemes A, B, C, or D. D is the default. ts_rank's default weight array is {0.1, 0.2, 0.4, 1.0}, mapping to D, C, B, A in that order. So a title match (A) is worth 10x a body-tail match (D) out of the box. Override it:

SELECT ts_rank('{0.05, 0.2, 0.6, 1.0}'::float4[], search_vec, q) AS rank
FROM docs, websearch_to_tsquery('english', 'connection pool') q
WHERE search_vec @@ q;

ts_rank_cd implements cover density ranking, which rewards query terms appearing close together. It needs positional information and returns 0 for a tsvector that's been stripped of positions. Combine that with the 16,383 position cap and you can see why ts_rank_cd degrades on very long documents.

Both functions take an integer normalization bitmask: 1 divides by 1 + log(length), 2 divides by length, 32 computes rank/(rank+1). Default is 0, meaning no length normalization at all, so long documents naturally score higher because they contain more matches. Pick 32 if you want scores bounded to [0,1) for display purposes. For a mixed corpus of one-paragraph FAQs and 30-page guides, I usually start at 1 and adjust from there.

Here's the part that decides whether Postgres works for you. Ranking functions must access the tsvector of every candidate row. The docs say so plainly. There is no rank stored in the GIN index, so this:

SELECT id, title, ts_rank(search_vec, q) AS rank
FROM docs, websearch_to_tsquery('english', 'error') q
WHERE search_vec @@ q
ORDER BY rank DESC
LIMIT 10;

is a bitmap heap scan over every matching row, a ts_rank call per row, and a full sort. If error matches 200,000 documents, you fetch and score 200,000 documents to return 10. The LIMIT saves you nothing. I've seen this exact query take 4 seconds on a table where the unranked version took 8ms.

Two mitigations. First, pre-filter hard on relational predicates so the candidate set is small before ranking: product, locale, date window, tenant. Postgres is genuinely good at this, and it's the thing a separate search engine makes awkward. Second, move ranking into the index with RUM or pg_search.

ts_headline highlighting without wrecking your latency

ts_headline works on the original document text, not the index. It re-parses the document every time. The docs recommend applying it only to rows you're actually displaying, and they mean it.

Bad:

SELECT id, title,
       ts_headline('english', body, q,
                   'StartSel=<mark>, StopSel=</mark>, MaxFragments=2') AS snippet,
       ts_rank(search_vec, q) AS rank
FROM docs, websearch_to_tsquery('english', 'connection pool') q
WHERE search_vec @@ q
ORDER BY rank DESC
LIMIT 10;

That calls ts_headline on every match, then throws away all but ten. Good:

WITH hits AS (
  SELECT id, title, body, ts_rank(search_vec, q) AS rank
  FROM docs, websearch_to_tsquery('english', 'connection pool') q
  WHERE search_vec @@ q
    AND deleted_at IS NULL
  ORDER BY rank DESC
  LIMIT 10
)
SELECT h.id, h.title, h.rank,
       ts_headline('english', h.body,
                   websearch_to_tsquery('english', 'connection pool'),
                   'StartSel=<mark>, StopSel=</mark>, '
                   'MaxWords=35, MinWords=15, ShortWord=3, '
                   'MaxFragments=2, FragmentDelimiter=" … "') AS snippet
FROM hits h;

Ten ts_headline calls instead of 200,000. On one catalog search this change alone took p95 from 1.9s to 130ms. This one change has fixed more "search feels slow" tickets for me than any index tuning.

pg_trgm fuzzy search: accents, typos, and iPhone12 vs iPhone 12

Accents first. The unaccent contrib dictionary strips accents from lexemes and is normally chained ahead of the stemmer:

CREATE EXTENSION IF NOT EXISTS unaccent;

CREATE TEXT SEARCH CONFIGURATION en_unaccent ( COPY = english );

ALTER TEXT SEARCH CONFIGURATION en_unaccent
  ALTER MAPPING FOR hword, hword_part, word
  WITH unaccent, english_stem;

Now café and cafe produce the same lexeme. Remember to use 'en_unaccent' in your generated column, and rebuild the column and index if you change the configuration after loading data.

Typos are a different problem, and full-text search doesn't solve them. The tokenizer splits iPhone 12 into two lexemes and iPhone12 into one; stemming won't bridge that. The tool is pg_trgm:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX CONCURRENTLY docs_title_trgm
  ON docs USING gin (title gin_trgm_ops);

SELECT id, title, similarity(title, 'postgrez conection')
FROM docs
WHERE title % 'postgrez conection'
ORDER BY similarity(title, 'postgrez conection') DESC
LIMIT 10;

The pattern I use in production: full-text search as the primary branch, and if it returns zero rows, fall back to a trigram similarity query on title and summary only. Two round trips in the miss case, near-zero cost in the hit case. Don't run both branches unconditionally and merge; trigram scans over a body column are expensive and you will feel it.

Synonyms and the managed-hosting wall

This is the underrated reason teams end up on a dedicated engine, and it has nothing to do with performance.

Synonym, thesaurus, and Ispell dictionaries are configured from files placed in $SHAREDIR/tsearch_data on the database server's filesystem. Not a table. Not a GUC. Files, on the server. Most managed Postgres platforms don't give you filesystem access, which means you can't ship a synonym list, can't update it, and can't let the content team own it. I've had this exact conversation with a client who wanted "sneakers" to match "trainers" and discovered their managed host had no path to add it server-side.

The workaround is query-side expansion in the application. Keep a synonyms table, expand the parsed query before it hits the database:

CREATE TABLE search_synonyms (
  term     text PRIMARY KEY,
  expands  text[] NOT NULL
);
INSERT INTO search_synonyms VALUES
  ('postgres', ARRAY['postgres','postgresql','pg']),
  ('k8s',      ARRAY['k8s','kubernetes']);

Build ('postgres' | 'postgresql' | 'pg') & 'timeout' in code and hand it to to_tsquery. It's less elegant than a server-side thesaurus dictionary, but it deploys with your application code, not a database file, which on a managed platform is the difference between "works" and "not possible." It's also a moving part you now own, and it doesn't handle multi-word synonyms cleanly. If your merchandising team changes synonyms weekly, this is a genuine argument for a dedicated engine.

Postgres full text search vs Elasticsearch: where Elasticsearch actually wins

Being honest here matters more than the rest of the article.

• BM25 relevance. Core Postgres ranking is not BM25. ts_rank and ts_rank_cd are weight- and cover-density-based scores with no corpus-wide inverse document frequency term. A term appearing in 90% of your documents contributes as much as a rare one. For a product catalog where "shirt" is in every title, that's a real quality gap.
• Faceted aggregations at scale. Counting matches per brand, per price bucket, per category, over millions of documents, in the same request. Postgres can do it; it won't do it in 50ms.
• Per-field analyzers and language detection. One Postgres tsvector column has one configuration. Multi-language corpora mean multiple columns or multiple indexes.
• Hot-reloadable synonym sets without touching the database filesystem or a deploy.
• Independent scale-out. Search replicas sized for search, not for OLTP, without competing for your primary's buffer cache.
• High-concurrency ranked top-N over tens of millions of documents. This is the ranking problem above, at a scale where pre-filtering doesn't save you and even RUM's approach starts to strain.
• Log and observability workloads. Not a contest.

The middle path worth knowing: ParadeDB's pg_search is an open-source Postgres extension that embeds a Tantivy-based BM25 index inside Postgres. You get BM25 scoring that core Postgres doesn't implement, without a second datastore or a sync pipeline. If your only blocker is relevance quality, look there before you look at a cluster.

The one time I got this badly wrong: a document archive where the customer wanted faceted counts across eight dimensions over 40 million documents, with sub-second response. I argued for Postgres. I was wrong by roughly an order of magnitude and we moved to a dedicated engine four months in. The lesson wasn't "Postgres is weak." The lesson was that I evaluated the search box and ignored the facet panel next to it.

A decision checklist I actually use

These thresholds are mine, from my own deployments. They're not laws.

1. Under ~5 million documents with a stored tsvector and GIN? Postgres. I've run 8 million comfortably on decent hardware; past that I start measuring seriously instead of assuming.
2. Do you need ranked top-N where a single common query matches more than ~100,000 rows? That pushes toward RUM, pg_search, or a dedicated engine. Plain ts_rank will disappoint you.
3. Are facet counts a first-class part of the UI? More than three or four facet dimensions over a large corpus, go dedicated.
4. How often do synonyms change? Monthly is fine with query-side expansion. Weekly, driven by non-engineers, is a real argument for Elasticsearch.
5. More than two languages in one corpus? Postgres gets awkward fast. Two is manageable with separate columns.
6. Do you have an engineer who will own a search cluster's upgrades, capacity, and failure modes? If the honest answer is no, don't add one. An unowned Elasticsearch cluster is worse than a mediocre Postgres search box.

The tiebreaker, when the answers are genuinely split: dual-write consistency. With a tsvector column, an update to the document and an update to the search index happen in the same transaction, with the same visibility rules and the same rollback. Search filters join your relational data directly, so "only documents this user can see, in this product, published before today" is a WHERE clause instead of a denormalized field that has to be reindexed whenever permissions change. A search result that references a deleted row, or that's missing a document created ten seconds ago, is a bug your users will notice — and it's structurally impossible with this setup. That property is worth a lot of relevance quality.

Operational checklist before you ship

• Reindex after any configuration change. Changing a text search configuration doesn't retroactively rewrite stored tsvector values. For a generated column, ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION and re-add, or rewrite the table. Then REINDEX INDEX CONCURRENTLY.
• Raise maintenance_work_mem for the build. It governs memory available to CREATE INDEX, and it materially speeds up large GIN builds — a stingy default can turn a ten-minute build into an hour. SET maintenance_work_mem = '2GB'; in the session before a big index build, not globally.
• ANALYZE after bulk loads. The planner's selectivity estimates for @@ are only as good as the statistics on the tsvector column, and a bad estimate flips you between a bitmap scan and a seq scan.
• Watch the GIN pending list. pgstatginindex() from pgstattuple reports pending pages. If they're consistently high on a read-heavy index, tune gin_pending_list_limit or disable fastupdate.
• Use ts_debug() when something doesn't match. It shows the token type, which dictionaries were consulted, and the resulting lexemes. Nine times out of ten the answer is that a stop word ate the term, or the query used a different configuration than the index. SELECT * FROM ts_debug('english', 'the C&A store');
• Test with real query logs. Not lorem ipsum, not the ten queries you thought of. Pull two weeks of actual searches, replay them, and look at the tail. The p99 is where the ranking problem shows up, and fabricated test data hides exactly the messy, typo-laden queries that reveal whether ranking and typo-tolerance actually work.

If you want a second pair of eyes on index bloat, unused indexes, and the plans behind your slowest search queries, MyDBA is a reasonable place to start.

Build the Postgres version first. It's a day of work, it deletes an entire category of consistency bug, and if you outgrow it you'll know exactly which of the six questions above you answered wrong.