Postgres Autovacuum Tuning for Write-Heavy Tables and Table Bloat

How MVCC creates dead tuples and table bloat

PostgreSQL implements MVCC by keeping multiple versions of a row in the table itself. An UPDATE does not overwrite the row in place. It writes a new tuple version and marks the old one as expired. A DELETE only marks the tuple as expired. Concurrent transactions still holding older snapshots can keep reading those versions. Once no transaction can see an old version, it becomes a dead tuple: it still occupies space on its page but serves no query.

Plain VACUUM removes dead tuples and records the freed space in the free space map, so future inserts and updates can reuse it. It usually does not return space to the operating system. The only exception is a run of completely empty pages at the end of the file, which it can truncate. Table bloat is the space a table occupies beyond what its live rows need. Bloat builds when dead tuples accumulate faster than vacuum reclaims them, or when a burst of churn leaves free space the table never refills.

Index bloat is a separate problem. Every new row version normally needs new entries in each index. The exception is a HOT (heap-only tuple) update, which applies when no indexed column changed and the same page has room. Vacuum removes index entries that point to dead tuples, but sparse index pages are not compacted. An index can stay bloated while its table looks healthy.

Write-heavy tables suffer most because churn is continuous. Queue tables, session stores, counters, and status-tracking rows generate dead tuples between every vacuum pass. If you own tables like these, bloat shows up as larger scans, worse cache hit rates, and growing storage.

Copy UPDATE r42 (new version)      DELETE r7 (mark expired)
        |                              |
        v                              v
+---------------------------------------------+
| HEAP PAGE (table file)                      |
| [r42 v1 DEAD] [r42 v2 live] [r7 DEAD]       |
| [r9 live]     [r11 live]    [free]          |
+---------------------------------------------+
        ^                              |
        | new version -> new index     | plain VACUUM
        | entries (skipped if HOT)     v
+------------------------+    +---------------------------------+
| INDEX (b-tree)         |    | removes dead tuples             |
| r42 -> v1  (dead)      |    | free space -> free space map    |
| r42 -> v2              |    | reused by later INSERT/UPDATE   |
| r7  -> v1  (dead)      |    | file size usually unchanged     |
| VACUUM drops dead      |    | (only trailing empty pages      |
| entries; sparse pages  |    |  are truncated)                 |
| stay: INDEX BLOAT      |    +---------------------------------+
+------------------------+                     |
                                               v
                  churn faster than vacuum reclaims => TABLE BLOAT

 HOT update: no indexed column changed + room on same page
             -> new version stays on page, no new index entries

Under MVCC, UPDATE and DELETE leave dead tuples in heap pages and dead entries in indexes; plain VACUUM makes heap space reusable via the free space map without shrinking the file, and leaves sparse index pages uncompacted, so sustained churn produces table and index bloat.

How autovacuum works

Autovacuum is a set of background processes, not a single job. A launcher process wakes up on a schedule controlled by autovacuum_naptime and starts worker processes in each database in turn. The number of workers running at once across the cluster is capped by autovacuum_max_workers. A worker looks at the tables in its database, compares each table's statistics counters with its thresholds, and runs VACUUM, ANALYZE, or both on the tables that qualify.

A vacuum pass on one table follows these steps:

1. Heap scan. The worker reads heap pages and collects the tuple IDs of dead tuples into memory. It skips pages that the visibility map marks as all-visible, because those pages hold no dead tuples. The memory available for dead tuple IDs comes from autovacuum_work_mem, or from maintenance_work_mem when that setting is not configured.
2. Index cleanup. The worker scans each index on the table and removes entries that point to the collected dead tuples. If the dead-tuple memory fills before the heap scan finishes, the worker has to clean the indexes, return to the heap, and repeat. This is one reason large backlogs take disproportionately long to clear.
3. Heap cleanup. The worker marks the dead tuples' line pointers as reusable, updates the free space map and the visibility map, and freezes old tuples where needed.
4. Optional truncation. If the end of the file consists of empty pages, the worker can truncate them and return that space to the operating system.

Workers are rate-limited by cost-based throttling, which is covered below, so a pass on a large table can take a long time. The following sections cover the two levers you control: when a table qualifies for a pass, and how fast the pass is allowed to run.

How autovacuum decides when to run, and why defaults fail hot tables

