Architecture and operating model
Under PostgreSQL MVCC, UPDATE and DELETE do not immediately remove old row versions from the file. VACUUM marks space reusable, updates the visibility map and freezes tuples against transaction-ID wraparound. The autovacuum launcher selects work, worker processes maintain tables, and ANALYZE separately refreshes planner statistics.
The default vacuum trigger is approximately base threshold plus scale factor times table tuples. A small percentage on a very large table can leave millions of dead tuples; a heavily updated table may need a lower per-table scale factor through reloptions. Very low thresholds on small tables can add unnecessary work. Freeze settings are a data-safety lifecycle, not merely bloat tuning.
Do not diagnose bloat from n_dead_tup alone; statistics are estimates and their reset time matters. Long transactions, replication slots or hot_standby_feedback can prevent cleanup. VACUUM FULL can shrink the file but requires stronger locking and extra space; it does not replace routine autovacuum tuning.
- 1UPDATE/DELETE creates old row version
- 2Autovacuum threshold and worker selection
- 3VACUUM cleanup, visibility and freeze
- 4Space reuse and planner statistics
Design parameters
- Trigger threshold
- Calculate threshold + scale factor as a row count from table size and change rate.
- Worker and I/O budget
- Plan maximum workers, cost limit/delay and maintenance I/O alongside other maintenance work.
- Long transactions
- Oldest xmin age and replication feedback can be the true limit on dead-tuple cleanup.
- Freeze margin
- Alert on age(relfrozenxid) with enough time before autovacuum_freeze_max_age for controlled maintenance.
Worked example
For a 500-million-row table, base threshold 50 and scale factor 0.2 can trigger near 100,000,050 changes. At 25 million updates per day, this approaches four days. A table-specific 0.01 scale factor triggers near 5,000,050 changes; measure worker duration and I/O impact in a pilot.
If autovacuum removes two million dead tuples per minute while the workload creates three million, backlog grows. Starting more frequently alone is insufficient; correlate worker sharing, index cost, long transactions and storage latency.
Example commands: replace lab values and confirm permissions and software versions before use.
SELECT relname,n_live_tup,n_dead_tup,last_autovacuum,autovacuum_count
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 20;
SELECT datname,age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;
SELECT * FROM pg_stat_progress_vacuum;
ALTER TABLE public.events SET (autovacuum_vacuum_scale_factor=0.01, autovacuum_vacuum_threshold=5000);
VACUUM (VERBOSE, ANALYZE) public.events;Troubleshooting
| Observation | Likely cause / distinction | Verification |
|---|---|---|
| n_dead_tup rises continuously | Worker capacity may be insufficient, vacuum blocked or threshold too broad. | Correlate pg_stat_user_tables, pg_stat_progress_vacuum and long transactions/slots. |
| VACUUM runs but file does not shrink | Normal VACUUM makes space reusable inside the table rather than returning it to the OS. | Observe table size, free space and later writes; do not run VACUUM FULL without assessing lock impact. |
| Wraparound warning appears | Freeze progress may be blocked or workload exceeds maintenance capacity. | Inspect age(relfrozenxid), oldest xmin, autovacuum logs and progress views. |
Acceptance checks
- Record statistics reset time and measure table change rate.
- Calculate actual trigger row counts for the largest and hottest tables.
- Check long, prepared transactions and replication slots.
- Use log_autovacuum_min_duration to observe duration and skipped-work causes.
- Pilot a per-table reloptions change for a full workload cycle.
- Compare freeze age, query latency, WAL and storage latency after the change.
Related concepts
Transaction boundary
A transaction groups operations that an application treats as jointly successful or failed. Database atomicity does not automatically include email delivery or changes in other services. Define the commit point and retry behavior. Evaluate idempotency to prevent processing the same request twice. Test connection loss before and after commit separately: a client-side error does not always mean the transaction failed.
Telemetry and time correlation
Telemetry combines logs, metrics and events that explain system behavior. A log describes an event, a metric shows behavior over time, and a distributed trace follows a request across components. Clock differences can make one event appear to occur at several times. Use synchronized clocks, reliable source identifiers and consistent time-zone handling. Alarm design should consider duration and user impact alongside thresholds. Monitor gaps in collection separately: absence of logs must not be interpreted as absence of incidents.
Capacity and usable headroom
Raw capacity is not the capacity available to applications. RAID or erasure coding, filesystems, reserved space, metadata, snapshots and growth headroom are separate deductions. TB and TiB representations also change the displayed number. Write calculations with units, establish protected usable capacity, then subtract operating reserves. Track growth rate as well as current utilization. The projected exhaustion date should leave enough time to procure and deploy additional capacity.
Latency distribution
Latency is the time between starting an operation and receiving its result. An average can hide a small number of very slow operations; medians and p95/p99 percentiles answer different questions. Network RTT, storage waits, processor queues and application processing contribute to end-to-end time. State whether measurements come from the client or server. Check whether increases coincide with traffic growth, maintenance or capacity limits. Record normal and peak-hour baselines before selecting an alert threshold.
Durability and power loss
Survival of committed data depends on write guarantees across the database, OS, filesystem, controller and disks. Do not disable safety settings for speed without understanding caches and flush behavior. Power-loss-protected storage helps but does not prove correctness of the entire chain. Run failure and recovery tests in a controlled lab, not production. Validate application records and supported consistency checks rather than merely opening a sample file.
Transaction logs and recovery chains
WAL, redo and transaction-log mechanisms record changes in order for durability and recovery, with product-specific details. Data copies and logs must belong to compatible timelines and chains. Having log files alone does not prove point-in-time recovery: a valid base backup and the required uninterrupted log range are also needed. Measure archival lag, log volume and replay time. Do not delete logs arbitrarily to regain space; use supported retention and cleanup methods.
Primary documentation
Prepared by the Doz Teknoloji technical team using the primary references below. Calculations and lab scenarios state their assumptions; validate the applicable product version before rollout.