Why PostgreSQL Full-Text Search Is Good Enough for Most Apps

Every time someone adds search to their app, the default advice is “spin up Elasticsearch.” That advice is expensive, operationally heavy, and usually wrong. PostgreSQL has had solid full-text search since version 8. For 90% of apps, it is genuinely good enough.

How PostgreSQL Full-Text Search Works

Postgres converts your text into a tsvector - a sorted list of normalized lexemes (words stripped of suffixes). Queries use tsquery to match against those vectors. The core functions are to_tsvector and to_tsquery.

SELECT title
FROM posts
WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('english', 'postgres & search');

The 'english' argument tells Postgres which dictionary to use for stemming. “searching” and “searched” both resolve to “search.” That covers most basic needs out of the box.

The Index That Makes It Fast

Without an index, every search is a full table scan. The fix is a GIN index on a generated tsvector column.

ALTER TABLE posts ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))
  ) STORED;

CREATE INDEX posts_search_idx ON posts USING GIN(search_vector);

With a GIN index, Postgres can find matching rows in logarithmic time. A table with 5 million rows returns results in under 50ms on modest hardware. That is competitive with Elasticsearch for most read patterns.

Ranking Results

Raw matches are not useful if the best result is buried. Postgres provides ts_rank and ts_rank_cd to score results by relevance.

SELECT title,
       ts_rank(search_vector, query) AS rank
FROM posts,
     to_tsquery('english', 'database & performance') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;

You can weight different fields differently. Title matches should count more than body matches.

setweight(to_tsvector('english', title), 'A') ||
setweight(to_tsvector('english', body), 'B')

Weights A through D map to multipliers 1.0, 0.4, 0.2, 0.1. Simple and effective.

Fuzzy Matching and Typo Tolerance

Full-text search is exact on stemmed terms. If someone types “postgress” you get nothing. The fix is pg_trgm - the trigram extension.

CREATE EXTENSION pg_trgm;
CREATE INDEX posts_title_trgm_idx ON posts USING GIN(title gin_trgm_ops);

SELECT title FROM posts
WHERE similarity(title, 'postgress') > 0.3
ORDER BY similarity(title, 'postgress') DESC;

Combining FTS for relevance ranking with trigram for fuzzy matching covers most real-world search requirements.

Where Postgres Falls Short

Be honest about the limits:

If you need faceted filtering across 50 million documents with custom boosting rules, Elasticsearch is the right tool. If you are building a blog search, a product search for a store with 100k SKUs, or an internal document search, Postgres handles it fine.

A Real Migration Story

One startup I know was running Elasticsearch on three r5.large instances ($1,400/month) for a document search feature serving 400 concurrent users. They migrated to Postgres FTS with GIN indexes. Median query latency went from 12ms to 28ms - slightly slower but still fast. Infrastructure cost dropped to zero since Postgres was already in their stack. Total migration time: two days.

The 16ms difference was not noticeable to users. The $1,400/month difference was very noticeable to the budget.

The Setup Worth Using in Production

-- Generated column + GIN index
ALTER TABLE documents ADD COLUMN fts tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title,'')), 'A') ||
    setweight(to_tsvector('english', coalesce(tags,'')), 'B') ||
    setweight(to_tsvector('english', coalesce(body,'')), 'C')
  ) STORED;

CREATE INDEX documents_fts_idx ON documents USING GIN(fts);

-- Query with ranking
SELECT id, title, ts_rank(fts, q) rank
FROM documents, plainto_tsquery('english', $1) q
WHERE fts @@ q
ORDER BY rank DESC
LIMIT 10;

plainto_tsquery is safer than to_tsquery for user input - it handles spaces and special characters without throwing errors.

Bottom Line

Before you add Elasticsearch, Redis Search, or Typesense to your stack, spend an afternoon with Postgres FTS. Set up the GIN index, add pg_trgm for fuzzy matching, and benchmark against your actual data size. Most teams find it handles their needs completely. One fewer service to operate, one fewer bill to pay, zero synchronization lag between your database and your search index. +++