Autovacuum checks each table periodically and starts a vacuum when its dead-tuple count crosses this threshold:

vacuum threshold = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × estimated row count

The row estimate comes from the planner statistics (reltuples in pg_class), not a live count. The stock defaults are a base threshold of 50 rows and a scale factor of 0.2, so a table is vacuumed once roughly 20% of it is dead. A similar formula, with its own settings, triggers auto-analyze.

Because the scale factor is a percentage, the trigger point grows with the table. On a 10,000-row lookup table, 20% is a small, frequent cleanup. On a large table under constant updates, it means a long wait.

Illustrative estimate. Assume a 50-million-row table that receives 2,000 updates per second, each producing one dead tuple.

Under the default, about ten million dead rows accumulate before vacuum starts. The vacuum then has a large backlog to scan, and new dead tuples keep arriving while it runs.

The global defaults are a compromise for a typical mix of mostly small or slow-changing tables. Lowering them cluster-wide would vacuum quiet tables more often than needed. Set storage parameters on the few hot tables instead, so that their cleanup scales with their write rate rather than their size.

Who should tune autovacuum per table

Per-table tuning is meant for the people who own a small number of tables with steady update or delete traffic:

• Application engineers who own churn-heavy tables. Examples include job queues, session stores, counters, carts, and rows that move through status values. These tables generate dead tuples continuously, and their owners usually notice the problem first as slower queries or growing storage.
• DBAs and SREs who run shared clusters. A handful of hot tables can account for most of the vacuum work on a cluster. Tuning only those tables avoids changing behavior for everything else.
• Teams with large tables that receive steady updates. The larger the table, the further the default scale factor pushes back its trigger point. A table with many millions of rows and a constant update rate is the typical case where defaults fall behind.

If you cannot name the tables involved, or you have not measured dead-tuple growth, start with the measurement section below rather than changing parameters.

When to avoid per-table autovacuum tuning

Per-table overrides are the wrong tool in several situations:

• Small or slow-changing tables. The defaults already vacuum these often enough. Overrides add configuration that someone has to maintain and gain little in return.
• Append-only or insert-mostly tables. Inserts do not create dead tuples. PostgreSQL 13 and later also trigger vacuum on insert volume (autovacuum_vacuum_insert_threshold and autovacuum_vacuum_insert_scale_factor), which keeps the visibility map current. Lowering the dead-tuple scale factor does little here.
• When something is holding back the xmin horizon. If a long transaction, an idle-in-transaction session, a replication slot, or a standby with hot_standby_feedback pins the horizon, vacuum cannot remove the dead tuples no matter how often it runs. Fix the blocker first, as described below.
• Systems that are already I/O-bound. More frequent vacuuming and higher cost limits add read and write load. If the storage is saturated, you may slow foreground queries more than you gain from reduced bloat.
• Tables loaded or rewritten in large batches. If a nightly job updates or deletes most of a table in one go, an explicit VACUUM (or VACUUM ANALYZE) at the end of the job is simpler and more predictable than lowering thresholds.
• Data that expires by time. If rows are deleted on age, partitioning and dropping old partitions removes the churn entirely. Tuning only cleans up after it.

Do not disable autovacuum on a table to work around its cost. That trades bloat for missing statistics and, eventually, a forced anti-wraparound vacuum.

Measuring dead-tuple growth before you tune

Before changing any storage parameters, confirm which tables actually need them. pg_stat_user_tables exposes the four counters that matter: n_dead_tup, n_live_tup, last_autovacuum, and autovacuum_count. A single reading tells you little, because a high dead-tuple count just before a scheduled vacuum is normal. Take two snapshots, for example an hour apart, and compare them. If n_dead_tup keeps rising while autovacuum_count stays flat, autovacuum is not keeping up. The same is true if each run finishes and the count climbs back to the same level within minutes. Rank candidates by the ratio of n_dead_tup to n_live_tup rather than by the raw count. Ten million dead rows matters on a 20-million-row table and barely registers on a 2-billion-row one.

These counters are estimates maintained by the statistics system, not exact counts. They can drift, they reset when statistics are reset, and they may be lost after a crash. Use them to find suspects, not to measure bloat precisely.

For an exact figure, use the pgstattuple extension. Its dead_tuple_percent and free_percent columns show how much of the relation is dead rows and how much is reclaimable free space. The cost is that it reads every page of the table. On a large relation that means sustained I/O and cache pressure, so run it off-peak or on a replica that holds the same data.

