vCenter database bloat: SEAT tables, statistics level, and the failed purge job

vCenter Server’s embedded PostgreSQL database (vPostgres) grows continuously. Under normal conditions, internal purge jobs keep the SEAT tables (Stats, Events, Alarms, Tasks) bounded by the configured retention windows. When retention is misconfigured, the statistics level is too high, or the purge job stops running, those tables grow without bound. The result is a slow vCenter, a filling /storage/seat or /storage/db partition, and eventually a vpxd crash when the database can no longer write.

The failure develops over weeks or months. Statistics Level 3 or 4 is often set during a troubleshooting window and never lowered. Performance degrades gradually enough that operators adapt. The first hard symptom is frequently vpxd refusing to start because the database partition is full, or the vSphere Client timing out on every page load.

This article covers the three primary drivers of vCenter database bloat: SEAT table growth from retention misconfiguration, statistics level overcollection, and the failed purge job that silently stops reclaiming space when vpxd is down.

What this means

The vCenter database stores inventory, tasks, events, alarms, and performance statistics in PostgreSQL. The largest growth contributors are the SEAT tables: VPXD_HIST_STAT* (performance statistics rolled up at 5-minute, 30-minute, 2-hour, and daily intervals), VPX_EVENT, and VPX_TASK. These tables are supposed to be bounded by retention settings, with an internal vpxd task that purges old data on a schedule.

The purge job is not a separate cron job or SQL agent task. It runs inside the vpxd process. If vpxd is stopped, crashed, or overloaded, the purge job does not run, and data accumulates. This is the single most common cause of unbounded growth: operators assume a separate scheduler handles cleanup, but the cleanup is embedded in the daemon that the bloat eventually kills.

A VCDB over 100GB is almost always a retention problem regardless of environment size. In a stable environment (no new hosts or VMs being added), growth exceeding 500MB per day is a red flag. The database does not need to be full to cause problems: table bloat from dead tuples slows queries, stats rollup jobs fall behind, and the vSphere Client performance charts show gaps.

flowchart TD
    A[Stats Level 3/4 or long retention] --> B[SEAT tables grow]
    C[vpxd stopped or overloaded] --> D[Purge job does not run]
    D --> B
    B --> E["/storage/seat or /storage/db fills"]
    E --> F[vPostgres cannot write WAL]
    F --> G[vpxd crashes or hangs]
    G --> D

The diagram shows the feedback loop: vpxd going down stops the purge job, which accelerates table growth, which fills the disk, which crashes vpxd again. Breaking this loop requires freeing space and fixing retention before restarting vpxd.

Common causes

CauseWhat it looks likeFirst thing to check
Statistics level 3 or 4VPXD_HIST_STAT* tables dominate database size; growth is steady and highvc.vpx_parameter for stats-related rows, plus the Statistics UI setting
Event retention too longVPX_EVENT and VPX_EVENT_ARG are the largest tables; row count far exceeds 30-day equivalentEvent retention setting in vCenter
Purge job not runningvpxd has been down or restarting; tables grow past retention window with no purge activityvpxd uptime and vpxd log for purge entries
Table bloat from failed autovacuumTable sizes are large but live row counts are low; dead tuple ratio is highpg_stat_user_tables for n_dead_tup vs n_live_tup
SDK client abusevpxd CPU high with FETCH queries scanning stats partitions; misbehaving integration flooding the APIActive SDK sessions and vpxd log for query patterns

Quick checks

These are safe, read-only checks. Run them from an SSH session to the VCSA.

# Check partition usage on the VCSA
df -h

# Check database total size
/opt/vmware/vpostgres/current/bin/psql -U postgres -d VCDB -c "SELECT pg_size_pretty(pg_database_size('VCDB'));"

# Top 20 largest tables by total size (table + indexes)
/opt/vmware/vpostgres/current/bin/psql -U postgres -d VCDB -c "
SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 20;"

# Approximate row counts for SEAT tables (use n_live_tup, not COUNT(*))
/opt/vmware/vpostgres/current/bin/psql -U postgres -d VCDB -c "
SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) as dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
WHERE relname LIKE 'vpxd_hist_stat%' OR relname LIKE 'vpx_event%' OR relname LIKE 'vpx_task%'
ORDER BY n_live_tup DESC LIMIT 20;"

