Reduce logical WAL volume with restricted replication slots - Mailing list pgsql-hackers

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
longjourney 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
duringtesting and debugging, and many of the necessary pieces were discovered along the way. So that, it ended up with
asingle large commit. If this makes the patch difficult to review, please let me know, and I will try to split it into
amore manageable series. 

# Motivation

PG19 introduced a new feature, effective_wal_level [1], that can toggle logical decoding dynamically based on the
presenceof logical slots. While reviewing that patch, I got the initial idea for this feature and talked to some DBAs
aboutit. 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
savemoney. 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
varioustools, 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.
Forexample, 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
thisfeature 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
slotdoes not currently map to any relations. From a slot alone, there is no way to determine which relations are
replicatedthrough 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_levelto 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
extralogical 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
addedagain 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
publisherand 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
theslot. Note that, here I just mean the relation will start to write logical WAL data, ALTER SUBSCRIPTION sub REFRESH
pubcommand 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
automaticallystart 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
automaticallystart 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,
thenew 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,
whenthe 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
conservativelyuntil 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
whetherlogical 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
nowthink 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).
Itroughly 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
truewhen the relation has one or more current restricted-slot mappings. So, the core of this feature is to maintain
restricted_wal_leveland relhasrestrictedslots efficiently. 

## Slot scope

A subscription normally maps to one slot. A subscription maps to one or more publications, and a publication maps to
oneor 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,
thischain 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
emittedby 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
oneincluded 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
wayfor 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
immutablepublication 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
conservativewriter-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
initializationinvolves 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
currentlycontain no relations, the absence of ordinary rows for the slot in pg_restricted_slot_relation cannot
distinguishan 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.
Forexample, if a slot is dropped and recreated with the same name, it is a different slot and all existing mappings
mustbe 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
flagis 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
sidecarfile. It then calls the existing EnsureLogicalDecodingEnabled() to make logical decoding available at the
clusterlevel. 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_levelto 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_relationand adds the slot to pending_ready_slots. 

• When the transaction that populates pg_restricted_slot_relation commits, slot_scope_xact_callback() marks the pending
restrictedslot as ready. It then requests removal of the temporary full WAL requirement, allowing restricted_wal_level
tocontrol 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
becauseit 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
attachedpartition's physical closure. 

#### Handle TOAST creation

LogicalSlotScopeNoteToastCreation() finds slots associated with the owning table and adds the new TOAST relation to
thoseslot 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
restrictedslot. 

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
droppinga 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
markerin pg_restricted_slot_relation. 

#### Concurrency and locking

Scope initialization and scope-changing DDL must be serialized carefully. A missing mapping could cause required
logicaltuple 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
thislock at command level, rather than retaining it until transaction end. A separate transaction-lifetime scope-change
markerrecords that the transaction has performed publication or structural DDL. Multiple DDL transactions can hold this
markerconcurrently, but restricted-slot initialization conditionally acquires it in exclusive mode and fails with a
retryableerror if uncommitted scope-changing DDL exists. 

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

Publication and partition hooks first perform a relevance check under the scope lock. If no restricted slot uses the
publicationor parent relation, they return without strongly locking the physical closure. When the change is relevant,
theylock 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-slotmappings. 

### 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
unrestrictedis not supported. For a restricted subscription, the publication list can only be reduced; adding a new
publicationrequires 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,
itcreates 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
explicittransaction block. A restricted slot should normally be created through CREATE SUBSCRIPTION on the subscriber.
Fortesting purposes, users may manually call pg_create_logical_replication_slot(), but that does not need to be done
insidea 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
immutablepublication 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
orafter the mapping transaction. A standby does not persist a synchronized restricted slot as ready until it has
flushedthrough 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
logicalWAL 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
scopedpartitioned 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-relationmapping 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
databaseis 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
tuplerequired 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/