PostgreSQL Jsonb Fulltext Search FAQ & Answers

Query types: (1) Plain text: plainto_tsquery('wireless headphones') - converts to 'wireless' & 'headphones', (2) Phrase search: phraseto_tsquery('exact phrase') - matches exact phrase, (3) Advanced queries: to_tsquery('(wireless | bluetooth) & headphones & !expensive') - boolean operators, (4) Web search syntax: websearch_to_tsquery('"noise cancelling" OR wireless -wired') - Google-like syntax (PostgreSQL 11+). Ranking results: SELECT *, ts_rank(description_fts, plainto_tsquery('phone')) as relevance FROM products WHERE description_fts @@ plainto_tsquery('phone') ORDER BY relevance DESC; - ranks by term frequency. Advanced ranking: ts_rank_cd() considers document length normalization.

Multilingual (2025 production pattern): Store language in JSONB: ALTER TABLE articles ADD COLUMN content_fts tsvector GENERATED ALWAYS AS (to_tsvector(COALESCE(data->>'language', 'english')::regconfig, data->>'content')) STORED; - uses language-specific dictionaries (english, spanish, french, german, etc.). Production configuration (postgresql.conf): default_text_search_config = 'pg_catalog.english', max_words_per_query = 6 (prevent resource exhaustion). Performance (2025 benchmarks): GIN index supports 100K+ documents with <50ms search time, 1M+ documents with <200ms. Index size: ~30-40% of tsvector column size.

Gotchas: (1) tsvector strips JSON structure - searches only values (no key names), (2) Stemming changes words ('running' → 'run'), (3) Stop words ignored ('the', 'and'), (4) Case-insensitive by default. Recommendations: (1) Use generated tsvector column for production (cleaner, better stats), (2) Index multi-field search vector for comprehensive results, (3) Use websearch_to_tsquery() for user-facing search (intuitive syntax), (4) Rank results by ts_rank() for relevance, (5) Monitor index size with pg_relation_size(), (6) Use language-specific dictionaries for international apps. Alternative for structure-aware search: WHERE data @@ '$.description like_regex "wireless" flag "i"' using jsonb_path_query - preserves JSON structure but no stemming/ranking.

Query types: (1) Plain text: plainto_tsquery('wireless headphones') - converts to 'wireless' & 'headphones', (2) Phrase search: phraseto_tsquery('exact phrase') - matches exact phrase, (3) Advanced queries: to_tsquery('(wireless | bluetooth) & headphones & !expensive') - boolean operators, (4) Web search syntax: websearch_to_tsquery('"noise cancelling" OR wireless -wired') - Google-like syntax (PostgreSQL 11+). Ranking results: SELECT *, ts_rank(description_fts, plainto_tsquery('phone')) as relevance FROM products WHERE description_fts @@ plainto_tsquery('phone') ORDER BY relevance DESC; - ranks by term frequency. Advanced ranking: ts_rank_cd() considers document length normalization.

Multilingual (2025 production pattern): Store language in JSONB: ALTER TABLE articles ADD COLUMN content_fts tsvector GENERATED ALWAYS AS (to_tsvector(COALESCE(data->>'language', 'english')::regconfig, data->>'content')) STORED; - uses language-specific dictionaries (english, spanish, french, german, etc.). Production configuration (postgresql.conf): default_text_search_config = 'pg_catalog.english', max_words_per_query = 6 (prevent resource exhaustion). Performance (2025 benchmarks): GIN index supports 100K+ documents with <50ms search time, 1M+ documents with <200ms. Index size: ~30-40% of tsvector column size.

Gotchas: (1) tsvector strips JSON structure - searches only values (no key names), (2) Stemming changes words ('running' → 'run'), (3) Stop words ignored ('the', 'and'), (4) Case-insensitive by default. Recommendations: (1) Use generated tsvector column for production (cleaner, better stats), (2) Index multi-field search vector for comprehensive results, (3) Use websearch_to_tsquery() for user-facing search (intuitive syntax), (4) Rank results by ts_rank() for relevance, (5) Monitor index size with pg_relation_size(), (6) Use language-specific dictionaries for international apps. Alternative for structure-aware search: WHERE data @@ '$.description like_regex "wireless" flag "i"' using jsonb_path_query - preserves JSON structure but no stemming/ranking.

Full-Text Search (FTS) on JSONB requires converting JSON values to tsvector (PostgreSQL's full-text search data type). Approach 1 (Expression index, quick start): CREATE INDEX idx_fts ON products USING GIN (to_tsvector('english', data->>'description')); Query: WHERE to_tsvector('english', data->>'description') @@ plainto_tsquery('wireless headphones');. Drawback: Query must match exact index expression (including language). Converts JSON text to searchable tsvector format with stemming and stop word removal.

Full-Text Search (FTS) on JSONB requires converting JSON values to tsvector (PostgreSQL's full-text search data type). Approach 1 (Expression index, quick start): CREATE INDEX idx_fts ON products USING GIN (to_tsvector('english', data->>'description')); Query: WHERE to_tsvector('english', data->>'description') @@ plainto_tsquery('wireless headphones');. Drawback: Query must match exact index expression (including language). Converts JSON text to searchable tsvector format with stemming and stop word removal.

Approach 2 (Generated column, recommended for production): ALTER TABLE products ADD COLUMN description_fts tsvector GENERATED ALWAYS AS (to_tsvector('english', data->>'description')) STORED; CREATE INDEX idx_description_fts ON products USING GIN (description_fts);. Benefits: (1) Cleaner queries (WHERE description_fts @@ plainto_tsquery('phone')), (2) Query planner has statistics on generated column, (3) Can add CHECK constraints, (4) Automatic updates when JSONB changes. Multi-field search: ALTER TABLE products ADD COLUMN search_vector tsvector GENERATED ALWAYS AS (to_tsvector('english', COALESCE(data->>'title', '') || ' ' || COALESCE(data->>'description', ''))) STORED; - searches title, description as single index.

Approach 2 (Generated column, recommended for production): ALTER TABLE products ADD COLUMN description_fts tsvector GENERATED ALWAYS AS (to_tsvector('english', data->>'description')) STORED; CREATE INDEX idx_description_fts ON products USING GIN (description_fts);. Benefits: (1) Cleaner queries (WHERE description_fts @@ plainto_tsquery('phone')), (2) Query planner has statistics on generated column, (3) Can add CHECK constraints, (4) Automatic updates when JSONB changes. Multi-field search: ALTER TABLE products ADD COLUMN search_vector tsvector GENERATED ALWAYS AS (to_tsvector('english', COALESCE(data->>'title', '') || ' ' || COALESCE(data->>'description', ''))) STORED; - searches title, description as single index.