DEV Community

Cover image for PostgreSQL VACUUM and Bloat: Tuning autovacuum Correctly
Mustafa ERBAY
Mustafa ERBAY

Posted on Originally published at mustafaerbay.com.tr

PostgreSQL VACUUM and Bloat: Tuning autovacuum Correctly

#postgres #guide #software

In PostgreSQL, UPDATE and DELETE do not immediately remove the old row version from its disk page. Multi-Version Concurrency Control (MVCC) requires that version to remain available until no transaction can see it; only then does it become a dead tuple. If autovacuum falls behind the workload, dead tuples can cause table and index bloat, extra disk I/O, and lower cache efficiency. If the same maintenance backlog also prevents old XIDs from being frozen, transaction ID wraparound becomes a separate risk.

PostgreSQL MVCC Architecture and Table Bloat Mechanism

PostgreSQL uses the MVCC mechanism to ensure that concurrent read and write operations do not block each other. Each row (tuple) carries system fields called xmin and xmax in its header information. When a row is updated, the current transaction ID (XID) is written to the xmax field of the existing row, and the new data is inserted into the page as a new tuple. In a delete operation, only the xmax value is updated.

+-----------------------------------------------------------------------+
| Disk Page (Page Buffer - 8KB)                                         |
|                                                                       |
|  [Tuple 1: xmin=100, xmax=105] ---> Dead Tuple                        |
|  [Tuple 2: xmin=105, xmax=0]   ---> Live Tuple                        |
|  [Tuple 3: xmin=101, xmax=108] ---> Dead Tuple                        |
+-----------------------------------------------------------------------+
Enter fullscreen mode Exit fullscreen mode

Data read queries decide which tuple version they can see based on their own transaction IDs. Old rows that are no longer visible to any active transaction become dead. The VACUUM process does not immediately return the space occupied by these dead tuples to the operating system; instead, it records these spaces in the "Free Space Map" (FSM) structure within the database, making them available for new INSERT or UPDATE operations.

Standard VACUUM (without FULL) takes a SHARE UPDATE EXCLUSIVE (ShareUpdateExclusiveLock) table lock. Normal SELECT, INSERT, UPDATE, and DELETE traffic can continue, but another VACUUM and conflicting schema changes cannot run concurrently. Standard VACUUM may briefly request ACCESS EXCLUSIVE while truncating completely empty pages from the end of a table. In contrast, VACUUM FULL rewrites the table and requires ACCESS EXCLUSIVE for the operation. Prefer frequent standard VACUUM for routine maintenance; use VACUUM FULL only after planning for its lock and temporary disk-space requirements.

Working Logic and Mathematics of Autovacuum Parameters

The autovacuum launcher periodically scans databases and starts workers for eligible tables. In PostgreSQL 18, the UPDATE/DELETE vacuum threshold is calculated as follows:

vacuum threshold = min(
  autovacuum_vacuum_max_threshold,
  autovacuum_vacuum_threshold
    + autovacuum_vacuum_scale_factor * pg_class.reltuples
)
Enter fullscreen mode Exit fullscreen mode

Setting autovacuum_vacuum_max_threshold = -1 disables the cap. PostgreSQL 18 defaults are autovacuum_vacuum_threshold = 50, autovacuum_vacuum_scale_factor = 0.2, and autovacuum_vacuum_max_threshold = 100000000. Insert-only activity has a separate trigger based on autovacuum_vacuum_insert_threshold and autovacuum_vacuum_insert_scale_factor. Check the documentation for your PostgreSQL major version because the available parameter set can differ.

For example, to trigger autovacuum with default settings on a table with 10 million rows:

50 + (0.20 * 10,000,000) = an estimated 2,000,050 dead tuples
Enter fullscreen mode Exit fullscreen mode

This result remains below the default 100-million cap. However, reltuples and the change counters are estimates maintained by an eventually consistent statistics system, and the launcher checks tables in rounds rather than continuously. Treat this as an approximate decision threshold, not an exact row count or start time.

ℹ️

The autovacuum_max_workers value is 3 by default. However, when there are multiple large tables that need to be cleaned in the system, these workers share the I/O cost limits collectively.

