PostgreSQL Full-Text Search vs Elasticsearch: Where the Line Actually Is

The instinct to reach for Elasticsearch the moment search requirements appear is understandable but often wrong. Elasticsearch is a capable system, but it is also a separate cluster to deploy, a consistency boundary to manage, a data synchronization problem to solve, and an operational burden to carry indefinitely. For a surprising range of workloads, PostgreSQL’s built-in full-text search is fast enough, expressive enough, and vastly simpler to operate.

This post is about knowing where the line is. PostgreSQL FTS handles millions of documents well, offers phrase search, relevance ranking, field boosting, result snippets, and fuzzy matching through pg_trgm. Elasticsearch adds faceted search, aggregations, autocomplete, multilingual analysis, and semantic vector search at a scale and sophistication that PostgreSQL cannot match. The question is whether your actual requirements cross that line — and most applications, at most stages of their life, do not.

How PostgreSQL Full-Text Search Works

PostgreSQL FTS is built on two types: tsvector and tsquery. Understanding what these types actually store explains both the capabilities and the limits of the system.

tsvector: the processed document

to_tsvector('english', text) takes a string, runs it through a text search configuration, and returns a tsvector: a sorted list of lexemes with their positions.

1
2

SELECT to_tsvector('english', 'The quick brown foxes jumped over the lazy dogs');
-- Result: 'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2

What happened:

• Stop words (the, over) were removed based on the english configuration’s stop word list
• Remaining words were stemmed (foxes → fox, jumped → jump, lazy → lazi)
• Position numbers were preserved (word 3 in the original sentence is brown)

The positions are not decorative. They enable phrase search — knowing that fox was at position 4 and jump at position 5 is what makes phraseto_tsquery('fox jump') work correctly. If you use tsvector(strip=true) to remove positions, you lose phrase search in exchange for a smaller index.

tsquery: the search expression

Four functions produce tsquery values, each with different semantics and safety profiles:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- AND all terms; safe for user input, never throws
plainto_tsquery('english', 'quick brown fox')
-- → 'brown' & 'fox' & 'quick'

-- Exact phrase; terms must appear adjacent in order
phraseto_tsquery('english', 'brown fox')
-- → 'brown' <-> 'fox'

-- Full boolean syntax; throws on malformed input; don't expose to users
to_tsquery('english', 'quick & (fox | rabbit) & !slow')
-- → 'quick' & ( 'fox' | 'rabbit' ) & !'slow'

-- Google-like syntax: quoted phrases, minus for NOT, OR; safe for user input (PG 11+)
websearch_to_tsquery('english', '"brown fox" OR rabbit -slow')
-- → 'brown' <-> 'fox' | 'rabbit' & !'slow'

websearch_to_tsquery is the right choice for any user-facing search input. It handles the constructs users naturally type — quoted phrases, minus to exclude, OR for alternatives — and never raises a syntax error on bad input. It arrived in PostgreSQL 11 and there is no good reason not to use it.

The match operator is @@:

1
2
3

SELECT title FROM articles
WHERE to_tsvector('english', title || ' ' || body)
   @@ websearch_to_tsquery('english', '"database index" OR tsvector');

Indexes: GIN is the right choice

PostgreSQL offers two index types for FTS: GIN and GiST. GIN wins for the common case.

GIN (Generalized Inverted Index) builds a map from each lexeme to the set of documents containing it — the same structure as an Elasticsearch inverted index. It is non-lossy (no false positives) and roughly 3x faster for queries than GiST. GiST is a lossy structure that requires heap rechecks to eliminate false positives; it builds faster but queries slower.

GIN has one behavioral subtlety: the fastupdate storage parameter (on by default) maintains a “pending list” of new entries that are merged into the main index structure lazily. This speeds up writes at the cost of occasional query latency spikes during merges. The pending list size is controlled by gin_pending_list_limit (default 4MB). For write-heavy tables where predictable query latency matters more than insert throughput, set fastupdate=off on the GIN index.

1
2
3
4
5

-- Standard GIN index
CREATE INDEX articles_search_idx ON articles USING GIN (search_vector);

-- GIN with fastupdate disabled (predictable latency, slower inserts)
CREATE INDEX articles_search_idx ON articles USING GIN (search_vector) WITH (fastupdate=off);

Generated columns for pre-computed tsvectors

Computing to_tsvector() at query time is wasteful. PostgreSQL 12+ supports generated stored columns that are computed at write time and maintained automatically:

