GIN Indexes for FTS

GIN indexes make tsvector @@ tsquery fast at scale. Build on a stored vector column, tune fastupdate and gin_pending_list_limit, and plan maintenance after bulk imports.

Recipe

-- Preferred: index stored tsvector column
CREATE INDEX CONCURRENTLY articles_fts_gin
  ON articles USING gin (search_vector);
 
-- Verify index use
EXPLAIN (COSTS OFF)
SELECT id FROM articles
WHERE search_vector @@ plainto_tsquery('english', 'postgresql index');
-- Bitmap Index Scan on articles_fts_gin

When to reach for this: Search latency exceeds your SLO on sequential scans, or EXPLAIN shows Seq Scan on @@ filters.

Working Example

-- Bulk load pattern with deferred indexing
BEGIN;
ALTER TABLE articles DROP CONSTRAINT IF EXISTS articles_pkey;
-- (use UNLOGGED staging table in real pipelines)
TRUNCATE articles RESTART IDENTITY;
 
COPY articles (title, body) FROM '/tmp/articles.csv' CSV HEADER;
 
-- search_vector is GENERATED STORED; rebuild index after bulk
COMMIT;
 
-- Rebuild GIN after large import (faster than incremental for some workloads)
REINDEX INDEX CONCURRENTLY articles_fts_gin;
 
ANALYZE articles;
 
SELECT pg_size_pretty(pg_relation_size('articles_fts_gin')) AS gin_size;

What this demonstrates:

• Generated tsvector stays current during COPY
• REINDEX CONCURRENTLY avoids long write locks post-import
• ANALYZE updates stats for bitmap vs seq scan choice
• Size monitoring prevents disk surprises

Deep Dive

GIN vs GiST for FTS

PostgreSQL docs recommend GIN for tsvector unless write-heavy micro-update workload proves GiST better in benchmarks.

Index Definition Patterns

-- Single column
CREATE INDEX ON docs USING gin (doc_tsv);
 
-- Partial index for published content only
CREATE INDEX docs_published_fts ON docs USING gin (doc_tsv)
  WHERE status = 'published';
 
-- jsonb + fts combined workloads: separate indexes, don't merge

fastupdate and Pending List

-- Per-index fastupdate (PostgreSQL 9.5+)
ALTER INDEX articles_fts_gin SET (fastupdate = on);
 
SHOW gin_pending_list_limit;  -- cluster setting, default 4MB

Pending list batches small inserts before merging into main GIN tree. Large imports may benefit from fastupdate = off during load, then reindex.

Maintenance Commands

-- After bulk import or version upgrade
REINDEX INDEX CONCURRENTLY articles_fts_gin;
 
-- Whole table FTS indexes
REINDEX TABLE CONCURRENTLY articles;
 
-- Bloat check (simplified)
SELECT indexrelid::regclass,
       pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE indexrelid::regclass::text LIKE '%fts%';

Autovacuum Interaction

Heavy UPDATE on text columns regenerates tsvector and churns GIN pages. Tune autovacuum on search tables:

ALTER TABLE articles SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01
);

Gotchas

• Indexing expression without IMMUTABLE guarantee - Some to_tsvector wrappers fail index creation. Fix: Stored generated column or trigger-maintained column.
• CREATE INDEX without CONCURRENTLY - Blocks writes on large tables. Fix: Always CONCURRENTLY in production.
• Forgot ANALYZE - Planner picks seq scan despite index. Fix: ANALYZE after reindex; check enable_bitmapscan.
• OR-heavy queries skip index - BitmapOr of multiple GIN scans still OK; very low selectivity may seq scan. Fix: Raise random_page_cost tuning only after measurement.
• Pending list bloat - Tiny frequent inserts swell pending list. Fix: Periodic VACUUM or gin_clean_pending_list() on supported versions.
• Reindex during peak - REINDEX CONCURRENTLY still consumes I/O. Fix: Schedule maintenance window; throttle parallel workers.

Alternatives

FAQs

Related

• Full-Text Search Basics - FTS overview
• tsvector & tsquery - vector contents
• GIN and GiST - access methods
• REINDEX CONCURRENTLY - online rebuilds
• Full-Text Search Best Practices - bulk import 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.