A cost-based mechanism is used to limit the I/O load of the cleanup process on the system:

  • autovacuum_vacuum_cost_limit: The total cost limit that workers can reach.
  • autovacuum_vacuum_cost_delay: The pause duration of the worker in milliseconds when the cost limit is reached.
  • vacuum_cost_page_hit: The cost of a page found in the cache (shared_buffers) (default: 1).
  • vacuum_cost_page_miss: The cost of a page read from disk (default: 2).
  • vacuum_cost_page_dirty: The disk write cost of a page modified because a dead tuple was deleted (default: 20).

The default for autovacuum_vacuum_cost_limit is -1, which makes autovacuum use the regular vacuum_cost_limit; that setting defaults to 200. When multiple autovacuum workers run, the global limit is distributed among them. An effective value of 200 may be inadequate for some write-heavy workloads, but there is no universal replacement value: choose it from observed disk latency and write throughput.

Global and Per-Table Autovacuum Tuning

Not all tables have the same access and write patterns. Applying the same autovacuum rules to large log or financial transaction tables as to static reference tables leads to inefficient use of system resources. To solve this problem, after configuring the server-wide postgresql.conf parameters, custom adjustments should be made for critical tables.

The following block is not a universal recommendation. It is a starting example that must be adapted after recording effective settings, I/O latency, worker saturation, and the available RAM budget:

# postgresql.conf configuration

# Enable the autovacuum service
autovacuum = on

# Maximum number of workers to run concurrently
autovacuum_max_workers = 4

# Reduce the default trigger rates of the processes
autovacuum_vacuum_scale_factor = 0.05    # Trigger at 5% dead tuples
autovacuum_analyze_scale_factor = 0.02   # Run ANALYZE at 2% change

# Increase the I/O budget only after measurements justify it
autovacuum_vacuum_cost_limit = 2000
autovacuum_vacuum_cost_delay = 2ms

# Per-autovacuum-worker ceiling; account for worker count in the RAM budget
autovacuum_work_mem = 512MB
Enter fullscreen mode Exit fullscreen mode

For frequently updated tables or tables with millions of rows, table-based parameters with fixed or much lower rates should be defined instead of a proportional scale factor:

-- Custom autovacuum settings for a high-volume table with millions of rows
ALTER TABLE siparis_hareketleri SET (
    autovacuum_vacuum_scale_factor = 0.01,   -- Trigger at 1% change
    autovacuum_vacuum_threshold = 10000,     -- Start at a minimum of 10,000 dead tuples
    autovacuum_vacuum_cost_limit = 3000,      -- Higher I/O limit for this table
    autovacuum_vacuum_cost_delay = 0         -- Remove pause time to finish quickly
);
Enter fullscreen mode Exit fullscreen mode

A worker processing a table with per-table autovacuum_vacuum_cost_limit or autovacuum_vacuum_cost_delay is excluded from global cost balancing. This can accelerate one critical table, but aggressive overrides on several tables can increase aggregate I/O. Apply the change to one table first and verify it with pg_stat_progress_vacuum, disk latency, and the dead-tuple trend.

Bloat Detection, Monitoring (Observability), and Blocker Analysis

There are external factors that prevent the autovacuum system from working properly. Even if autovacuum is triggered on a table, if the dead tuples in the table cannot be cleaned, there may be a process (blocker) in the background preventing the cleanup.

You can use the following SQL query to detect the tables containing the most dead tuples in the system:

SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    ROUND((n_dead_tup::float / NULLIF(n_live_tup + n_dead_tup, 0)) * 100) AS dead_tuple_percent,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;
Enter fullscreen mode Exit fullscreen mode

If the number of dead tuples is constantly increasing and the last_autovacuum time is not being updated, three main reasons preventing the VACUUM process should be investigated:

  1. Long-running Transactions: An open transaction prevents the cleanup of dead tuples created after it started.
  2. Idle in Transaction: Clients in the idle in transaction state continue to hold their transaction ID.
  3. Old Prepared Transactions or Replication Horizons: Old entries in pg_prepared_xacts, and old non-null xmin or catalog_xmin values in pg_replication_slots, can hold back the VACUUM horizon. A physical slot that only has an old restart_lsn primarily retains WAL; do not assume that it retains heap dead tuples by itself.

To detect long transactions causing blockages:

SELECT
    pid,
    usename,
    state,
    xact_start,
    clock_timestamp() - xact_start AS transaction_duration,
    backend_xmin,
    age(backend_xmin) AS xmin_age,
    query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;
