Index bloat requiring maintenance

Tokens

Latency

Turns

Tool Calls

Schema Calls

Response

vs

I'll help you choose the right approach for handling PostgreSQL index bloat in production while minimizing impact on your workload. ## Quick Recommendation **For PostgreSQL 12+**: Use **REINDEX CONCURRENTLY** for individual indexes **For complex scenarios or table bloat too**: Use **pg_repack** Here's how to handle this safely: ## REINDEX CONCURRENTLY (Recommended for most cases) **Pros:** - Native PostgreSQL feature (no extensions needed) - Non-blocking for reads and writes - Simpler to execute **Cons:** - Takes 2-3x longer than regular REINDEX - Requires 2x disk space temporarily - Can leave invalid indexes if interrupted **Safe execution:** ```sql -- 1. Check your PostgreSQL version (needs 12+) SELECT version(); -- 2. Identify bloated indexes SELECT schemaname, tablename, indexname, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes JOIN pg_index USING (indexrelid) ORDER BY pg_relation_size(indexrelid) DESC; -- 3. Run during off-peak hours REINDEX INDEX CONCURRENTLY your_bloated_index; -- 4. Monitor progress (separate session) SELECT pid, query, state, wait_event_type, wait_event FROM pg_stat_activity WHERE query LIKE '%REINDEX%'; ``` **Important safeguards:** ```sql -- Set statement timeout to prevent runaway operations SET statement_timeout = '2h'; -- Check for invalid indexes after (clean up if REINDEX failed) SELECT indexrelid::regclass, indisvalid FROM pg_index WHERE NOT indisvalid; ``` ## pg_repack (For complex scenarios) **Use when:** - You have PostgreSQL < 12 - You need to rebuild tables too (table bloat) - You want more granular control - You need to rebuild multiple objects in one operation **Installation:** ```bash # Install extension CREATE EXTENSION pgstattuple; -- for bloat checking # Install pg_repack (OS level) # Ubuntu/Debian: sudo apt-get install postgresql-15-repack # RHEL/CentOS: sudo yum install pg_repack15 ``` **Safe execution:** ```bash # 1. Dry run first pg_repack -h localhost -U postgres -d your_db \ --table your_table --dry-run # 2. Run on specific index pg_repack -h localhost -U postgres -d your_db \ --index your_bloated_index \ --no-kill-backend \ --wait-timeout=10 # 3. Run on table (rebuilds all indexes too) pg_repack -h localhost -U postgres -d your_db \ --table your_table \ --no-kill-backend ``` ## Production Safety Checklist **Before starting:** ```sql -- 1. Check available disk space (need 2x index size) SELECT pg_size_pretty(pg_database_size(current_database())) as db_size, pg_size_pretty(sum(pg_relation_size(indexrelid))) as total_index_size FROM pg_stat_user_indexes; -- 2. Identify low-traffic window SELECT date_trunc('hour', now()) as hour, count(*) as connections, sum(xact_commit) as commits FROM pg_stat_database WHERE datname = current_database() GROUP BY 1; -- 3. Set up monitoring CREATE OR REPLACE VIEW index_bloat_monitor AS SELECT schemaname, tablename, indexname, pg_size_pretty(pg_relation_size(indexrelid)) AS size, idx_scan, round(100 * (pgstatindex(indexrelid)).avg_leaf_density, 2) as leaf_density FROM pg_stat_user_indexes WHERE schemaname NOT IN ('pg_catalog', 'information_schema'); ``` **During execution:** ```sql -- Monitor locks (ensure no blocking) SELECT locktype, relation::regclass, mode, granted, pid FROM pg_locks WHERE NOT granted ORDER BY relation; -- Monitor disk I/O SELECT tablename, heap_blks_read, heap_blks_hit, idx_blks_read, idx_blks_hit FROM pg_statio_user_tables; ``` ## Prioritization Strategy ```sql -- Rank indexes by bloat impact WITH index_sizes AS ( SELECT indexrelid, schemaname || '.' || tablename || '.' || indexname as full_name, pg_relation_size(indexrelid) as size_bytes, idx_scan, idx_tup_read FROM pg_stat_user_indexes ) SELECT full_name, pg_size_pretty(size_bytes) as size, idx_scan as scans, CASE WHEN idx_scan = 0 THEN 'UNUSED - Consider dropping' WHEN size_bytes > 1073741824 THEN 'HIGH PRIORITY' -- >1GB WHEN size_bytes > 104857600 THEN 'MEDIUM PRIORITY' -- >100MB ELSE 'LOW PRIORITY' END as priority FROM index_sizes ORDER BY size_bytes DESC; ``` ## My Recommendation 1. **Start with REINDEX CONCURRENTLY** on your top 3-5 largest bloated indexes during your lowest traffic period 2. **One at a time** - don't run multiple concurrent reindexes 3. **Monitor I/O wait** - if you see performance degradation, space them out more 4. **Consider pg_repack** only if you also have significant table bloat (check with `pgstattuple`) Would you like me to create a monitoring script or help you set up an automated bloat detection system?

