pg_table_bloat
Estimate table bloat in a PostgreSQL schema by returning dead tuple ratio, last vacuum timestamps, and total size. Identify tables that need VACUUM using estimate, approx, or exact methods.
Instructions
Estimate table bloat (dead tuples + free space) for tables in a schema. Returns live tuples, dead tuples, dead-tuple ratio, last_vacuum / last_autovacuum timestamps, and total relation size. A high dead_ratio with a stale last_autovacuum is a sign a table needs VACUUM.
Three methods are available via the method parameter:
estimate(default): reads pg_stat_user_tables -- fast, no extensions, ANALYZE-driven approximations. Use this first.approx: uses pgstattuple_approx() -- fast sampling pass, more accurate than estimates, requires the pgstattuple extension.exact: uses pgstattuple() -- full table scan, exact counts, slow on large tables, requires the pgstattuple extension. Always passschemawith method='exact' -- scanning all user tables in one statement will hit statement_timeout on non-trivial databases. Install pgstattuple withCREATE EXTENSION pgstattuple(requires superuser).
Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| limit | No | Max rows to return (default 50). | |
| method | No | Bloat measurement method. 'estimate' (default) uses pg_stat_user_tables (fast, no extensions). 'approx' uses pgstattuple_approx() (fast sampling, more accurate). 'exact' uses pgstattuple() (full scan, exact but slow). Both 'approx' and 'exact' require the pgstattuple extension. | estimate |
| schema | No | Limit to one schema. If omitted, all user schemas are included. | |
| minDeadRatio | No | Minimum dead-tuple fraction to include - dead / (live + dead). Default 0.1 = 10%. |