Your Engineers Shouldn’t Be Doing PostgreSQL 18 Vacuum Tuning at Midnight

PostgreSQL 18 vacuum tuning is a design activity that belongs in a change review, next to your schema migrations and your capacity model. In most engineering organisations it is still handled somewhere else entirely: at 00:42, in a production psql session, by an application engineer who last read the autovacuum chapter eleven months ago and is now changing autovacuum_vacuum_cost_delay by feel because a disk-usage alert will not stop firing.

That pattern is not a knowledge failure. It is an ownership failure, and PostgreSQL 18 — released on 25 September 2025 — gives you enough new control surface and enough new telemetry to fix it properly. This article lays out what changed, which parameters actually matter, how to express vacuum policy per table instead of per incident, and how to move the whole problem from a 2 a.m. pager into a Tuesday afternoon design review.

What this article covers

• Why PostgreSQL 18 vacuum tuning keeps landing on a pager at midnight
• What actually changed in PostgreSQL 18
• The PostgreSQL 18 vacuum tuning parameters that matter, with defaults
• A workload-tiered PostgreSQL 18 vacuum tuning policy, with SQL
• Measure first: four queries we run before touching anything
• The midnight problem is an ownership problem
• A 30-day plan to get vacuum off the on-call rotation
• Anti-patterns we still find in production
• Frequently asked questions
• Sources and further reading

Why PostgreSQL 18 vacuum tuning keeps landing on a pager at midnight

PostgreSQL is a multi-version concurrency control system. An UPDATE does not overwrite a row; it writes a new version and leaves the old one behind. A DELETE only marks a version invisible. Those old versions stay on the heap until vacuum proves that no snapshot can still see them and reclaims the space, and until vacuum freezes old transaction IDs so the 32-bit XID counter can keep turning safely. Every question in PostgreSQL 18 vacuum tuning descends from those two jobs: reclaim and freeze.

Both jobs are quiet and continuous when they are working. When they stop working, the failure is loud and it is almost always nocturnal, for three structural reasons.

The workloads that break vacuum run at night. Nightly ETL, bulk reconciliation, retention jobs that delete tens of millions of rows, logical backups that hold a snapshot open for two hours — all of these either generate enormous dead-tuple volume or pin the xmin horizon, so that vacuum runs, does work, and reclaims nothing.

The symptom is cumulative. Bloat, index degradation and XID age build up quietly for days and then cross a threshold. The alert time is the crossing time, not the cause time, which is why PostgreSQL 18 vacuum tuning done reactively is always done too late.

The throttle is designed to lose. Autovacuum ships deliberately gentle so that it cannot hurt a busy system, which also means it cannot catch up with a table being rewritten faster than the cost limit allows. That is a policy choice, not a law of physics.

So the pager fires, and it fires at the person on rotation rather than the person who owns the policy. The engineer has no baseline, no rollback path, and no idea whether the correct answer is a cost-limit change, a per-table storage parameter, an index rebuild, a partition drop, or a hunt for the idle-in-transaction session that is actually the root cause. Under that pressure, someone eventually types VACUUM FULL, takes an ACCESS EXCLUSIVE lock, and converts a bloat problem into an outage.

If you want the mechanics underneath this section, our deep dive on PostgreSQL autovacuum tuning internals and configuration covers the daemon in detail. This is why we treat PostgreSQL troubleshooting and vacuum policy as separate disciplines. Firefighting restores service. Policy stops the fire from being scheduled.

What actually changed in PostgreSQL 18

PostgreSQL 18 is the most vacuum-relevant release in several years. The documentation itself was reorganised: the configuration chapter is now §19.10 Vacuuming rather than a section about automatic vacuuming, which is a fair signal of intent. Six changes matter for real PostgreSQL 18 vacuum tuning work.

1. A ceiling on the trigger equation: autovacuum_vacuum_max_threshold

For two decades the trigger was purely proportional: vacuum when dead tuples exceed autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples. With defaults of 50 and 0.2, a billion-row table needs roughly 200 million dead tuples before autovacuum shows any interest — which is exactly why teams end up hand-editing scale factors table by table at night.

PostgreSQL 18 adds autovacuum_vacuum_max_threshold, a hard cap on that calculation, defaulting to 100,000,000 dead tuples. Set it to -1 to restore the old unbounded behaviour. For large tables this single default removes a whole class of surprise: the proportional formula can no longer defer vacuum indefinitely just because the table is big. It is the most consequential change in PostgreSQL 18 vacuum tuning for anyone running tables above a few hundred million rows.

2. Worker capacity you can change without a restart: autovacuum_worker_slots

