Vacuum Analysis

Vacuum Analysis is a screen that visually reconstructs the Bloating, Dead Tuple, Age, and other information provided by Top Objects from a Vacuum perspective. You can get an at-a-glance view of the Vacuum status of an instance through Summary cards, Top 5 charts, and Vacuum execution history.

• With Vacuum Health Summary, you can quickly understand the overall Vacuum health status of the instance.

• In Table Analysis, you can identify tables that need attention based on Dead Tuple and Bloat Size criteria.

• With Vacuum Execution History, you can check Vacuum execution patterns and history in time series.

Note

What is Vacuum?

Because PostgreSQL uses MVCC (Multi-Version Concurrency Control), existing rows are not immediately deleted when UPDATE or DELETE is performed, but remain as Dead Tuples. Vacuum is a maintenance operation that cleans up these Dead Tuples so disk space can be reused.

If Vacuum is not executed in a timely manner, the following problems can occur:

• Increased table Bloat: Unnecessary space increases, leading to higher disk usage and query performance degradation

• Transaction ID Wraparound: When Transaction Age reaches the limit, a safety mechanism kicks in that automatically stops DB writes

Prerequisites

DB configuration

Grant permissions to the monitoring account so that the agent can query schema information.

-- Grant schema access permission
GRANT USAGE ON SCHEMA {schema_name} TO {DB_User};

-- Grant query permission for existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA {schema_name} TO {DB_User};

-- Automatically grant query permission for tables created in the future
ALTER DEFAULT PRIVILEGES FOR USER {owner_name} IN SCHEMA {schema_name} GRANT SELECT ON TABLES TO {DB_User};

Statement	Description
GRANT USAGE ON SCHEMA	Grants permission to access the schema.
GRANT SELECT ON ALL TABLES IN SCHEMA	Grants query permission for existing tables in the schema.
ALTER DEFAULT PRIVILEGES ... GRANT SELECT	Automatically grants query permission for new tables created in the schema in the future.

Agent configuration

Check the following settings in whatap.conf. Since the default value of pg_object is true, collection occurs without any additional configuration.

pg_object=true

Option	Type	Default	Description
pg_object	boolean	true	Configures whether to collect Top Object information. Default is true.
pg_object_hour	int	5	Configures the collection time for Top Object information. Default is 5, collection begins at 5 AM.

Data collection sources

The data on the Vacuum Analysis screen is collected from PostgreSQL system catalogs and statistics views.

Section	Main Source	Notes
Dead Tuples	pg_class, pg_namespace	Queries tuple count using pg_stat_get_live_tuples() and pg_stat_get_dead_tuples() functions.
Top Bloat Size	pg_class, pg_stats, pg_stat_user_tables	Estimates Bloat size based on column statistics (null_frac, avg_width).
Transaction Age	pg_catalog.pg_database, pg_catalog.pg_settings	Calculates Transaction Age by database using age(datfrozenxid) and compares with the autovacuum_freeze_max_age setting.
Vacuum Execution History	pg_stat_progress_vacuum, pg_stat_activity	Queries the progress, wait events, and query information of running Vacuum.

Analyzing Vacuum

You can analyze the table status and whether maintenance is needed based on Vacuum-related metrics. The key metrics are as follows.

Basic options

In the top filter area, you can set the time and target instance.

• Time: Select the date to query.

• Instance: Select the target DB instance.

Vacuum Health Summary

Provides 3 summary cards with key Vacuum-related metrics for the instance. Dead Tuples and Top Bloat Size are collected once a day (based on the pg_object_hour setting time), and the collection time is displayed in parentheses next to the card title. The Table Analysis data at the bottom is also collected at the same time.

Card	Description
Dead Tuples	Displays total Dead Tuple count and distribution by database.
Top Bloat Size	Displays the largest table Bloat size (MB) and distribution by database.
Transaction Age	Displays the Age of the oldest transaction and the percentage (%) against the total limit. The percentage by database can also be checked.