Enter fullscreen mode Exit fullscreen mode

PostgreSQL forces an anti-wraparound autovacuum when a table reaches autovacuum_freeze_max_age, even if autovacuum is otherwise disabled. Cost-based delay is not discarded merely because that threshold is reached. It is disabled later, when the last-resort vacuum_failsafe_age mechanism activates; some nonessential work is skipped at that point. Monitor XID age before the system reaches the failsafe stage.

Safe Change, Verification, and Rollback Steps

A gradual verification strategy should be followed before applying system-wide postgresql.conf or table-based autovacuum settings to the production environment.

1. Implementation Step

Many settings can be applied with a reload, but that is not true for every parameter or major version. In PostgreSQL 18, autovacuum_worker_slots can only be changed at server start, and autovacuum_max_workers cannot exceed that slot pool. Do not rely on pg_reload_conf() alone; inspect pg_settings.pending_restart on the target version.

# Check the PostgreSQL configuration file and verify its validity
sudo -u postgres psql -c "SELECT name, setting, pending_restart FROM pg_settings WHERE name LIKE 'autovacuum%';"
Enter fullscreen mode Exit fullscreen mode

After writing the parameters to the postgresql.conf file, reload the configuration without interrupting the service:

-- Reload PostgreSQL configuration without stopping the service
SELECT pg_reload_conf();
Enter fullscreen mode Exit fullscreen mode

2. Verification Step

To monitor the impact of the changes, logging of autovacuum processes should be enabled:

-- Write autovacuum operations taking longer than 2 seconds to the log
ALTER SYSTEM SET log_autovacuum_min_duration = 2000;
SELECT pg_reload_conf();
Enter fullscreen mode Exit fullscreen mode

Autovacuum duration and cleanup work can be tracked in the database logs. The following block is an illustrative format example only; fields can differ by PostgreSQL version:

LOG:  automatic vacuum of table "public.siparis_hareketleri": index scans: 1
        pages: 0 removed, 24500 remain, 1200 skipped due to pins
        tuples: 15400 removed, 890000 remain, 0 are dead but not yet removable
        avg read rate: 12.45 MB/s, avg write rate: 25.10 MB/s
        buffer usage: 4200 hits, 1200 misses, 850 dirtied
Enter fullscreen mode Exit fullscreen mode

In one VACUUM record, 0 are dead but not yet removable means that run did not report dead tuples retained by an old visibility horizon. It does not prove that the entire cluster has no open transaction. Check pg_stat_activity, pg_prepared_xacts, and pg_replication_slots separately.

3. Rollback Step

If an excessive increase in disk I/O values is observed as a result of the tuning work, the parameters can be safely reverted to their default values or to the previous configuration backup.

To reset table-level settings:

-- Remove table-specific settings and revert to server defaults
ALTER TABLE siparis_hareketleri RESET (
    autovacuum_vacuum_scale_factor,
    autovacuum_vacuum_threshold,
    autovacuum_vacuum_cost_limit,
    autovacuum_vacuum_cost_delay
);
Enter fullscreen mode Exit fullscreen mode

To revert system-level settings, the ALTER SYSTEM RESET command can be used:

-- Revert parameters changed with ALTER SYSTEM to default
ALTER SYSTEM RESET autovacuum_vacuum_cost_limit;
ALTER SYSTEM RESET autovacuum_vacuum_cost_delay;
SELECT pg_reload_conf();
Enter fullscreen mode Exit fullscreen mode

Conclusion

In PostgreSQL, autovacuum is the core maintenance mechanism for space reuse, planner statistics, the visibility map, and XID/MXID wraparound protection. Database size alone does not tell you whether defaults are sufficient; evaluate change rate, I/O budget, worker saturation, and XID consumption together.

For a proper maintenance strategy:

  1. Do not change the global scale factor or cost limit without measuring n_dead_tup, vacuum duration, and I/O latency.
  2. Tune write-heavy tables with per-table settings, then watch aggregate worker, RAM, and I/O impact.
  3. Monitor long and prepared transactions, non-null slot horizons, and XID age together.
  4. Run standard VACUUM often enough to reduce the need for VACUUM FULL and its long ACCESS EXCLUSIVE lock.

Official Sources

Top comments (0)