Raising autovacuum_max_workers used to require a restart, which meant the decision was made either months early or never. PostgreSQL 18 introduces autovacuum_worker_slots (default 16, set at server start) to reserve backend slots up front; autovacuum_max_workers (default 3) can then be raised or lowered at runtime up to that ceiling with a configuration reload. Concurrency becomes an operational dial instead of a maintenance window, which changes how PostgreSQL 18 vacuum tuning gets reviewed and rolled out: you can trial a higher worker count on a Tuesday and roll it back before lunch.

3. Eager freezing: vacuum_max_eager_freeze_failure_rate

Historically a normal vacuum skipped all-visible pages entirely, so freezing work piled up until an aggressive anti-wraparound vacuum was forced to scan the whole relation — typically the single most disruptive maintenance event a large PostgreSQL table experiences. PostgreSQL 18 lets normal vacuums eagerly freeze some of those all-visible pages, amortising the cost.

The aggressiveness is bounded by vacuum_max_eager_freeze_failure_rate, default 0.03. It is a failure budget, not a work budget: only pages that are eagerly scanned and then fail to be set all-frozen count against the 3% cap, and successful freezes are separately capped internally at 20% of the relation's all-visible-but-not-all-frozen pages. A value of 0 disables eager scanning. For append-mostly tables, raising this is often the highest-value change in a PostgreSQL 18 vacuum tuning exercise, because it turns one catastrophic scan into a long series of unremarkable ones.

4. vacuum_truncate is now a server setting

Trailing-page truncation is useful because it returns space to the filesystem, and dangerous because it needs a brief ACCESS EXCLUSIVE lock. That storage parameter now has a server-level counterpart, vacuum_truncate (default on), so latency-sensitive fleets can make a deliberate global choice instead of relying on every table being configured correctly. One fewer per-table detail to get wrong in a PostgreSQL 18 vacuum tuning audit.

5. Asynchronous I/O changes vacuum throughput

PostgreSQL 18's new AIO subsystem, selected with io_method (worker by default, io_uring on suitably built Linux systems, or sync), lets backends queue multiple reads. Vacuum is an explicit beneficiary, and maintenance_io_concurrency now defaults to 16 and is meaningful even on platforms without posix_fadvise(). Practical consequence: on modern NVMe storage your old cost-based delay settings are probably calibrated for a machine you no longer own. Re-derive them as part of the PostgreSQL 18 vacuum tuning exercise rather than carrying them forward.

6. You can finally see what vacuum is costing you

This is the change that makes policy auditable. pg_stat_progress_vacuum gains delay_time, the time a vacuum has spent asleep in cost-based delay. pg_stat_all_tables gains total_vacuum_time, total_autovacuum_time, total_analyze_time and total_autoanalyze_time. VACUUM (VERBOSE) and the autovacuum log now report full WAL buffer counts. And the new pg_signal_autovacuum_worker predefined role lets you cancel a runaway worker without handing out superuser.

Two smaller items are worth knowing: PostgreSQL 18 adds an ONLY keyword to VACUUM and ANALYZE so you can process a partitioned parent without touching its children — genuinely useful, because autovacuum never processes the parent relation itself — and COPY FREEZE is no longer permitted on foreign tables. Taken together, these additions move PostgreSQL 18 vacuum tuning from folklore to something you can measure, budget and review.

The PostgreSQL 18 vacuum tuning parameters that matter, with defaults

Everything below is a documented PostgreSQL 18 default. Do not copy the values; copy the habit of knowing what the default is before you change it. Competent PostgreSQL 18 vacuum tuning is almost entirely a matter of understanding these nineteen knobs and then touching four of them.

One default deserves a warning label. vacuum_cost_limit is divided among running autovacuum workers, so raising autovacuum_max_workers without raising the cost limit does not buy throughput — it buys more workers moving more slowly. That interaction is responsible for a large share of the "we increased the workers and nothing improved" tickets that reach our PostgreSQL support desk.

A workload-tiered PostgreSQL 18 vacuum tuning policy, with SQL

Global settings are a fleet-wide compromise. Real PostgreSQL 18 vacuum tuning policy lives in per-table storage parameters, because vacuum requirements follow write patterns, not table size. We classify every significant relation into one of four tiers, write the tier into the schema migration, and review it like application code.

Tier 1 — high-churn OLTP tables

Heavily updated, latency-sensitive, small enough that frequent vacuum is cheap. The goal is many small vacuums instead of a few large ones.

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold    = 1000,
  autovacuum_vacuum_cost_delay   = 0,
  autovacuum_vacuum_cost_limit   = 2000,
  autovacuum_analyze_scale_factor = 0.01,
  fillfactor = 90
);