Tuning is warranted when both sources agree: the statistics show sustained accumulation, and pgstattuple confirms a large share of the table is dead or free space.

Copy-- 1. Snapshot the counters. Run the INSERT now and again ~1 hour later.
CREATE TABLE IF NOT EXISTS dead_tup_snap AS
  SELECT now() AS taken_at, relid, relname, n_dead_tup, n_live_tup,
         autovacuum_count, last_autovacuum
  FROM pg_stat_user_tables WITH NO DATA;

INSERT INTO dead_tup_snap
  SELECT now(), relid, relname, n_dead_tup, n_live_tup,
         autovacuum_count, last_autovacuum
  FROM pg_stat_user_tables;

-- 2. Compare the first and latest snapshots. Suspects: dead tuples rose,
--    and few or no autovacuum runs happened in between. Rank by ratio, not raw count.
WITH s AS (SELECT min(taken_at) AS t0, max(taken_at) AS t1 FROM dead_tup_snap)
SELECT b.relname,
       a.n_dead_tup                              AS dead_before,
       b.n_dead_tup                              AS dead_after,
       b.autovacuum_count - a.autovacuum_count   AS vacuums_between,
       round(b.n_dead_tup::numeric / NULLIF(b.n_live_tup, 0), 3) AS dead_ratio,
       b.last_autovacuum
FROM s
JOIN dead_tup_snap a ON a.taken_at = s.t0
JOIN dead_tup_snap b ON b.relid = a.relid AND b.taken_at = s.t1
WHERE b.n_dead_tup > a.n_dead_tup
ORDER BY dead_ratio DESC NULLS LAST
LIMIT 20;

-- 3. Confirm a suspect with an exact figure. pgstattuple reads every page
--    of the relation: run it off-peak or on a replica with the same data.
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT pg_size_pretty(table_len) AS table_size,
       dead_tuple_percent,
       free_percent
FROM pgstattuple('public.orders');

Two snapshots of pg_stat_user_tables find tables where dead tuples outpace autovacuum, and pgstattuple then gives an exact dead and free space percentage for one suspect table.

Per-table autovacuum tuning and cost-based throttling

Per-table storage parameters let you change trigger points only for the tables that need them. Lower autovacuum_vacuum_scale_factor and set a fixed autovacuum_vacuum_threshold on the hot table. This makes vacuum start after a modest number of dead tuples, not after a fraction of a large table. Other tables keep the global defaults.

The trigger condition is:

dead tuples > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples

Illustrative estimate (assumed numbers, not a benchmark): assume a table with 50,000,000 rows. With threshold 50 and scale factor 0.2, vacuum waits for 50 + 0.2 × 50,000,000 = 10,000,050 dead tuples. With threshold 1,000 and scale factor 0.01, it starts at 1,000 + 0.01 × 50,000,000 = 501,000. Each run then has about one-twentieth as much work.

Starting vacuum earlier only helps if the worker can finish. Cost-based throttling assigns a cost to each page hit, page miss, and dirtied page. When a worker's accumulated cost reaches the cost limit, it sleeps for autovacuum_vacuum_cost_delay. On a busy table, a worker throttled too hard can remove dead tuples more slowly than writers create them, so it never catches up. Raise the cost limit or shorten the delay for that table only.

By default, the global cost limit is divided among the active workers. Tables with their own cost settings are excluded from that balancing. Raising autovacuum_max_workers without raising the limit spreads the same budget across more workers.

Trade-offs:

• Added load: more frequent vacuuming adds I/O and CPU, which hurts on systems that are already I/O-bound.
• Worker starvation: if you lower the scale factor on many tables, they compete for a limited pool of workers, and large tables can wait a long time for a turn.

Copy-- Hot table: vacuum earlier and in smaller batches.
-- Only this table changes; all other tables keep the global settings.
ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.01,  -- 1% of rows instead of the 20% default
  autovacuum_vacuum_threshold    = 1000,  -- fixed floor of dead tuples
  autovacuum_vacuum_cost_limit   = 1000,  -- larger per-table budget so the worker keeps up
  autovacuum_vacuum_cost_delay   = 2      -- milliseconds to sleep once the budget is spent
);

-- Confirm the per-table overrides are stored.
SELECT relname, reloptions
FROM pg_class
WHERE relname = 'events';

