PG19 Hacktober: 31 Days of New Features: Publications and Conflicts: EXCEPT, retain_dead_tuples, and New Subscription Stats

By /

Welcome back to the PG19 Hacktober series.

1. Introduction

In this blog, we’ll explore how publications work in PostgreSQL 19, including the EXCEPT clause for publications and the retain_dead_tuples subscription option for conflict detection. This feature fulfills requirements such as controlling which tables are replicated and improving the detection of certain replication conflicts. 

  • EXCEPT: exclude selected tables from a publication.
  • retain_dead_tuples: retain information that helps detect certain replication conflicts.

2. Background / Context

The problem in PostgreSQL 18 or earlier is that when a developer wants to replicate the whole database except one or two tables, like audit tables or audit logs, it was not possible whole database had to be replicated.

The main disaster here is that whenever a developer wants to add a new feature with a new table to the application, replication completely ignores it. Unless a developer manually altered the publication every single time a feature was deployed, the new data was entirely missing on the subscriber. This resulted in production data gaps and broken application features after a failover

How Postgres 19 Solves It: By pairing FOR ALL TABLES with EXCEPT, you can securely “set and forget” replication. New tables are automatically safely synchronized, while your specific disk-heavy or sensitive exclusions remain safely isolated. 

3. What’s New in PostgreSQL 19

The EXCEPT clause allows PostgreSQL 19 to publish all tables while excluding selected tables from replication.

For example, a database may contain hundreds of tables, including application tables, audit logs, and temporary business data. If only a few tables need to be excluded, administrators can use EXCEPT instead of listing every table that should be replicated.

retain_dead_tuples is a PostgreSQL 19 subscription option that retains information such as dead tuples, commit timestamps, and transaction origins to help detect certain logical replication conflicts.

By default, this option is false. When enabled, it allows PostgreSQL to detect update_deleted conflicts, provided the required configuration is in place.

4. Hands-On Example

To demonstrate PostgreSQL 19 logical replication using three tables in the existing pagila database, where the EXCEPT clause excludes a specific table from replication, and retain_dead_tuples enables subscriber-side conflict detection.

Objective: Understand how table exclusion works, how PostgreSQL detects conflicts when a row is deleted on the subscriber but updated on the publisher, and how an insufficient retention duration can affect conflict detection during logical replication.

On the publisher, create three tables.:

pagila=# CREATE TABLE public.demo_departments (
    department_id INT PRIMARY KEY,
    department_name TEXT
);
CREATE TABLE public.demo_employees (
    employee_id INT PRIMARY KEY,
    employee_name TEXT,
    department_id INT
);
CREATE TABLE public.demo_audit_log (
    log_id INT PRIMARY KEY,
    action TEXT
);
CREATE TABLE
CREATE TABLE
CREATE TABLE

Step 2: Insert sample data on the publisher:

pagila=# INSERT INTO public.demo_departments VALUES
(10, 'IT'),
(20, 'HR');
INSERT INTO public.demo_employees VALUES
(1, 'Ravi', 10),
(2, 'Priya', 20);
INSERT INTO public.demo_audit_log VALUES
(1, 'Employee created'),
(2, 'Department changed');
INSERT 0 2
INSERT 0 2
INSERT 0 2

Step 3: Create a publication with EXCEPT on the publisher.

pagila=# CREATE PUBLICATION Except_demo_pub
FOR ALL TABLES;
CREATE PUBLICATION
pagila=# ALTER PUBLICATION Except_demo_pub
SET ALL TABLES;

Now change it to publish all eligible tables except the audit table:

pagila=# ALTER PUBLICATION Except_demo_pub
SET ALL TABLES
EXCEPT (TABLE public.demo_audit_log);
ALTER PUBLICATION

Check the publication configuration:

pagila=# SELECT pubname
FROM pg_publication
WHERE pubname = 'except_demo_pub';
     pubname
-----------------
 except_demo_pub

