What is Postgres full text search and how does tsvector work?
A WHERE body ILIKE '%invoice%' query has two problems. A btree index stores values sorted from the first character, so a pattern that starts with a wildcard has no starting point in that order. Postgres has to scan every row and test each string. The match is also purely textual. A search for "invoices" will not find "invoice", and "running" will not find "ran" or "run".
Full text search replaces string matching with lexeme matching. to_tsvector parses a document into tokens, drops stop words, and normalizes the remaining words into lexemes, keeping their positions. to_tsquery (and its friendlier variants) applies the same processing to the query. The @@ operator then tests whether the document's lexemes satisfy the query. Both sides must use the same text search configuration, because the configuration decides which dictionaries, stop-word lists and stemmer apply.
| Input | ILIKE substring |
'simple' config |
'english' config |
|---|---|---|---|
| "Jumped" | literal text only | jumped |
jump |
| "the" | matches inside "other" | the |
removed as stop word |
| "university" | literal text only | university |
univers |
| "universe" | literal text only | universe |
univers |
Stemming can produce surprising results. With 'english', "university" and "universe" reduce to the same lexeme, so a search for one returns the other. A query made only of stop words, such as "to be or not to be", becomes an empty tsquery. Postgres raises a notice and the query matches no rows. A user sees this as broken search, so the application should detect the empty case and show a message.
-- Same sentence, two configurations: 'english' drops stop words and stems; 'simple' only lowercases.
SELECT to_tsvector('english', 'The quick brown foxes jumped');
-- 'brown':3 'fox':4 'jump':5 'quick':2
SELECT to_tsvector('simple', 'The quick brown foxes jumped');
-- 'brown':3 'foxes':4 'jumped':5 'quick':2 'the':1
-- The query goes through the same pipeline, so 'jumping' matches 'jumped'.
SELECT to_tsquery('english', 'jumping'); -- 'jump'
SELECT to_tsvector('english', 'The fox jumped')
@@ to_tsquery('english', 'jumping'); -- true
-- ILIKE is plain substring matching: '%the%' matches inside 'other'.
SELECT 'other' ILIKE '%the%'; -- true
-- Surprise 1: different words that stem to the same lexeme.
SELECT to_tsvector('english', 'university')
@@ to_tsquery('english', 'universe'); -- true (both become 'univers')
-- Surprise 2: a query made only of stop words becomes empty.
SELECT plainto_tsquery('english', 'to be or not to be');
-- NOTICE: text-search query contains only stop words or doesn't contain lexemes, ignored
-- result: empty tsquery, which matches no rows
-- Detect the empty case so the application can show a message
-- instead of an empty result list.
SELECT numnode(plainto_tsquery('english', 'to be or not to be')) = 0
AS query_is_empty; -- true
Shows how the 'english' and 'simple' configurations turn text into lexemes, how @@ matches stemmed terms where ILIKE only matches substrings, and how to detect a query that contains only stop words with numnode().
Why use Postgres full text search in a monolith, and who should start here?
If your application already writes to Postgres, a search index there is updated in the same transaction as the row it describes. When a ticket is saved, its tsvector is saved with it, and a rollback undoes both. Adding Elasticsearch means a second datastore and a pipeline to keep it in sync, usually change data capture or a queue. It also brings the dual-write problem: the database commit succeeds, the index update fails, and search quietly drifts from the source of truth.
This approach suits backend and platform teams building product search over catalogs, support tickets, documentation or user-generated content. It fits especially well when data is tenant-scoped. A filter like tenant_id = $1 sits in the same SQL statement as the text match, and existing row-level security policies still apply. You do not have to re-implement authorization inside a separate search cluster.
Illustrative estimate. Assume these inputs, which are not measured:
- Building and testing a sync pipeline with backfill and retry handling takes 3 engineer-weeks.
- Ongoing upkeep, including mapping changes, reindexing and drift investigations, takes 2 engineer-days per month.
On those inputs, staying in Postgres for the first year saves about 15 + 24 = 39 engineer-days, before counting cluster hosting and on-call load.
The opposite risk is real. If you wait until relevance tuning, query volume or index size has clearly outgrown Postgres, the migration happens under pressure. Treat Postgres as a deliberate first stage, and watch for the signals described in section 6.
A) Postgres only: one datastore, one transaction
+-------------+ +--------------------------------------+
| App |----->| Postgres, one transaction |
| (monolith) | | UPDATE ticket row |
+-------------+ | and its tsvector, same commit |
| | ROLLBACK undoes both |
| +--------------------------------------+
| ^
+--- search: same SQL statement --+
WHERE tenant_id = $1 AND tsv @@ query; RLS applies
B) Postgres + Elasticsearch: second datastore and sync pipeline
+-------------+ +------------+ +-------------+ +----------+
| App |----->| Postgres |----->| CDC / queue |----->| Elastic- |
| (monolith) | | (commit OK)| | sync worker | | search |
+-------------+ +------------+ +-------------+ +----------+
| | ^
| if index write fails, |
| search drifts from DB |
+------ search query (authz rebuilt in cluster) ------------+
With Postgres alone, the row and its tsvector commit or roll back together, and tenant filters and row-level security stay in the same query. Adding Elasticsearch introduces a sync pipeline, where a failed index write leaves search out of step with the database.
How do you store the tsvector and build a GIN index?
Storing the vector, rather than computing it on every query, lets a GIN index do the work. There are three common ways to do it.
| Approach | Extra storage | Query must reference | Changing fields or config |
|---|---|---|---|
Generated column (GENERATED ALWAYS AS (...) STORED) |
Column plus index | The column | Drop and re-add the column, which rewrites the table |
| Trigger-maintained column | Column plus index | The column | Update the trigger function, then backfill every row |
| Expression index | Index only | The exact indexed expression | Create a new index and update every query |
Whichever you choose, pass the configuration explicitly: to_tsvector('english', body), not to_tsvector(body). The one-argument form reads default_text_search_config, a session setting, so its result can change between sessions. Postgres marks it as not immutable and refuses to use it in an index or a generated column.
For weighting, concatenate weighted vectors: setweight(to_tsvector('english', coalesce(title, '')), 'A') || setweight(to_tsvector('english', coalesce(body, '')), 'C'). The coalesce matters because a NULL title would make the whole vector NULL.
Build the index with CREATE INDEX ... USING gin (search_vector). Run EXPLAIN on a @@ query and look for a Bitmap Index Scan on that index feeding a Bitmap Heap Scan. With an expression index, the planner only matches the query if it repeats the indexed expression exactly. A different configuration name or a missing coalesce falls back to a sequential scan.
GIN indexes are large and expensive to update. Each row change touches many posting lists. With fastupdate on, new entries go into a pending list. That list is flushed by vacuum, or by whichever insert pushes it past gin_pending_list_limit, so occasional writes stall. Searches also have to scan the unmerged list. On write-heavy tables, keep an eye on autovacuum frequency and that limit.
-- Option 1: generated column (PostgreSQL 12+). The two-argument to_tsvector is immutable,
-- so it is allowed here. coalesce keeps a NULL title from nulling the whole vector.
ALTER TABLE articles
ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (
setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
setweight(to_tsvector('english', coalesce(body, '')), 'C')
) STORED;
CREATE INDEX articles_search_idx ON articles USING gin (search_vector);
-- Expect a Bitmap Index Scan on articles_search_idx feeding a Bitmap Heap Scan.
-- On a tiny table the planner may still prefer a Seq Scan, so test with realistic data.
EXPLAIN
SELECT id, title
FROM articles
WHERE search_vector @@ websearch_to_tsquery('english', 'connection pooling');
-- Option 2: expression index, with no extra column.
CREATE INDEX articles_body_fts_idx ON articles
USING gin (to_tsvector('english', coalesce(body, '')));
-- Matches the index: the expression is repeated exactly.
EXPLAIN SELECT id FROM articles
WHERE to_tsvector('english', coalesce(body, '')) @@ to_tsquery('english', 'vacuum');
-- Does NOT match because coalesce is missing. Expect a Seq Scan.
EXPLAIN SELECT id FROM articles
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'vacuum');
-- Write-heavy table: either cap the pending list or turn it off to get steadier insert latency.
ALTER INDEX articles_search_idx SET (gin_pending_list_limit = 1024); -- value in kB
-- ALTER INDEX articles_search_idx SET (fastupdate = off);
-- Flush the pending list on your own schedule instead of during a user's insert.
SELECT gin_clean_pending_list('articles_search_idx'::regclass);
This example stores a weighted tsvector in a generated column with a GIN index and checks the plan with EXPLAIN. It shows that an expression index is used only when the query repeats the indexed expression exactly, and how to tune or flush the GIN pending list.
How do you turn user input into safe, ranked queries?
Never pass raw user input to to_tsquery. It parses its own operator syntax (&, |, !, <->, :*), so input like c++ & ( raises a syntax error, and a valid but surprising expression changes what the query means. Use one of the parsers built for free text:
plainto_tsquerynormalizes the words and ANDs them together. It ignores punctuation and operators.phraseto_tsqueryjoins terms with<->, so the words must appear adjacent and in order.websearch_to_tsqueryaccepts search-engine syntax: quoted phrases,or, and-for exclusion. It does not raise syntax errors on malformed input, which makes it a sensible default for a search box.
Order results with ts_rank, which weighs term frequency, or ts_rank_cd, which rewards terms that appear close together and needs positional information in the tsvector. Both run once per matching row. They read each row's stored tsvector, and the GIN index cannot help with that step. Both functions also score each document in isolation. They have no corpus-wide statistics such as IDF or BM25, so a common term counts as much as a rare one.
The practical pattern has two steps. In an inner query, apply the indexed @@ match together with tenant_id and status filters, and cap the result with LIMIT, for example to a few thousand candidates. In the outer query, rank only those rows, then sort and paginate. This keeps latency predictable. The cost is recall: a relevant row that falls outside the cap is never scored, so deep pages become unreliable. Prefer keyset pagination on rank and id over large OFFSET values.
If most queries are scoped to a single tenant and tenants vary widely in size, the btree_gin extension lets you build a composite GIN index on (tenant_id, tsv). The planner can then apply both conditions in one index scan instead of intersecting results from separate indexes.
-- One-time setup: composite GIN index so the tenant filter and the text match
-- are served by the same index scan.
CREATE EXTENSION IF NOT EXISTS btree_gin;
CREATE INDEX documents_tenant_tsv_idx ON documents USING gin (tenant_id, tsv);
-- $1 = tenant_id (bigint), $2 = raw search box text,
-- $3, $4 = rank and id of the last row on the previous page.
-- For the first page, omit the keyset predicate in the outer WHERE.
WITH q AS (
-- websearch_to_tsquery does not raise syntax errors on malformed input.
SELECT websearch_to_tsquery('english', $2) AS query
),
candidates AS (
-- Step 1: indexed filtering only. No ranking here.
SELECT d.id, d.title, d.tsv
FROM documents d, q
WHERE d.tenant_id = $1
AND d.status = 'published'
AND d.tsv @@ q.query
-- The cap trades recall for predictable latency: without ORDER BY,
-- rows past the cap are never scored, even if they are relevant.
LIMIT 2000
),
ranked AS (
-- Step 2: rank only the bounded set. ts_rank_cd needs positions in tsv.
SELECT c.id, c.title, ts_rank_cd(c.tsv, q.query) AS rank
FROM candidates c, q
)
SELECT id, title, rank
FROM ranked
-- Keyset pagination: avoids scanning and discarding rows with a large OFFSET.
WHERE (rank, id) < ($3, $4)
ORDER BY rank DESC, id DESC
LIMIT 20;
A btree_gin composite index on (tenant_id, tsv) plus a two-step query. The query first selects a capped candidate set using the indexed @@ match and the tenant and status filters, then ranks only those rows with ts_rank_cd and paginates by keyset on (rank, id).
Prefix search, typo tolerance, snippets and multilingual content
Search-as-you-type
websearch_to_tsquery does not support prefix matching, so autocomplete needs a different query builder. Split the input into words, strip characters that have meaning in tsquery syntax (&, |, !, :, *, parentheses, quotes), join the words with &, and append :* to the last one. Pass the result to to_tsquery. Typing postg then becomes postg:*, which matches any lexeme that starts with postg, and the existing GIN index serves the query. Run the prefix term through the same configuration as the stored document. Otherwise stemming can produce a prefix that never matches the stored lexemes.
Typo tolerance and substrings
A tsvector stores whole lexemes, so it cannot match postgers to postgres or find gres inside a word. The pg_trgm extension handles both cases. Add a GIN index using gin_trgm_ops on the column. The % similarity operator then becomes indexable, and so do LIKE and ILIKE with leading wildcards. This has costs:
- Trigram indexes on long text are often larger than the tsvector index.
- Write amplification increases.
- You have to tune
pg_trgm.similarity_thresholdagainst your own data.
Restrict trigram indexes to short fields such as titles and names, where typos actually occur.
Snippets
ts_headline does not use the stored tsvector. It re-parses the original text on every call, which is expensive for long documents. Rank and paginate first, then call ts_headline only on the 10–20 rows you return, in an outer query.
Multilingual content
Store the language as a regconfig column and build the vector with to_tsvector(lang, body) so each row is stemmed in its own language. For SKUs, error codes and personal names, use the simple configuration. It lowercases tokens without stemming or removing stopwords, so ABC-100 and surnames stay intact.
When should you avoid Postgres full text search and add Elasticsearch?
Write these exit criteria down before you ship. Then the decision to add a search cluster rests on evidence, not on how the project feels. Measure each one with your own EXPLAIN ANALYZE output, pg_stat_statements and host metrics. Do not assume any of them.
| Criterion | What to measure | Signal that Postgres is no longer enough |
|---|---|---|
| Relevance | Ranking quality on a fixed set of labelled queries | You need BM25-style scoring with corpus-wide term statistics, per-field boosts or synonym sets beyond what you can maintain in dictionary files. ts_rank scores each row in isolation. |
| Fuzzy matching | Recall on misspelled queries | Trigram similarity can't express the edit-distance, phonetic or per-field fuzziness you need. |
| Facets | Execution time of count queries per facet | Each facet means another aggregate over the whole match set. Broad queries scan many rows even when you show only 20. |
| Resource contention | CPU, buffer cache hit ratio and I/O wait during peak search | Search queries evict hot OLTP pages or push transactional p99 latency over its budget. |
| Latency | Search p95/p99 after index and query tuning | The target is still missed once you have stored vectors, GIN indexes and bounded result sets. |
Before you add another system, consider moving search to a read replica. This isolates search CPU and cache pressure from writes and adds no new technology to operate. The cost is replication lag. A record that was just created or edited may not show up in search right away. Decide whether your product can accept that. If users expect to find what they saved a moment ago, the replica creates the same freshness problem that an external index would.
What are the alternatives to Postgres full text search?
Each option trades relevance against operational weight differently:
| Option | Relevance quality | Operational cost | Consistency | Sync pipeline | Team effort |
|---|---|---|---|---|---|
LIKE/ILIKE |
Substring match only, no ranking | None | Transactional | None | Minimal |
tsvector + GIN |
Stemming, weights, ts_rank |
Low: index bloat, write cost | Transactional | None | Low |
pg_trgm |
Fuzzy and typo-tolerant, no linguistics | Low: large indexes | Transactional | None | Low |
tsvector on a read replica |
Same as primary | Moderate: replica to run | Lagged | Built-in replication | Low |
| Ranking extension (e.g. BM25) | Closer to a search engine | New dependency to install and upgrade | Transactional | None | Moderate |
| Elasticsearch | Strongest: analyzers, facets, tuning | High: cluster, mappings, upgrades | Eventual | Required | High |
Unanchored LIKE '%term%' cannot use a B-tree index, so it degrades to a scan as the table grows. pg_trgm and tsvector complement each other: trigrams for names and codes, tsvector for prose. A ranking extension can close much of the relevance gap without a second datastore. Before adopting one, check whether your managed Postgres provider allows it, who maintains it, and how it behaves across major-version upgrades. Elasticsearch adds the most capability. It also makes you responsible for indexing, backfills, and reconciling drift.
Whatever you pick, route every search through one module that exposes a narrow contract: query text, filters, and pagination in; ranked IDs and snippets out. If controllers never build tsquery strings or SQL directly, a later move to Elasticsearch means writing one new implementation behind that interface and comparing its results against the old one. You avoid hunting down search logic scattered across the codebase.
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
.envby 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.