How does Postgres full text search work?

Postgres full text search (FTS) is a built-in feature that turns documents and queries into normalized forms so they can be compared by meaning rather than by exact characters. Teams use it because it handles word variants, stop words and relevance ranking inside the same database that already holds the data, with no extra service to run.

The work is done by a text search configuration such as 'english'. When you call to_tsvector('english', 'The quick brown foxes jumped'), the parser splits the text into tokens. Dictionaries drop stop words like "the" and stem the remaining words to lexemes. The result is 'brown':3 'fox':4 'jump':5 'quick':2. Each entry is a normalized lexeme with its position, which is what ranking and phrase matching use later.

Queries go through the same pipeline. to_tsquery('english', 'jumping & fox') becomes 'jump' & 'fox'. The @@ operator then checks whether a tsvector satisfies a tsquery. Because both sides were normalized by the same configuration, "jumping" matches "jumped" and "fox" matches "foxes".

Compare that with WHERE body ILIKE '%jumping%'. It does a case-insensitive substring scan. It will not match "jumped", it has no notion of relevance, and a leading wildcard means a normal B-tree index can't help, so Postgres reads every row. FTS fixes all three problems: stemming handles word variants, ranking functions order the results, and a specialized index (covered next) makes lookups fast.

-- Normalize a document: stop words dropped, words stemmed, positions kept
SELECT to_tsvector('english', 'The quick brown foxes jumped');
-- 'brown':3 'fox':4 'jump':5 'quick':2

-- Normalize a query with the same configuration
SELECT to_tsquery('english', 'jumping & fox');
-- 'jump' & 'fox'

-- Compare both approaches on the same sample rows
WITH posts(id, body) AS (
  VALUES
    (1, 'The quick brown foxes jumped'),
    (2, 'A lazy dog slept all day'),
    (3, 'Jumping fox spotted near the river')
)
SELECT
  id,
  -- FTS: matches word variants (jumped/jumping, foxes/fox)
  to_tsvector('english', body) @@ to_tsquery('english', 'jumping & fox') AS fts_match,
  -- ILIKE: literal case-insensitive substring, so no stemming
  body ILIKE '%jumping%' AS ilike_match
FROM posts
ORDER BY id;

--  id | fts_match | ilike_match
-- ----+-----------+-------------
--   1 | t         | f
--   2 | f         | f
--   3 | t         | t

Shows how to_tsvector and to_tsquery normalize text so @@ matches 'jumped' and 'foxes' for the query 'jumping & fox', while ILIKE '%jumping%' only finds the literal substring.

How do you store and index tsvector data with GIN?

You have two ways to give that index something to work with. A stored generated column keeps a precomputed tsvector beside the row. In the DDL, add a search column of type tsvector, GENERATED ALWAYS AS (...) STORED. Build it from setweight(to_tsvector('english', coalesce(title, '')), 'A') || setweight(to_tsvector('english', coalesce(body, '')), 'B'). Then run CREATE INDEX ... USING gin (search). A trigger-maintained column does the same job on older schemas, but it is more code to keep correct.

An expression index on to_tsvector('english', body) saves the column's disk space. The catch is that every query must repeat that exact expression, including the configuration name, or the planner won't match it to the index and falls back to a sequential scan. Passing the configuration explicitly matters because the one-argument form depends on default_text_search_config, so it can't be indexed. Ranking also costs more with an expression index, because ts_rank has to recompute the vector for each matching row.

GIN GiST
Lookup Exact, fast Lossy, needs heap recheck
Size Larger Smaller
Writes Expensive Cheaper

GIN maps each lexeme to the rows containing it, so @@ lookups touch only the relevant posting lists. That is why it is the default. The cost falls on writes, because a single document updates many index entries.

fastupdate defers that work by buffering new entries in a pending list. The list is merged when it exceeds gin_pending_list_limit or when vacuum runs. That merge produces occasional slow inserts and slower searches while the list is large. On write-heavy tables, tune autovacuum to run more often on the table, or disable fastupdate to get steadier latency.

-- Weighted tsvector kept beside the row; requires PostgreSQL 12+ for generated columns.
CREATE TABLE articles (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title  text NOT NULL,
  body   text,
  search tsvector GENERATED ALWAYS AS (
    -- Explicit 'english' config makes the expression immutable, so it can be stored and indexed.
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body,  '')), 'B')
  ) STORED
);

-- GIN maps each lexeme to its rows. fastupdate = off trades slower individual writes
-- for steadier latency, because no pending list builds up and gets merged later.
CREATE INDEX articles_search_gin ON articles USING gin (search)
  WITH (fastupdate = off);

-- Write-heavy table: have autovacuum visit it more often than the global default.
ALTER TABLE articles SET (autovacuum_vacuum_scale_factor = 0.02);

-- The query reads the stored column, so it can use the index and ts_rank does not recompute vectors.
SELECT id, title, ts_rank(search, q) AS rank
FROM articles, websearch_to_tsquery('english', 'gin index bloat') AS q
WHERE search @@ q
ORDER BY rank DESC
LIMIT 20;

