Reduce logical WAL volume with restricted replication slots

Hi,

# To Reviewers

This is the biggest feature I have worked on so far. I would not be surprised if I have made mistakes or missed some cases. I would greatly appreciate anyone who reviews this patch. Given the size of the patch, I believe it will take a long journey of revisions, so for the initial rounds, please focus on the design, though any kind of comments are still appreciated.

Sorry for not splitting this large patch into multiple commits. The design and implementation evolved significantly during testing and debugging, and many of the necessary pieces were discovered along the way. So that, it ended up with a single large commit. If this makes the patch difficult to review, please let me know, and I will try to split it into a more manageable series.

# Motivation

PG19 introduced a new feature, effective_wal_level [1], that can toggle logical decoding dynamically based on the presence of logical slots. While reviewing that patch, I got the initial idea for this feature and talked to some DBAs about it. Currently, once a logical slot exists, all eligible tables write the extra WAL data required for logical decoding. So, the basic idea is: can we write the extra WAL data only for tables that are being replicated?

When I discussed the idea with some DBAs, they expressed strong interest. Their most direct reaction was that it could save money. They confirmed two things:

* They have use cases where they logically replicate only a small set of tables.
* Although PG has a mechanism to automatically clean up WAL files, in practice they manually back up WAL files, using various tools, and retain them for years.

Nowadays, long-term storage costs can be significant for many users, so reducing WAL volume would help relieve that pain.

I also briefly discussed the idea with Masahiko in a review thread [2], and he expressed some interest as well.

# Design Goal

The first thing I considered was whether this feature would be generally helpful to all deployments. My answer was no. For example, if a deployment:

* logically replicates most tables, this feature will not save significant storage;
* has mostly INSERT operations, the additional logical WAL overhead may be relatively small;
* does not retain WAL data for a long time, temporarily generating extra WAL data will not be a major concern;

Therefore, some users might be excited about this feature, while others might not. The first decision I made was that this feature should be opt-in. A DBA should be able to turn it on as easily as possible and without much burden. In particular, it should NOT require a server restart.

Second, because this feature is opt-in, when it is not enabled, it should add minimal overhead, ideally zero overhead.

Third, this feature is based on effective_wal_level. If a cluster sets wal_level = logical, this feature has no effect. Therefore, this feature should be built on the infrastructure established for effective_wal_level.

# Gap

effective_wal_level is raised from replica to logical based on the presence of logical slots, but a logical replication slot does not currently map to any relations. From a slot alone, there is no way to determine which relations are replicated through it.

# Design

## How to make the feature opt-in?

My idea is to add an attribute to replication slots, currently called unrestricted:

* If true, which is the default, the slot retains the behavior from before this feature. An unrestricted slot causes effective_wal_level to be raised from replica to logical, and all eligible tables write the extra logical WAL data.
* If false, the slot is restricted and does not raise effective_wal_level. Only relations mapped to the slot write the extra logical WAL data.

## User experience

This feature is intended not to change the data replication semantics, subject to the restrictions described below.

Currently, restricted slots have the following restrictions:

* They currently support only pgoutput; this is enforced when a restricted slot is created.
* They cannot be created from FOR ALL TABLES publications. Replicating all tables should use an unrestricted slot instead.
* The publication list of a restricted subscription can only be reduced. Once a publication is removed, it cannot be added again without recreating the subscription.
* pg_create_logical_replication_slot() cannot create a restricted slot inside an explicit transaction block.

The expected user experience is:

1. When the cluster's wal_level is set to replica and there are no logical slots, no logical WAL data is written.
2. When a user decides to logically replicate a table, they follow the normal procedure to create a publication on the publisher and enable this feature from the subscriber when creating the subscription. For example:
```
CREATE SUBSCRIPT sub
CONNECTION ‘connection_str’
PUBLICATION pub
WITH (other opts, unrestricted_slot=false);
```

3. Only tables mapped to the restricted slot will start writing the extra logical WAL data.
4. If the user wants to replicate another table, they simply add the table to an existing publication associated with the slot. Note that, here I just mean the relation will start to write logical WAL data, ALTER SUBSCRIPTION sub REFRESH pub command is still required as a normal workflow.
5. If a publication includes all tables in a schema, then when a new table is created in that schema, the table should automatically start writing the extra WAL data.
6. For a partitioned table in the publication, if a new partition is created or attached, the new partition should automatically start writing the extra WAL data.
7. If table t belongs to a restricted slot and the user adds a varchar column that causes a TOAST table to be created, the new TOAST table should automatically start writing the extra WAL data.
8. Disabling a subscription only pauses replication; it does not stop logical WAL data from being written. Therefore, when the subscription is enabled again, changes made in the meantime can still be replicated.
9. Dropping the subscription normally removes its associated slot. Logical WAL may continue to be written conservatively until stale mappings are cleaned up, or indefinitely if another slot still covers the relation.

