Postgres Index Bloat Maintenance Runbook Builder
Build a practical Postgres index bloat diagnosis and maintenance runbook with safe checks, reindex options, monitoring, and rollback planning.
Prompt Template
You are a senior Postgres DBA helping an engineering team plan index bloat maintenance. Build a runbook for: Database version: [Postgres version] Deployment environment: [RDS, Aurora, Cloud SQL, self-hosted, Kubernetes, managed service] Workload pattern: [OLTP, analytics, multi-tenant SaaS, high-write tables, mixed] Symptoms: [slow queries, storage growth, vacuum lag, high write amplification, index scan issues] Tables/indexes of concern: [names, size, row counts, write volume] Available extensions/tools: [pgstattuple, pg_stat_statements, pg_repack, pg_cron, monitoring tool] Maintenance window: [none, short window, weekend, online only] Replication/HA setup: [read replicas, streaming replication, logical replication, failover constraints] Risk tolerance: [low, medium, high] Access level: [read-only, migration role, superuser, managed service permissions] Rollback constraints: [snapshot, PITR, replica rebuild, no downtime] Create: 1. Pre-flight checklist and data to collect before touching indexes 2. Safe diagnostic queries for index size, usage, bloat estimate, and write pressure 3. Decision tree: leave alone, drop unused index, REINDEX CONCURRENTLY, pg_repack, rebuild table, or investigate vacuum 4. Step-by-step maintenance runbook for the chosen option 5. Locking, replication, disk-space, and performance risks 6. Monitoring plan during and after maintenance 7. Rollback and communication plan 8. Follow-up prevention actions: autovacuum tuning, index review cadence, query changes, fillfactor considerations 9. Executive summary for non-DBA stakeholders Favor low-risk online operations unless the inputs justify downtime. Include warnings where managed Postgres services behave differently.
Example Output
# Postgres Index Bloat Runbook
Pre-Flight
- Confirm Postgres version and whether REINDEX CONCURRENTLY is available.
- Take a recent snapshot or verify PITR before maintenance.
- Check free disk space; online rebuilds can temporarily require another copy of the index.
Diagnostic Query
Review pg_stat_user_indexes for idx_scan, pg_relation_size for index size, and write-heavy tables with frequent updates or deletes. If pgstattuple is available, sample suspected indexes during a quiet period.
Decision
If an index is large and unused for 30+ days, propose drop after code-owner review. If it is used and heavily bloated, schedule REINDEX INDEX CONCURRENTLY during low traffic and monitor locks, replica lag, CPU, and disk.
Communication
Risk: temporary IO increase and possible slower writes. Customer impact expected: none if concurrent rebuild completes successfully.
Tips for Best Results
- 💡Provide your Postgres version; index maintenance options changed across versions.
- 💡Include maintenance-window and replica details so the runbook can account for locks, lag, and disk pressure.
- 💡Ask for a decision tree before commands; the right answer may be dropping an unused index, not rebuilding it.
Related Prompts
Code Review Assistant
Get a thorough, senior-level code review with actionable feedback on quality, security, performance, and best practices.
Debugging Detective
Systematically debug errors and unexpected behavior with root cause analysis and fix suggestions.
Code Refactoring Advisor
Transform messy, complex code into clean, maintainable, well-structured code with clear explanations.