fillfactor matters more than most tuning guides admit: leaving free space on the page keeps updates HOT, which means the index does not need touching and vacuum has far less to do. That is a schema decision an application engineer legitimately owns, and it is the one part of PostgreSQL 18 vacuum tuning that genuinely belongs in application code review.

Tier 2 — append-only event and log tables

Almost no dead tuples, enormous freezing debt. This is where the PostgreSQL 18 eager-freeze budget earns its keep.

ALTER TABLE events_2026_08 SET (
  autovacuum_vacuum_insert_scale_factor  = 0.01,
  vacuum_max_eager_freeze_failure_rate   = 0.10,  -- PostgreSQL 18
  autovacuum_freeze_min_age              = 0
);

Freeze aggressively while the pages are still in cache and the table is still being written sequentially. Then retire data by dropping partitions, never by issuing a bulk DELETE that creates a hundred million dead tuples for vacuum to clean up during your busiest hour.

Tier 3 — queue and state-machine tables

Small, brutally hot, updated many times per row. These tables bloat in minutes, not days, and they are the single most common cause of a midnight page — so they are where PostgreSQL 18 vacuum tuning should start.

ALTER TABLE job_queue SET (
  autovacuum_vacuum_scale_factor = 0.005,
  autovacuum_vacuum_threshold    = 200,
  autovacuum_vacuum_cost_delay   = 0,
  fillfactor = 80
);

Tier 4 — cold and archive tables

Stock defaults are usually correct. The only real risk is a synchronised wraparound storm when a hundred archive partitions cross autovacuum_freeze_max_age in the same week. Stagger them deliberately — remembering that the per-table value may only be reduced, never raised above the server setting.

ALTER TABLE invoices_2019 SET (
  autovacuum_freeze_max_age = 120000000,
  vacuum_truncate           = off          -- PostgreSQL 18 storage parameter and GUC
);

Two PostgreSQL 18 operational conveniences belong in the same runbook. Concurrency without a restart:

-- PostgreSQL 18: raise autovacuum concurrency at runtime,
-- up to autovacuum_worker_slots (default 16)
ALTER SYSTEM SET autovacuum_max_workers = 8;
ALTER SYSTEM SET vacuum_cost_limit      = 2000;   -- remember: shared across workers
SELECT pg_reload_conf();

And a safe escape hatch for on-call, without superuser:

-- PostgreSQL 18 predefined role
GRANT pg_signal_autovacuum_worker TO sre_oncall;

-- statistics for a partitioned parent, without walking every child
ANALYZE ONLY events;

Measure first: four queries we run before touching anything

No PostgreSQL 18 vacuum tuning change should be made without a before-and-after number. These four queries produce them, and the last two use columns that did not exist before PostgreSQL 18.

1. Where is the bloat, and how much time has vacuum already spent there?

SELECT relid::regclass                AS table_name,
       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,
       total_autovacuum_time,          -- new in PostgreSQL 18 (ms)
       total_vacuum_time               -- new in PostgreSQL 18 (ms)
FROM   pg_stat_all_tables
WHERE  n_dead_tup > 10000
ORDER  BY dead_pct DESC
LIMIT  25;

2. How much XID headroom is left? This is the query that tells you whether you have a performance problem or a countdown.

SELECT c.oid::regclass AS table_name,
       age(c.relfrozenxid) AS xid_age,
       current_setting('autovacuum_freeze_max_age')::bigint
         - age(c.relfrozenxid) AS xids_before_forced_vacuum,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM   pg_class c
JOIN   pg_namespace n ON n.oid = c.relnamespace
WHERE  c.relkind IN ('r','m','t')
AND    n.nspname NOT IN ('pg_catalog','information_schema')
ORDER  BY age(c.relfrozenxid) DESC
LIMIT  25;

3. Is the running vacuum working, or sleeping? Before PostgreSQL 18 this was guesswork; delay_time now answers it directly.

SELECT p.pid,
       p.relid::regclass AS table_name,
       p.phase,
       p.heap_blks_scanned,
       p.heap_blks_total,
       round(100.0 * p.heap_blks_scanned
             / NULLIF(p.heap_blks_total, 0), 1) AS pct_scanned,
       p.index_vacuum_count,
       p.delay_time,                   -- new in PostgreSQL 18
       now() - a.xact_start AS running_for
FROM   pg_stat_progress_vacuum p
JOIN   pg_stat_activity a USING (pid)
ORDER  BY running_for DESC;

