Full-text search on 50M documents still does seq scan despite GIN index on tsvector. EXPLAIN shows index is unused. What's the root cause and how to force the index?
GIN (Generalized Inverted Index) is optimal for full-text search but planner may avoid it if cost calculation favors seq scan. Check: EXPLAIN SELECT * FROM docs WHERE tsv_col @@ to_tsquery('hello & world'). If seq scan, likely reasons: (1) GIN not created correctly: CREATE INDEX idx_docs_tsv ON docs USING GIN(tsv_col). (2) Statistics old: ANALYZE docs. (3) GIN cost too high; planner underestimates seq scan cost. Check: SHOW gin_pending_list_limit (default 4MB; tune for large batches). Force index: SET enable_seqscan = off; EXPLAIN SELECT * FROM docs WHERE tsv @@ to_tsquery(...). If now uses GIN and is faster, random_page_cost issue. For GIN: ALTER SYSTEM SET gin_fuzzy_search_limit = 50000 to cap fetch from GIN (trading accuracy for speed). Also tune: ALTER SYSTEM SET gin_pending_list_limit = '16MB' to batch updates. Verify index effectiveness: SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes WHERE indexrelname LIKE '%tsv%'. High idx_scan but low idx_tup_fetch means efficient. Also check: SELECT * FROM pg_stat_user_indexes WHERE indexrelname='idx_docs_tsv' AND schemaname='public' to see size and usage.
Follow-up: Explain how GIN indexes work internally. Why is GIN better than B-tree for full-text search?
A GiST index on geometry (PostGIS) is bloated to 50GB while the table is 10GB. Index queries are slow. Why is the index so large and how to rebuild?
GiST (Generalized Search Tree) indexes can bloat due to: (1) Overlapping bounding boxes; GiST stores intermediate nodes as bboxes, overlapping regions cause inefficiency. (2) Not rebuilt after bulk loads; old tuples remain. (3) High fanout; GiST differs from B-tree. Check: SELECT schemaname, indexname, pg_size_pretty(pg_relation_size(schemaname||'.'||indexname)) as size FROM pg_indexes WHERE indexname LIKE '%gist%'. Rebuild: REINDEX INDEX CONCURRENTLY idx_gist_geom (non-blocking). If still large, root cause may be poor spatial clustering or bad data distribution. Optimize: (1) Tune fillfactor: CREATE INDEX idx_gist_geom_new ON table USING GIST(geom_col) WITH (fillfactor = 70) (default 90; lower = less splits, better pack). (2) Cluster table by geometry: CLUSTER table USING idx_gist_geom_old; DROP INDEX idx_gist_geom_old; CREATE INDEX idx_gist_geom ON table USING GIST(geom_col). (3) Check query selectivity: SELECT count(*) FROM table WHERE geom_col && bbox('box here'); if returns many rows, index less useful. (4) Monitor: SELECT * FROM pg_stat_user_indexes WHERE indexname='idx_gist_geom' to track idx_blks_read (fetches). If high, consider alternative spatial index (BRIN for very large datasets, or partitioning).
Follow-up: Explain GiST tree structure and why it's useful for spatial data. When is BRIN better?
Table has 1 trillion rows (time-series data). B-tree index is 500GB. You have a time-range query that could use BRIN. Design BRIN index strategy and show the trade-off vs. B-tree.
BRIN (Block Range Index) is compact; stores min/max per 128-page range. For time-series sorted by timestamp, BRIN is 100-1000x smaller than B-tree while still supporting range queries. Create: CREATE INDEX idx_ts_brin ON events USING BRIN(event_time) WITH (pages_per_range = 128). Query: SELECT * FROM events WHERE event_time >= '2026-04-01' AND event_time < '2026-04-07' uses BRIN to skip ranges outside the range. Trade-offs: (1) BRIN size: 50MB vs. B-tree 500GB. (2) Query speed: BRIN slower than B-tree (must check more rows) but still index-assisted. (3) Maintenance: BRIN much faster to build and maintain. Tuning: Adjust pages_per_range based on data clustering. For highly sorted data (like time-series), pages_per_range=64 is tighter; for random data, pages_per_range=256. Verify: EXPLAIN (ANALYZE) SELECT * FROM events WHERE event_time >= '2026-04-01'. BRIN index shows "Index Only Scan" if covering, or "Index Scan" if not. Monitor: SELECT * FROM pg_stat_user_indexes WHERE indexname='idx_ts_brin'; idx_blks_hit should be low (many ranges scanned). Best practice: Use BRIN for time-series, logs, immutable append-only tables. Use B-tree for frequently updated, random-access tables. For 1T rows: Partition by date (weekly/monthly), BRIN on each partition partition separately (further reduces index size).
Follow-up: Explain BRIN block range concept. Why is BRIN so compact compared to B-tree?
Composite B-tree index on (col1, col2, col3) is 50GB but only col1 is used in queries. Design single-column and multi-column index strategy for your workload.
Composite B-tree indexes are useful for certain query patterns but can bloat if not used effectively. Check usage: SELECT schemaname, indexname, idx_scan, idx_tup_read FROM pg_stat_user_indexes WHERE indexname LIKE 'idx_composite%' ORDER BY idx_scan DESC. If idx_scan is high but only col1 is used in WHERE, the index is overly broad. Analysis: B-tree on (col1, col2, col3) helps: (1) WHERE col1 = X AND col2 = Y AND col3 = Z (all 3 columns). (2) WHERE col1 = X AND col2 = Y (first 2). (3) WHERE col1 = X (first column; can be used). Does NOT help: (1) WHERE col2 = Y (no leading col1; index skipped). (2) WHERE col3 = Z (no leading col1, col2; index skipped). Strategy: (1) Analyze query patterns: SELECT query FROM pg_stat_statements WHERE query LIKE '%SELECT%' ORDER BY calls DESC LIMIT 10. (2) For each query, identify the WHERE clause columns in order. (3) Build indexes matching most common patterns: CREATE INDEX idx_col1 ON table(col1); CREATE INDEX idx_col1_col2 ON table(col1, col2);. (4) Add INCLUDE columns for covered index: CREATE INDEX idx_col1_covered ON table(col1) INCLUDE (col2, col3) (cols 2,3 stored in index but not used for range traversal; faster fetch). (5) Drop unused indexes: SELECT indexname FROM pg_stat_user_indexes WHERE idx_scan = 0 to find dead indexes. Rebuild composite index: REINDEX INDEX CONCURRENTLY idx_composite.
Follow-up: Explain index column order in B-tree. Why does (a,b,c) not help WHERE b = X?
You need to index a JSON array column (array of tags). Regular B-tree fails because JSON is complex. Choose GIN vs. GiST, design the index, and test performance.
JSON/JSONB columns are not directly indexable with B-tree (no natural ordering). Use GIN (preferred) or GiST. For tag search (does JSON contain tag?), GIN is optimal. Create: CREATE INDEX idx_tags_gin ON products USING GIN(tags jsonb_path_ops). (Note: jsonb_path_ops is more efficient than default ops for @> containment). Query: SELECT * FROM products WHERE tags @> '["electronics"]'::jsonb uses the index. If you need range queries or complex operators, GiST: CREATE INDEX idx_tags_gist ON products USING GiST(tags). Test: EXPLAIN (ANALYZE) SELECT * FROM products WHERE tags @> '["electronics"]'. Check stats: SELECT schemaname, indexname, idx_scan, idx_blks_read FROM pg_stat_user_indexes WHERE tablename='products'. GIN vs. GiST trade-off: (1) GIN faster for lookups, larger index. (2) GiST slower but smaller, good for mixed queries. For array of tags, GIN is typical choice. Tune: ALTER SYSTEM SET gin_pending_list_limit = '32MB' for batch inserts. Also: Consider denormalizing tags to separate table if complex queries (JOIN instead of JSONB @>). But for simple tag membership, GIN+jsonb_path_ops is production-standard.
Follow-up: Explain jsonb_path_ops vs. standard jsonb ops in GIN. When is each better?
You're replacing B-tree indexes with BRIN on append-only table to save disk. But one query now takes 10x longer (from 10ms to 100ms). Trade-off acceptable? Design hybrid approach.
BRIN saves disk at the cost of query latency; not always a win for OLTP. Assess: Is 100ms acceptable for your SLA? If not, hybrid approach: Keep B-tree for hot queries, use BRIN for cold queries. Identify query frequency: SELECT query, calls, mean_exec_time FROM pg_stat_statements WHERE query LIKE '%SELECT%' ORDER BY calls DESC. Top 10% of queries likely account for 90% of calls. For top queries, keep B-tree. For rest, BRIN. Example: CREATE INDEX idx_id_btree ON events(id); CREATE INDEX idx_ts_brin ON events USING BRIN(event_time). Query planner will choose best index per query. Tuning for hybrid: (1) For BRIN slow queries, try parallel workers: ALTER TABLE events SET (parallel_workers = 4); ALTER SYSTEM SET max_parallel_workers_per_gather = 4. (2) Partition by hot/cold: Recent data on B-tree (in "hot" partition), older data on BRIN. CREATE TABLE events_recent PARTITION OF events FOR VALUES FROM ('2026-04-01') TO ('2026-04-07'); CREATE TABLE events_archive PARTITION OF events FOR VALUES FROM ('2025-01-01') TO ('2026-04-01'). Create indexes per partition strategy. (3) Monitor impact: Set log_min_duration_statement=100 to log slow queries; identify any regressions. Rollback if 10x latency affects SLA.
Follow-up: Explain partitioning vs. indexing strategy. When would you partition instead of relying on indexes?
Index bloat detected: 30GB index on 10GB table. REINDEX takes 1h and blocks queries. Design an online reindex strategy without downtime.
REINDEX locks table, blocking queries. Online strategies: (1) REINDEX CONCURRENTLY (PG12+): REINDEX INDEX CONCURRENTLY idx_name; slower but non-blocking. Downsides: Still locks briefly for old index drop; two indexes exist during rebuild. (2) Create new index, swap: (a) Create new index: CREATE INDEX idx_name_new ON table(col) (parallel to old). (b) Analyze to build stats: ANALYZE table. (c) Rename: ALTER INDEX idx_name RENAME TO idx_name_old; ALTER INDEX idx_name_new RENAME TO idx_name; DROP INDEX idx_name_old (brief locks during rename). (3) Use pg_repack (extension): pg_repack -d dbname -i idx_name; rebuilds without bloat. (4) Partition and reindex per partition: REINDEX TABLE CONCURRENTLY partition_1; REINDEX TABLE CONCURRENTLY partition_2 (parallel per partition). For 30GB index: (a) First, diagnose bloat: SELECT * FROM pgstattuple_approx('idx_name') to see bloat %. (b) If > 30% bloat: REINDEX CONCURRENTLY, run during off-peak. (c) For frequent reindexing: Lower fillfactor at index creation: CREATE INDEX idx_name ON table(col) WITH (fillfactor = 70) to reduce splits. (d) Set maintenance schedule: Weekly reindex during maintenance window. Monitoring: SELECT indexname, pg_size_pretty(pg_relation_size(schemaname||'.'||indexname)) FROM pg_indexes WHERE schemaname='public' to track index size growth.
Follow-up: Explain index bloat and how dead tuples accumulate. Why does REINDEX CONCURRENTLY solve bloat?