pganalyze VACUUM Advisor

VACUUM Advisor is split into four separate categories:

Bloat - Reduce unnecessary empty space (bloat) in tables
Freezing - Understand how Postgres prevents transaction ID and multixact ID wraparound
Performance - Tune how fast, how efficient, and how often autovacuum is running
Activity - Detailed statistics about all autovacuum and manual VACUUM activity

VACUUM Advisor Insights

The following insights are currently included with pganalyze VACUUM Advisor:

The insights are enabled by default for all your database servers, but alerts need to be enabled via alert policies. You can also utilize Slack notifications to notify you of newly detected insights. Read more details about the Alerts & Check-Up system.

Limitations of VACUUM Advisor

Table bloat estimations are based on heuristics and can be incorrect - use pgstattuple to confirm actual bloat for a table
Index bloat is not yet tracked/estimated (only table bloat)
The "Bloat - Insufficient VACUUM Frequency" insight requires a narrow set of conditions that does not capture all conditions where table bloat can occur (it works best after compacting the table with pg_repack or similar)
VACUUM Advisor does not distinguish between dead row versions and dead line pointers (LP_DEAD), e.g. as caused by HOT updates and on-access pruning
Insights for tuning autovacuum_cost_delay, autovacuum_cost_limit and autovacuum_max_workers are still under development
Email alerts are not yet enabled for VACUUM Advisor insights
In the "VACUUM Blocked by Xmin Horizon" graph, when the assigned time of the xmin horizon is older than the the statistics retention period (35 days), it will use the oldest known statistics collected time. For example, even if the xmin horizon was assigned 60 days ago, the xmin horizon in the graph would show that it was assigned 35 days ago

Couldn't find what you were looking for or want to talk about something specific?
Start a conversation with us →