Slots that expire when nobody reads

The abandoned slot, and why it has always been fatal

A replication slot is a promise. It tells the server to keep write-ahead log that a consumer has not read yet, and, if the slot is logical, to hold back the horizon below which dead rows can be cleaned up. The server keeps that promise absolutely, because the alternative is a consumer that silently misses changes, and a replication system that silently misses changes is worthless.

The consequence is the oldest operational hazard in PostgreSQL replication. A standby is decommissioned and its slot is not dropped. A change-data-capture consumer is switched off for a maintenance window that turns into a quarter. A developer creates a slot to try something and forgets. In each case nothing is wrong, nothing alerts, and the log directory grows until the disk fills and the primary stops accepting writes.

There has been a partial answer for some releases, in the form of a ceiling on how much log a slot may hold. It works, and it is blunt: it is expressed in bytes, so the time a consumer has to reconnect depends on how busy the cluster happens to be, and a quiet weekend gives an abandoned slot much longer to survive than a busy Monday does. It also does nothing at all about the other half of a logical slot’s promise, which is the cleanup horizon.

PostgreSQL 18 adds a second answer expressed in time. A slot that has had no consumer attached for longer than a configured duration is invalidated, and the reason is recorded.

The setting does not exist on 17.

ALTER SYSTEM SET idle_replication_slot_timeout = '2s';

ERROR:  unrecognized configuration parameter "idle_replication_slot_timeout"

That error is the shape a fleet-wide configuration change meets on the first cluster that has not been upgraded. There is no spelling that means the same thing on an earlier major, so a template carrying this setting has to be conditional on the version rather than uniform.

What an abandoned slot does on 17

A logical slot is created, nothing ever reads from it, time passes, and a checkpoint happens.

SELECT slot_name FROM pg_create_logical_replication_slot('orders_stream', 'pgoutput');
SELECT pg_sleep(8);
CHECKPOINT;
SELECT slot_name, active, invalidation_reason,
       inactive_since IS NOT NULL AS has_an_idle_stamp
FROM pg_replication_slots;

   slot_name   
---------------
 orders_stream
(1 row)

 pg_sleep 
----------
 
(1 row)

CHECKPOINT
   slot_name   | active | invalidation_reason | has_an_idle_stamp 
---------------+--------+---------------------+-------------------
 orders_stream | f      |                     | t
(1 row)

The slot is inactive and it is perfectly valid. The column recording when it last had a consumer is populated, which is the improvement an earlier release made and is a genuine help: it turns “this slot has no consumer right now” into “this slot has had no consumer since Tuesday”, and those are different statements about very different situations. What 17 will not do is act on the difference. The slot goes on holding log and holding the horizon indefinitely.

The same slot on 18, with a timeout

The setting is cluster-wide and takes a reload rather than a restart. Two seconds is absurd as a production value and is what makes the demonstration fit on a page.

ALTER SYSTEM SET idle_replication_slot_timeout = '2s';
SELECT pg_reload_conf();
SELECT pg_sleep(1);
SHOW idle_replication_slot_timeout;

ALTER SYSTEM
 pg_reload_conf 
----------------
 t
(1 row)

 pg_sleep 
----------
 
(1 row)

 idle_replication_slot_timeout 
-------------------------------
 2s
(1 row)

Now the same abandoned slot, observed before and after a checkpoint.

SELECT slot_name FROM pg_create_logical_replication_slot('orders_stream', 'pgoutput');
SELECT slot_name, active, invalidation_reason,
       inactive_since IS NOT NULL AS has_an_idle_stamp
FROM pg_replication_slots;
SELECT pg_sleep(8);
SELECT slot_name, invalidation_reason AS before_a_checkpoint FROM pg_replication_slots;
CHECKPOINT;
SELECT slot_name, active, invalidation_reason AS after_a_checkpoint FROM pg_replication_slots;

   slot_name   
---------------
 orders_stream
(1 row)

   slot_name   | active | invalidation_reason | has_an_idle_stamp 
---------------+--------+---------------------+-------------------
 orders_stream | f      |                     | t
(1 row)

 pg_sleep 
----------
 
(1 row)

   slot_name   | before_a_checkpoint 
---------------+---------------------
 orders_stream | 
(1 row)

CHECKPOINT
   slot_name   | active | after_a_checkpoint 
---------------+--------+--------------------
 orders_stream | f      | idle_timeout
(1 row)

Read the middle reading first, because it is the part that decides how this behaves in practice. Eight seconds after creation, four times over a two-second timeout, the slot is still valid. The invalidation is not performed by a timer. It is performed during a checkpoint, which is when the server is already deciding which log segments it may remove, and the check happens as part of that work.

