Postgres Full-Text Search vs Elasticsearch

Postgres has shipped full-text search — tokenization, stemming, stop words, ranking, highlighting — as a built-in since version 8.3 in 2008. For the search box on your product, docs, or dashboard, a tsvector column with a GIN index answers queries in milliseconds without a second system, and unlike an Elasticsearch cluster, the results are always transactionally consistent with your data: a deleted row can never linger in yesterday's index. The gap that used to justify Elasticsearch — BM25 relevance ranking — closed too: ParadeDB (9,000+ GitHub stars) runs BM25 scoring inside Postgres on the Tantivy engine. What remains genuinely Elasticsearch territory is log analytics at terabytes per day, which is a different problem from application search and the reason GitLab sells its Elasticsearch-backed "advanced search" as a separate premium capability.

Can Postgres full-text search replace Elasticsearch?

Split the question by workload. Application search — users searching products, tickets, articles, messages — is a few million rows, short queries, and an expectation that results reflect the current state of the data. Postgres handles this directly. Log analytics — engineers slicing terabytes of append-only events by a dozen dimensions — is what the ELK stack was actually built for. Most teams adopt Elasticsearch for the first workload, then discover they are running a three-node cluster and a reindexing pipeline for a search box.

The pipeline is the real tax. Search in a separate system means every write must reach two places, so you add change-data-capture or dual writes, then reconciliation jobs for when they drift, then alerting for the reconciliation jobs. Search inside Postgres deletes that entire layer — the same consolidation argument that applies to queues and caches, with consistency as the bonus: search results can never reference a row your transaction rolled back.

How does tsvector search actually work?

Postgres preprocesses text into a tsvector of normalized lexemes — parsing tokens, folding case, stemming ("running" → "run"), and dropping stop words. Store it as a generated column so it maintains itself, index it with GIN, and query it with websearch_to_tsquery, which accepts Google-style syntax (quoted phrases, -exclusions, OR) instead of requiring you to build boolean expressions:

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

CREATE INDEX articles_search_idx ON articles USING gin (search);

SELECT id, title,
       ts_rank(search, query) AS rank,
       ts_headline('english', body, query) AS snippet
FROM articles, websearch_to_tsquery('english', 'postgres "full text" -mysql') AS query
WHERE search @@ query
ORDER BY rank DESC
LIMIT 20;

setweight makes title hits outrank body hits, ts_rank orders by relevance, and ts_headline returns highlighted snippets — the three features people assume require a search engine. On a few hundred thousand rows this runs in single-digit milliseconds with a warm GIN index. More SQL patterns live in the PostgreSQL cheat sheet.

How do you handle typos and autocomplete?

Stemming does not forgive misspellings — "postgers" matches nothing. The pg_trgm extension fixes that by comparing three-character sequences: the % operator returns rows above a similarity threshold (default 0.3), and a trigram GIN index also accelerates the ILIKE '%term%' queries that are otherwise sequential scans:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX articles_title_trgm ON articles USING gin (title gin_trgm_ops);

SELECT title, similarity(title, 'postgers full text serach') AS sml
FROM articles
WHERE title % 'postgers full text serach'
ORDER BY sml DESC
LIMIT 5;

For autocomplete, prefix-match the last word with to_tsquery('english', 'postg:*') and debounce on the client. The practical recipe most applications land on: tsvector for the main query, pg_trgm as the "did you mean" fallback when it returns nothing.

What if I need BM25 ranking or better relevance?

The honest weakness of built-in search is ranking quality. ts_rank scores by term frequency and proximity but ignores document length and corpus-wide term rarity — the two ingredients that make BM25 (Elasticsearch's default since 2016) rank a short, precise match above a long document that mentions the term often. ParadeDB closes exactly this gap: its pg_search extension embeds the Rust search engine Tantivy in Postgres, adding BM25 scoring, custom tokenizers, and highlighting as a CREATE EXTENSION instead of a second datastore. For multilingual corpora — especially CJK languages the default parser tokenizes poorly — PGroonga does the same job. Extensions are the escalation path: you exhaust built-in FTS, then add an extension, and only then consider leaving Postgres — the same ladder the queue guide climbs from raw SKIP LOCKED to pgmq.

When is Elasticsearch actually the right choice?

• Observability and log analytics — ingesting terabytes a day of append-only events and aggregating across dozens of fields is the workload Elasticsearch's inverted indexes, shard model, and Kibana were designed around. Postgres is the wrong shape for this.
• Search as the product at scale — GitLab runs its advanced search on Elasticsearch: cross-project search over hundreds of millions of documents, operated by a dedicated team. If search relevance is a competitive feature with headcount attached, the specialist tool earns its cluster.
• Heavy faceted aggregations — sub-second facet counts across many high-cardinality dimensions on large datasets favor purpose-built aggregation engines.

The symmetry with the rest of the series holds: the exceptions are named, measurable workloads — not "we might need better search someday". Start in Postgres, escalate on evidence.

References

• PostgreSQL docs — Full Text Search introduction — tsvector/tsquery, preprocessing, and operators
• PostgreSQL docs — pg_trgm — trigram similarity operators and index support
• ParadeDB — BM25 full-text search in Postgres, built on Tantivy
• GitLab docs — Advanced search — a named example of Elasticsearch deployed where it genuinely earns its keep
• Tiger Data — It's 2026, Just Use Postgres — the consolidation case, including BM25-in-Postgres