If delay_time is a large fraction of running_for, your problem is the throttle, not the hardware. If it is near zero and the vacuum is still slow, look at index count, I/O and WAL instead.

4. Who is stopping vacuum from reclaiming anything? A vacuum that runs to completion and frees nothing is nearly always blocked by a held snapshot: a long transaction, an abandoned replication slot, an orphaned prepared transaction, or hot_standby_feedback from a lagging replica — see troubleshooting PostgreSQL streaming replication lag for that last case.

SELECT pid, state, backend_xmin,
       age(backend_xmin) AS xmin_age,
       now() - xact_start AS txn_duration,
       left(query, 60) AS query_head
FROM   pg_stat_activity
WHERE  backend_xmin IS NOT NULL
ORDER  BY age(backend_xmin) DESC
LIMIT  10;

SELECT slot_name, active, xmin, catalog_xmin,
       age(coalesce(xmin, catalog_xmin)) AS slot_xmin_age
FROM   pg_replication_slots
ORDER  BY slot_xmin_age DESC NULLS LAST;

SELECT gid, prepared, age(transaction) AS prepared_xid_age
FROM   pg_prepared_xacts
ORDER  BY prepared_xid_age DESC;

Turn these into dashboards and alerts rather than incident-time archaeology. That is the core of our PostgreSQL load monitoring and PostgreSQL health check practice: alert on dead-tuple ratio and XID age trends days early, in daylight, with a named owner.

The midnight problem is an ownership problem

Suppose you do all of the above. Tiers are defined, parameters are in version control, PostgreSQL 18 eager freezing is budgeted, dashboards are green. Someone still has to be awake when a query plan flips, a partition is not created, or a replication slot is left behind by a failed change-data-capture job.

The question is not whether the work exists. It is who does it, at what hour, with what authority. Almost every organisation that pages application engineers for vacuum problems has never written that answer down. PostgreSQL 18 vacuum tuning has to belong to a role, not to a rotation.

A workable division of responsibility looks like this. Application engineers own query and schema shape: HOT-friendly update patterns, fillfactor, and above all not holding transactions open across network calls. They escalate; they do not tune server parameters in a live session.

Platform and SRE on-call own alerting, capacity and the documented runbook, and escalate when the failsafe age is approached. A PostgreSQL specialist owns PostgreSQL 18 vacuum tuning itself — policy, per-table parameters, the eager-freeze budget, reclaim strategy and change review — and is on call for the database specifically. The data platform owner owns the service level objective and accepts the residual risk.

Most mid-sized teams cannot justify three PostgreSQL specialists across three time zones, which is precisely the gap remote DBA services and 24×7 emergency DBA coverage exist to fill. Your engineers keep building product. Someone who tunes vacuum for a living is already awake.

A 30-day plan to get PostgreSQL 18 vacuum tuning off the on-call rotation

Week 1 — baseline. Capture the four queries above into time series. Record current per-table storage parameters and the full non-default GUC set. Inventory long transactions, replication slots and prepared transactions. Confirm the actual PostgreSQL minor version, and whether io_method is worth changing on your storage.

Week 2 — classify and write it down. Assign every table above a size or churn threshold to one of the four tiers. Produce the ALTER TABLE statements as a reviewed migration, not as ad-hoc psql. Decide the eager-freeze budget per tier. Decide explicitly whether vacuum_truncate stays on.

Week 3 — apply and observe. Roll out tier by tier, starting with the queue tables that page you most. Compare total_autovacuum_time and dead-tuple ratio against the Week 1 baseline. Watch delay_time to confirm whether cost limits are now the binding constraint. Resist changing more than one variable per tier per week: PostgreSQL 18 vacuum tuning is a sequence of experiments, not a configuration dump.

Week 4 — automate the escalation. Alert on dead-tuple ratio, XID age headroom in days rather than transactions, autovacuum queue depth, and vacuum duration outliers. Grant pg_signal_autovacuum_worker to on-call. Write the runbook: symptom, first query, safe action, escalation path, and an explicit prohibition on VACUUM FULL during business hours. Then schedule the review that repeats this quarterly — the shape of the work we deliver as a PostgreSQL maintenance plan.

Anti-patterns we still find in production

Disabling autovacuum on a busy table. This never removes the work; it defers it into an anti-wraparound vacuum that arrives with no throttle and no negotiation. If a table cannot tolerate autovacuum, the parameters are wrong, not the feature.

VACUUM FULL as a routine remedy. It rewrites the table under an ACCESS EXCLUSIVE lock and needs free space equal to the new relation. For online reclaim use pg_repack or, better, partition rotation so the space is never trapped in the first place.

