Prompt and scope
You maintain a write-heavy PostgreSQL cluster where autovacuum cannot keep up with bloat. The team wants to test PostgreSQL 19 Beta before production. The project says the release can still change and adds autovacuum_max_parallel_workers plus a new vacuum-priority mechanism. Design an evaluation that does not affect live transactions.
What the interviewer is testing
The interviewer wants you to treat a beta as an experiment rather than a free upgrade, and to distinguish cleanup throughput from I/O, lock waits, query latency, and replication lag. A strong answer limits the experiment, establishes a baseline, plans rollback, and states when not to enter production.
Questions to clarify first
- Which tables, indexes, or long transactions cause the bloat?
- What are the current autovacuum-worker, I/O, WAL, and replication-lag baselines?
- Is the goal to reduce vacuum backlog or validate the new priority mechanism?
- Is there a rebuildable read-only replica and a rehearsed recovery procedure?
30-second answer
“I would put PostgreSQL 19 Beta on a disposable replica or shadow environment first, recording table bloat, dead tuples, vacuum duration, I/O, lock waits, WAL, and replication lag. Start with a low worker cap and a small set of tables, replaying the same writes for comparison. If query p99, replication lag, or I/O guardrails breach, stop the trial and restore the old version. Until the beta becomes a release, I would not treat its result as a production commitment.”
Step-by-step solution
1. Make the beta boundary explicit
Record version, commit hash, build options, and configuration; treat new parameters as experimental interfaces that may change. Production should expose only a reversible replica or shadow path, never a direct beta upgrade route.
2. Establish a table-level baseline
Record dead tuples, bloat, autovacuum trigger intervals, duration, index-cleanup ratio, I/O, WAL, lock waits, and query latency per table. Identify long transactions, high-update tables, and large indexes instead of hiding hotspots behind a cluster average.
3. Choose the parallel experiment unit
Replay representative writes on an isolated replica and enable low parallelism for a small table group. Compare one worker with the parallel setting for cleanup throughput, I/O queues, CPU, cache hit, WAL, and replication catch-up time. Parallelism must fit the storage and instance budget.
4. Validate priority changes
Record which tables the new scoring or priority mechanism selects, checking that low-write but highly bloated tables are not starved. Observe long transactions, partitioned tables, and large indexes separately because their cleanup bottlenecks differ.
5. Set release guardrails
Gate on query p95/p99, transaction errors, lock waits, replication lag, I/O saturation, WAL growth, and vacuum backlog. Keep a bootable old-version image, configuration snapshot, and recovery-time objective during the trial. When a guardrail fires, stop admitting new tables instead of raising parallelism.
6. Roll back and conclude
Stop beta writes first, wait for or switch the replica, preserve diagnostics, then restore the old version and configuration. Compare bloat, latency, cost, and failures across the full experiment window. Plan production only after beta iterations are stable, the upgrade path is explicit, and the final release confirms the capability.
Model answer
I would treat PostgreSQL 19 Beta as disposable experimentation. Replay real write patterns on a shadow replica and establish per-table baselines for dead tuples, bloat, vacuum duration, I/O, WAL, lock waits, and replication lag. Start workers at a low cap for representative hotspot tables and verify that the new priority mechanism does not starve others. Query p99, replication lag, or I/O breaches stop the trial and restore the old version. Do not promise production compatibility during beta; move to a staged upgrade only after a final release and recovery rehearsal pass.
Common mistakes
- Deploying the beta directly to production → APIs and behavior may change → use a shadow environment and record the version.
- Watching cleanup throughput only → queries and replication suffer → measure latency, I/O, WAL, and replication together.
- Raising parallelism for every table → I/O peaks and lock contention grow → canary by table and replica.
- Ignoring long transactions → dead tuples remain uncleaned → include transaction age in the baseline.
- No exit path → failure requires downtime → keep an old image, config, and recovery rehearsal.
Follow-up questions and responses
Why not validate only with test data?
Test data rarely reproduces production update ratios, index sizes, and long transactions. At minimum, replay an anonymized production write distribution on an isolated replica.
Is more parallelism always better?
No. Cleanup throughput can rise while I/O, CPU, WAL, and replication pressure also rise. The instance budget and latency guardrails constrain the worker count.
How do you detect priority starvation?
Record trigger, start, and completion times for every table, then compare by write volume and bloat. A table that remains unselected should trigger an alert and manual review.
When should the beta trial stop?
Stop expansion when the target does not improve, guardrails repeatedly fire, rollback is not repeatable, or the final-release schedule is uncertain. Preserve the data and wait for a later version.