## restricted_wal_level

effective_wal_level has established useful infrastructure, including how to enable logical decoding and where to decide whether logical WAL data should be written. I am therefore trying to build this feature on that infrastructure. Initially, I considered reusing effective_wal_level with some additional flags. After further investigation, however, I now think keeping effective_wal_level unchanged may be the best approach. Therefore, once an unrestricted slot is created, effective_wal_level is raised to logical, preserving the current behavior.

The key point for deciding whether logical WAL should be written for a relation is RelationIsLogicallyLogged(relation). It roughly checks:

* If the common conditions are not met, for example for an unlogged table, return false.
* If wal_level >= logical, return true.
* If effective_wal_level >= logical, return true.
* Otherwise, return false.

With this feature, RelationIsLogicallyLogged(relation) is extended to behave roughly as follows:

* If the common conditions are not met, for example for an unlogged table, return false.
* If wal_level >= logical, return true.
* If effective_wal_level >= logical, return true.
* If restricted_wal_level < logical, return false.
* If relation->rd_rel->relhasrestrictedslots is true, return true.
* Otherwise, return false.

So, when effective_wal_level >= logical, this feature has no effect.

restricted_wal_level is raised to logical when there is a restricted slot. A relation's relhasrestrictedslots flag is true when the relation has one or more current restricted-slot mappings. So, the core of this feature is to maintain restricted_wal_level and relhasrestrictedslots efficiently.

## Slot scope

A subscription normally maps to one slot. A subscription maps to one or more publications, and a publication maps to one or more tables. A table may in turn map to multiple physical relations, such as partitions and TOAST relations. Therefore, from a subscription, we can determine the relations for which its slots may require logical WAL. However, this chain is long, so the information needs to be maintained efficiently at runtime.

This patch introduces the term SlotScope for the mechanism that maintains the mapping between a restricted slot and its relations. Accordingly, the main implementation is in slotscope.h and slotscope.c.

The SlotScope mapping determines where logical tuple WAL may be required. It doesn't determine which relations are emitted by pgoutput; the current publication definitions and normal pgoutput filtering remain responsible for that.

For simplicity, throughout the rest of this doc, I use the terms “scoped table” and “unscoped table”. A scoped table is one included in the scope of at least one restricted logical replication slot.

The key components of the SlotScope module include:

### Publication sidecar file

A slot can be associated with publications through a subscription. The difficulty is that the slot exists on the publisher, while the subscription information is stored in the subscriber's pg_subscription catalog. There is no easy way for the publisher to query the subscriber's pg_subscription, so the mapping must also be stored on the publisher.

Because slots are stored on disk, this feature adds a sidecar file, pg_replslot/<slot>/publications, to store the immutable publication OIDs associated with the slot. The membership of those publications may still change normally.

### A new pg_restricted_slot_relation catalog

On the publisher, the new catalog stores the slot name, relation OID, and slot incarnation. It maintains the conservative writer-side mapping used to determine whether a relation may belong to a restricted slot.

A relation may be included in multiple restricted slots, so the catalog may contain multiple entries for the same relation.

A special tuple, (slot_name, InvalidOid, slot_incarnation), acts as a completion marker for restricted-slot initialization. This is necessary because slot info is stored on disk and is not transactional. Restricted-slot initialization involves approximately these steps:

Step 1: Persist the slot information to disk.
Step 2: Populate pg_restricted_slot_relation transactionally.
Step 3: Persist the slot's ready state to disk.

If a crash occurs before step 3, recovery needs to determine whether step 2 committed. Because a publication may currently contain no relations, the absence of ordinary rows for the slot in pg_restricted_slot_relation cannot distinguish an empty scope from a failed step 2.

### Additional fields added to slot

• bool unrestricted: indicates whether the slot is unrestricted.

• uint64 restricted_scope_incarnation: identifies a particular creation of the slot and distinguishes slot-name reuse. For example, if a slot is dropped and recreated with the same name, it is a different slot and all existing mappings must be rebuilt.

