The slot column that is null when it matters

Moving decoding off the primary, and the cost of doing it

Logical replication has always had an awkward asymmetry. The subscriber can be anything you like, but the publisher had to be the primary, because a logical slot needs to decode write-ahead log records and only the primary could host one. A fleet that had built a change-data-capture pipeline therefore had the pipeline’s read load, its slot, and its retention risk all sitting on the node least able to absorb any of them.

PostgreSQL 16 allows a logical slot to live on a standby. The Logical Replication section of the PostgreSQL 16 release notes records it, along with the function you have to know about to use it, and the release adds a column to the slot view because the new arrangement creates a failure mode that did not previously exist.

The failure mode is worth stating plainly, because it is the reason the column exists. A slot on a standby holds a position in a stream the standby is replaying, not producing. If the primary removes a row version that the slot still needs, or if the primary’s own horizon advances past what the slot depends on, the standby has to choose between stalling recovery and invalidating the slot. It invalidates the slot. The consumer stops, and the only evidence is in the slot row.

The column, and the version that does not have it

SELECT pg_create_physical_replication_slot('shipper_phys');
SELECT slot_name FROM pg_create_logical_replication_slot('shipper_logical', 'pgoutput');

 pg_create_physical_replication_slot 
-------------------------------------
 (shipper_phys,)
(1 row)

    slot_name    
-----------------
 shipper_logical
(1 row)

A monitoring query written for 15 that wants to know whether a slot has been invalidated has one column to work with, and it is about retention rather than conflict:

SELECT slot_name, slot_type, active, wal_status FROM pg_replication_slots ORDER BY slot_name;

    slot_name    | slot_type | active | wal_status 
-----------------+-----------+--------+------------
 shipper_logical | logical   | f      | reserved
 shipper_phys    | physical  | f      | 
(2 rows)

Ask 15 the question 16 can answer and the parser stops you:

SELECT slot_name, conflicting FROM pg_replication_slots ORDER BY slot_name;

ERROR:  column "conflicting" does not exist
LINE 1: SELECT slot_name, conflicting FROM pg_replication_slots ORDE...
                          ^

The function that makes slot creation on a standby practical is missing too:

SELECT pg_log_standby_snapshot();

ERROR:  function pg_log_standby_snapshot() does not exist
LINE 1: SELECT pg_log_standby_snapshot();
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

On 16, and the null that catches people

SELECT slot_name, slot_type, plugin, active, conflicting FROM pg_replication_slots ORDER BY slot_name;

    slot_name    | slot_type |  plugin  | active | conflicting 
-----------------+-----------+----------+--------+-------------
 shipper_logical | logical   | pgoutput | f      | f
 shipper_phys    | physical  |          | f      | 
(2 rows)

Read that output carefully. The logical slot reports false, meaning it exists and has not been invalidated by a conflict. The physical slot reports nothing at all, and nothing is not false. Conflicts of this kind apply to logical decoding, so the column is undefined for a physical slot and the server says so with a null rather than by asserting a value it does not have.

That is correct and it is also the sharpest edge on this page, because of what null does inside a predicate.

SELECT slot_name, slot_type FROM pg_replication_slots WHERE NOT conflicting ORDER BY slot_name;
SELECT slot_name, slot_type FROM pg_replication_slots WHERE conflicting IS NOT TRUE ORDER BY slot_name;

    slot_name    | slot_type 
-----------------+-----------
 shipper_logical | logical
(1 row)

    slot_name    | slot_type 
-----------------+-----------
 shipper_logical | logical
 shipper_phys    | physical
(2 rows)

The first query is the one somebody writes when they read the release note, and it silently drops every physical slot on the cluster. If that query is behind a check that counts healthy slots, the count is wrong on every cluster that has a standby attached, and it is wrong in the quiet direction: fewer rows, no error, a number that still looks plausible. The second form is the one to keep.

The same care is needed on the way out to a metrics system. A boolean that is sometimes null becomes, depending on the exporter, a zero, a one, or an absent series, and only the last of those is defensible. Check what yours does before you alert on it.

The function you have to call

Creating a logical slot requires a particular kind of write-ahead log record that describes the state of running transactions. On a primary, one of those turns up on its own soon enough. A standby does not produce write-ahead log records at all, so slot creation there waits for the primary to emit one, and on a quiet primary that wait can be long enough to look like a hang.