Dead Tuples

If Dead Tuple counts are high, Vacuum may not be executing in a timely manner. Clicking a database name filters the Table Analysis Dead Tuple Top 5 at the bottom by that database.

Note

When Dead Tuples are high

• Execute VACUUM table_name;

• Dead Tuples cause query performance degradation and increased disk usage.

• Check whether Autovacuum is enabled in settings and whether the threshold is too high.

Top Bloat Size

Bloat refers to a state where a table is excessively large relative to actual data due to Dead Tuples, etc. Large Bloat wastes unnecessary disk space and degrades sequential scan performance. Clicking a database name filters the Table Analysis Bloat Size Top 5 at the bottom by that database.

Note

When Bloat Size is high

• Execute VACUUM FULL table_name;

• VACUUM FULL causes table locking, so it is recommended to perform it outside business hours.

• Severe Bloat causes query performance degradation, index inefficiency, and disk waste.

• Also check Autovacuum settings, UPDATE/DELETE patterns, and whether HOT Update is possible.

• Using pg_repack allows space reclamation without table locking.

Transaction Age

PostgreSQL's Transaction ID can use approximately 2 billion IDs. When Transaction Age approaches this limit, XID Wraparound occurs and the DB may be forcibly shut down to prevent data corruption.

• Agent version v2.59.01 or later is required, and data is collected at 5-second intervals.

Note

When Transaction Age is high

• Execute VACUUM FREEZE database_name;

• If Transaction ID exceeds 2 billion, XID wraparound occurs and the DB may be forcibly shut down to prevent data corruption.

• Be sure to identify and resolve the cause:

  • Long-running transactions
  • Unprocessed replication slots
  • Disabled autovacuum, etc.

Threshold settings

Clicking the Threshold Settings button in the upper right of the screen allows you to set thresholds for Dead Tuples and Bloat Size. When a threshold is set, the currently configured value is displayed at the top, and cards exceeding the threshold have their background color changed so you can quickly recognize abnormal states. To clear the settings, click the Clear Settings button in the threshold settings window.

Metric	Setting	Description
Dead Tuples	Warning ≥	Warning-level Dead Tuple count
	Critical ≥	Critical-level Dead Tuple count
Bloat Size (GB)	Warning ≥	Warning-level Bloat size (GB)
	Critical ≥	Critical-level Bloat size (GB)

• When Warning is exceeded: Card background changes to orange.

• When Critical is exceeded: Card background changes to red.

Table Analysis

Provides Top 5 charts for analyzing Vacuum-related metrics by table.

Dead Tuple Top 5

Displays the top 5 tables with the most Dead Tuples as a horizontal bar chart. Table names are displayed in database.schema.table format.

• Tables with many Dead Tuples are in a state that requires Vacuum.

• Detailed data can be checked using filter and table view toggle buttons.

Bloat Size Top 5

Displays the top 5 tables with the largest Bloat Size as a horizontal bar chart. The unit is bytes.

• For tables with large Bloat Size, consider reclaiming space with VACUUM FULL or pg_repack.

• Detailed data can be checked using filter and table view toggle buttons.

Vacuum Execution History

Displays the Vacuum execution history for the selected period as a time-series chart.

• You can identify patterns of whether Vacuum is being executed periodically or concentrated in specific time periods.

• If there are long intervals without Vacuum execution, Autovacuum settings should be checked.

Vacuum & Analyze History

Clicking a specific time period in the chart displays the Vacuum & Analyze History table at the bottom, where you can check details of Vacuum and Analyze operations performed in that time period (1-hour units).