• bool restricted_scope_ready: indicates whether initialization of the restricted slot has completed. If step 2 above fails, no extra logical WAL will be written for the corresponding tables, so the slot is not ready for replication. This flag is set to true by the transaction commit callback after the mapping transaction commits.

### Key functions

#### Creating a restricted slot

• LogicalSlotScopePrepareFromPublications() resolves publication names to publication OIDs and then calls LogicalSlotScopePrepareFromPublicationOids().

• LogicalSlotScopePrepareFromPublicationOids() marks the slot as restricted and incomplete and persists the publication sidecar file. It then calls the existing EnsureLogicalDecodingEnabled() to make logical decoding available at the cluster level. There is a short period between slot creation and the commit of the transaction that populates pg_restricted_slot_relation, so the function temporarily enables full logical WAL logging, equivalent to raising effective_wal_level to logical. It then enables restricted mode and marks the slot state dirty so that it will be persisted.

• LogicalSlotScopeFinishCreate() is called after initialize_logical_replication_slot(). It populates pg_restricted_slot_relation and adds the slot to pending_ready_slots.

• When the transaction that populates pg_restricted_slot_relation commits, slot_scope_xact_callback() marks the pending restricted slot as ready. It then requests removal of the temporary full WAL requirement, allowing restricted_wal_level to control WAL logging unless another unrestricted slot still requires full logical WAL.

#### Handle publication changes

LogicalSlotScopePublicationAddRelations() finds restricted slots containing the changed publication, expands the added relations, and installs their mappings.

Removing a relation from a publication does not immediately remove its writer-side mapping. The stale mapping is safe because it causes only unnecessary logical WAL, and normal publication filtering prevents the relation from being emitted.

#### Handle partition attachment

CheckLogicalSlotScopeHierarchyChange() finds mappings associated with the parent relation and applies them to the attached partition's physical closure.

#### Handle TOAST creation

LogicalSlotScopeNoteToastCreation() finds slots associated with the owning table and adds the new TOAST relation to those slot incarnations.

#### Handle relation drops

LogicalSlotScopeRelationDrop() deletes mappings for a relation when the relation itself is dropped.

#### Lazy cleanup

LogicalSlotScopeCleanup() removes mappings whose (slot name, incarnation) no longer corresponds to a live, valid restricted slot.

After a restricted slot is dropped, its mapping data is not deleted immediately. Instead, autovacuum lazily calls LogicalSlotScopeCleanup() to remove the stale data. This may cause some extra logical WAL data to be written after dropping a restricted slot, but that is safe and matches the lazy strategy used when dropping a slot under effective_wal_level.

#### WAL insertion decision

The main entry point, RelationIsLogicallyLogged(relation), remains unchanged, but it now calls RelationNeedsLogicalTupleWAL().

#### Recovery

LogicalSlotScopeReconcileDatabase() reconciles incomplete restricted-slot state after a restart using the completion marker in pg_restricted_slot_relation.

#### Concurrency and locking

Scope initialization and scope-changing DDL must be serialized carefully. A missing mapping could cause required logical tuple WAL to be omitted, while inconsistent lock ordering could deadlock slot creation with publication, partition, or TOAST changes.

So we use a database-level scope lock to serialize changes to the mapping catalog. DDL operations acquire and release this lock at command level, rather than retaining it until transaction end. A separate transaction-lifetime scope-change marker records that the transaction has performed publication or structural DDL. Multiple DDL transactions can hold this marker concurrently, but restricted-slot initialization conditionally acquires it in exclusive mode and fails with a retryable error if uncommitted scope-changing DDL exists.

When a physical relation closure contains multiple relations, the relations are discovered first, sorted by OID, and then locked in deterministic order. DDL takes the required relation locks before the database scope lock. Restricted-slot initialization already holds the database scope lock, so it acquires relation locks conditionally and asks the user to retry slot creation if a conflicting relation lock exists. This avoids waiting for a relation lock while blocking DDL on the scope lock.

Publication and partition hooks first perform a relevance check under the scope lock. If no restricted slot uses the publication or parent relation, they return without strongly locking the physical closure. When the change is relevant, they lock the closure, reacquire the scope lock, recheck the relevant mappings, and then install the additions.

## Other changes

Most of the other changes add calls to functions defined in slotscope.h. Some notable changes are highlighted below.

