Full-Text Search in PostgreSQL: No Elasticsearch Needed

Question the Elasticsearch Reflex

“We need search” too often becomes “stand up Elasticsearch” before anyone checks whether the database already does the job. For a large share of applications PostgreSQL’s built-in full-text search is genuinely enough — and it keeps your search data transactional, consistent and in one place instead of shipping it to a second system you have to keep in sync.

The model: tsvector, tsquery, @@

A document becomes a tsvector — normalized lexemes with positions (stemming, stop-words and case handled by a language config). A search becomes a tsquery. The @@ operator matches them:

SELECT title
FROM articles
WHERE to_tsvector('english', title || ' ' || body)
      @@ websearch_to_tsquery('english', 'postgres "full text"');

websearch_to_tsquery is the one to use for user input — it accepts Google-style syntax (quoted phrases, or, -exclude) without you parsing anything.

The modern pattern: generated column + GIN

Don’t recompute the tsvector on every query. Store it in a STORED generated column and put a GIN index on it — precomputed and fast:

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

CREATE INDEX ix_articles_search ON articles USING GIN (search);

-- rank + excerpt for a real results page
SELECT title,
       ts_rank(search, q) AS rank,
       ts_headline('english', body, q) AS snippet
FROM articles, websearch_to_tsquery('english', 'index bloat') q
WHERE search @@ q
ORDER BY rank DESC
LIMIT 20;

Typos and fuzzy matching: pg_trgm

Full-text search matches words, not misspellings. For typo tolerance and LIKE '%foo%' speed, add the pg_trgm extension and a trigram index — often paired with FTS to cover both exact-ish and fuzzy queries.

Where Postgres FTS stops

Be honest about the ceiling. Reach for a dedicated engine when you truly need massive scale, sophisticated relevance tuning and learning-to-rank, rich faceting/aggregations, or multi-language analyzers beyond what the built-in configs offer. For everything short of that — which is most apps — Postgres FTS saves you an entire moving part. For Oracle DBAs, it’s the direct replacement for Oracle Text (CONTEXT indexes and CONTAINS).