vCenter vPostgres autovacuum and WAL: dead tuples and an unbounded pg_wal

The embedded PostgreSQL database in vCenter Server Appliance (vPostgres) stores every managed object, event, task, alarm, and performance stat in the environment. Two internal mechanisms keep that database from consuming its own disk: autovacuum, which reclaims space from deleted and updated rows, and WAL (Write-Ahead Log) management, which controls how transaction logs are written, checkpointed, and recycled.

When either mechanism falls behind, the symptoms are subtle at first. Queries slow down. vpxd task execution takes longer. The /storage/db partition creeps upward. By the time an outside-in probe notices (vSphere Client timing out, vpxd crashing, services refusing to start), the database has been degraded for 30-60 minutes.

Dead tuple accumulation and unbounded pg_wal growth are the two main vPostgres degradation patterns that threaten /storage/db. They have different root causes but converge on the same outcome: /storage/db fills, vPostgres stops accepting writes, and the management plane goes dark. This article covers what to monitor, what the thresholds mean, and what not to change without VMware support guidance.

flowchart TD
    A[vCenter DB writes] --> B[Dead tuples created]
    A --> C[WAL segments written]
    B --> D{Autovacuum}
    D -->|Blocked by idle-in-txn| E[Dead tuples over 20 percent: bloat]
    D -->|Running| F[Space reclaimed]
    C --> G{WAL consumed?}
    G -->|Checkpoint plus VCHA OK| H[Segments recycled]
    G -->|VCHA lag or checkpoint fail| I[pg_wal grows unbounded]
    E --> J["/storage/db fills"]
    I --> J

The autovacuum cycle and dead tuples

Every UPDATE or DELETE in PostgreSQL creates a dead tuple: a row version no longer visible to any transaction but whose physical space has not been reclaimed. Autovacuum is the background process that scans tables, identifies dead tuples, and marks their space as reusable. Without effective autovacuum, dead tuples accumulate. The table file grows even when the live row count is flat, queries must scan past dead space, and disk consumption rises.

vCenter’s workload is delete-heavy. Events, tasks, and historical statistics are continuously inserted and purged on retention schedules. The purge process marks rows as deleted; autovacuum must reclaim that space. If autovacuum cannot keep pace with the deletion rate, the SEAT tables (Stats, Events, Alarms, Tasks) bloat.

The signal that matters is the dead tuple ratio: the percentage of total tuples in a table that are dead. PostgreSQL exposes this through pg_stat_user_tables.

# Check dead tuple ratio on major vCenter tables
/opt/vmware/vpostgres/current/bin/psql -U postgres -d VCDB -c "
SELECT schemaname, 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, last_analyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC LIMIT 20;"

What the ratio tells you:

A dead tuple ratio above 20-30% on major tables means autovacuum is losing ground. The tables bloat, queries slow, and disk grows even when row counts are flat. Above 30% on major tables is where vpxd task latency typically starts to degrade noticeably.

The last_autovacuum column is equally important. If it shows a timestamp hours or days stale on tables with high dead tuple counts, autovacuum is either not triggering or is being blocked. A NULL last_autovacuum means autovacuum has never completed on that table. The most common blocker is a long-running idle-in-transaction session.

What blocks autovacuum: idle-in-transaction sessions

PostgreSQL’s MVCC model means dead tuples cannot be reclaimed until no active transaction can still see them. A single long-running or idle-in-transaction session holds back the transaction ID horizon (the xmin horizon). This blocks autovacuum from cleaning up dead tuples across all tables in the database, not just the ones the session touched. One stuck session can silently halt vacuum progress database-wide.

In vCenter, idle-in-transaction sessions typically come from SDK clients: backup solutions, monitoring tools, orchestration platforms, or custom scripts that open a transaction and then stall. The vpxd process itself can also hold long transactions during heavy operations.

# Find idle-in-transaction and long-running sessions
/opt/vmware/vpostgres/current/bin/psql -U postgres -c "
SELECT pid, now() - xact_start AS xact_duration,
       now() - query_start AS query_duration, state, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start LIMIT 10;"

A session in idle in transaction state for more than a few minutes is suspicious in a vCenter context. Most vCenter operations are short. A session holding a transaction open for an hour or more is almost certainly blocking autovacuum progress.

The fix is to identify and terminate the offending session with pg_terminate_backend(pid). Exercise caution: terminating a vpxd-managed connection can crash vCenter services. Confirm the session belongs to an external SDK client before acting. The root cause is whatever client opened the transaction and left it idle. Without fixing the client behavior, the problem will recur. Correlate the pid and query text with the SDK client responsible.

Unbounded pg_wal growth

The Write-Ahead Log (pg_wal, called pg_xlog in PostgreSQL 9.x and earlier) is a sequence of fixed-size segment files, typically 16 MB each. Every transaction that modifies data writes to the WAL before the changes are applied to the data files. Under normal operation, PostgreSQL recycles WAL segments: once a segment has been checkpointed (its data flushed to disk) and, if applicable, consumed by replication, the file is reused.

Unbounded pg_wal growth means segments are accumulating faster than they can be recycled. This points to one of two causes: checkpoint problems or replication lag.

# Check WAL directory size and segment count
du -sh /storage/db/vpostgres/pg_wal/
ls -la /storage/db/vpostgres/pg_wal/ | wc -l

A growing segment count on a non-VCHA vCenter typically means checkpoints are not completing on schedule. vPostgres uses a custom checkpoint configuration tuned to spread I/O over longer windows than upstream PostgreSQL defaults. If the checkpoint process is starved for I/O (because /storage/db is on a slow or contended datastore) or if the write rate exceeds what checkpoints can absorb, WAL segments pile up.