### rel.h

Change RelationIsLogicallyLogged(relation) to call RelationNeedsLogicalTupleWAL(relation).

Add a new macro, RelationCanBeLogicallyLogged(relation), which wraps the common eligibility conditions. RelationNeedsLogicalTupleWAL(relation) calls this macro.

### pg_class.h

Add a new field, relhasrestrictedslots, to pg_class, indicating whether the relation has one or more current or stale restricted-slot mappings.

### xlog.h

Add XLogRestrictedInfo, which serves a role similar to XLogLogicalInfo for effective_wal_level.

### GUC

Add restricted_wal_level.

### pg_restricted_slot_relation.h

Add a new catalog table that stores the conservative writer-side mappings from restricted slots to relation OIDs.

### subscriptioncmds.c

On the subscriber, add a new unrestricted_slot option to CREATE SUBSCRIPTION.

So far, this patch only allows the creation of new restricted slots. Switching an existing slot between restricted and unrestricted is not supported. For a restricted subscription, the publication list can only be reduced; adding a new publication requires recreating the subscription.

### libpqwalreceiver.c

On the subscriber, extend the CREATE_REPLICATION_SLOT protocol command to pass publication names. The command now looks like:
```
CREATE_REPLICATION_SLOT "pg_16419_sync_16420_7676018444727456626" LOGICAL pgoutput (PUBLICATION_NAMES 'schema_pub', SNAPSHOT 'use')
```

### walsender.c

On the publisher, create a restricted slot when a CREATE_REPLICATION_SLOT command contains PUBLICATION_NAMES.

### slotfuncs.c

Extend pg_create_logical_replication_slot() to accept a list of publication names. When publication names are supplied, it creates a restricted slot.

Extend pg_get_replication_slots() to show restricted-slot info.

One thing to note is that pg_create_logical_replication_slot() rejects the creation of a restricted slot inside an explicit transaction block. A restricted slot should normally be created through CREATE SUBSCRIPTION on the subscriber. For testing purposes, users may manually call pg_create_logical_replication_slot(), but that does not need to be done inside a transaction block. Supporting cases such as the following would make the implementation considerably more complicated:
```
BEGIN
pg_create_logical_replication_slot(’slot-name’, ‘pgoutput’, publications=>[’pub'])
ALTER TABLE t ADD COLUMN str varchar; # add a toast table
ALTER TABLE p ATTACHE PARTITION p1;
ABORT;
```

### slot.c

Add the new restricted-scope fields to replication slots, as described above.

### pgoutput.c

Validate that a restricted slot is ready and that the publications requested by pgoutput are contained in the slot's immutable publication set. Normal publication filtering continues to determine which relations are emitted.

### slotsync.c

Synchronize restricted-slot metadata for failover slots. The restricted_scope_ready_lsn field records a WAL position at or after the mapping transaction. A standby does not persist a synchronized restricted slot as ready until it has flushed through this position, ensuring that the corresponding mapping catalog changes are locally durable before promotion.

### heapam.c

Add special handling for speculative insertions. Even when the target table is outside the restricted scope, write the logical WAL required for a speculative insertion because the reorder buffer needs to process its confirmation record.

### reorderbuffer.c

Replace RelationIsLogicallyLogged() with RelationCanBeLogicallyLogged() while processing changes, including TRUNCATE.

### logicalctl.c

Handle LogicalDecodingCtl->xlog_restricted_info.

### autovacuum.c

Call LogicalSlotScopeCleanup() to lazily clean up stale restricted-slot mappings.

### tablecmds.c

In StoreCatalogInheritance1(), add a call to CheckLogicalSlotScopeHierarchyChange(). When a partition is created to scoped partitioned table, add the partition to the restricted slot to relation mapping.

When ALTER TABLE ... SET SCHEMA moves a table into a schema included in a publication, update the restricted-slot-to-relation mapping after the namespace change becomes command-visible.

### publicationcmds.c

When a new table or schema is added to a publication, update the restricted-slot-to-relation mapping.

### toasting.c

When creating a TOAST table, if its owning table is scoped, make the TOAST table scoped as well.

### heap.c

In heap_create_with_catalog(), when creating a table in a schema included in a publication associated with a restricted slot, make the new table scoped.

# A Basic Demo

## Init two clusters

One cluster is called pubdb, and the other is called subdb. Create a database in both clusters. In this demo, the database is called evantest.

