PostgreSQL Partial Indexes Jsonb FAQ & Answers

Indexes with WHERE clause that index subset of rows. For JSONB: index only rows matching specific JSON criteria. Example: CREATE INDEX ON orders USING GIN ((data -> 'items')) WHERE data->>'status' = 'pending'. Smaller index size (50-90% reduction), faster queries on filtered data, lower maintenance cost.

Use partial indexes when: (1) Queries consistently filter on same JSONB field (e.g., status='active'), (2) Filtered subset <20% of total rows, (3) Frequent queries on specific JSON values, (4) Index size matters (limited disk/memory). Example: orders table with 10M rows, but only 100K 'pending' orders – partial index saves 99% space.

Syntax: CREATE INDEX idx_name ON table_name USING GIN ((jsonb_column -> 'key')) WHERE jsonb_column->>'filter_key' = 'value'. Example: CREATE INDEX idx_active_users ON users USING GIN ((data -> 'preferences')) WHERE data->>'status' = 'active'. Query must match WHERE clause to use index.

Index recent data only: CREATE INDEX ON events USING GIN (data) WHERE created_at > NOW() - INTERVAL '30 days'. Queries on last 30 days use small index. Older data: sequential scan or partition. Automate with pg_cron: drop old partial index, create new monthly. Reduces index size 80-95% for time-series workloads.

Unique partial index for conditional uniqueness: CREATE UNIQUE INDEX ON users ((data->>'email')) WHERE data->>'email' IS NOT NULL. Enforces uniqueness only for non-null emails. Smaller index than full GIN. Upsert: INSERT ... ON CONFLICT ((data->>'email')) WHERE data->>'email' IS NOT NULL DO UPDATE. 30-50% faster than full unique index.

Pitfalls: (1) Query WHERE must match index WHERE exactly (planner won't use index if different), (2) Overly specific WHERE (index too small, poor selectivity), (3) Multiple partial indexes on same column (planner confusion), (4) Forgetting to update queries when adding partial index, (5) Index bloat if filter condition drifts over time.

Expression index on computed JSONB value with partial filter: CREATE INDEX ON products ((data->>'price')::numeric) WHERE (data->>'price')::numeric > 100. Indexes expensive products only. Query: SELECT * FROM products WHERE (data->>'price')::numeric > 100 AND (data->>'price')::numeric < 500. Fast range scan on small index.

Verification: (1) EXPLAIN ANALYZE shows 'Index Scan using idx_name' with Filter matching WHERE, (2) pg_stat_user_indexes: idx_scan increments, (3) pg_index: indpred shows WHERE clause, (4) pg_stat_statements to find queries not using index. Unused partial index: idx_scan = 0, consider dropping.

Partial indexes reduce vacuum overhead: smaller index = faster vacuum. autovacuum triggers when % of indexed rows change (not total rows). Example: partial index on 10% of table triggers autovacuum at lower absolute row count. Monitor: pg_stat_user_tables.n_tup_ins/upd/del vs pg_stat_user_indexes.idx_scan ratio.

Per-tenant partial indexes: CREATE INDEX idx_tenant_123 ON events USING GIN (data) WHERE tenant_id = 123. For 100 tenants: 100 small indexes vs 1 huge index. Trade-off: more indexes (metadata overhead) vs faster tenant queries. Use when: <1000 tenants, tenant queries dominate, each tenant <10% of data. Consider partitioning for >1000 tenants.

Migration steps: (1) CREATE partial index CONCURRENTLY, (2) Verify query plans use new index (EXPLAIN), (3) Monitor pg_stat_user_indexes for both indexes, (4) Drop old full index: DROP INDEX CONCURRENTLY old_idx. CONCURRENTLY prevents locking during creation/drop. Rollback: old index still exists until step 4. Test on replica first.

Indexes with WHERE clause that index subset of rows. For JSONB: index only rows matching specific JSON criteria. Example: CREATE INDEX ON orders USING GIN ((data -> 'items')) WHERE data->>'status' = 'pending'. Smaller index size (50-90% reduction), faster queries on filtered data, lower maintenance cost.

Use partial indexes when: (1) Queries consistently filter on same JSONB field (e.g., status='active'), (2) Filtered subset <20% of total rows, (3) Frequent queries on specific JSON values, (4) Index size matters (limited disk/memory). Example: orders table with 10M rows, but only 100K 'pending' orders – partial index saves 99% space.

Syntax: CREATE INDEX idx_name ON table_name USING GIN ((jsonb_column -> 'key')) WHERE jsonb_column->>'filter_key' = 'value'. Example: CREATE INDEX idx_active_users ON users USING GIN ((data -> 'preferences')) WHERE data->>'status' = 'active'. Query must match WHERE clause to use index.

Index recent data only: CREATE INDEX ON events USING GIN (data) WHERE created_at > NOW() - INTERVAL '30 days'. Queries on last 30 days use small index. Older data: sequential scan or partition. Automate with pg_cron: drop old partial index, create new monthly. Reduces index size 80-95% for time-series workloads.

Unique partial index for conditional uniqueness: CREATE UNIQUE INDEX ON users ((data->>'email')) WHERE data->>'email' IS NOT NULL. Enforces uniqueness only for non-null emails. Smaller index than full GIN. Upsert: INSERT ... ON CONFLICT ((data->>'email')) WHERE data->>'email' IS NOT NULL DO UPDATE. 30-50% faster than full unique index.

Pitfalls: (1) Query WHERE must match index WHERE exactly (planner won't use index if different), (2) Overly specific WHERE (index too small, poor selectivity), (3) Multiple partial indexes on same column (planner confusion), (4) Forgetting to update queries when adding partial index, (5) Index bloat if filter condition drifts over time.

Expression index on computed JSONB value with partial filter: CREATE INDEX ON products ((data->>'price')::numeric) WHERE (data->>'price')::numeric > 100. Indexes expensive products only. Query: SELECT * FROM products WHERE (data->>'price')::numeric > 100 AND (data->>'price')::numeric < 500. Fast range scan on small index.

Verification: (1) EXPLAIN ANALYZE shows 'Index Scan using idx_name' with Filter matching WHERE, (2) pg_stat_user_indexes: idx_scan increments, (3) pg_index: indpred shows WHERE clause, (4) pg_stat_statements to find queries not using index. Unused partial index: idx_scan = 0, consider dropping.

Partial indexes reduce vacuum overhead: smaller index = faster vacuum. autovacuum triggers when % of indexed rows change (not total rows). Example: partial index on 10% of table triggers autovacuum at lower absolute row count. Monitor: pg_stat_user_tables.n_tup_ins/upd/del vs pg_stat_user_indexes.idx_scan ratio.

Per-tenant partial indexes: CREATE INDEX idx_tenant_123 ON events USING GIN (data) WHERE tenant_id = 123. For 100 tenants: 100 small indexes vs 1 huge index. Trade-off: more indexes (metadata overhead) vs faster tenant queries. Use when: <1000 tenants, tenant queries dominate, each tenant <10% of data. Consider partitioning for >1000 tenants.

Migration steps: (1) CREATE partial index CONCURRENTLY, (2) Verify query plans use new index (EXPLAIN), (3) Monitor pg_stat_user_indexes for both indexes, (4) Drop old full index: DROP INDEX CONCURRENTLY old_idx. CONCURRENTLY prevents locking during creation/drop. Rollback: old index still exists until step 4. Test on replica first.