-- Compare dead tuples with this table's trigger point and check
-- whether autovacuum is running and finishing.
SELECT s.relname,
       s.n_dead_tup,
       1000 + 0.01 * c.reltuples AS approx_trigger_point,
       s.last_autovacuum,
       s.autovacuum_count
FROM pg_stat_user_tables s
JOIN pg_class c ON c.oid = s.relid
WHERE s.relname = 'events';

-- Revert to the global defaults if the extra I/O is too costly.
-- ALTER TABLE events RESET (
--   autovacuum_vacuum_scale_factor, autovacuum_vacuum_threshold,
--   autovacuum_vacuum_cost_limit, autovacuum_vacuum_cost_delay
-- );

This example sets per-table autovacuum trigger and cost-throttling parameters on one write-heavy table, checks that they are stored, monitors dead tuples against the new trigger point, and shows how to revert the changes.

When tuning will not help: finding what blocks vacuum

Vacuum can only remove a dead tuple once no transaction could still need to see it. The cutoff is the oldest transaction horizon (xmin) held anywhere in the cluster. If something pins that horizon, autovacuum runs on schedule, scans the table, and reclaims almost nothing. More aggressive settings just repeat that wasted work more often.

Finding the oldest xmin holder

There are four common culprits:

• Long-running transactions. In pg_stat_activity, sort by age(backend_xmin) and compare xact_start with now().
• Sessions idle in transaction. Filter on state = 'idle in transaction'. These are usually application bugs, such as a missing commit or a connection checked out from a pool and never returned. Setting idle_in_transaction_session_timeout limits the damage.
• Abandoned replication slots. In pg_replication_slots, check xmin, catalog_xmin, and active. An inactive slot keeps its horizon until someone drops it.
• hot_standby_feedback on replicas. With this enabled, a long query on a standby holds back cleanup on the primary. The standby's horizon appears as backend_xmin in pg_stat_replication on the primary.

Each of these needs a fix in application code or operations: timeouts, connection hygiene, slot lifecycle ownership, and query limits on replicas. Autovacuum parameters cannot override a held horizon.

Wraparound is a separate problem

Transaction ID wraparound protection does not depend on dead tuples. Vacuum must also freeze old rows before their transaction IDs age past autovacuum_freeze_max_age. When a table crosses that threshold, PostgreSQL forces an anti-wraparound vacuum, even if autovacuum is disabled for the table.

A pinned horizon blocks freezing as well as cleanup. A table can show little bloat and still be approaching wraparound. Track age(datfrozenxid) in pg_database and age(relfrozenxid) in pg_class separately from your dead-tuple metrics.

Copy-- 1. Sessions holding back the xmin horizon (long transactions, idle in transaction)
SELECT pid, usename, state,
       age(backend_xmin)     AS xmin_age,
       now() - xact_start    AS xact_duration,
       left(query, 60)       AS last_query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 5;

-- 2. Replication slots pinning the horizon. Inactive slots are the usual suspects.
SELECT slot_name, slot_type, active,
       age(xmin)         AS xmin_age,
       age(catalog_xmin) AS catalog_xmin_age
FROM pg_replication_slots
WHERE xmin IS NOT NULL OR catalog_xmin IS NOT NULL
ORDER BY greatest(age(xmin), age(catalog_xmin)) DESC;

-- 3. Standbys with hot_standby_feedback report their horizon here (run on the primary)
SELECT application_name, client_addr, age(backend_xmin) AS xmin_age
FROM pg_stat_replication
WHERE backend_xmin IS NOT NULL;

-- 4. Wraparound is monitored separately: how close is each database to a forced freeze?
SELECT datname,
       age(datfrozenxid) AS frozen_xid_age,
       current_setting('autovacuum_freeze_max_age')::bigint AS freeze_max_age,
       round(100.0 * age(datfrozenxid)
             / current_setting('autovacuum_freeze_max_age')::bigint, 1) AS pct_of_threshold
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

-- Fixes are operational, not autovacuum settings, for example:
-- SELECT pg_terminate_backend(<pid>);                   -- after confirming with the owner
-- SELECT pg_drop_replication_slot('<abandoned_slot>');  -- only if the consumer is gone

Diagnostic queries that find the oldest xmin holder across sessions, replication slots, and hot_standby_feedback standbys, plus a separate check of how close each database is to the anti-wraparound freeze threshold.