Copying parameters from a blog post. Including this one. There is no universal PostgreSQL 18 vacuum tuning profile. A cost delay that is right for a 2 TB database on NVMe with sixteen eager-freeze-heavy partitions is wrong for a 40 GB database on network storage. Baseline, change one thing, measure.

Tuning vacuum to fix a held-snapshot bug. If xmin is pinned, no amount of PostgreSQL 18 vacuum tuning will reclaim a single byte. Fix the application transaction or the abandoned slot.

Ignoring indexes. Vacuum cleaning the heap does not undo a bloated B-tree. Index maintenance is a parallel workstream to PostgreSQL 18 vacuum tuning, not a subset of it. Track index bloat separately; see the PostgreSQL wiki's database bloat estimation queries, and note that they are estimates.

Assuming the upgrade did the tuning for you. PostgreSQL 18's better defaults reduce the number of ways to get this wrong; they do not encode your workload. If you are still planning the jump, our notes on why PostgreSQL 17 matters and on running PostgreSQL on Kubernetes cover the surrounding decisions.

Frequently asked questions about PostgreSQL 18 vacuum tuning

Does PostgreSQL 18 make manual vacuum tuning unnecessary?

No, but it narrows the gap. autovacuum_vacuum_max_threshold stops the proportional trigger from deferring vacuum forever on huge tables, and eager freezing reduces the shock of aggressive vacuums. Workload-specific decisions — queue tables, retention strategy, cost limits for your storage — still require deliberate PostgreSQL 18 vacuum tuning.

What should I set vacuum_max_eager_freeze_failure_rate to?

Start with the 0.03 default, then treat it as a per-tier decision in your PostgreSQL 18 vacuum tuning policy. Raise it for append-mostly and time-partitioned tables where freezing succeeds most of the time; leave it alone for tables whose pages are frequently re-dirtied, since eager freezes on those pages are wasted work. Setting it to 0 disables eager scanning entirely and returns you to pre-18 behaviour.

Is it safe to raise autovacuum_max_workers at runtime in PostgreSQL 18?

Yes, up to autovacuum_worker_slots, with a configuration reload. Raise vacuum_cost_limit at the same time, because the budget is shared between workers — otherwise you get more workers each running slower.

Why does vacuum finish successfully but reclaim no space?

Something is holding an old snapshot: a long-running or idle-in-transaction session, an inactive replication slot, an orphaned prepared transaction, or standby feedback. Query four in this article identifies all four cases.

How do I know whether autovacuum is throttled or genuinely slow?

Compare delay_time in pg_stat_progress_vacuum with the vacuum's elapsed time. A high ratio means cost-based delay is the constraint. A low ratio with slow progress points at index count, I/O bandwidth or WAL generation, all of which PostgreSQL 18's VERBOSE and autovacuum logging now report in more detail.

Who should own vacuum tuning in an engineering organisation?

A database specialist. In a healthy model, PostgreSQL 18 vacuum tuning sits with that specialist, application engineers own schema and transaction hygiene, and on-call owns documented, bounded actions. If no one owns it, the pager owns it — and the pager always chooses midnight.

Stop paying for this at 2 a.m.

PostgreSQL 18 gives you better defaults, a hard ceiling on the trigger equation, runtime worker concurrency, an eager-freeze budget and real vacuum telemetry. What it cannot supply is an owner. MinervaDB provides that: PostgreSQL consulting for the policy design, performance troubleshooting when something has already gone wrong, and 24×7 consultative support so that PostgreSQL 18 vacuum tuning happens in a design review with a named owner — not in a psql session at 00:42 by whoever happened to be holding the pager.

Sources and further reading

• PostgreSQL 18 documentation — §19.10 Vacuuming (all autovacuum, cost-delay and freezing parameters)
• PostgreSQL 18 documentation — §24.1 Routine Vacuuming, including eager freezing and wraparound
• PostgreSQL 18 documentation — VACUUM command reference (including the new ONLY option)
• PostgreSQL 18 documentation — Progress Reporting (pg_stat_progress_vacuum.delay_time)
• PostgreSQL 18 documentation — Monitoring statistics (total_vacuum_time, total_autovacuum_time)
• PostgreSQL 18 documentation — Resource consumption (io_method, maintenance_io_concurrency)
• PostgreSQL 18 documentation — Predefined roles (pg_signal_autovacuum_worker)
• PostgreSQL 18 documentation — Table storage parameters
• PostgreSQL 18.0 release notes
• PostgreSQL 18 release announcement, 25 September 2025
• PostgreSQL wiki — database bloat estimation queries