# Check statistics-level related parameters
<!-- TODO: verify whether the effective statistics level (1-4) is queryable from vc.vpx_parameter, or whether it lives in a different table or only in vpxd.cfg. The LIKE '%stats%' match may return maxStatsAgeInDays but not the collection level itself. -->
/opt/vmware/vpostgres/current/bin/psql -U postgres -d VCDB -c "
SELECT * FROM vc.vpx_parameter WHERE name LIKE '%stats%';"

# Check latest sample time per stats level (detects rollup lag)
/opt/vmware/vpostgres/current/bin/psql -U postgres -d VCDB -c "
SELECT 'stat1' as level, max(sample_time) as latest FROM vc.vpxd_hist_stat1
UNION ALL SELECT 'stat2', max(sample_time) FROM vc.vpxd_hist_stat2
UNION ALL SELECT 'stat3', max(sample_time) FROM vc.vpxd_hist_stat3
UNION ALL SELECT 'stat4', max(sample_time) FROM vc.vpxd_hist_stat4;"

# Check vpxd service status
/usr/lib/vmware-vmon/vmon-cli --status vpxd

# Check vpxd log for purge activity
grep -i "purge\|retention\|cleanup" /var/log/vmware/vpxd/vpxd.log | tail -30

Do not run COUNT(*) on the SEAT tables. On multi-gigabyte tables, PostgreSQL performs a full table scan, which is slow and I/O-intensive. Use pg_stat_user_tables.n_live_tup for approximate counts instead.

How to diagnose it

  1. Identify which partition is filling. Run df -h and check /storage/seat and /storage/db. SEAT data may be on a separate partition from the main database files depending on the vSphere version and deployment size.

  2. Identify the largest tables. Run the top-20 table size query. If VPXD_HIST_STAT* tables dominate, the problem is statistics collection. If VPX_EVENT and VPX_EVENT_ARG dominate, the problem is event retention or an event storm.

  3. Check the statistics level. Confirm the level in the vSphere Client under Administration > vCenter Server Settings > Statistics, and cross-check vc.vpx_parameter. Level 1 is the default and generates modest data. Level 3 and 4 generate the most data and are the most common cause of statistics-driven bloat.

  4. Check for purge job activity. Search the vpxd log for purge, retention, or cleanup entries. If there are no recent entries, the purge job is not running. Confirm vpxd has been continuously up: if vpxd has been restarting or was stopped for maintenance, the purge job did not run during that time.

  5. Check for table bloat. Query pg_stat_user_tables for n_dead_tup vs n_live_tup. A dead tuple ratio above 20% means autovacuum is falling behind. A ratio above 30% indicates significant bloat that will slow queries and waste disk space.

  6. Check for rollup lag. Query the latest sample_time per stats level. If the latest sample is more than two intervals behind the current time, the rollup jobs are falling behind. This creates a backlog that increases I/O pressure and degrades current operations.

  7. Check for SDK client abuse. If vpxd CPU is high and the vpxd log shows FETCH queries scanning VPXD_HIST_STAT partitions, a monitoring tool or integration may be querying performance data without a time bound, forcing full table scans across all stats partitions.

Metrics and signals to monitor

SignalWhy it mattersWarning sign
/storage/seat or /storage/db utilizationvPostgres crashes when the partition fills; it cannot write WALAbove 80% is the danger zone; above 90% risks unrecoverable situations
VCDB total sizeGrowth rate indicates whether retention is workingAbove 100GB is almost always a retention problem; above 500MB/day in a stable environment is a red flag
VPXD_HIST_STAT* table sizesStats tables are the largest growth contributor when level is too highGrowing faster than other tables; dominating the top-20 list
VPX_EVENT row countEvent tables should be bounded by retention (default 30 days)Row count far exceeds 30-day equivalent; n_live_tup growing without bound
Dead tuple ratioHigh dead tuples mean autovacuum is not keeping upAbove 20% on any major table; above 30% is significant bloat
vpxd uptimePurge job only runs when vpxd is upFrequent restarts or extended downtime mean purge was not running
Stats rollup lagRollup falling behind creates I/O pressure and stale chartsLatest sample_time more than two intervals behind current time
vpxd CPU during stats windowsStats processing consumes vpxd computeSustained high CPU outside normal 5-minute rollup spikes