So the effective expiry is the timeout rounded up to the next checkpoint. On a cluster with the default checkpoint interval, a timeout of an hour means a slot survives somewhere between an hour and an hour and five minutes, which is fine. A timeout shorter than the checkpoint interval does not give you a shorter expiry, it gives you the checkpoint interval, and configuring one in the belief that it does is the misunderstanding this page exists to prevent. After the checkpoint, the reason is recorded.

The slot is then unusable, and says so clearly.

SELECT count(*) FROM pg_logical_slot_get_changes('orders_stream', NULL, NULL);

ERROR:  can no longer access replication slot "orders_stream"
DETAIL:  This replication slot has been invalidated due to "idle_timeout".

That message is better than it needs to be. It names the slot, it says the slot was invalidated rather than dropped, and it gives the reason, so a consumer that reconnects after an outage gets an error it can act on rather than a generic failure. The slot row also stays in the view, which matters: an invalidated slot is evidence that something went wrong, and a slot that had been dropped outright would leave the operator with a consumer that cannot connect and no explanation on the server.

Choosing a value, which is a policy decision rather than a tuning one

The setting is off by default, and leaving it off is a defensible choice. What it does is convert one failure into another: instead of an abandoned slot filling a disk, you get a consumer that was legitimately offline for longer than you guessed and can no longer resume. Neither is good. The question is which one your organisation can recover from faster, and that is not a database question.

Three considerations make the answer less arbitrary.

The first is what the consumer does when it cannot resume. A logical replication subscriber whose slot has gone needs a fresh copy of the data, which on a large table is a serious operation. A change-data-capture pipeline that can be pointed at a fresh slot and tolerate a gap is a different matter entirely. A physical standby needs rebuilding either way. If the answer to “what happens if this slot expires” is a multi-hour resync, the timeout should be much longer than the longest outage you can imagine, and probably off.

The second is that the timeout applies to every slot on the cluster. There is no per-slot setting, so the value has to suit the most fragile consumer rather than the most disposable one. A cluster with both a production subscriber and a handful of experimental slots gets the production subscriber’s value, and the experimental slots have to be cleaned up some other way.

The third is that this is not a substitute for monitoring the slots. A slot that is about to expire and a slot that has expired are both things you would rather hear about from an alert than from a consumer, and the idle stamp has been available for that purpose since before this setting existed. The timeout is the backstop for the case where nobody was watching, and a backstop that fires is still an incident.

A value of several days is where most fleets will land: long enough that an ordinary outage, a long weekend or a slow rebuild does not trip it, short enough that a slot forgotten in March does not take down the primary in April.

What to put in place alongside it

• Alert on the idle stamp before the timeout can fire, with a threshold comfortably below it. The order you want is monitoring first, backstop second.
• Alert on the invalidation reason separately, and treat any value in it as an incident rather than as information. A slot does not invalidate itself for a good reason.
• Set the value per cluster rather than fleet-wide, because the right answer depends on what consumes from that cluster and those differ more than the clusters do.
• Do not lower the checkpoint interval to make the timeout more precise. Checkpoint spacing is a much larger lever with much larger consequences, and the imprecision here is not worth paying for.

How it sits beside the setting you may already have

Most clusters that have worried about this problem already have the other guard in place, and the two do not overlap as much as they look like they should.

The byte ceiling invalidates a slot that has fallen too far behind in the log. It is a measure of distance, so it fires when a slot is behind by more than the cluster is willing to retain, and how long that takes depends entirely on how much log the workload is generating. The same abandoned slot on the same cluster can survive for days over a quiet holiday and for twenty minutes during a bulk load.

The timeout is a measure of absence, so it fires after a fixed interval regardless of what the cluster is doing. For a slot that has genuinely been abandoned, that is the property you want: the behaviour of the guard does not depend on the behaviour of a workload that has nothing to do with the slot.

The two also protect different resources. The byte ceiling protects the log directory and nothing else. The timeout is the only one of the two that does anything about a logical slot’s other promise, which is holding back the cleanup horizon, and that is the more insidious problem: a logical slot with no consumer stops dead rows from being removed anywhere in the database, so tables the slot has nothing to do with stop being vacuumable and grow without any log directory growing to explain it. That failure is diagnosed wrongly more often than almost anything else in PostgreSQL operations, because every symptom points at vacuum.

So the sensible arrangement is both, set for different purposes. The byte ceiling sized to what the disk can absorb, as a hard stop. The timeout set to the longest outage a consumer is expected to survive, as the thing that actually deals with abandonment. And an alert on the idle stamp well before either can fire, because both are backstops and a backstop firing means the monitoring did not work.

What it costs

The check itself is free. It happens during a checkpoint, which is looking at the slots anyway, and it compares two timestamps.

The cost is entirely in what happens when it fires, which is why the setting is off by default and why it deserves more thought than most settings of its size. It takes a reload, it can be turned back off the same way, and turning it off does not revive a slot that has already been invalidated.