诊断并处理 PostgreSQL 表膨胀
依据数据库证据梳理膨胀成因与清理阻碍,提供真空调优、空间回收步骤及监控查询。
完整提示词
<context>
Postgres updates and deletes leave dead row versions that vacuum reclaims for reuse; when autovacuum falls behind, tables and indexes grow, scans slow down, and in the worst case transaction ID wraparound forces an emergency. Autovacuum usually falls behind for reasons other than its settings: a long-running or idle-in-transaction session, an abandoned replication slot, a prepared transaction, or a standby with `hot_standby_feedback` holding back the cleanup horizon so nothing can be removed. Defaults suit small tables: the scale factor triggers vacuum after 20 percent of a table changes, which for a 500 million row table is far too late, so large and hot tables need per-table settings, and the cost limit often throttles workers too hard on modern storage. Plain `VACUUM` rarely shrinks files; reclaiming space needs `VACUUM FULL` (exclusive lock, full rewrite) or an online tool such as pg_repack, and indexes can be rebuilt with `REINDEX CONCURRENTLY` on recent versions. Queue-like tables with constant insert and delete are a classic bloat source and may need a different design.
</context>
<task>
Environment: {{environment}}
<evidence>
{{evidence}}
</evidence>
1. Read the evidence: tables with the most dead tuples and their ratio to live tuples, last vacuum and analyse times, table and index sizes against expected sizes, transaction ID age per database and table, and any long transactions, idle-in-transaction sessions, replication slots or prepared transactions.
2. Diagnose: real bloat versus normal free space, autovacuum not triggering (thresholds), triggering but too slow (cost limits, worker count, I/O), or blocked by the cleanup horizon. Rate wraparound urgency from the ages shown.
3. Name the blockers and how to remove each safely (end or time out sessions, drop or fix an abandoned slot after checking who uses it, review `hot_standby_feedback`), with the risk of each action.
4. Recommend tuning: per-table scale factor and threshold for large or hot tables, cost limit and delay, worker count and memory, `idle_in_transaction_session_timeout`, and any managed-service limits that apply. Show `ALTER TABLE ... SET (...)` statements.
5. Plan cleanup for space already lost: which tables need a rewrite, online versus locking options, disk space needed during the rewrite, index rebuilds, and order of work.
6. Give monitoring queries and alert thresholds for dead tuples, vacuum age, transaction ID age and slot lag.
</task>
<constraints>
- Do not recommend disabling autovacuum or running `VACUUM FULL` on a busy table without stating the exclusive lock and the alternative.
- If the evidence suggests wraparound is close, put that first and say plainly how urgent it is.
- Mark version-specific features and managed-service differences as "to verify".
- Base conclusions on the evidence; list missing queries to run when it is incomplete.
{{> output/uncertainty}}
</constraints>
<output_format>
## Diagnosis
Table: Table | Live | Dead | Size | Last autovacuum | Finding. Then summary with urgency.
## Blockers
Bullets: blocker, evidence, safe action, risk.
## Tuning
SQL and config with reasons.
## Cleanup plan
Numbered steps with lock impact and disk needs.
## Monitoring
Queries and thresholds.
</output_format>可替换内容
- {{environment}}
- {{evidence}}
- {{> output/uncertainty}}
分类与标签
编程开发、PostgreSQL运维、数据库性能、真空清理