Fixes

Reduce the statistics level

If the statistics level is set to 3 or 4, lower it to 1 or 2. Level 1 collects average values at 5-minute intervals and is modest. Level 3 adds peak values at 2-minute intervals. Level 4 collects all counters at 30-second intervals and generates the most data. Most production environments run Level 1 or 2.

Changing the statistics level in vCenter does not immediately purge existing data. The purge job runs on its schedule as a vpxd internal task. Space reclamation begins when the purge job next runs, and the database files may not shrink on disk immediately even after purge, because PostgreSQL reclaims space within tables for reuse rather than returning it to the filesystem.

Reduce event and task retention

The default event retention is 30 days. Increasing event retention beyond 30 days causes significant database growth. If retention is set higher, reduce it to 30 days or lower.

Like the statistics level, changing retention does not immediately purge data. The purge job runs on its schedule. Old data is removed incrementally, not all at once.

Run the purge job or use the bulk purge script

If the purge job has not been running (because vpxd was down) and tables have grown far past the retention window, you may need to force a purge. VMware provides a bulk purge script for tasks, events, and stats that accepts parameters for maximum age in days.

Before running any bulk purge operation, take a file-based backup of the VCSA through the VAMI interface. VM-level snapshots are crash-consistent, not application-consistent, and are not a supported backup method for vCenter.

Address table bloat with VACUUM

After purging old data, the database files may not shrink on disk. PostgreSQL reclaims space within tables for reuse but does not return it to the filesystem without a VACUUM FULL. VACUUM FULL locks the table and requires equivalent free space to complete.

Do not run VACUUM FULL on the entire VCDB without VMware support guidance. VMware configures vPostgres with specific settings for each deployment size. Manual vacuum operations should target specific bloated tables, not the entire database.

Handle a full /storage/seat partition at 100%

When /storage/seat is at 100%, even TRUNCATE and VACUUM may fail because PostgreSQL needs working space to complete these operations. Recovery in this state requires either freeing space by other means (deleting old WAL files, truncating the largest table from a different connection) or expanding the partition.

This is a last-resort recovery procedure. Contact VMware support before attempting it.

Prevention

  • Statistics level check after troubleshooting. Level 3 or 4 is often set during an investigation and never lowered, causing slow database growth over months.
  • vpxd uptime and restart monitoring. The purge job only runs when vpxd is up; frequent restarts mean purge was not running and data accumulated.
  • VCDB growth rate alerting. A stable database that suddenly starts growing indicates a failed purge job or a new event storm; alert on growth exceeding 500MB/day in a stable environment.
  • Event retention at 30 days or lower. VMware’s default is 30 days; increasing it causes significant database growth and can shut down vCenter.
  • Dead tuple ratio monitoring on SEAT tables. A rising ratio means autovacuum is falling behind; catch it before it becomes bloat requiring VACUUM FULL.
  • No manual vPostgres tuning. VMware configures vPostgres with specific settings per deployment size; manual changes to max_connections, shared_buffers, or autovacuum settings may make support refuse to assist.
  • File-based VCSA backups before database maintenance. Use the VAMI backup interface, not VM snapshots, which are crash-consistent only.

How Netdata helps

  • Per-second VCSA disk partition metrics. Netdata monitors each /storage/* mount independently, so you catch /storage/seat or /storage/db filling before vPostgres crashes, even when root / looks healthy.
  • vPostgres internal health signals. Netdata surfaces connection count, dead tuple ratios, and WAL directory size from PostgreSQL’s system views. These give 30 to 60 minutes of warning before a database-related outage.
  • vpxd process metrics. Netdata tracks vpxd CPU, memory, and restart count. A rising restart count means the purge job has been intermittently stopped, which is the root cause of unbounded SEAT table growth.
  • Database size growth rate. Netdata computes the rate of change of VCDB size, so you detect a failed purge job or a new event storm before the partition fills.
  • Correlation across layers. When /storage/seat utilization rises, Netdata correlates it with vpxd CPU, vPostgres query latency, and stats rollup timing. This distinguishes “stats level too high” from “purge job stopped” from “SDK client abuse” without manual log mining.