How would you design PostgreSQL 18 autovacuum worker capacity management?

Question and context

A PostgreSQL 18 cluster serves a high-update orders table and low-frequency archive tables. Dead tuples accumulate, queries slow down, and disk I/O spikes. Design autovacuum worker capacity management and explain autovacuum_worker_slots, autovacuum_max_workers, table settings, parallel maintenance, and incident trade-offs.

What the interviewer evaluates

• Whether you separate worker capacity, concurrent workers, trigger thresholds, and per-command parallelism.
• Whether you explain PostgreSQL 18 autovacuum_worker_slots and its runtime tuning boundary.
• Whether you use progress views, dead tuples, transaction age, and I/O metrics to locate bottlenecks.
• Whether you prioritize transaction ID wraparound protection against latency and disk pressure.

Clarifying questions first

Workload

What are update, delete, and insert rates per database and table? Do hot tables have many indexes, long transactions, or bulk deletes? Is the SLA about query latency, write throughput, or disk growth?

Resource limits

What are CPU, I/O, memory, and disk-growth limits? How are max_worker_processes, max_parallel_workers, maintenance_work_mem, and container or VM limits configured?

Risk and recovery

Is the cluster approaching transaction ID wraparound? Can you run manual VACUUM or partition swaps off-peak? Which tables may temporarily reduce writes, and which evidence must remain auditable?

A 30-second answer

I would quantify dead-tuple growth, autovacuum delay, transaction age, and I/O by table and database, then size worker slots and normal concurrency. Hot tables get lower scale factors and suitable thresholds; cold tables avoid needless scans. Before increasing concurrency, I would check CPU, I/O, and maintenance memory, then use progress views to verify backlog reduction. Wraparound risk is the top priority; rate limiting and targeted manual maintenance are bounded emergency controls.

Deep-dive solution

1. Map the resource hierarchy

autovacuum_worker_slots reserves backend slots for autovacuum workers at server start. PostgreSQL 18 typically defaults to 16 slots, subject to kernel support. autovacuum_max_workers limits concurrent autovacuum processes and cannot exceed the effective slot pool. max_worker_processes, CPU, and I/O remain shared resources, so one setting cannot be tuned in isolation.

2. Design layered concurrency

Set autovacuum_worker_slots as the capacity pool for the peak you can afford, then use autovacuum_max_workers for normal concurrency. PostgreSQL 18 allows autovacuum_max_workers to be changed at runtime up to the slot limit, while slots themselves require a server restart. Before increasing capacity, reserve background processes, connections, and operating-system semaphores.

3. Set table-specific triggers

Global thresholds provide a safety baseline. Hot tables can lower autovacuum_vacuum_scale_factor or set a more suitable autovacuum_vacuum_threshold; insert-heavy tables also need autovacuum_vacuum_insert_threshold. Compute table overrides from dead-tuple growth and table size so small tables are not delayed and large tables are not scanned too often.

4. Bound one VACUUM's resources

Regular VACUUM can run with reads and writes, but it creates I/O. max_parallel_maintenance_workers applies to index builds and VACUUM without FULL; actual workers are also limited by max_worker_processes and max_parallel_workers. maintenance_work_mem is applied to the utility command as a whole, while CPU and I/O can still rise with parallelism.

5. Handle wraparound and long transactions

Even when regular autovacuum is disabled, PostgreSQL launches necessary work to prevent transaction ID wraparound. Monitor oldest transaction age, freeze progress, and long transactions blocking VACUUM. Wraparound protection cannot be overridden by ordinary delay policies. If necessary, terminate the blocker, pause low-priority batch work, and maintain the target table.

6. Build actionable observability

Use pg_stat_progress_vacuum for phase, heap blocks, cleaned blocks, index cycles, dead-tuple bytes, and cost delay. Combine it with pg_stat_all_tables values such as n_dead_tup, last_autovacuum, last_autoanalyze, table size, and transaction age to estimate backlog and completion time. Keep slow-task evidence with log_autovacuum_min_duration; use pg_stat_io to isolate autovacuum-worker I/O.

7. Tune gradually and roll back

Change one dimension at a time: increase slots or max workers, observe CPU, I/O, latency, and backlog, then proceed. If contention rises, reduce concurrency, lower a table's frequency, or reschedule bulk jobs. Record version, table scope, metric window, and rollback value for every change so a short backlog is not mistaken for a permanent capacity shortage.

Example of a strong answer

I treat worker slots as the resource pool, max workers as normal concurrency, and table thresholds as the work generator. Hot tables get thresholds based on dead-tuple growth; cold tables avoid unnecessary scans. I first measure the effect of worker concurrency on I/O and query latency, then increase it gradually. Observability combines progress phases, n_dead_tup, transaction age, autovacuum logs, and pg_stat_io. Wraparound outranks ordinary latency, and a blocking long transaction is handled before routine tuning. Every change has a rollback record.

Common mistakes

• Increasing autovacuum_max_workers without adding slots or checking the shared worker pool.
• Treating slots as actual concurrency even though they are configured at startup.
• Applying one scale factor to every table, delaying small tables and over-scanning large ones.
• Using VACUUM FULL as routine maintenance despite locks and table rewrite cost.
• Watching disk space while ignoring transaction age, dead tuples, and progress phases.
• Disabling autovacuum for latency and forgetting wraparound protection.

Follow-up questions and answers

What is the difference between autovacuum_worker_slots and autovacuum_max_workers?

The first reserves worker backend slots at startup; the second caps concurrently running autovacuum processes. Raising max workers above the effective slots does not create that concurrency. PostgreSQL 18 allows runtime max-worker changes within the slot limit.

Does more concurrency make VACUUM faster?

Not automatically. It may shorten maintenance time while increasing CPU, I/O, and application contention. Judge it with backlog completion time, query latency, and I/O peaks together.

Why use table-specific scale factors?

A ratio can translate into a huge absolute dead-tuple count on a large table and an overly sparse trigger on a small one. Table overrides align work with update rate, size, and SLA.

How do you prove I/O is slowing workers?

Use pg_stat_progress_vacuum phases and delay time, correlate autovacuum-worker rows in pg_stat_io with disk latency and application query metrics, and compare the same time window.

When must wraparound work take priority?

When oldest transaction age nears the protection threshold, freeze progress falls behind, or the cluster runs wraparound-prevention autovacuum, remove blockers and complete freezing first. Routine business tuning yields to that risk.