analyze_table_bloat
Identify PostgreSQL table bloat by analyzing dead tuples and free space to determine when VACUUM operations are needed for performance optimization and disk space reclamation.
Instructions
Analyze table bloat using the pgstattuple extension.
Note: This tool analyzes only user/client tables and excludes PostgreSQL system tables (pg_catalog, information_schema, pg_toast). This focuses the analysis on your application's custom tables.
Uses pgstattuple to get accurate tuple-level statistics including:
Dead tuple count and percentage
Free space within the table
Physical vs logical table size
This helps identify tables that:
Need VACUUM to reclaim space
Need VACUUM FULL to reclaim disk space
Have high bloat affecting performance
Requires the pgstattuple extension to be installed: CREATE EXTENSION IF NOT EXISTS pgstattuple;
Note: pgstattuple performs a full table scan, so use with caution on large tables. For large tables, consider using pgstattuple_approx instead (use_approx=true).
Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| table_name | No | Name of the table to analyze (required if not using schema-wide scan) | |
| schema_name | No | Schema name (default: public) | public |
| use_approx | No | Use pgstattuple_approx for faster but approximate results (recommended for large tables) | |
| min_table_size_gb | No | Minimum table size in GB to include in schema-wide scan (default: 5) | |
| include_toast | No | Include TOAST table analysis if applicable |