# Checkpoint frequency and type
/opt/vmware/vpostgres/current/bin/psql -U postgres -c "
SELECT checkpoints_timed, checkpoints_req,
       buffers_checkpoint, buffers_clean, buffers_backend
FROM pg_stat_bgwriter;"

checkpoints_req increasing relative to checkpoints_timed indicates the system is checkpointing more often than configured because WAL volume is reaching max_wal_size before checkpoint_timeout fires. Note that pg_stat_bgwriter counters reset on restart, so compare counts against uptime.

VCHA replication lag as a WAL driver

In vCenter High Availability (VCHA) configurations, WAL segments are streamed from the active node to the passive node via PostgreSQL streaming replication. If the passive node falls behind or the replication network degrades, the active node cannot recycle WAL segments until they have been consumed by the replica. WAL accumulates on the active node’s /storage/db partition.

# Check VCHA replication lag (run on active node)
/opt/vmware/vpostgres/current/bin/psql -U postgres -c "
SELECT client_addr, state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) as replay_lag_bytes,
       pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) as sent_lag_bytes
FROM pg_stat_replication;"

A replay_lag_bytes value growing or sustained above 100 MB means the passive node cannot keep up with WAL replay. The active node holds those segments, consuming /storage/db space. If replication breaks entirely (state is not streaming), WAL accumulates without bound until the partition fills.

The replication network is dedicated and typically uses a separate NIC. Packet loss, MTU mismatches, or bandwidth constraints on this link cause silent degradation. The default wal_sender_timeout of 60 seconds can be too low for VCHA deployments under high CPU load, causing WAL sender processes to time out and replication to stall.

The hand-tuning trap

When autovacuum falls behind, the instinct is to adjust vacuum parameters: increase autovacuum_vacuum_cost_limit, lower autovacuum_vacuum_scale_factor, or add autovacuum workers. Resist this. VMware configures vPostgres with settings tuned to each deployment size (Tiny, Small, Medium, Large, X-Large). Changing max_connections, shared_buffers, or autovacuum parameters without VMware support guidance can produce unpredictable interactions and may affect supportability.

There is one documented exception. Broadcom KB 316343 identifies the default vacuum_cost_limit of 200 as inadequate for busy vCenter environments and recommends setting it to 10000 via ALTER SYSTEM SET vacuum_cost_limit = 10000; followed by SELECT pg_reload_conf();. This is a VMware-sanctioned fix, not arbitrary tuning. Apply it only as documented and only if your symptoms match: autovacuum falling behind, /storage/db filling, vCenter becoming unresponsive. The autovacuum_vacuum_cost_limit setting defaults to -1, meaning it inherits from vacuum_cost_limit, so changing the global value affects autovacuum workers automatically.

If the root cause is an idle-in-transaction session blocking vacuum, no amount of vacuum tuning will fix it. You must find and terminate the blocking session. If the root cause is VCHA replication lag, you must fix the replication network or the passive node. Tuning vacuum parameters in these cases masks the symptom while the underlying problem worsens.

Never manually delete files from pg_wal. PostgreSQL expects to manage those files internally. Removing them can corrupt the database. If disk is full, the safe options are to increase the WAL VMDK size, fix the underlying cause (replication lag, checkpoint tuning), or engage VMware support.

Signals to watch

These signals give 30-60 minutes of warning before an outside-in probe (API timeout, service crash) detects the problem. The dead tuple ratio and WAL segment count are the two earliest indicators. By the time /storage/db utilization crosses 85%, the database is under pressure. vPostgres needs free space for WAL, temporary files, sort operations, and vacuum work. Running at 90% or above risks an unrecoverable state where the database cannot complete the operations needed to reclaim space.

How Netdata helps

Netdata’s PostgreSQL data collector collects the signals that matter for this failure pattern continuously, with per-second granularity:

• Dead tuple ratio per table (pg_stat_user_tables): makes autovacuum falling behind visible as a trend before /storage/db fills.
• WAL segment count and directory size: unbounded growth shows up as a rate anomaly, not just a threshold crossing.
• Connection count vs max_connections: shows whether vpxd or external clients are approaching the connection ceiling, which interacts with autovacuum worker availability.
• Replication lag in bytes (pg_stat_replication): for VCHA, replay lag indicates whether the passive node is keeping up before WAL accumulates on the active node.
• Anomaly detection: ML-based anomaly flags on WAL growth rate, dead tuple ratio, and connection count catch the slow drift that precedes a disk-full event, even when no static threshold has been crossed.

Related guides

• vSphere active vs consumed vs granted memory: why the percentage lies
• vSphere CPU co-stop high (%CSTP): the SMP vCPU co-scheduling penalty
• vSphere CPU limit hit (%MLMTD): the forgotten MHz cap that silently throttles a VM
• vSphere CPU ready time high (%RDY): VMs starved while the guest looks idle
• vSphere datastore full: ‘No space left on device’, paused VMs, and power-on failures
• vSphere datastore IOPS and throughput: spotting storage saturation before latency bites
• vSphere datastore latency high: reading GAVG, DAVG, and KAVG
• vSphere dropped packets (%DRPRX/%DRPTX): ring buffers, CPU, and uplink backpressure
• vSphere DRS not balancing: affinity rules and reservations blocking placement
• vSphere DRS thrashing: vMotion churn with no stable placement
• vSphere storage latency cliff: the ’everything is slow’ incident that hits every VM at once
• vSphere HA host isolation and split-brain: when isolation response goes wrong