-- Alternative: an expression index with no extra column. Queries must repeat this exact
-- expression, including 'english', or the planner will not use the index.
-- CREATE INDEX articles_body_fts ON articles USING gin (to_tsvector('english', body));
-- ... WHERE to_tsvector('english', body) @@ websearch_to_tsquery('english', 'gin');

DDL for a weighted, stored generated tsvector column with a GIN index that has fastupdate disabled and per-table autovacuum tuning, followed by a query that can use the index and the expression-index alternative.

How do you turn user input into queries and ranked results?

Users type loose text, so pick a parser that accepts it without errors. plainto_tsquery normalizes the words and joins them with AND. phraseto_tsquery requires the words to appear adjacent and in order. websearch_to_tsquery handles the syntax people already know: quoted phrases, or, and a leading - to exclude a term. It also never raises a syntax error on malformed input, which makes it the safe default for a search box. For search-as-you-type, build a to_tsquery string that appends :* to the last token so postg matches postgres. Strip operator characters from the input first, because to_tsquery rejects bad syntax.

For ranking, apply setweight when you build the tsvector: give the title A and the body D, and a title hit outweighs several body hits. ts_rank scores by term frequency. ts_rank_cd (cover density) also rewards matched terms that sit close together, but it needs position data and returns zero on stripped vectors. The normalization bitmask adjusts scores for document length. For example, 1 divides by one plus the log of the length, and 32 scales the score into the 0–1 range.

Neither function is BM25. They do not use corpus-wide term statistics, so a rare term counts no more than a common one.

Cost matters as much as relevance. ORDER BY rank LIMIT 10 still scores every matching row before it can sort, so a broad query pays for all of them. ts_headline re-parses the original text, which makes it more expensive still. In the complete query, rank and limit in a subquery, then call ts_headline only on the rows that survive.

-- Weighted search vector: title hits (A) outrank body hits (D).
-- With an explicit config, to_tsvector is immutable, so it can back a generated column.
ALTER TABLE articles ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'D')
  ) STORED;

CREATE INDEX articles_search_idx ON articles USING GIN (search_vector);

-- websearch_to_tsquery accepts raw search-box input: quotes, OR, and -exclusions.
-- For search-as-you-type, sanitize the input in the app and use a prefix query instead,
-- e.g. to_tsquery('english', 'full & text & postg:*').
WITH q AS (
  SELECT websearch_to_tsquery('english', 'postgres "full text" -elasticsearch') AS query
)
SELECT top.id,
       top.title,
       top.rank,
       -- ts_headline re-parses the body, so run it only on the 10 surviving rows.
       ts_headline('english', top.body, q.query,
                   'StartSel=<mark>, StopSel=</mark>, MaxFragments=2, MinWords=5, MaxWords=20') AS snippet
FROM (
  -- Every row matching @@ is scored before the sort, so LIMIT does not cap this cost.
  -- Normalization 32 scales the rank into the 0-1 range.
  SELECT a.id, a.title, a.body,
         ts_rank_cd(a.search_vector, q.query, 32) AS rank
  FROM articles a, q
  WHERE a.search_vector @@ q.query
  ORDER BY rank DESC
  LIMIT 10
) AS top, q
ORDER BY top.rank DESC;

A weighted generated tsvector with a GIN index, plus a websearch_to_tsquery query that ranks and limits in a subquery before calling ts_headline on only the top rows.

Why is search inside your relational database a time saver for a monolith?

The teams that benefit most run a monolith whose searchable data already lives in Postgres: tickets, orders, documents, products. They need keyword matching, sensible ranking and heavy filtering. The filtering is where the savings come from. A real search request is rarely "find these words". It is usually "find these words in this tenant's open records from the last 90 days that this user may see." In Postgres, that is one query. The @@ match sits next to tenant_id, status and date predicates and joins against your permissions tables. It runs inside a transaction, so a row committed a moment ago is searchable immediately, and a revoked permission takes effect on the next query.

Single database:
  app ──► Postgres (rows + tsvector + permissions) ──► results

External index:
  app ──► Postgres ──► CDC / queue ──► Elasticsearch ──► results
                                       (copied fields + copied ACL rules)

With an external index, the second path has two costs. First, you need a sync pipeline that can lag, drop events or need a full reindex. Second, authorization has to be copied into the index, either as denormalized fields or as query-time filters. Every permission change now has two implementations that must agree.

Keeping search in Postgres also means one fewer system to deploy, monitor, patch, secure, back up and reindex. You also have one fewer on-call runbook.

Keep this option open by putting search behind a narrow interface, such as a SearchService that takes a query plus filters and returns IDs. Callers then never build SQL themselves. If you later outgrow Postgres, you replace one implementation instead of every caller.

-- Keep a tsvector in sync with the row itself (Postgres 12+ generated column),
-- so there is no separate indexing job to run.
ALTER TABLE tickets
  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 tickets_search_idx ON tickets USING GIN (search_vector);

-- One query: keyword match, ranking, tenant/status/date filters and the
-- permission check all run in the same transaction against current rows.
-- $1 = user search text, $2 = tenant_id, $3 = current user_id
SELECT t.id,
       t.title,
       ts_rank(t.search_vector, q.query) AS rank
