Full-Text Search Basics

Q: plainto_tsquery vs websearch_to_tsquery?

plainto_tsquery ANDs terms. websearch_to_tsquery supports quotes and minus terms like web search boxes.

Q: Do I need Elasticsearch for autocomplete?

Not always. pg_trgm on prefix queries or materialized prefix tables work for moderate traffic.

Q: How big can GIN indexes get?

Roughly comparable to corpus token count. Monitor with pg_relation_size.

Q: Multi-language content?

Use simple config plus language column, or per-language tsvector columns.

Q: Highlighting in API?

ts_headline in SQL or return positions via tsvector parsing in application.

Q: Managed Postgres FTS limits?

Same engine; check extension allowlist for unaccent. No Elasticsearch-style managed sibling on RDS.

Q: Upgrade notes PG 18?

Core FTS stable; watch websearch_to_tsquery behavior in release notes for parser tweaks.

PostgreSQL ships native full-text search with tsvector, tsquery, and GIN indexes. For many products it replaces a separate Elasticsearch cluster when data volume, ranking needs, and operational headcount favor one datastore.

Recipe

-- Minimal searchable documents table
CREATE TABLE articles (
  id          bigserial PRIMARY KEY,
  title       text NOT NULL,
  body        text NOT NULL,
  published   timestamptz DEFAULT now()
);
 
-- Generated tsvector column (PostgreSQL 12+)
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;
 
CREATE INDEX articles_search_idx ON articles USING gin (search_vector);
 
-- Search
SELECT id, title, ts_rank(search_vector, query) AS rank
FROM articles, plainto_tsquery('english', 'postgresql replication') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;

When to reach for this: Full-text over relational data, moderate corpus size (millions of rows), and team wants transactional consistency without dual-write to a search engine.

Working Example

INSERT INTO articles (title, body) VALUES
  ('Streaming Replication', 'Physical WAL shipping to standbys for HA.'),
  ('Logical Replication', 'Row-level changes for upgrades and fan-out.'),
  ('Full-Text Search', 'Built-in tsvector ranking with GIN indexes.');
 
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title,
       ts_rank_cd(search_vector, query) AS rank,
       ts_headline('english', body, query,
         'MaxFragments=2, MaxWords=20, MinWords=8') AS snippet
FROM articles,
     websearch_to_tsquery('english', 'replication OR "full text"') query
WHERE search_vector @@ query
ORDER BY rank DESC;

What this demonstrates:

Weighted fields (A title, B body) bias ranking
websearch_to_tsquery accepts user-friendly search syntax
ts_headline builds result snippets in SQL
EXPLAIN confirms GIN index usage on @@

Deep Dive

Built-in FTS vs External Search

Factor	Postgres FTS	Elasticsearch/OpenSearch
Ops	One cluster you already run	Dedicated cluster, JVM tuning, upgrades
Consistency	Same transaction as row data	Dual-write or CDC lag
Ranking	`ts_rank`, custom weights	Rich analyzers, learning-to-rank plugins
Scale ceiling	Millions to low billions with tuning	Billions of docs, heavy aggregations
Facets	SQL `GROUP BY` on columns	Native aggregations

ADR default: Start with Postgres FTS when corpus fits one instance, queries are keyword + light fuzzy, and team size cannot babysit Elasticsearch.

Reach for OpenSearch when: sub-100ms search at huge scale, complex analyzers per language, heavy autocomplete sharding, or dedicated search SRE team.

Core Objects

tsvector - Normalized lexeme bag with optional weights/positions.
tsquery - Parsed search expression with operators (&, |, !, <-> phrase).
@@ match operator - Boolean match test; GIN index accelerates it.
Dictionaries - Stemming, stop words, synonyms (see tsvector article).

Language Configuration

SELECT cfgname FROM pg_ts_config;
-- common: english, simple, french
SELECT to_tsvector('simple', 'Running runs RUN');
-- simple: no stemming, good for product SKUs and codes

Use simple for identifiers; use english (or locale-specific) for prose.

Gotchas