pubdb runs on the standard port, 5432, and subdb runs on port 55432.

## In pubdb, create two tables

```
CREATE TABLE scoped_table (
id integer PRIMARY KEY,
payload text
);

CREATE TABLE unscoped_table (
id integer PRIMARY KEY,
payload text
);
```

## In pubdb, create a publication

```
CREATE PUBLICATION test_pub FOR TABLE scoped_table;
```

## In pubdb, create a role to use for replication

```
CREATE ROLE repl;
ALTER ROLE repl PASSWORD 'xxx';
GRANT USAGE ON SCHEMA public TO repl;
GRANT SELECT ON scoped_table TO repl;
ALTER ROLE repl WITH LOGIN REPLICATION;
```

## In subdb, create the scoped table

```
CREATE TABLE scoped_table (
id integer PRIMARY KEY,
payload text
);
```

## In subdb, create a restricted subscription, which will create a restricted slot in pubdb

```
CREATE SUBSCRIPTION test_sub
CONNECTION 'host=127.0.0.1 port=5432 dbname=evantest user=repl password=xxx'
PUBLICATION test_pub
WITH (
create_slot = true,
copy_data = true,
unrestricted_slot = false
);
```

## In pubdb, check the restricted slot status

```
evantest=# SELECT slot_name, plugin, slot_type, database, unrestricted, publication_oids, restricted_scope_ready, restricted_scope_incarnation, restricted_scope_ready_lsn FROM pg_replication_slots;
slot_name | plugin | slot_type | database | unrestricted | publication_oids | restricted_scope_ready | restricted_scope_incarnation | restricted_scope_ready_lsn
-----------+----------+-----------+----------+--------------+------------------+------------------------+------------------------------+----------------------------
test_sub | pgoutput | logical | evantest | f | {16401} | t | -5474929704368200547 | 0/01C414A8
(1 row)

evantest=# select rsrslotname, rsrrelid::regclass, rsrincarnation from pg_restricted_slot_relation;
rsrslotname | rsrrelid | rsrincarnation
-------------+-------------------------+----------------------
test_sub | scoped_table | -5474929704368200547
test_sub | pg_toast.pg_toast_16385 | -5474929704368200547
test_sub | - | -5474929704368200547
(3 rows)

evantest=# SHOW effective_wal_level;
effective_wal_level
---------------------
replica
(1 row)

evantest=# SHOW restricted_wal_level;
restricted_wal_level
----------------------
logical
(1 row)
```

## In pubdb, insert a row to both scoped_table and unscoped_table

```
INSERT INTO scoped_table VALUES (1, 'a’);
INSERT INTO unscoped_table VALUES (1, 'a');
```

## In subdb, check the scoped_table, the row should be replicated

```
evantest=# select * from scoped_table;
id | payload
----+---------
1 | a
(1 row)
```

## In pubdb, dump WAL data

```
evantest=# SELECT pg_current_wal_flush_lsn() AS end_lsn;
end_lsn
------------
0/01C476D0
(1 row)
```

Then use pg_waldump:
```
% pg_waldump -p /Users/chaol/Downloads/pg_data/pubdb -s '0/01C414A8' -e '0/01C476D0'
rmgr: Heap len (rec/tot): 61/ 61, tx: 707, lsn: 0/01C43DB0, prev 0/01C41D90, desc: INSERT+INIT off: 1, flags: 0x08, blkref #0: rel 1663/16384/16385 blk 0

rmgr: Heap len (rec/tot): 61/ 61, tx: 708, lsn: 0/01C47438, prev 0/01C45B38, desc: INSERT+INIT off: 1, flags: 0x00, blkref #0: rel 1663/16384/16393 blk 0
```

As shown above, the first INSERT has flags 0x08, where 0x08 is XLH_INSERT_CONTAINS_NEW_TUPLE, indicating that the new tuple required for logical decoding is included. The second INSERT, into unscoped_table, doesn't have XLH_INSERT_CONTAINS_NEW_TUPLE. This demonstrates the selective logical WAL behavior implemented by this patch.

[1] https://git.postgresql.org/cgit/postgresql.git/commit/?id=67c20979ce72b8c236622e5603f9775968ff501c
[2] https://www.postgresql.org/message-id/CAD21AoAAJ2tgL7%2BTeR4gRo5q28Gvx1hfC7BQ2nTrqL_O3GdU6w%40mail.gmail.com

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/