1
2
3
4
5
6
7
8
9

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

CREATE INDEX articles_search_idx ON articles USING GIN (search_vector);

The setweight() call assigns importance levels to different fields. 'A' is highest, 'D' is lowest. When ts_rank or ts_rank_cd scores a match, terms found in an 'A'-weighted field contribute more to the score than terms found in a 'C'-weighted field. A query matching the title ranks above an equal query matching only the body.

The generated column updates automatically when title, body, or author change. No application code, no triggers, no synchronization lag.

Ranking

ts_rank_cd() is the better ranking function for most applications. It implements cover density ranking: documents where query terms appear close together score higher than documents where the same terms are scattered. The _cd variant requires position information in the tsvector (the default — only an issue if you deliberately strip positions).

1
2
3
4
5
6
7
8
9

SELECT
  id,
  title,
  ts_rank_cd(search_vector, query, 32) AS rank
FROM articles,
     websearch_to_tsquery('english', 'postgresql index performance') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;

The third argument to ts_rank_cd is a normalization bitmask. Values can be ORed together:

32 is useful when you want rank values that are comparable across queries. The default (0) produces unbounded values that are only meaningful relative to other results from the same query.

Result snippets

ts_headline() extracts relevant fragments from the original text, with matched terms highlighted:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

SELECT
  id,
  title,
  ts_headline(
    'english',
    body,
    query,
    'MaxWords=60, MinWords=25, MaxFragments=2, FragmentDelimiter=" ... "'
  ) AS snippet
FROM articles,
     websearch_to_tsquery('english', 'postgresql index') query
WHERE search_vector @@ query
ORDER BY ts_rank_cd(search_vector, query) DESC
LIMIT 10;

Sample output:

...improving <b>PostgreSQL</b> query performance begins with understanding how the query
planner uses <b>indexes</b>. The most common <b>index</b> types ... the executor uses the
<b>index</b> scan to fetch matching rows from the heap...

ts_headline() runs against the original unprocessed text, not the tsvector. It is computationally more expensive than the search itself — fine for pagination-limited result sets, potentially slow if called on thousands of rows simultaneously. Apply it after filtering and limiting, not before.

pg_trgm: Fuzzy Matching and LIKE Acceleration

The pg_trgm extension gives PostgreSQL two capabilities that standard FTS does not: tolerance for typos and acceleration of arbitrary LIKE/ILIKE patterns.

A trigram is a sequence of three consecutive characters. pg_trgm pads strings with spaces at the boundaries: 'cat' becomes ' c', 'ca', 'at', 't '. The similarity between two strings is the fraction of trigrams they share:

1
2
3
4
5

CREATE EXTENSION pg_trgm;

SELECT similarity('postgresql', 'postgresl');   -- 0.5714... (one missing letter)
SELECT similarity('database', 'databse');        -- 0.5000... (transposition)
SELECT similarity('index', 'indx');              -- 0.3333...

The % operator returns true when similarity exceeds the threshold set by pg_trgm.similarity_threshold (default 0.3):

1
2
3
4
5

-- Find products with names close to user input despite typos
SELECT name, similarity(name, 'postgresl') AS sim
FROM products
WHERE name % 'postgresl'
ORDER BY sim DESC;

The more important use case for most applications is LIKE acceleration. Without pg_trgm, any LIKE pattern with a leading wildcard (%smith%, %@example.com) forces a sequential scan — no B-tree index can help because the index is ordered by the leading characters, not the middle. With a pg_trgm GIN index, PostgreSQL decomposes the pattern into trigrams and uses the index to find candidates:

1
2
3
4
5

CREATE INDEX idx_email_trgm ON users USING GIN (email gin_trgm_ops);

-- Previously a sequential scan; now uses the GIN index
SELECT * FROM users WHERE email ILIKE '%@gmail.com';
SELECT * FROM users WHERE name ILIKE '%smith%';

The minimum pattern length for trigram acceleration is 3 characters (enough to form one trigram). Patterns shorter than 3 characters still trigger a sequential scan. For a table of 10M users, this is the difference between a 200ms query and a 0.3ms query.

word_similarity() is a variant that tests whether a string is similar to any word-length fragment of another, rather than the full string. It is better than similarity() for fuzzy matching of single words within longer strings — useful for correcting misspelled search terms against a dictionary.

Multilingual Search in PostgreSQL

