Full-Text Search in Postgres: Good Enough, Until It Isn't

Postgres full-text search is one of the few database features where "the built-in is enough" is genuinely the right answer for many applications. Most teams that reach for Elasticsearch on day one would have been fine with tsvector and tsquery.

The other half of the time, Postgres FTS is the wrong tool and trying to force it produces years of frustration. The line between the two cases is clearer than people think.

What Postgres FTS does well

Searching one or two text fields with prefix matching, stemming, and ranking. The classic blog post search: title and body, search for "postgres performance," results ranked by relevance.

ALTER TABLE articles
  ADD COLUMN search_vector 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_vector);

-- Query
SELECT *,
  ts_rank(search_vector, query) AS rank
FROM articles, plainto_tsquery('english', 'postgres performance') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;

This works well. The setup is one column and one index. The performance is acceptable on a few-million-row corpus. The ranking is good enough for a content site.

Multi-language support. to_tsvector('turkish', text) works and includes appropriate stemming.

Combined with structured filters. Postgres lets you mix FTS with WHERE clauses on other columns naturally — "search for X within articles published last month, by author Y." Elasticsearch makes this awkward.

What Postgres FTS does NOT do well

Fuzzy matching and typo tolerance. Postgres has the pg_trgm extension for trigram similarity, but it is not as sophisticated as Elasticsearch's analyzers. "postre" will not find "postgres" without explicit configuration.

Faceted search. "Show me products matching 'shoes', filtered by brand and color, with counts of each." Possible in Postgres but the queries get unwieldy.

Search-as-you-type with autocomplete. Possible with prefix matching but not as smooth as a dedicated search engine.

Multi-entity search. Searching across articles, products, users, support tickets in one query with cross-entity ranking. Painful in Postgres.

Massive scale. Tens of millions of documents with high query volume. Postgres can do it but Elasticsearch is purpose-built for it.

Synonyms and word vectors. "Search for cat, return results about cats and feline." Elasticsearch has this; Postgres requires custom dictionary configuration that few teams will maintain.

The decision criteria

For most applications:

• One or two text fields, simple ranking, modest scale → Postgres FTS.
• Faceted product search, autocomplete, fuzzy matching → consider Meilisearch or Typesense (lighter alternatives to Elasticsearch).
• Cross-entity search at large scale, advanced relevance, ML-driven ranking → Elasticsearch or its commercial alternatives.

The middle ground is the trickiest. Teams sometimes pick Postgres FTS hoping to upgrade later and end up halfway between — using it past its capabilities, suffering performance issues, but unable to justify the migration.

If the requirements include fuzzy matching or autocomplete, I usually recommend Meilisearch or Typesense from the start. They are much lighter than Elasticsearch and cover the gap.

The basics, in detail

tsvector is the indexed representation of text. It contains words (after stemming and stopword removal) and their positions. Two text strings produce the same tsvector if they have the same content meaning.

tsquery is the query representation. "postgres performance" becomes 'postgres' & 'performance', meaning both words must be present. Operators include & (AND), | (OR), ! (NOT), and <-> (phrase).

to_tsvector(config, text) converts text to a tsvector. The config parameter chooses language: 'english', 'turkish', etc. The default is 'english'.

@@ is the match operator: tsvector @@ tsquery returns true if the document matches.

Ranking

SELECT *,
  ts_rank(search_vector, query) AS basic_rank,
  ts_rank_cd(search_vector, query) AS cover_density_rank
FROM articles, websearch_to_tsquery('english', 'postgres performance') query
WHERE search_vector @@ query
ORDER BY ts_rank_cd(search_vector, query) DESC;

Two ranking functions:

• ts_rank: counts term frequency. Higher when the query terms appear more.
• ts_rank_cd: cover density. Considers proximity of terms (matches close together rank higher than scattered).

For most use cases, ts_rank_cd produces better rankings.

The setweight calls in the column definition ('A', 'B', 'C', 'D') influence ranking — higher weights for title, lower for body. Without weights, all matches rank equally regardless of where they appeared.

websearch_to_tsquery: the query parser people want

Writing tsquery syntax ('postgres' & 'performance') is awkward for user-facing search. websearch_to_tsquery parses Google-style queries:

websearch_to_tsquery('english', 'postgres performance "index optimization" -elasticsearch')

This handles quoted phrases ("index optimization"), excluded terms (-elasticsearch), and OR operators. It is the right query parser for most application search inputs.

Performance characteristics

A GIN index on the search_vector column scales well to a few million rows. Query latency is typically a few milliseconds for typical search terms. Index size is roughly 30-50% of the indexed data.

For very large datasets (10M+ rows) or very high query volumes, the GIN index gets expensive to maintain on writes. The alternative is a GiST index (smaller, slower reads, faster writes) or a separate search tier.

A migration path

If you start with Postgres FTS and outgrow it, the migration to a dedicated search engine usually goes:

1. Add the search engine alongside Postgres. Mirror the searchable content to it.
2. Dual-write: every update to Postgres also updates the search engine.
3. Dual-read: queries that need fuzzy/autocomplete go to search engine; everything else stays on Postgres.
4. Verify consistency for a few weeks.
5. Cut over the affected query paths to the search engine.

This is doable but takes weeks. The cost of being wrong about Postgres FTS is real but bounded.

What I recommend by default

For a new application with text search needs:

1. Start with Postgres FTS unless you specifically need faceted search, autocomplete, or fuzzy matching.
2. Use tsvector as a generated column.
3. Use a GIN index.
4. Use websearch_to_tsquery for parsing user input.
5. Use ts_rank_cd for relevance ranking.
6. Plan the migration path to a dedicated search engine in case you outgrow it.

Most applications never need step 6. The ones that do, the migration is worth doing deliberately when the time comes.