Column	Description
Time	Vacuum/Analyze execution time
Database	Target database name
Phase	Vacuum execution phase (e.g., scanning heap, vacuuming indexes, etc.)
PID	Process ID that performed Vacuum
Query	Executed Vacuum/Analyze query
Heap Scanned	Number of Heap blocks scanned
Heap Total	Total number of Heap blocks
Heap Vacuumed	Number of Heap blocks processed by Vacuum
Index Vacuum	Number of index Vacuum executions
Max Dead Tuples	Maximum number of Dead Tuples that can be processed at once
Dead Tuples	Number of Dead Tuples found
Runtime	Vacuum execution time
Scanned %	Scan ratio relative to total Heap
Vacuumed %	Vacuum processing ratio relative to total Heap
Wait Event	Wait event name
Wait Event Type	Wait event type

Configuration

Displays Vacuum-related parameters configured for the current DB instance for reference. Only Vacuum-related items among PostgreSQL's various DB parameters are selected and shown, so you can check Autovacuum behavior or I/O cost settings without connecting to the DB separately.

AUTOVACUUM SETTINGS

Parameter	Description
autovacuum	Whether the Autovacuum daemon is enabled
autovacuum_max_workers	Maximum number of Autovacuum worker processes that can run simultaneously
autovacuum_naptime	Autovacuum execution interval (unit: seconds)
autovacuum_vacuum_threshold	Minimum number of Dead Tuples required to trigger Vacuum
autovacuum_vacuum_scale_factor	Ratio of Dead Tuples to table size to trigger Vacuum
autovacuum_vacuum_cost_delay	I/O cost delay time for Autovacuum (unit: milliseconds)
autovacuum_vacuum_cost_limit	I/O cost limit that Autovacuum can accumulate before pausing

Autovacuum executes Vacuum on a table when Dead Tuples of autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × table row count have occurred.

I/O IMPACT SETTINGS

Parameter	Description
vacuum_cost_page_hit	Cost when a page is found in shared buffer
vacuum_cost_page_miss	Cost when a page needs to be read from disk
vacuum_cost_page_dirty	Cost when a page is modified (dirtied)

Data interpretation guide

Key metrics interpretation

Metric	Normal	Warning	Critical
Dead Tuples	Small amount relative to table	Continuously increasing trend	Large accumulation, Vacuum not running
Bloat Size	Below 20% of table size	20–50% of table size	Above 50% of table size
Transaction Age	Below 5%	Above 50%	Above 80% → Immediate action required

Situations requiring action

• Dead Tuples continuously increasing

  Autovacuum cannot keep up with the workload → Lower autovacuum_vacuum_scale_factor, increase autovacuum_max_workers

• Tables with large Bloat Size

  Normal Vacuum does not return space to the OS → Consider VACUUM FULL or pg_repack (Note: VACUUM FULL causes table locking)

• High Transaction Age ratio

  Vacuum cannot clean up old transactions → Check long-running transactions, consider running manual VACUUM FREEZE

• Gaps in Vacuum Execution History

  Autovacuum is disabled or workers are insufficient and cannot run → Check Autovacuum settings

Analysis using Top Objects

Vacuum Analysis is a screen that visually reconstructs the Bloating, Dead Tuple, and Age information provided by Top Objects from a Vacuum perspective. Both screens are based on the same data (collected at pg_object_hour time), but provide different perspectives depending on the analysis purpose.

Comparison Item	Vacuum Analysis	Top Objects
Analysis Perspective	Vacuum maintenance-focused	Object (table/index)-focused
Data Scope	Bloating, Dead Tuple, Age + Vacuum execution history	6 types: Bloating, Scan, DML, Analyze Time, Age, Dead Tuple
Representation	Summary cards + Top 5 charts + time-series history	Sortable table list (Top N)
Unique Features	Vacuum Health Summary, Vacuum execution history, threshold alerts, DB parameter view	Scan/DML analysis, Object Detail, index analysis

Usage guide

Tip

• For the purpose of understanding overall Vacuum status → Quickly check the current situation with Summary cards and Top 5 charts on the Vacuum Analysis screen.

• For detailed analysis of a specific table → Analyze in detail with various tabs and sort criteria on the Top Objects screen.

• When identifying tables that need Vacuum and then analyzing the cause, using both screens together is effective.