PostgreSQL VACUUM Troubleshooting Interview Questions: Long Transactions and Bloat

PostgreSQL VACUUM troubleshooting interview questions test whether you can distinguish three different problems: obsolete row versions that cannot yet be removed, reusable space that has not returned to the operating system, and maintenance that is not keeping up with new work. Those problems can produce similar charts but require different fixes.

The useful answer is not "run VACUUM FULL." It is a diagnostic chain: identify what is growing, inspect the cleanup horizon and maintenance progress, choose the least disruptive intervention, and define how you will verify it. This guide uses PostgreSQL 18 terminology and original incident exercises, not a claim about a specific company's interview.

Here, we focus on the next question: what evidence explains a VACUUM that appears not to work?

Why did VACUUM finish without making the table smaller?

Ordinary VACUUM primarily makes eligible obsolete tuple space available for reuse. It normally does not compact the whole relation into a smaller operating-system file. There is a limited exception for removable empty pages at the end. PostgreSQL's routine-vacuuming documentation distinguishes this from VACUUM FULL, which rewrites the relation and takes an ACCESS EXCLUSIVE lock.

Imagine a simplified table file occupying ten equal-sized pages. Six pages' worth of data remain useful; four pages contain reusable holes mixed among those live rows. A successful ordinary vacuum can leave a ten-page file. Later writes may reuse those holes instead of extending the file.

That is not the same as failure. Measure whether eligible dead tuples were cleaned and whether growth stabilizes under comparable workload. If your only success metric is a smaller file immediately after maintenance, you are testing for compaction rather than routine cleanup.

Also separate heap, indexes, and TOAST storage. A total relation-size graph can grow even when the main table behaves as expected. Ask which size function or monitoring series produced the alert before explaining the cause.

What does a long transaction actually prevent?

PostgreSQL must retain a row version while an active visibility horizon may still need it. A sufficiently old transaction or snapshot can therefore hold back cleanup. But transaction age alone is a lead, not proof: inspect relevant XID and xmin information and the session's state.

Consider this teaching timeline. At 09:00, session A establishes a Repeatable Read snapshot. At 09:05, session B updates many orders and commits. At 09:10, VACUUM runs while A is still open. Versions visible to A cannot simply disappear because B committed.

Once A finishes, those versions may become eligible for removal, assuming no other horizon retains them. VACUUM must then process the newly eligible work. Ending A does not instantly compact the file, and a second old transaction can keep the same symptom alive.

Be precise about isolation level. A Read Committed transaction does not generally keep one statement snapshot for its whole lifetime in the same way as Repeatable Read. Open transactions can still matter through their transaction IDs and other retained state. Diagnose observed retention rather than asserting that every old connection blocks every vacuum.

Which evidence should I collect first?

Start with read-only inspection. The following queries are diagnostic examples; visibility of other sessions depends on permissions. Replace the example relation with the actual schema-qualified name and examine results before considering any intervention.

SELECT pid, state, xact_start, backend_xid, backend_xmin,
       wait_event_type, wait_event
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

SELECT relname, n_live_tup, n_dead_tup,
       last_vacuum, last_autovacuum,
       vacuum_count, autovacuum_count
FROM pg_stat_user_tables
WHERE relid = 'public.orders'::regclass;

SELECT pg_size_pretty(pg_relation_size('public.orders')) AS heap,
       pg_size_pretty(pg_indexes_size('public.orders')) AS indexes,
       pg_size_pretty(pg_total_relation_size('public.orders')) AS total;

n_live_tup and n_dead_tup are estimates, not an exact bloat measurement. A large dead-tuple estimate is a reason to investigate, not enough evidence to calculate precise reclaimable disk space. Statistics can lag or be cached within a monitoring transaction, so collect comparable observations deliberately.

Record the time, workload, database, relation, and maintenance state. A graph without those labels invites misleading comparisons. If a bulk update is still running, a rising dead-tuple count may reflect incoming work faster than cleanup rather than a vacuum that does nothing.

Can you diagnose this synthetic incident?

Here is an original interview dataset. Treat the values as invented practice evidence, not a real customer incident or a recommended threshold.

Observation	At 10:00	At 10:15	What it suggests
orders heap size	40 GB	40 GB	File size alone cannot prove cleanup
Estimated dead tuples	8 million	8.4 million	Backlog may be growing
Last autovacuum	09:58	10:13	Workers are running, not absent
Reporting session	Old backend_xmin	Still retained	Possible visibility-horizon blocker
Update workload	Active	Active	New dead versions continue arriving

A weak answer says autovacuum is broken because the count increased. A stronger answer has two hypotheses: retention prevents removal, or workers cannot keep up with churn. The next observation should discriminate between those explanations.

Inspect the old reporting session's transaction and owner. Review vacuum logs for removable versus not-yet-removable tuples where available. Check progress and wait events. Compare a bounded period after an authorized transaction completion with a similar workload period beforehand.

If dead tuples fall after the old snapshot ends but the file remains 40 GB, the result is consistent with successful reuse-oriented cleanup. If the backlog remains high, continue checking other horizons and worker capacity. Do not declare victory from one favorable counter.

Is autovacuum blocked, slow, or not starting?

These are separate branches. "Not starting" sends you toward table-specific settings, trigger thresholds, worker availability, and whether the relevant activity has been recognized. "Started but waiting" calls for lock and wait-event inspection. "Running and making progress slowly" calls for workload, I/O, cost throttling, and relation-size context.

