Full-Text Search in Postgres: Skip Elasticsearch for Most Use Cases

Most teams reach for Elasticsearch the moment a product manager says "search." That's usually the wrong call. Elasticsearch is operationally expensive, adds deployment complexity, and requires keeping data in sync with your primary store. Postgres has had a capable full-text search engine for over a decade — and for the majority of applications, it's more than enough.

How Postgres Full-Text Search Works

The core primitives are tsvector and tsquery. A tsvector is a sorted list of normalized lexemes (stemmed tokens) extracted from text. A tsquery is a search expression — terms with boolean operators and optional weights.

-- Convert text to a searchable vector
SELECT to_tsvector('english', 'The quick brown fox jumps over the lazy dog');
-- 'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2
 
-- Build a query
SELECT to_tsquery('english', 'quick & fox');
-- 'quick' & 'fox'
 
-- Match them with the @@ operator
SELECT to_tsvector('english', 'The quick brown fox') @@ to_tsquery('english', 'quick & fox');
-- true

Postgres stems words automatically based on the configured language dictionary — jumps becomes jump, lazy becomes lazi. Searching for "jumping" matches documents containing "jumps" or "jumped" without any extra work on your part.

Storing Vectors with Generated Columns

Computing tsvector inline at query time defeats the point — you can't index an expression that changes per-row at query time without materializing it first. Generated columns make this automatic and always consistent.

-- ❌ Inline computation — full table scan, no index benefit
SELECT title, body
FROM articles
WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('english', $1);
 
-- ✅ Persisted tsvector column — updated automatically, fully indexable
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;

setweight assigns importance levels (A through D) to different fields. Title matches rank higher than body matches — which is almost always what you want. The coalesce guards against NULL columns; to_tsvector returns NULL if its input is NULL, which silently breaks matches.

Indexing with GIN

A GIN (Generalized Inverted Index) on the stored vector column turns full-text search from a sequential scan into an indexed lookup.

CREATE INDEX articles_search_idx ON articles USING GIN (search_vector);

That's the entire indexing story. With a GIN index, Postgres resolves a tsquery against millions of rows in milliseconds. For most SaaS applications serving under a few million documents, query latency stays comfortably under 10ms.

Ranking and Highlighting Results

The ts_rank function scores each matched document based on term frequency and field weights. ts_headline generates a highlighted excerpt — it finds the most relevant passage and wraps matching terms in your chosen delimiters.

import postgres from "postgres";
 
const sql = postgres(process.env.DATABASE_URL!);
 
interface SearchResult {
  id: string;
  title: string;
  excerpt: string;
  rank: number;
}
 
async function searchArticles(
  query: string,
  limit = 20,
  offset = 0,
): Promise<SearchResult[]> {
  // plainto_tsquery handles arbitrary user input without throwing on special chars.
  // to_tsquery requires valid tsquery syntax — never pass raw user input to it.
  return sql<SearchResult[]>`
    SELECT
      id,
      title,
      ts_headline(
        'english',
        body,
        plainto_tsquery('english', ${query}),
        'MaxWords=35, MinWords=15, StartSel=<mark>, StopSel=</mark>'
      ) AS excerpt,
      ts_rank(search_vector, plainto_tsquery('english', ${query})) AS rank
    FROM articles
    WHERE search_vector @@ plainto_tsquery('english', ${query})
    ORDER BY rank DESC
    LIMIT ${limit}
    OFFSET ${offset}
  `;
}

Notice plainto_tsquery over to_tsquery for user-supplied input. to_tsquery throws a syntax error if the input contains unbalanced operators or unsupported characters — a sure way to cause 500s in production. plainto_tsquery treats its input as plain text and converts it to an AND-joined query safely.

Autocomplete with Prefix Search and Trigrams

Full-text search requires complete words. For autocomplete — matching partial input like "doc" → "docker" — use the :* prefix operator or a separate trigram index from pg_trgm.

-- Prefix matching with tsquery: 'dock:*' matches "docker", "docking", etc.
SELECT title FROM articles
WHERE search_vector @@ to_tsquery('english', 'dock:*')
LIMIT 10;
 
-- Fuzzy matching for typos (requires pg_trgm extension)
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX articles_title_trgm_idx ON articles USING GIN (title gin_trgm_ops);
 
-- % is the similarity threshold operator (default threshold: 0.3)
SELECT title, similarity(title, 'documetnation') AS sim
FROM articles
WHERE title % 'documetnation'
ORDER BY sim DESC
LIMIT 10;

Use pg_trgm trigram similarity for typo-tolerant search and the :* prefix operator for autocomplete dropdowns. The two approaches cover different query shapes and compose well — full-text for body search, trigrams for title completion.

When Postgres Isn't Enough

Postgres full-text search has real limits. Know them before committing.

If search needs facets (filter by category, price range, and tags simultaneously), custom relevance scoring pipelines, or you're genuinely operating at hundreds of millions of documents, Elasticsearch or a managed alternative earns its cost. For a product catalog, a knowledge base, a blog platform, or an internal document store, Postgres handles it without any additional infrastructure.

The break-even point is higher than most engineers assume. Running a dedicated Elasticsearch cluster means another service to monitor, another deployment pipeline, another failure mode, and a data sync layer that will eventually drift. Don't pay that cost until Postgres demonstrably can't keep up.

Key Takeaways

1. Store tsvector as a generated column — computing it inline at query time prevents index use entirely
2. Use setweight for multi-field relevance — title and heading matches should outrank body matches
3. GIN indexes are the unlock — without one, every search is a full sequential scan
4. Use plainto_tsquery for user input, never to_tsquery — it handles raw text safely without syntax errors
5. Add pg_trgm for fuzzy and prefix matching — full-text search and trigrams address different query shapes and stack cleanly
6. Reach for Elasticsearch only when you need facets at scale — the operational cost is real; don't pay it until Postgres measurably falls short