SELECT pg_log_standby_snapshot() IS NOT NULL AS record_logged;

 record_logged 
---------------
 t
(1 row)

That call is made on the primary, and it is what unblocks a slot creation that is waiting on the standby. The practical shape is two connections: start the creation against the standby, then make this call against the primary. Anyone who tries it in the other order, or who forgets the second connection entirely, sees a command that never returns and concludes the feature does not work.

The other prerequisite is feedback from the standby to the primary. Without it the primary has no reason to hold back the row versions a slot on the standby still needs, and conflicts become routine rather than exceptional. Turning it on has its own cost, which is that the primary’s cleanup horizon is now partly controlled by a replica, and a standby that stops reporting becomes a reason for bloat on the primary.

What to watch once it is running

Three things, and they are not the ones a primary-hosted slot needed.

• The conflict flag on every logical slot, on every node, using a predicate that survives nulls. This is the new failure and the one with no other symptom.
• Retention, as before. A slot that nobody consumes still pins write-ahead log, and on a standby the consequence lands on the standby’s own storage.
• Whether the standby is still sending feedback to the primary, because that is what stands between a working arrangement and a primary that cannot clean up.

An invalidated slot does not recover on its own. It has to be dropped and recreated, and the consumer has to deal with the gap, which for most change-data-capture pipelines means a resynchronisation of whatever it was following. Planning that path before it is needed is the difference between an inconvenience and an incident, and it is the argument for keeping the conflict flag on a page somebody actually looks at rather than only in an alert rule.

A slot view read on the primary says nothing about slots that live on the standby, which is the part that surprises teams whose monitoring has only ever pointed at one node. Slots are local to the cluster that holds them. After this change, “how many logical slots does this fleet have” is a question you have to ask every node, not just the writable one, and a slot inventory that assumes otherwise will be missing exactly the slots this feature was added to enable. The reasons a slot can be invalidated grew again in the next release, where the server started recording which one applied.

SELECT pg_drop_replication_slot('shipper_phys');
SELECT pg_drop_replication_slot('shipper_logical');

 pg_drop_replication_slot 
--------------------------
 
(1 row)

 pg_drop_replication_slot 
--------------------------
 
(1 row)

Before you move a pipeline onto a standby

The feature is attractive for exactly the reason it is risky: it takes load off the node you care about most and puts it on a node whose health nobody was watching as closely. Four things are worth settling before the move rather than after it.

Decide what happens when the standby is rebuilt. A replica that is recreated from a fresh base backup does not bring its slots with it, so every logical slot living there disappears with it. If your rebuild procedure is a wiki page from before this feature existed, it now silently destroys a pipeline.

Decide what happens at failover. A slot on a standby that gets promoted is in a different position than a slot on a standby that stays a replica, and a slot on the old primary is not reachable at all once it has gone. Which node the pipeline follows after a failover is a decision, and making it during the failover is the expensive way.

Decide who owns the feedback setting. Turning it on ties the primary’s cleanup horizon to a replica’s progress, which means a standby that pauses or falls behind becomes a source of bloat on the primary. That is a trade worth making deliberately, with somebody watching the primary’s oldest transaction age afterwards.

Decide how the consumer resynchronises. An invalidated slot cannot be resumed, so the pipeline has to have a way of recovering from a gap. For a change-data-capture consumer that usually means a full reload of whatever it was tracking, and knowing how long that takes is the difference between a planned outage and an unplanned one.

None of those are reasons not to do it. They are the questions the conflict column exists to make answerable, and answering them while nothing is broken takes an afternoon.

What it costs

The column costs nothing to read and the view needs no setting. Decoding on a standby costs what decoding always costs, moved to a different machine: the standby now does the work of turning write-ahead log into a change stream, and that work competes with replay.

The real cost is the coupling. Before this change, a standby could fall behind or be rebuilt without anybody thinking about the replication pipeline. After it, the pipeline’s health depends on a node that also has to keep up with recovery, and on a feedback loop that ties the primary’s cleanup to the standby’s progress. That is a good trade for most fleets and it is not a free one, and the conflict column is the instrument that tells you when the trade has gone against you.