Full-Text Search Best Practices

Q: Multi-language?

Separate tsvector per language or simple plus language-specific embedding models.

Q: Substring needs?

Add pg_trgm index alongside GIN; expose different search modes in API.

How to Use This List

Apply before shipping user-facing search to production.
Re-run after bulk content imports or dictionary changes.
Pair with hybrid search checklist if pgvector is enabled.

A - Schema and Vectors

Store tsvector in a column, not runtime to_tsvector() in WHERE. Generated STORED or trigger-maintained.
Weight title higher than body. setweight A for title, B for body, C for tags.
Pick config per content type. english for prose; simple for SKUs and error codes.
Keep content and vector in sync. Single transaction updates text and any derived fields.
Add tenant or ACL columns before indexes. Filter early in hybrid and FTS queries.

B - Indexes

Default to GIN on tsvector. GiST only after write-heavy benchmark proves benefit.
Build with CREATE INDEX CONCURRENTLY on large tables. Avoid production write locks.
Reindex after bulk imports. REINDEX INDEX CONCURRENTLY or rebuild during maintenance window.
Run ANALYZE after reindex. Planner needs fresh stats for bitmap scans.
Partial indexes for published-only rows. Shrinks index when drafts are numerous.

C - Queries and UX

Never concatenate user input into tsquery strings. Use plainto_tsquery or websearch_to_tsquery.
Cap OR fanout. Require minimum ts_rank or limit OR terms from user parsers.
Provide snippets with ts_headline. Improves result UX without app-side parsing.
Order by rank then recency. ORDER BY rank DESC, published_at DESC breaks ties sensibly.
Log zero-hit queries. Feed synonym dictionary and content gap analysis.

D - Operations

Monitor GIN index size and idx_scan. Flat idx_scan with slow queries means missing ANALYZE or wrong config.
Tune autovacuum on churny search tables. Text updates regenerate vectors and GIN pages.
Document dictionary and synonym changes. Requires reindex or rebuild vectors in some cases.
Benchmark before Elasticsearch. Prove Postgres FTS misses SLO with realistic concurrency.
Test RLS policies with search API role. FTS inherits table policies; verify tenant isolation.

FAQs

When to reindex?

After bulk COPY import, major version upgrade, or dictionary file changes affecting stemming.

fastupdate on or off?

On for steady trickle inserts; consider off during huge load then reindex.

Hybrid search weights?

Document starting weights; evaluate recall@10 quarterly on golden queries.

Multi-language?

Separate tsvector per language or `simple` plus language-specific embedding models.

Substring needs?

Add `pg_trgm` index alongside GIN; expose different search modes in API.

Staging data volume?

Use representative corpus size; empty staging hides seq scan mistakes.

Connection poolers?

pgbouncer transaction mode OK; search is read-heavy SELECT workload.

Read replicas?

Route search reads to replicas; accept replication lag for near-real-time indexes.

OpenSearch escape hatch?

Document ADR criteria before adoption; dual-write is expensive.

CI checks?

Migration lint ensures generated column and GIN index created together.

Full-Text Search Basics - FTS vs Elasticsearch ADR
GIN Indexes for FTS - build and maintain
tsvector & tsquery - ranking and dictionaries
Hybrid Search - pgvector combination
Index Design Best Practices - general index hygiene

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 Best Practices

How to Use This List

Apply before shipping user-facing search to production.
Re-run after bulk content imports or dictionary changes.
Pair with hybrid search checklist if pgvector is enabled.

A - Schema and Vectors

Store tsvector in a column, not runtime to_tsvector() in WHERE. Generated STORED or trigger-maintained.
Weight title higher than body. setweight A for title, B for body, C for tags.
Pick config per content type. english for prose; simple for SKUs and error codes.
Keep content and vector in sync. Single transaction updates text and any derived fields.
Add tenant or ACL columns before indexes. Filter early in hybrid and FTS queries.

B - Indexes

Default to GIN on tsvector. GiST only after write-heavy benchmark proves benefit.
Build with CREATE INDEX CONCURRENTLY on large tables. Avoid production write locks.
Reindex after bulk imports. REINDEX INDEX CONCURRENTLY or rebuild during maintenance window.
Run ANALYZE after reindex. Planner needs fresh stats for bitmap scans.
Partial indexes for published-only rows. Shrinks index when drafts are numerous.

C - Queries and UX

Never concatenate user input into tsquery strings. Use plainto_tsquery or websearch_to_tsquery.
Cap OR fanout. Require minimum ts_rank or limit OR terms from user parsers.
Provide snippets with ts_headline. Improves result UX without app-side parsing.
Order by rank then recency. ORDER BY rank DESC, published_at DESC breaks ties sensibly.
Log zero-hit queries. Feed synonym dictionary and content gap analysis.

D - Operations

Monitor GIN index size and idx_scan. Flat idx_scan with slow queries means missing ANALYZE or wrong config.
Tune autovacuum on churny search tables. Text updates regenerate vectors and GIN pages.
Document dictionary and synonym changes. Requires reindex or rebuild vectors in some cases.
Benchmark before Elasticsearch. Prove Postgres FTS misses SLO with realistic concurrency.
Test RLS policies with search API role. FTS inherits table policies; verify tenant isolation.

FAQs

When to reindex?

After bulk COPY import, major version upgrade, or dictionary file changes affecting stemming.

fastupdate on or off?

On for steady trickle inserts; consider off during huge load then reindex.

Hybrid search weights?

Document starting weights; evaluate recall@10 quarterly on golden queries.

Multi-language?

Separate tsvector per language or `simple` plus language-specific embedding models.

Substring needs?

Add `pg_trgm` index alongside GIN; expose different search modes in API.

Staging data volume?

Use representative corpus size; empty staging hides seq scan mistakes.

Connection poolers?

pgbouncer transaction mode OK; search is read-heavy SELECT workload.

Read replicas?

Route search reads to replicas; accept replication lag for near-real-time indexes.

OpenSearch escape hatch?

Document ADR criteria before adoption; dual-write is expensive.

CI checks?

Migration lint ensures generated column and GIN index created together.

Full-Text Search Basics - FTS vs Elasticsearch ADR
GIN Indexes for FTS - build and maintain
tsvector & tsquery - ranking and dictionaries
Hybrid Search - pgvector combination
Index Design Best Practices - general index hygiene

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 Best Practices

Search Documentation

Full-Text Search Best Practices