PostgreSQL ships with text search configurations for major European languages: english, french, german, spanish, portuguese, italian, dutch, russian, swedish, norwegian, and others. Each configuration specifies a stemmer and a stop word list. Querying with the correct configuration is critical: to_tsvector('english', 'running') produces 'run', but to_tsvector('french', 'running') produces 'running' (no French stemmer for English words).

For documents in a single known language, the approach is straightforward: pass the language name to to_tsvector and to_tsquery. For mixed-language documents or for documents whose language is not known until runtime, store the language in a column and pass it dynamically:

1
2
3
4
5
6
7
8
9

-- Store language per document
ALTER TABLE articles ADD COLUMN lang regconfig DEFAULT 'english';

-- Generated column using per-row language
ALTER TABLE articles ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector(lang, coalesce(title, '')), 'A') ||
    setweight(to_tsvector(lang, coalesce(body, '')), 'C')
  ) STORED;

The unaccent extension removes diacritics before indexing, making queries accent-insensitive:

1
2
3
4
5
6

CREATE EXTENSION unaccent;

-- Create a custom text search configuration using unaccent
CREATE TEXT SEARCH CONFIGURATION french_unaccent (COPY = french);
ALTER TEXT SEARCH CONFIGURATION french_unaccent
  ALTER MAPPING FOR hword, hword_part, word WITH unaccent, french_stem;

With unaccent applied at index time and query time, café matches cafe.

For Chinese, Japanese, and Korean, the standard PostgreSQL parser is useless — these languages have no word boundaries that the whitespace/punctuation tokenizer can detect. The pg_jieba extension integrates the Jieba segmentation library for Chinese text. Japanese and Korean require separate extensions or a dedicated search engine. If your content is primarily CJK, PostgreSQL FTS is not the right tool — this is one scenario where Elasticsearch or OpenSearch with language-specific analyzers is genuinely better.

Elasticsearch: Where It Is Actually Different

Elasticsearch (and its downstream fork OpenSearch) is built on Lucene, a Java full-text search library that has been under active development since 2001. The fundamental data structure is the same as PostgreSQL’s FTS: an inverted index mapping terms to documents. The difference is in everything built on top.

BM25 scoring

Elasticsearch has used BM25 as its default scoring algorithm since version 5.0. BM25 improves on raw TF-IDF in two ways: term frequency saturation and document length normalization.

Term frequency saturation: A term appearing 20 times in a document is not 20x as relevant as one appearing once. BM25 applies a saturation function controlled by the k1 parameter (default 1.2). As term frequency increases, the marginal relevance gain decreases rapidly. A term appearing 3 times is roughly as relevant as one appearing 10 times.

Document length normalization: Long documents should not automatically outrank short ones just because they contain more words. The b parameter (default 0.75) penalizes long documents relative to the average document length in the index.

PostgreSQL’s ts_rank_cd() with normalization flags approximates these effects, but BM25 is more principled and produces more consistent relevance across diverse datasets. For simple queries on homogeneous documents the difference is minor; for complex multi-field queries across documents of widely varying lengths, Elasticsearch’s BM25 implementation produces noticeably better results.

Analysis pipeline

Elasticsearch’s analysis pipeline gives you fine-grained control over how text is processed at index time and query time. A custom analyzer chains character filters, a tokenizer, and token filters:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

PUT /articles
{
  "settings": {
    "analysis": {
      "filter": {
        "my_stop": {
          "type": "stop",
          "stopwords": "_english_"
        },
        "my_stemmer": {
          "type": "stemmer",
          "language": "english"
        },
        "synonym_filter": {
          "type": "synonym",
          "synonyms": ["db, database", "pg, postgres, postgresql"]
        }
      },
      "analyzer": {
        "my_analyzer": {
          "type": "custom",
          "tokenizer": "standard",
          "filter": ["lowercase", "my_stop", "my_stemmer", "synonym_filter"]
        }
      }
    }
  }
}

Synonyms at index time ("pg, postgres, postgresql") mean that a query for pg matches documents containing postgresql without any application code changes. PostgreSQL FTS has no equivalent — you would need to expand synonyms in the query itself before passing it to websearch_to_tsquery, which works but requires maintaining the synonym list in application code.

Faceted search and aggregations

The feature that most commonly justifies Elasticsearch in practice is not search relevance — it is faceted navigation. When a user searches for “laptop” and the results page shows “Filter by brand: Dell (47), Apple (31), Lenovo (28)” and “Filter by price: Under $500 (18), $500-$1000 (52), Over $1000 (36)”, those counts come from aggregations running alongside the search query:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