No index on to_tsvector() in WHERE - Expression not stored means seq scan. Fix: Generated stored column or trigger-maintained tsvector.
OR queries explode results - websearch_to_tsquery with many OR terms. Fix: Require minimum rank threshold or AND-heavy defaults.
Case and diacritics - FTS normalizes case; accents depend on dictionary. Fix: unaccent extension or ICU collation for locale-sensitive search.
Substring search - FTS matches lexemes, not LIKE '%foo%'. Fix: pg_trgm for substring; combine in hybrid queries.
Stale vectors after bulk load - Generated columns update automatically; trigger-based ones may not. Fix: Verify maintenance path after ETL.
Elasticsearch envy - Rebuilding inverted index for every schema tweak. Fix: Iterate ranking in SQL before adopting second system.

Alternatives

Alternative	Use When	Don't Use When
Elasticsearch/OpenSearch	Huge scale, advanced relevance	Small team, strong transactional coupling
`pg_trgm` only	SKU prefix, email substring	Stemmed natural language prose
External SaaS (Algolia)	Fast launch, hosted relevance	Data residency blocks third party
Hybrid with pgvector	Semantic + keyword (see hybrid article)	Pure keyword site with no embeddings

FAQs

Is Postgres FTS good enough for SaaS search?

Often yes up to millions of documents with GIN and proper weights. Benchmark p95 before adopting Elasticsearch.

plainto_tsquery vs websearch_to_tsquery?

`plainto_tsquery` ANDs terms. `websearch_to_tsquery` supports quotes and minus terms like web search boxes.

Do I need Elasticsearch for autocomplete?

Not always. `pg_trgm` on prefix queries or materialized prefix tables work for moderate traffic.

How big can GIN indexes get?

Roughly comparable to corpus token count. Monitor with `pg_relation_size`.

Multi-language content?

Use `simple` config plus language column, or per-language `tsvector` columns.

Highlighting in API?

`ts_headline` in SQL or return positions via `tsvector` parsing in application.

Replication lag impact?

FTS reads local indexes on replica; no special lag beyond normal streaming replication.

Managed Postgres FTS limits?

Same engine; check extension allowlist for `unaccent`. No Elasticsearch-style managed sibling on RDS.

Security?

FTS inherits RLS policies on base table. No separate search ACL layer.

Upgrade notes PG 18?

Core FTS stable; watch `websearch_to_tsquery` behavior in release notes for parser tweaks.

tsvector & tsquery - dictionaries and ranking
GIN Indexes for FTS - index build and maintenance
Hybrid Search - combine with pgvector
GIN and GiST - access method overview
Full-Text Search Best Practices - operational checklist

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PostGIS 3.5+, pgbouncer 1.x, and Patroni 3.x.

Recipe

-- Minimal searchable documents table
CREATE TABLE articles (
  id          bigserial PRIMARY KEY,
  title       text NOT NULL,
  body        text NOT NULL,
  published   timestamptz DEFAULT now()
);
 
-- Generated tsvector column (PostgreSQL 12+)
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;
 
CREATE INDEX articles_search_idx ON articles USING gin (search_vector);
 
-- Search
SELECT id, title, ts_rank(search_vector, query) AS rank
FROM articles, plainto_tsquery('english', 'postgresql replication') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;

When to reach for this: Full-text over relational data, moderate corpus size (millions of rows), and team wants transactional consistency without dual-write to a search engine.

Working Example

INSERT INTO articles (title, body) VALUES
  ('Streaming Replication', 'Physical WAL shipping to standbys for HA.'),
  ('Logical Replication', 'Row-level changes for upgrades and fan-out.'),
  ('Full-Text Search', 'Built-in tsvector ranking with GIN indexes.');
 
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title,
       ts_rank_cd(search_vector, query) AS rank,
       ts_headline('english', body, query,
         'MaxFragments=2, MaxWords=20, MinWords=8') AS snippet
FROM articles,
     websearch_to_tsquery('english', 'replication OR "full text"') query
WHERE search_vector @@ query
ORDER BY rank DESC;

What this demonstrates:

Weighted fields (A title, B body) bias ranking
websearch_to_tsquery accepts user-friendly search syntax
ts_headline builds result snippets in SQL
EXPLAIN confirms GIN index usage on @@

Deep Dive