For a running ordinary vacuum, PostgreSQL exposes pg_stat_progress_vacuum. Its phase and block counters help identify the current work. These values are not a simple universal percentage complete because vacuum has multiple phases and can revisit index cleanup work.

SELECT pid, relid::regclass, phase,
       heap_blks_total, heap_blks_scanned,
       heap_blks_vacuumed
FROM pg_stat_progress_vacuum;

Take two observations rather than treating one static row as a stall. If counters move, estimate progress cautiously and compare resource use. If a session waits, inspect the actual blocker before changing vacuum settings.

Manual VACUUM FULL is reported through the cluster-style progress machinery, not the ordinary vacuum view. Naming the wrong view can produce a convincing but false claim that nothing is running. Always connect the observation to the exact maintenance command.

What else can retain the cleanup horizon?

Long transactions are not the only possibility. Prepared transactions and replication-related xmin retention can matter. Inspect pg_prepared_xacts and the relevant fields in pg_replication_slots; understand whether a slot is active and whether a downstream consumer still needs it.

Do not collapse WAL retention and tuple retention into one problem. A slot retaining WAL can fill storage for a different reason than a relation accumulating dead tuples. Fields such as restart_lsn, xmin, and catalog_xmin describe different retention concerns.

Likewise, standby feedback can preserve row versions on the primary to protect standby queries. The trade-off is deliberate: avoiding query cancellations can increase primary-side cleanup pressure. Explain the trade-off before suggesting a setting change.

Never recommend dropping slots or terminating sessions indiscriminately. A slot may support a business-critical replication pipeline, and termination rolls back active work. Identify ownership, confirm the recovery plan, and use the smallest authorized action. In an interview, saying what you would verify before acting is part of the technical answer.

When is VACUUM FULL the right answer?

Consider it when physical compaction is genuinely required, the expected benefit is meaningful, and the lock and temporary-space costs fit a planned maintenance window. It is not the routine response to every high dead-tuple estimate.

Need	Initial direction	Verify before committing
Reuse obsolete row space	Ordinary vacuum	Cleanup eligibility and ongoing churn
Refresh planner estimates	Analyze	Estimate quality and changed data distribution
Return substantial relation space	Planned rewrite/compaction	Exclusive-lock impact and free disk
Resolve transaction-age risk	Appropriate vacuum and blocker resolution	XID ages and official recovery guidance

Suppose a one-time data purge removed most of an archival table that will not refill. Physical shrinkage may be valuable. Contrast that with a high-churn queue that naturally returns to its previous size each afternoon. Repeatedly rewriting that queue may buy little and impose substantial disruption.

Do not describe VACUUM ANALYZE as identical to either operation alone. Vacuum and analyze address different maintenance concerns even when requested together. A faster query after fresh statistics does not prove that physical bloat was removed.

How do transaction-ID freezing questions change the priority?

Freezing protects the validity of very old tuple visibility information as transaction IDs age. This is not just a disk-space optimization. A database approaching transaction-ID exhaustion needs a different urgency and recovery discussion from a mildly oversized table.

Use transaction age, not elapsed calendar time, when interpreting XID-age metrics. A busy database can advance much faster than a quiet one. Inspect table and database ages and follow version-specific PostgreSQL guidance instead of reciting a memorized emergency command.

A useful interview distinction is that low dead-tuple counts do not prove freezing maintenance is unnecessary. A mostly static table can contain old unfrozen tuples. Conversely, a table with many dead tuples may not yet be close to transaction-age limits.

Do not disable autovacuum as a generic performance fix. If maintenance competes with foreground work, investigate the workload, scheduling, table settings, and I/O budget. Removing the maintenance mechanism without a replacement creates a larger delayed problem.

What would a convincing verification plan look like?

State both a cleanup hypothesis and a user-facing objective. For example: "The retained reporting snapshot prevents removal; ending that transaction should allow the backlog to fall while keeping request latency within our accepted range." This is testable and does not promise a smaller operating-system file.

Before the change, capture relation sizes, estimated dead tuples, vacuum history, progress, transaction ages, and representative latency. After the authorized intervention, inspect the same metrics under comparable load. Track trends long enough to distinguish a brief dip from sustainable cleanup.

Also check side effects: replication lag, lock waits, disk headroom, and application errors. A maintenance operation that solves storage pressure by causing an unacceptable outage is not a successful operational fix.

For a follow-up exercise, remove the old snapshot from the dataset and leave the growing backlog. Now your answer should shift toward capacity and scheduling evidence. If you still propose terminating the reporting session, you have memorized the story rather than learned the diagnosis.

How should I practice this in an interview?

Build your answer around a sequence of observations, not a list of database commands. Ask what grew, whether cleanup is eligible, whether maintenance runs, what it waits for, and what would prove the hypothesis wrong.

Rehearse a ninety-second explanation of the synthetic incident, then let a partner change one fact: the table shrank but latency rose, the worker never started, or an inactive replication slot retains xmin. Give a different next step for each variation.

Your final deliverable is a small incident note: suspected cause, supporting evidence, missing evidence, proposed action, risk, and verification. The goal is to make every maintenance recommendation answerable to an observation.

Sources and Further Reading

Checked September 23, 2026 against PostgreSQL 18 documentation. Diagnostic datasets and incident outcomes in this article are illustrative.

• PostgreSQL 18: routine vacuuming and freezing
• PostgreSQL 18: activity and cumulative statistics
• PostgreSQL 18: maintenance progress reporting
• PostgreSQL 18: VACUUM command and locking
• PostgreSQL 18: replication-slot retention fields