POST /products/_search
{
  "query": {
    "match": { "description": "laptop" }
  },
  "aggs": {
    "brands": {
      "terms": { "field": "brand.keyword", "size": 10 }
    },
    "price_ranges": {
      "range": {
        "field": "price",
        "ranges": [
          { "to": 500 },
          { "from": 500, "to": 1000 },
          { "from": 1000 }
        ]
      }
    }
  },
  "size": 10
}

This returns search results and aggregation counts in a single request. Doing the equivalent in PostgreSQL requires separate COUNT queries with the same WHERE clause for each facet, or a complex query with FILTER clauses. It works, but it is not efficient at scale and requires more application code to maintain.

Autocomplete

PostgreSQL has no native autocomplete support. The closest approximation is a trigram-indexed prefix query or a full-text search of a terms table. Elasticsearch has multiple dedicated strategies:

Edge n-gram tokenizer indexes every prefix of a term: "database" is indexed as "d", "da", "dat", …, "database". A query for "data" matches without any application logic.

search_as_you_type field type (ES 7.10+) creates three sub-fields optimized for prefix, infix, and full-term matching and combines them intelligently based on query input. It produces good results with minimal configuration:

1
2
3
4
5
6
7
8
9

{
  "mappings": {
    "properties": {
      "title": {
        "type": "search_as_you_type"
      }
    }
  }
}

Completion suggester stores an FST (finite state transducer) in memory for O(1) prefix lookups — extremely fast for autocomplete but limited to exact-prefix matching with no relevance scoring.

Relevance tuning

When the default BM25 score does not produce the ranking you need, Elasticsearch’s function_score query allows custom scoring formulas:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

POST /articles/_search
{
  "query": {
    "function_score": {
      "query": {
        "multi_match": {
          "query": "database performance",
          "fields": ["title^3", "tags^2", "body"]
        }
      },
      "functions": [
        {
          "filter": { "range": { "published_at": { "gte": "now-30d" } } },
          "weight": 1.5
        },
        {
          "field_value_factor": {
            "field": "view_count",
            "modifier": "log1p",
            "missing": 1
          }
        }
      ],
      "boost_mode": "multiply"
    }
  }
}

This boosts recent articles by 1.5x, multiplies by the log of view count, and ranks title matches 3x higher than body matches. Building equivalent scoring in PostgreSQL requires custom SQL expressions that grow unwieldy quickly and are harder to tune interactively.

Vector search

Elasticsearch 8.0 introduced approximate k-nearest-neighbor search using the HNSW (Hierarchical Navigable Small World) algorithm. A dense_vector field stores embeddings from a text encoder model; a knn query retrieves semantically similar documents regardless of keyword overlap:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

{
  "mappings": {
    "properties": {
      "embedding": {
        "type": "dense_vector",
        "dims": 768,
        "index": true,
        "similarity": "cosine"
      }
    }
  }
}

1
2
3
4
5
6
7
8
9

POST /articles/_search
{
  "knn": {
    "field": "embedding",
    "query_vector": [0.12, -0.34, ...],
    "k": 10,
    "num_candidates": 100
  }
}

HNSW is approximate — it trades a small recall penalty (typically 95%+ recall) for roughly 1000x speedup over exact nearest-neighbor search. The HNSW graph must fit in memory for performance; a 768-dimensional embedding for 1M documents occupies roughly 3GB of RAM.

PostgreSQL has pgvector, an extension that adds a vector type and IVFFlat/HNSW indexes. As of PostgreSQL 16, pgvector’s HNSW implementation is production-ready for moderate scales (single digits of millions of vectors). For large-scale semantic search, Elasticsearch or a dedicated vector database remains more capable and better tooled.

The Operational Reality

The operational cost of Elasticsearch is real and frequently underestimated.

Data synchronization: PostgreSQL FTS is zero-lag — the search index updates in the same transaction as the write. Elasticsearch requires an external synchronization mechanism. The three patterns:

1. Dual-write: the application writes to both databases. Fast, simple, wrong — a crash between the two writes leaves them inconsistent with no recovery path.

2. Change data capture via Debezium: reads the PostgreSQL WAL, publishes changes to Kafka, streams changes into Elasticsearch. Reliable but adds Debezium, Kafka, and the Kafka-to-Elasticsearch connector as operational dependencies. End-to-end latency is typically 500ms–2s. This is the correct approach for production, but it means running five systems (Postgres, Debezium, Kafka, Kafka Connect, Elasticsearch) where you previously ran one.