FROM tickets t
CROSS JOIN LATERAL websearch_to_tsquery('english', $1) AS q(query)
-- Authorization comes from the same tables the rest of the app uses,
-- so a revoked grant takes effect on the next query.
JOIN project_members pm
  ON pm.project_id = t.project_id
 AND pm.user_id = $3
WHERE t.search_vector @@ q.query
  AND t.tenant_id = $2
  AND t.status = 'open'
  AND t.created_at >= now() - interval '90 days'
ORDER BY rank DESC, t.created_at DESC
LIMIT 20;

A single Postgres query that combines full text matching and ranking with tenant, status, date and permission filters, using a generated tsvector column and a GIN index instead of an external sync pipeline.

What about typos and fuzzy matching with pg_trgm?

Built-in full text search has no notion of typos. to_tsvector and to_tsquery normalize words through a dictionary, so "postgres" and "postgresql" may or may not match depending on the configuration, and "postgers" matches nothing. Stemming handles word forms. It does not handle misspellings.

The pg_trgm extension fills that gap. It breaks strings into three-character sequences and scores how many they share. You create a GIN index on a short column such as a product name or title using the gin_trgm_ops operator class. You then query with the % operator, which uses the index and filters by pg_trgm.similarity_threshold, and order the survivors by similarity(name, $1). The usual pattern is a fallback. Run the tsvector query first, and only when it returns nothing or very little, run the trigram query to offer "did you mean" results. Keep pg_trgm as the fallback rather than the primary search path, because trigram scoring knows nothing about word importance or document length.

The costs are real. A trigram index stores many entries per row, so on long text columns it can grow larger than the table itself. Low-selectivity queries can still force heavy rechecks. Restrict trigrams to short fields.

Language handling has limits of its own. A single configuration per tsvector stems mixed-language content poorly. Stemmers occasionally conflate unrelated words. Stop-word removal can make a query like "the who" empty. Changing the configuration means recomputing every tsvector and rebuilding its index, so choose the configuration before you have a large table.

Postgres full text search vs Elasticsearch: what are the alternatives and trade-offs?

The core trade-off is consistency against capability. A tsvector column updates in the same transaction as the row, so a search can never return a deleted product or miss one that was just committed. Elasticsearch is a separate system. You need a sync path such as dual writes, an outbox table, or change data capture. Results are eventually consistent, and a mapping change means a full reindex.

Concern Postgres full text search Elasticsearch
Consistency Transactional, no sync Eventual; sync pipeline and reindexing
Operational load Existing database, backups, monitoring Separate cluster to run, upgrade and secure
Relevance ts_rank/ts_rank_cd, weights A–D BM25 by default, synonyms, learning-to-rank tooling
Analyzers Text search configurations and dictionaries Large library of tokenizers, filters and language analyzers
Facets GROUP BY counts over matches Native aggregations built for this
Scaling Vertical, plus read replicas Horizontal via shards and replicas

The ranking gap matters most. ts_rank scores term frequency within a document but has no corpus-wide statistics like inverse document frequency, so rare terms are not boosted the way BM25 boosts them. Facets in Postgres work, but counting several dimensions over a large match set means extra queries that each scan the matches.

Search also competes with your OLTP traffic for CPU, memory and buffer cache. A burst of broad queries can slow checkout. Read replicas move that load elsewhere, but they introduce replication lag, which weakens the consistency advantage. They also give you more servers to provision and monitor.

When should you avoid Postgres search, and how do you tell you've outgrown it?

Those costs are worth paying only when specific signals show up:

  • Search competes with OLTP latency. Broad queries show up near the top of your slow-query log and coincide with p99 spikes on writes.
  • Relevance needs outgrow ts_rank. You need per-field boosting, learned ranking, or large synonym sets that change often. Changing a thesaurus dictionary means reindexing and redeploying.
  • Faceted counts on every request. Postgres computes each facet by aggregating every matching row.
  • Many languages per document. Each language needs its own text search configuration and often its own tsvector column.
  • Ranking every match is too slow. ts_rank cannot use the index, so the cost of a query grows with the number of matches, not with the page size you return.

To verify, run EXPLAIN (ANALYZE, BUFFERS) on a production-sized copy using real queries. Read the plan in layers. The Bitmap Index Scan on the GIN index is the lookup cost. The Bitmap Heap Scan, with its shared read and hit counts, is the cost of fetching rows. The Sort node above it is where ranking happens. If most of the time sits in the heap scan and the sort, a better index won't help.

Illustrative estimate (assumed inputs, not measured): with 20 million documents and a common term matching 5% of them, a query fetches and ranks 1 million rows to return 20 results.

If you migrate, keep Postgres as the source of truth. Capture changes with a transactional outbox table or logical decoding, then feed a search engine through an idempotent indexer. Make sure you can rebuild the whole index from Postgres. Run both systems side by side and compare their results before you switch reads over.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to postgres full text search, this is where it helps:

Saves time

  • Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
  • One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
  • Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

Saves cost

  • Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
  • Less duplicated configuration to maintain: one shared definition inherited by many services.
  • A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
  • OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.