I'll help you choose the right approach for handling PostgreSQL index bloat in production. ## Quick Recommendation For **PostgreSQL 12+**: Use **REINDEX CONCURRENTLY** - it's built-in and doesn't require external tools. For **PostgreSQL 11 or earlier**, or if you also have table bloat: Use **pg_repack**. ## Comparison ### REINDEX CONCURRENTLY (PostgreSQL 12+) **Pros:** - Built-in, no external dependencies - Doesn't hold exclusive locks (can run during production) - Straightforward syntax **Cons:** - Requires 2x disk space temporarily (builds new index alongside old) - Can't be run inside a transaction block - Slower than regular REINDEX **Usage:** ```sql -- Single index REINDEX INDEX CONCURRENTLY idx_users_email; -- All indexes on a table REINDEX TABLE CONCURRENTLY users; -- System catalog indexes REINDEX SCHEMA CONCURRENTLY public; ``` ### pg_repack **Pros:** - Works on PostgreSQL 9.1+ - Can rebuild both tables AND indexes - Efficiently handles table bloat too - Slightly more space-efficient in some cases **Cons:** - Requires installation (external extension) - Another dependency to maintain - Slightly more complex setup **Usage:** ```bash # Install extension first CREATE EXTENSION pg_repack; # Repack specific table (rebuilds table + indexes) pg_repack -t users -d mydb # Just indexes pg_repack --index idx_users_email -d mydb ``` ## Safe Production Execution ### 1. Check Current Bloat ```sql -- Check index bloat SELECT schemaname, tablename, indexname, pg_size_pretty(pg_relation_size(indexrelid)) as index_size, round(100 * (pg_relation_size(indexrelid) - pg_relation_size(indexrelid::regclass, 'main'))::numeric / NULLIF(pg_relation_size(indexrelid), 0), 2) as bloat_pct FROM pg_stat_user_indexes WHERE pg_relation_size(indexrelid) > 10000000 -- >10MB ORDER BY pg_relation_size(indexrelid) DESC; ``` ### 2. Execute During Low Traffic ```bash # Monitor active connections first psql -c "SELECT count(*) FROM pg_stat_activity WHERE state = 'active';" # Run during maintenance window or low-traffic period REINDEX INDEX CONCURRENTLY idx_users_email; ``` ### 3. Monitor Progress ```sql -- In another session, watch progress SELECT pid, now() - query_start as duration, state, query FROM pg_stat_activity WHERE query LIKE '%REINDEX%' AND pid != pg_backend_pid(); ``` ### 4. Resource Throttling ```sql -- Reduce impact on production SET maintenance_work_mem = '1GB'; -- Adjust based on available RAM SET max_parallel_maintenance_workers = 2; -- Don't overwhelm CPU ``` ## Important Gotchas 1. **Disk Space**: Ensure you have enough free space (>2x the index size) 2. **Long-running queries**: REINDEX CONCURRENTLY waits for conflicting queries to finish 3. **Failures**: If interrupted, the command can leave behind an INVALID index that you must manually drop 4. **Primary keys**: Can be reindexed concurrently, but requires special syntax: ```sql REINDEX INDEX CONCURRENTLY users_pkey; ``` ## My Recommended Workflow ```sql -- 1. Identify bloated indexes \di+ -- List all indexes with sizes -- 2. Prioritize by bloat % and size -- Focus on indexes >30% bloat and >100MB -- 3. Schedule during low-traffic window -- Check with monitoring/metrics -- 4. Run one at a time REINDEX INDEX CONCURRENTLY idx_name_1; -- Wait for completion, verify REINDEX INDEX CONCURRENTLY idx_name_2; -- etc. -- 5. Verify bloat reduction SELECT pg_size_pretty(pg_relation_size('idx_name_1')); ``` ## When to Use pg_repack Instead - You're on PostgreSQL 11 or earlier - You have **both** table and index bloat - You need to rebuild many objects efficiently - You're already using it in your toolchain Would you like me to help you assess your specific indexes or create a maintenance script?

Check `postgresql-index-bloat` to confirm which indexes exceed 30% and prioritize by absolute size using `index-size-size`. An index that's 50% bloated but only 100MB is less urgent than one that's 35% bloated but 10GB. Focus remediation on indexes where both bloat percentage and absolute wasted space are significant—typically those where bloat consumes multiple gigabytes.

Review `postgresql-last-vacuum-age` and `dead-tuple` (n_dead_tup) to determine if bloat is from inadequate vacuuming or just natural fragmentation. If `postgresql-last-vacuum-age` is high (>24 hours on busy tables) and `dead-tuple` counts are elevated, your autovacuum isn't keeping up. Compare `postgresql-vacuum-count` against write volume—if vacuum frequency is low relative to updates/deletes, tune autovacuum settings before considering REINDEX.

Compare `postgresql-index-scans` against `postgresql-index-rel-scans` on bloated indexes to see if queries are actively suffering. Bloated covering indexes that should enable index-only scans will show degraded performance, requiring more page scans than necessary. If scans are infrequent on a bloated index, it's lower priority for immediate maintenance—focus first on indexes with high scan rates where the bloat directly impacts query latency.

Monitor `postgresql-locks` during peak hours to understand baseline lock activity. REINDEX CONCURRENTLY (PostgreSQL 12+) takes longer but doesn't block reads or writes, while pg_repack creates a new copy and swaps it. If you see frequent lock contention already, REINDEX CONCURRENTLY is safer since it won't add significant blocking—but it does hold a ShareUpdateExclusiveLock that prevents schema changes. For PostgreSQL <12, pg_repack is your only online option.

For PostgreSQL 12+, use `REINDEX INDEX CONCURRENTLY idx_name` which is built-in and simpler than pg_repack, though it temporarily doubles disk space for that index. For older versions or if you need table defragmentation too, use pg_repack which requires extension installation but handles both tables and indexes. Schedule during lower traffic windows if possible, and reindex one large index at a time to control I/O impact and disk space requirements.

After maintenance, recheck `postgresql-index-bloat` and `index-size-size` to confirm bloat dropped below 20% and absolute size decreased proportionally. Monitor `postgresql-index-scans` to verify query performance improved—you should see faster index scans and better cache hit rates. If bloat returns quickly (within days), revisit autovacuum tuning—the underlying issue is write-heavy workload overwhelming vacuum, not just accumulated bloat.