3. Scheduled bulk reindex: rebuild the Elasticsearch index from Postgres on a schedule. Simple to implement, but search results are always stale by the interval between runs.

Schema migrations: Adding a field to Elasticsearch requires reindexing all documents. For an index with 100M documents, this is a multi-hour operation (Elasticsearch’s _reindex API, background task, rate-limited to avoid disrupting live traffic). PostgreSQL schema migrations can be done with ALTER TABLE plus an index build in most cases, and the search index updates automatically through the generated column.

Shard tuning: Elasticsearch performance is sensitive to shard count and size. The canonical guidance is 10–50GB per shard. Too few shards limits parallelism; too many shards creates coordination overhead. Getting this wrong on initial deployment means a difficult reindex operation to correct it. PostgreSQL has no equivalent concern.

Where the Line Is

PostgreSQL FTS with GIN indexes handles 10–50 million documents effectively for most query patterns. Beyond that range, performance degrades without additional measures (table partitioning, more aggressive autovacuum, dedicated search replicas). The upper bound is not a hard wall — with careful tuning, some teams run FTS queries against 200M-row tables in under a second — but it requires increasing effort.

The cases where Elasticsearch is genuinely necessary:

• Faceted search with dynamic facet counts at any scale. The aggregation API has no practical PostgreSQL equivalent.
• Autocomplete with sub-50ms latency requirements. PostgreSQL can approximate this but not elegantly.
• Synonyms at index time. Managing synonym expansion in application code is fragile.
• Hundreds of millions of documents without partitioning complexity.
• Polyglot content — multiple languages in the same index with per-language analysis. PostgreSQL requires separate tsvector columns per language.
• Semantic/vector search at meaningful scale (millions of vectors).
• Complex relevance tuning that needs interactive experimentation. The function_score API and Query DSL are much easier to iterate on than custom PostgreSQL ranking expressions.

The cases where Postgres FTS is the right answer and Elasticsearch is over-engineering:

• A SaaS application where users search their own content. Documents per user are bounded; total corpus is manageable.
• Internal tools and admin interfaces. Low query volume, modest document counts, transactional consistency matters.
• Blog or documentation search. Millions of documents at most; English only; basic relevance is fine.
• Any application where the team does not have Elasticsearch operational experience and cannot afford to acquire it.

The migration path when Postgres FTS stops being sufficient: implement CDC via Debezium before you need it. If your Postgres tables are already change-captured into Kafka for other reasons, adding an Elasticsearch sink is straightforward. If they are not, the CDC setup is the majority of the work. Either way, keep Postgres as the source of truth and treat Elasticsearch as a read-only search replica.

Reference: SQL Patterns

Multi-column search with ranking and snippets:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT
  id,
  title,
  ts_headline('english', body, query, 'MaxWords=60, MaxFragments=2') AS snippet,
  ts_rank_cd(search_vector, query, 32) AS rank
FROM articles,
     websearch_to_tsquery('english', 'postgresql index tuning') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

Phrase search:

1
2
3

SELECT id, title
FROM articles
WHERE search_vector @@ phraseto_tsquery('english', 'query planner statistics');

Combined keyword + fuzzy matching (recent queries, misspelled titles):

1
2
3
4
5
6
7

SELECT id, title, similarity(title, 'postresql') AS sim
FROM articles
WHERE title % 'postresql'          -- trigram similarity
   OR search_vector @@ websearch_to_tsquery('english', 'postresql')
ORDER BY sim DESC, ts_rank_cd(search_vector,
  websearch_to_tsquery('english', 'postresql'), 32) DESC
LIMIT 10;

Find all articles containing a substring (LIKE with trigram index):

1
2
3
4
5

CREATE INDEX idx_body_trgm ON articles USING GIN (body gin_trgm_ops);

SELECT id, title FROM articles
WHERE body ILIKE '%connection pooling%';
-- Uses the GIN trigram index; no sequential scan

Diagnose FTS query behavior:

1
2
3
4
5
6
7
8
9

-- What does the parser produce?
SELECT to_tsvector('english', 'PostgreSQL full-text search tutorial');

-- What does the query produce after normalization?
SELECT websearch_to_tsquery('english', '"full text" postgresql -mysql');

-- Would this query match this document?
SELECT to_tsvector('english', 'PostgreSQL full-text search')
    @@ websearch_to_tsquery('english', '"full text"');

These three queries answer most debugging questions about why a search is or is not returning expected results without needing to run the full production query.