Reducing dead-tuple churn at the source

Leave room for HOT updates with fillfactor

A heap-only tuple (HOT) update writes the new row version on the same page and adds no index entries, as long as no indexed column changed and the page has free space. Dead HOT versions can be pruned during normal page access, so they need far less vacuum work. Tables default to a fillfactor of 100, which packs pages full and leaves no space for the new version. Setting fillfactor to 70–90 on a frequently updated table reserves that space. Compare n_tup_hot_upd to n_tup_upd in pg_stat_user_tables to see whether the change helped. The cost is that the table takes more pages, so more disk is used, fewer rows fit in shared buffers, and sequential scans read more.

Skip no-op updates

Many ORMs write every column on save. Postgres does not block HOT when an indexed column is set to its current value, but any UPDATE that matches a row still creates a new row version and a dead tuple. Add a WHERE ... IS DISTINCT FROM ... guard or attach the built-in suppress_redundant_updates_trigger. Keep frequently changing columns, such as counters or updated_at, out of indexes unless queries actually need them there.

Partition so old data is dropped, not deleted

When rows expire by time, range partitioning lets you detach and drop an entire partition. This produces no dead tuples and needs no vacuum. The trade-offs are real:

• Primary keys and unique constraints must include the partition key.
• Queries that do not filter on the partition key scan every partition.
• Planning time grows with the number of partitions.
• Every migration now touches a parent table and its children.

Recovering space with pg_repack vs VACUUM FULL, and the cost to your team

Once churn is under control, the space a bloated table already holds stays allocated. Plain VACUUM marks dead tuples as reusable, but it gives space back to the operating system only when the empty pages sit at the end of the file. To shrink the files on disk, you have to rewrite the table.

pg_repack builds a new copy of the table while a trigger records concurrent changes. It replays those changes into the copy and then swaps the two tables. It has three requirements:

• The table needs a primary key or a unique index on NOT NULL columns.
• The pg_repack extension must be installed in the database.
• You need enough free disk for roughly a full copy of the table.

Illustrative estimate (assumed numbers, not a benchmark). Free disk needed ≈ live table size + index size + WAL headroom. With an assumed 40 GB of live data, 15 GB of indexes and 10 GB of WAL headroom, you need about 65 GB free.

pg_repack has to wait behind long-running transactions to take its locks. A killed run can leave triggers and work tables behind, and you must clean them up before you retry. Schedule it during low-write windows, check for long transactions first, and process one table at a time.

Per-table autovacuum tuning, based on measured dead-tuple growth, costs some engineering hours up front. It saves you from emergency rewrites, incident response and paying for extra storage to hold bloat. Use pg_repack occasionally, as a planned job, not as routine maintenance.

How per-table tuning saves engineering time

The time savings come from replacing unplanned work with a small amount of planned work:

• Fewer bloat-driven incidents. When a hot table is vacuumed in small, frequent passes, it stays close to its working size. Fewer people end up investigating why a query that used to be fast now reads far more pages, or why disk usage keeps rising.
• Fewer table rewrites. Each VACUUM FULL or pg_repack run needs a maintenance window, a disk-space check, a check for long transactions, and someone watching it. Keeping bloat from building up means you schedule these rarely instead of repeatedly.
• Faster diagnosis. The snapshot queries and xmin-holder queries in this article are reusable. Once they are saved as runbook steps or monitoring checks, the first question in an incident ("is vacuum falling behind, or is something blocking it?") takes minutes to answer instead of starting from scratch.
• Changes are small and reversible. Overrides apply to one table, are visible in reloptions, and can be undone with ALTER TABLE ... RESET. Reviewing and rolling them back takes little effort compared with cluster-wide configuration changes.

Illustrative estimate (assumed inputs, not measured). Assume a team currently runs an unplanned pg_repack on one hot table about once a quarter. Assume each run takes about 6 engineer-hours across preparation, monitoring, and follow-up, plus about 4 hours of incident investigation beforehand. That is about 40 hours a year. If one-time tuning and monitoring setup takes an assumed 8 hours and removes most of those runs, the work pays for itself within the first year. Substitute your own incident frequency and hours. If your hot tables never reach the point of needing a rewrite, the savings will be smaller.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to postgres autovacuum tuning, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.