Built-in FTS vs External Search

Factor	Postgres FTS	Elasticsearch/OpenSearch
Ops	One cluster you already run	Dedicated cluster, JVM tuning, upgrades
Consistency	Same transaction as row data	Dual-write or CDC lag
Ranking	`ts_rank`, custom weights	Rich analyzers, learning-to-rank plugins
Scale ceiling	Millions to low billions with tuning	Billions of docs, heavy aggregations
Facets	SQL `GROUP BY` on columns	Native aggregations

ADR default: Start with Postgres FTS when corpus fits one instance, queries are keyword + light fuzzy, and team size cannot babysit Elasticsearch.

Reach for OpenSearch when: sub-100ms search at huge scale, complex analyzers per language, heavy autocomplete sharding, or dedicated search SRE team.

Core Objects

tsvector - Normalized lexeme bag with optional weights/positions.
tsquery - Parsed search expression with operators (&, |, !, <-> phrase).
@@ match operator - Boolean match test; GIN index accelerates it.
Dictionaries - Stemming, stop words, synonyms (see tsvector article).

Language Configuration

SELECT cfgname FROM pg_ts_config;
-- common: english, simple, french
SELECT to_tsvector('simple', 'Running runs RUN');
-- simple: no stemming, good for product SKUs and codes

Use simple for identifiers; use english (or locale-specific) for prose.

Gotchas

No index on to_tsvector() in WHERE - Expression not stored means seq scan. Fix: Generated stored column or trigger-maintained tsvector.
OR queries explode results - websearch_to_tsquery with many OR terms. Fix: Require minimum rank threshold or AND-heavy defaults.
Case and diacritics - FTS normalizes case; accents depend on dictionary. Fix: unaccent extension or ICU collation for locale-sensitive search.
Substring search - FTS matches lexemes, not LIKE '%foo%'. Fix: pg_trgm for substring; combine in hybrid queries.
Stale vectors after bulk load - Generated columns update automatically; trigger-based ones may not. Fix: Verify maintenance path after ETL.
Elasticsearch envy - Rebuilding inverted index for every schema tweak. Fix: Iterate ranking in SQL before adopting second system.

Alternatives

Alternative	Use When	Don't Use When
Elasticsearch/OpenSearch	Huge scale, advanced relevance	Small team, strong transactional coupling
`pg_trgm` only	SKU prefix, email substring	Stemmed natural language prose
External SaaS (Algolia)	Fast launch, hosted relevance	Data residency blocks third party
Hybrid with pgvector	Semantic + keyword (see hybrid article)	Pure keyword site with no embeddings

FAQs

Is Postgres FTS good enough for SaaS search?

Often yes up to millions of documents with GIN and proper weights. Benchmark p95 before adopting Elasticsearch.

plainto_tsquery vs websearch_to_tsquery?

`plainto_tsquery` ANDs terms. `websearch_to_tsquery` supports quotes and minus terms like web search boxes.

Do I need Elasticsearch for autocomplete?

Not always. `pg_trgm` on prefix queries or materialized prefix tables work for moderate traffic.

How big can GIN indexes get?

Roughly comparable to corpus token count. Monitor with `pg_relation_size`.

Multi-language content?

Use `simple` config plus language column, or per-language `tsvector` columns.

Highlighting in API?

`ts_headline` in SQL or return positions via `tsvector` parsing in application.

Replication lag impact?

FTS reads local indexes on replica; no special lag beyond normal streaming replication.

Managed Postgres FTS limits?

Same engine; check extension allowlist for `unaccent`. No Elasticsearch-style managed sibling on RDS.

Security?

FTS inherits RLS policies on base table. No separate search ACL layer.

Upgrade notes PG 18?

Core FTS stable; watch `websearch_to_tsquery` behavior in release notes for parser tweaks.

tsvector & tsquery - dictionaries and ranking
GIN Indexes for FTS - index build and maintenance
Hybrid Search - combine with pgvector
GIN and GiST - access method overview
Full-Text Search Best Practices - operational checklist

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PostGIS 3.5+, pgbouncer 1.x, and Patroni 3.x.

Full-Text Search Basics

Search Documentation

Full-Text Search Basics