Question and context
A PostgreSQL 18 publisher serves an analytics subscription, an audit consumer, and temporary backfill jobs. Some consumers stay offline, preventing WAL recycling and rapidly consuming disk. Design rollout, observability, invalidation, and recovery for idle_replication_slot_timeout.
What the interviewer evaluates
- Whether you explain why slots retain WAL and when an idle timeout actually takes effect.
- Whether you distinguish logical and physical slots, subscription recovery, and rebuild boundaries.
- Whether you design deletion safeguards, alerting, auditability, and disk-pressure controls.
- Whether you provide a resnapshot, rebuild, or human-confirmation path after consumer recovery.
Clarifying questions first
Consumer semantics
What does each slot serve? May downtime data be lost, or must the consumer resume from a boundary? Does a temporary backfill slot have an explicit maximum lifetime?
Resources and windows
How much WAL and disk remain, and what are each slot's restart_lsn and consumer lag? How often do checkpoints run? Can subscriptions or consumers be rebuilt during a maintenance window?
Change authority
Who approves automatic invalidation? Is consumer confirmation, a ticket, or dual approval required first? Which slots must be protected permanently for disaster recovery or compliance audit?
A 30-second answer
I would inventory each slot's owner, purpose, activity, and retained WAL, then classify critical, recoverable, and temporary slots. Recoverable slots get an idle timeout with advance alerts; critical slots alert without automatic invalidation. Because invalidation occurs during a checkpoint, record slot state, reason, and disk metrics in the audit trail. On recovery, choose resume, resnapshot, or backup restore according to the data-loss contract.
Deep-dive solution
1. Explain slot retention
A replication slot makes the publisher retain WAL needed by a consumer that has not acknowledged it. A logical slot whose restart_lsn falls behind extends WAL lifetime; an offline consumer can turn normal growth into an unbounded risk. Record slot name, database, plugin, consumer, and owner before setting policy.
2. Classify slots
Use critical, recoverable, and temporary classes. Critical slots require human action and larger capacity budgets. Recoverable slots may expire after warnings. Temporary slots receive an expiration time at creation. Keep the protected list outside consumer-submitted configuration so an application cannot mark a critical slot as disposable.
3. Use the idle timeout correctly
PostgreSQL 18's idle_replication_slot_timeout invalidates a slot that has not been used by a replication connection for longer than the configured duration; zero disables it. Invalidation is triggered at checkpoint, so the actual time can exceed the threshold. The setting is server-level and does not replace per-slot business classification.
4. Build observability and alerts
Read pg_replication_slots regularly for slot type, database, restart_lsn, active state, invalidation reason, and failover or synced state. Calculate retained WAL bytes and oldest-slot age. Alert on growth rate, disk headroom, idle duration, and impending invalidation, including the owner and recovery runbook.
5. Handle checkpoint and races
Timeout evaluation runs during a checkpoint, so the configured threshold is not an exact deadline. A consumer may reconnect near the threshold; record last-used time, checkpoint time, and final invalidation reason. Before changing policy or dropping a slot, check for same-name creation, standby synchronization, or an in-flight subscription recovery.
6. Design recovery
After invalidation, a consumer cannot assume its old boundary remains available. If downtime loss is acceptable, create a new slot and take an initial snapshot. If loss is unacceptable, restore from backup or retained WAL and rebuild the subscription. Record the old slot, data boundary, snapshot time, and validation evidence.
7. Link capacity and change control
When the WAL directory nears its limit, pause low-priority backfills, limit WAL-producing batch work, and protect primary availability; do not delete an unknown slot. Roll out timeout changes in stages, observe WAL growth, checkpoints, replication lag, and consumer errors, then adjust the policy.
Example of a strong answer
I would maintain a slot catalog with owners and classify slots as critical, recoverable, or temporary. Recoverable and temporary slots get idle timeouts; critical slots alert only. Since invalidation occurs during a checkpoint, monitoring records checkpoint time and the actual reason. Daily reports calculate retained WAL, idle duration, and disk risk; thresholds pause backfills and notify owners. After invalidation, the recovery path chooses resnapshot, backup restore, or approved rebuild according to the data-loss contract, with every action audited.
Common mistakes
- Assuming a slot invalidates exactly when the timeout expires and ignoring checkpoints.
- Applying a short timeout to every slot and deleting disaster-recovery or audit slots.
- Watching only
activeinstead of calculating WAL retained fromrestart_lsn. - Reconnecting a consumer without deciding whether data or snapshot boundaries were lost.
- Deleting an unknown slot during a disk incident and breaking a live replication path.
- Having no owner, protected list, or executable recovery runbook.
Follow-up questions and answers
When does idle_replication_slot_timeout take effect?
After a slot has been unused by a replication connection for longer than the setting, invalidation is triggered during a subsequent checkpoint, so it may be delayed.
Should physical slots expire automatically too?
That depends on the disaster-recovery contract. A physical slot may be required by a standby; confirm another protection mechanism before allowing automatic invalidation.
How do you calculate WAL retained by one slot?
Compare the current WAL position with the slot's restart_lsn, then aggregate oldest position, growth rate, and disk headroom per slot. active alone does not describe space risk.
Can a consumer resume after returning?
Only if the required WAL still exists and the slot remains valid. After invalidation or WAL removal, resnapshot, backup restore, or an explicit data gap is required.
How do you prevent temporary backfill slot leaks?
Record expiry and owner at creation, alert separately, move to a confirmation state before invalidation, and verify that WAL recycling recovers afterward.