Now we have created a publication for two tables demo_employees and demo_departments should be published, and demo_audit_log The EXCEPT clause is applied for the table demo_audit_log on the subscriber, the demo_audit_log table should be excluded.

Step 4: On the subscriber, create the subscription

pagila=# CREATE SUBSCRIPTION Except_demo_sub
CONNECTION 'host=192.168.56.12 port=5434 dbname=pagila user=repl_user2 password=mydb'
PUBLICATION Except_demo_pub
WITH (
    copy_data = true,
    retain_dead_tuples = true,
    max_retention_duration = 60000
);
NOTICE:  created replication slot "except_demo_sub" on publisher
CREATE SUBSCRIPTION

Verify subscription:

pagila=# SELECT subname,
       subenabled,
       subretaindeadtuples,
       submaxretention,
       subretentionactive
FROM pg_subscription
WHERE subname = 'except_demo_sub';
     subname     | subenabled | subretaindeadtuples | submaxretention | subretentionactive
-----------------+------------+---------------------+-----------------+--------------------
 except_demo_sub | t          | t                   |           60000 | t
(1 row)

Wait for initial synchronization, then run on the subscriber:

pagila=# SELECT * FROM public.demo_employees ORDER BY employee_id;
SELECT * FROM public.demo_departments ORDER BY department_id;
SELECT * FROM public.demo_audit_log;
 employee_id | employee_name | department_id
-------------+---------------+---------------
           2 | Priya         |            20
(1 row)
 department_id | department_name
---------------+-----------------
            10 | IT
            20 | HR
(2 rows)
pagila=# SELECT * FROM public.demo_audit_log;
 log_id | action
--------+--------
(0 rows)

Note: the audit table is not replicated.

Now, to understand retain_dead tuples, delete one record from the subscriber and update the same record in the publisher. For this, please make sure that “ track_commit_timestamp” is on.

pagila=# SHOW track_commit_timestamp;
 track_commit_timestamp
------------------------
 on
(1 row)

Check whether the special conflict-detection slot exists:

pagila=# SELECT slot_name, slot_type, active, xmin
FROM pg_replication_slots
WHERE slot_name = 'pg_conflict_detection';
       slot_name       | slot_type | active | xmin
-----------------------+-----------+--------+-------
 pg_conflict_detection | physical  | t      | 10300
(1 row)

On the subscriber, delete employee 1:

pagila=# DELETE FROM public.demo_employees
WHERE employee_id = 1;
DELETE 1

Update the same record on the publisher:

pagila=# UPDATE public.demo_employees
SET department_id = 20
WHERE employee_id = 1;
update

Before deleting or updating rows for your test, enable retention and ensure track_commit_timestamp = on before reproducing the conflict.

After a conflict: Enabling it afterward cannot recover the metadata that has already been removed.

You can change the setting while the subscription is disabled, but PostgreSQL warns that retained conflict information can accumulate while the subscription is disabled.

Inspect subscriber statistics:

pagila=# SELECT *
FROM pg_stat_subscription_stats
WHERE subname = 'except_demo_sub';
-[ RECORD 1 ]-------------------+----------------
subid                           | 32953
subname                         | except_demo_sub
apply_error_count               | 211
sync_seq_error_count            | 0
sync_table_error_count          | 976
confl_insert_exists             | 0
confl_update_origin_differs     | 0
confl_update_exists             | 0
confl_update_deleted            | 1
confl_update_missing            | 0
confl_delete_origin_differs     | 0
confl_delete_missing            | 0
confl_multiple_unique_conflicts | 0
stats_reset                     |

What do we learn? The publisher’s update is applied to a row that was deleted locally on the subscriber, as can be seen against confl_update_deleted. Retained information can help PostgreSQL detect this type of conflict. The exact result depends on the conflict and replication state.

