Why autovacuum tuning in PostgreSQL cannot keep up with the load
The first symptom is almost never failure — response time grows during peaks first.
Tables grow faster than background cleanup can process them: dead row versions remain in pages, space for them is not returned to circulation, and each query reads noticeably more pages than useful data in them.
Separately alarming, it looks like normal operation — indexes are used, query plans do not change, and degradation accumulates for months.
Factory thresholds are designed for a different load profile. A threshold of one fifth of table size for a large table means cleanup starts after tens of millions of changed rows — and by that time just as many changes have accumulated.
Limiting the number of parallel workers means one heavy pass holds the queue and other tables wait; default delays between passes are too large for a database with constant writes.
As a result, background cleanup cannot keep up not because of a configuration error but because no one recalculated the parameters for the actual change stream.
Then feedback kicks in: the more tables bloat, the longer cleanup takes, the less often it completes, and the faster the database grows.
Scheduled maintenance turns into emergency maintenance — manually running cleanup during business hours, locks, degradation during the pass, risk of running out of free disk space.
Let's break down how this is addressed: how to calculate thresholds for the actual write profile and how to restore regularity to maintenance.
What managed table cleanup gives: effect in numbers
Bloat and storage volume
The default trigger threshold is tied to a share of the table, so on a large table autovacuum waits until millions of dead rows accumulate — and files grow faster than cleanup can return them.
We lower the threshold to an acceptable number of rows, add free space reserve in pages for future row versions, and set separate rules for "hot" tables instead of one global rule.
System counters show that the share of dead rows decreases severalfold, and relation size stops growing between cleanup cycles.
Response and stability
The usual picture under load — a long cleanup pulls disk I/O and holds locks exactly when peak queries arrive: response time grows in spikes, and the query queue accumulates.
We limit cleanup intensity, distribute it across workers, and move heavy runs to low-load windows, leaving autostart enabled for other tables.
As a result, peak queries stop running into cleanup, response remains predictable, and manual emergency runs on a schedule become a class of failures of the past.
VACUUM modes: comparison of approaches and schedule optimization
Dead rows accumulate under any mode — the difference is who starts cleanup and when. Below are four working alternatives and the conditions under which each is appropriate.
| Option | When suitable | Limitations |
|---|---|---|
| Threshold autovacuum | Steady change stream, tables of predictable size | Percentage-of-size threshold triggers late: dead rows accumulate between passes |
| Manual cleanup by window | Batch loads, nightly calculations, mass updates | Requires an external scheduler; if the window falls on peak traffic, latency grows |
| Cleanup by partitions | Partitioned logs and events with old partitions dropped | A pass over the parent table does not clean partitions — each one must be maintained |
| Deferred freezing | Tables with rare updates and old row versions | Start is not tied to a maintenance window; the pass runs across the whole table |
How to choose a mode for the load profile
Look at the ratio of dead rows to live rows, the rate of transaction counter growth, and the presence of a traffic-dip window.
Global thresholds set default behavior, but for hot tables they are overridden at the table level — a large table stops waiting for the general autovacuum queue and does not accumulate dead rows between infrequent passes.
Limitations: locks, replication, replica lag
Cleanup does not block reads and writes, but conflicts with changes to table structure and indexes. At the same time it writes to the write-ahead log: the change stream grows on replicas, and a replica may lag during heavy passes.
The pace is limited by cost delays, and full passes and deferred freezing are moved to a low-load window so maintenance does not coincide with reports or exports.
Work order: from database inventory to maintenance regulations
We build the work from facts, not from a standard set of parameters: first we capture the database state, then change configuration.
The six stages below produce a verifiable artifact at each step, so the decision to include it in the production loop is made before edits, not after.
Preparation and safe pilot
- We collect cleanup statistics: how many dead rows have accumulated by table, how often autovacuum runs, where the dead-row counter grows fastest.
- We collect the load and table growth profile: peak hours of writes and updates, daily and weekly growth, long transactions that hold a snapshot and interfere with cleanup.
- We design thresholds and windows: separate coefficients for large and active tables, a forced cleanup schedule in off-peak intervals, a load limit on the disk subsystem.
- We rehearse the scheme on a copy: deploy a database copy, reproduce the real load, observe cleanup behavior under concurrent writes, and measure lock duration.
How we verify the result
- We take control measurements before and after: share of dead rows, heavy query time, file size, frequency of emergency cleanup.
- We hand over the regulations: a table map with thresholds, revision rules as data grows, a set of queries for self-checking, and the procedure for anomalies.
Configurations, window schemes, and maintenance regulations on output
We hand over not a set of one-off commands but a working configuration: rules by which the database maintains itself, and procedures for cases when autovacuum cannot cope.
Everything is in the configuration repository and documentation — settings are reproduced on staging and in production without discrepancies.
What we configure in the database
- Autovacuum thresholds by table groups: for hot tables the share of changed rows triggers earlier; for large archives intervals are extended so scheduled cleanup does not displace urgent cleanup.
- Maintenance window scheme: heavy operations only during low-load hours, with duration limits and duty binding.
- List of exceptions: tables and large archives with a separate scenario — one-off inserts, partitions to be cut off, read-only data.
- Control queries and alerts: share of dead rows, time since last cleanup, approach to the transaction counter limit, file growth rate.
What remains with your team
- Scheduled maintenance procedure: who opens the window, what is checked before and after, where the result is recorded.
- Escalation regulations: by which indicators the on-call engineer raises a task, to whom it is handed over, and what can be done before rollback.
- Access and manual operation log: separate accounts for maintenance, restriction and traceability of execution.
- Documentation justifying each threshold — as data grows, rules are recalculated rather than selected anew.
Case: bloat reduced threefold without stopping the service
Project inputs and constraints
A PostgreSQL cluster served a service with a continuous write stream: several log tables were constantly updated, and autovacuum ran on standard trigger thresholds.
Bloat grew faster than cleanup could pick it up — the share of dead rows in the hottest tables approached 40%, and storage volume grew to almost three times the useful data.
There was no downtime window: the service runs 24/7, and we could not stop the database for manual cleanup.
The second constraint was emergency cleanup runs. They were launched manually when bloat crossed a critical mark; each run degraded response and kept engineers on edge.
Result and what changed in maintenance
Trigger thresholds and cleanup cost were recalculated for the write profile of specific tables, and the transition was made on a live database: first a run of the new settings on a replica, comparison of plans and load, then a smooth switchover without a downtime window.
The share of dead rows fell from 38% to 11%, storage volume was cut threefold, peak response became more even — cleanup dips stopped being noticeable in metrics.
The number of emergency runs decreased from several per month to one or two per quarter, and even those for planned reasons.
Maintenance moved from "firefighting" mode to observation: bloat, cleanup frequency, and the age of the oldest transactions are now on the metrics dashboard, and thresholds are revised when the write profile changes.
How do threshold policies differ from window-based scheduling?
Threshold policies rely on the number of changed rows: the share of dead rows in a table exceeds a specified percentage — cleanup starts without waiting for a calendar date.
This gives reaction to spikes: a batch delete or mass load immediately raises the counter, and obsolete row versions do not have time to bloat files.
The flip side is unpredictability in load. On a large table, the percentage threshold is reached rarely and late; on a small one, cleanup runs almost continuously.
I/O peaks occur when the system is already busy, and they cannot be coordinated with backups and updates: there is simply no schedule.
Scheduled maintenance windows remove this uncertainty. Cleanup start is tied to a nightly interval, the engineer knows in advance when the database will spend resources on garbage collection, and combines it with other routine work.
The price is delay: between windows the table accumulates dead rows, and a spike at an unfortunate moment can inflate files faster than the schedule arrives.
A hybrid cleanup scheme takes the reactivity of thresholds and the predictability of windows: background cleanup runs on a schedule; for tables with high write rates, thresholds are lowered and a more aggressive mode is set; while freezing and reindexing remain in the window.
For databases already maintained manually, this is a workable option: manual starts remain as a control point, and thresholds insure against a missed window.
Autovacuum under load: locks, large tables, verification
Autovacuum starts on its own and on a loaded database regularly runs into locks, large tables, and lack of time for a pass. Below are answers to doubts most often voiced before changing these settings.
All of them are about operation without downtime: what can be changed on a live database, and what requires a window.
Will cleanup lock tables and bring down the service?
A routine pass takes a weak lock and interferes with neither reads nor writes. An exclusive lock is held only by a full table rebuild and index rebuild — we move those to a maintenance window.
Can parameters be changed without restarting?
Thresholds and delays are set for a specific table and take effect for subsequent passes — no restart is needed. Shared memory pool settings are applied at startup; we plan those in advance.
What should be done with large tables and archives?
On a table of hundreds of gigabytes, the share of changed rows takes a long time to reach the threshold, and it remains unprocessed. We split it into partitions by time, set thresholds separately, and maintain old archives under their own regulations outside autovacuum.
How can we make sure processing is happening?
We look at system views and the log: time of last pass, number of dead rows, freeze counters. From them we configure alerts — we learn about stagnation before the file grows.
The database has been maintained for years, everything is running — where to start?
With an assessment: where dead data accumulates, which long transactions hold up cleanup, where the transaction ID counter is close to the limit. We run changes on a copy and only then transfer them to the production loop.
Let's discuss the task: we'll assemble a load profile and work plan
To make the assessment based on facts rather than assumptions, we need source data for your database. It can be collected in one pass through system views and logs — on this basis we will calculate the scope of work and the order of changes.
- cleanup statistics: how many dead rows accumulate, which tables fall out of processing, how often autovacuum triggers, and where it cannot keep up with writes;
- table and index growth dynamics — how much volume is added per day and on which objects growth outpaces cleanup;
- load windows: when the write peak is, during which hours heavy operations can be run without affecting users;
- downtime constraints: allowable unavailability duration, availability of replicas and the possibility of switching to them;
- already applied settings — changed autovacuum thresholds, manual schedules, workaround rules that someone introduced earlier;
- server parameters and branch version: memory size, number of cores, disk type, transaction log configuration;
- incident history: when the log last grew or query speed dropped after a mass update.
Describe the task and attach what you have — we will come back with an estimate and work plan. The plan will show what we change in thresholds, what we move into regular regulations, and with which control measurements we confirm the result.