confl_update_deleted :Number of times the tuple that was already deleted locally was updated by another source during the application of changes.`

5. Why It Matters

WHY Except CLAUSE MATTERS

1.Simplifies publication management for databases with many tables.

2.Allows specific tables to be excluded from logical replication.

3.Reduces the need to maintain long lists of included tables.

4.Makes it easier to change the exclusion list as requirements change.

WHY retain_dead_tuples MATTERS

Consider a logical replication environment where applications can modify data on both the publisher and subscriber.

  1. A row exists on both databases.
  2. An application deletes the row directly on the subscriber.
  3. The publisher later updates the same row.
  4. The update reaches the subscriber, but the row no longer exists there.

PostgreSQL needs information about previous row versions and transaction history to identify this conflict correctly. The retain_dead_tuples option helps preserve that information.

Use case

Imagine a global SaaS e-commerce company that maintains a central, massive Global Reporting & Analytics Database that consolidates data from multiple regional transactional databases (e.g., US, EU, ).

The platform faces two primary challenges:

  1. Sensitive Data Compliance: Certain regional tables (like European user data governed by GDPR) or local high-frequency scratch tables must not leave their home regions. We can use Except clause to exclude specific tables from replicating.
  2. Conflict Detection in High-Volume Writes: The centralized reporting cluster handles heavy concurrent processing, leading to replication conflicts (e.g., update/delete conflicts) when data synchronizes from multiple sources. We can detect dead tuples by using retain_dead _tuples feautre.

6. Caveats / Things to Watch Out For

Cautions on subscriber:

1. Deadlock Risk: If publisher and subscriber schemas differ, replication transactions may experience deadlocks. PostgreSQL automatically retries affected transactions, but repeated retries can cause replication delays. Keep schemas compatible.

2. Subscription Disabled: When a subscription is disabled, conflict-detection information cannot be purged normally and may accumulate. If the subscription will remain inactive for a long time, consider disabling retain_dead_tuples.

3. Apply Worker Failure: If the apply worker stops or repeatedly fails, retained information can accumulate even when the subscription is enabled. Check pg_stat_subscription and PostgreSQL logs to identify and fix errors.

4. Monitor Retained Information: Use the xmin value of the pg_conflict_detection slot in pg_replication_slots to monitor the oldest transaction horizon being retained. An old xmin may delay vacuum cleanup and increase storage pressure.

5. Replication Topology and Performance: For large tables and write-enabled subscribers, use suitable row filters and multiple publications and subscriptions where appropriate. This can distribute workloads across apply workers and reduce contention, but it does not automatically resolve conflicting writes.

6. Retention Duration Caution: max_retention_duration controls retention only while the apply worker is active. A non-zero value may remove conflict-detection information too early, causing incorrect conflict detection. Monitor worker health and choose the duration carefully.

Cautions on Publisher:

  1. Risk of Data Loss or Duplication: Changing publish_via_partition_root can cause replicated data to be duplicated or lost at the subscriber because it changes the identity and schema used for published tables.
  2. When It Applies: This risk occurs when a partition root table is specified as the replication target.
  3. Avoid Modifying Partition Leaf Tables: After running ALTER PUBLICATION … SET, do not modify partition leaf tables until the subscriber’s publication refresh is complete.Refresh the Subscription: Run the following command on the subscriber to apply the publication changes:
  4. Prevent Duplicate Data Copying: Using copy_data = false prevents PostgreSQL from copying existing table data again during the refresh.
ALTER SUBSCRIPTION mysubscription
REFRESH PUBLICATION WITH (copy_data = false);
  1. Prevent Duplicate Data Copying: Using copy_data = false prevents PostgreSQL from copying existing table data again during the refresh.
  2. Best Practice: Carefully plan changes to publish_via_partition_root and refresh the subscription with copy_data = false to reduce the risk of data loss or duplication.

7. Conclusion

PostgreSQL 19 improves logical replication management through more flexible publication configuration and enhanced conflict detection.

The EXCEPT clause helps administrators decide which tables should be replicated, while retain_dead_tuples helps PostgreSQL identify certain conflicts when rows are changed or deleted on the subscriber.

Together, these capabilities are useful for managing complex replication environments, especially when selective replication and subscriber-side writes are part of the database architecture.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top