If you are managing PostgreSQL logical replication in a production environment, you may have encountered several operational challenges. One notable issue is that generated columns do not appear in the replication stream, leaving subscribers without access to their values. Additionally, replication conflicts can cause the apply worker to halt, with server logs providing insufficient detail to diagnose the underlying issues. Furthermore, replication slots that remain after decommissioned subscribers can continue to consume write-ahead log (WAL) files, potentially triggering disk space alerts.
PostgreSQL 18 introduces a series of enhancements aimed at addressing these concerns. The new version allows STORED generated columns to be included in the replication stream, provided you opt in using a new publication parameter. Additionally, seven new conflict counters in pg_stat_subscription_stats offer a detailed breakdown of replication errors, moving beyond the previous opaque error count. Idle replication slots can now be invalidated automatically through a new server parameter, and two-phase commit settings can be toggled on a live subscription using ALTER SUBSCRIPTION, eliminating the need for a complete resynchronization.
This article explores these logical replication features within the context of Amazon Relational Database Service (Amazon RDS) for PostgreSQL 18.3. We will set up a publisher and subscriber, execute relevant SQL commands, and present the resulting output. The SQL syntax remains consistent whether you are using RDS for PostgreSQL, Amazon Aurora PostgreSQL-Compatible Edition, or self-managed PostgreSQL instances.
Prerequisites
This guide is designed to be self-contained, allowing you to follow along without any prior setup. However, if you wish to execute the examples, ensure you have the following prerequisites:
- Two PostgreSQL 18 instances (one publisher and one subscriber) with network connectivity. Logical replication necessitates separate PostgreSQL instances; replication cannot occur between schemas or tables within the same instance. The subscriber must connect to the publisher on port 5432, and for Amazon RDS and Aurora, security groups should permit inbound traffic from the subscriber.
- An Amazon Elastic Compute Cloud (Amazon EC2) instance or bastion host within the same virtual private cloud (VPC) to execute psql commands on both instances.
- A PostgreSQL user with replication privileges on the publisher. For Amazon RDS and Aurora, this user requires the rds_replication role. For self-managed PostgreSQL, the user must have the
REPLICATIONattribute andSELECTprivileges on the published tables. - Logical replication enabled on the publisher. For Amazon RDS and Aurora, set
rds.logical_replication = 1in your custom parameter group and reboot. For self-managed PostgreSQL, configurewal_level = logicalinpostgresql.confand restart. - For conflict monitoring with origin tracking, enable
track_commit_timestamp = onon the subscriber. - For two-phase commit testing, set
max_prepared_transactionsto a non-zero value (e.g., 10) on both publisher and subscriber, followed by a reboot of both instances.
Setting up the test environment
For this demonstration, we provisioned two Amazon RDS for PostgreSQL 18.3 instances in the N. Virginia Region (us-east-1). Both instances reside in a private subnet without public access. The security group permits PostgreSQL traffic solely between the two instances and from an EC2 bastion host within the same VPC, which we utilize for executing psql commands via AWS Systems Manager.
The configuration is as follows:
- Publisher instance
pg18-publisher: PostgreSQL 18.3,db.t3.micro, not publicly accessible. Key parameters includerds.logical_replication = 1andmax_prepared_transactions = 10. - Subscriber instance
pg18-subscriber: PostgreSQL 18.3,db.t3.micro, not publicly accessible. Key parameters includetrack_commit_timestamp = onandmax_prepared_transactions = 10.
Upon instance initialization, we connected to verify the settings:
SHOW wal_level;
-- logical
SHOW track_commit_timestamp;
-- on
SHOW max_prepared_transactions;
-- 10The architecture utilized in this walkthrough is illustrated below.
Figure 1: Logical replication setup with Amazon RDS for PostgreSQL 18
Replicating generated columns
Prior to PostgreSQL 18, tables with STORED generated columns on the publisher would omit these columns during logical replication. Subscribers either had to recompute the values locally (if they supported the same expression) or forgo them altogether.
PostgreSQL 18 rectifies this issue with a new publication parameter named publish_generated_columns. This parameter accepts two values:
stored: This option publishes all STORED generated columns in the table, including them in the replication stream as if they were standard columns.none(default): This option excludes generated columns from the publication, maintaining backward compatibility with PostgreSQL 17 and earlier versions.
Moreover, if an explicit column list is defined in the publication, it takes precedence over the publish_generated_columns parameter, allowing for selective inclusion of specific generated columns while excluding other regular columns.
Replicating the stored value is particularly beneficial when the subscriber is a non-PostgreSQL system that cannot evaluate the generation expression, or when a read-heavy subscriber would otherwise waste resources recomputing a value that the publisher has already calculated.
Step 1: Create the publisher table
We begin by creating a products table where final_price is automatically calculated based on the base price, discount rate, and tax rate:
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
base_price NUMERIC(10,2) NOT NULL,
discount_rate NUMERIC(5,2) DEFAULT 0,
tax_rate NUMERIC(5,2) DEFAULT 0.10,
final_price NUMERIC(10,2) GENERATED ALWAYS AS
(base_price * (1 - discount_rate) * (1 + tax_rate)) STORED
);Next, we insert a few rows:
INSERT INTO products (name, base_price, discount_rate, tax_rate) VALUES
('Widget A', 100.00, 0.10, 0.08),
('Widget B', 200.00, 0.15, 0.10),
('Widget C', 50.00, 0.00, 0.12);Step 2: Create the publication
We create a publication that includes the generated column:
CREATE PUBLICATION pub_products FOR TABLE products
WITH (publish_generated_columns = stored);To confirm that final_price is included, we check pg_publication_tables:
SELECT pubname, attnames FROM pg_publication_tables
WHERE pubname = 'pub_products';
pubname | attnames
--------------+------------------------------------------------------------
pub_products | {product_id,name,base_price,discount_rate,tax_rate,final_price}Step 3: Create the subscriber table
On the subscriber, we create the same table structure. However, since our publication uses the stored option, final_price must be defined as a regular column on the subscriber rather than as a generated column. If the subscriber also defines it as GENERATED, the apply process will fail because PostgreSQL cannot write a received value into a generated column.
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
base_price NUMERIC(10,2) NOT NULL,
discount_rate NUMERIC(5,2) DEFAULT 0,
tax_rate NUMERIC(5,2) DEFAULT 0.10,
final_price NUMERIC(10,2) -- Regular column, NOT generated
);Step 4: Create the subscription and verify
CREATE SUBSCRIPTION sub_products
CONNECTION 'host= port=5432 dbname=testdb
user=pgadmin password='
PUBLICATION pub_products;After the initial synchronization completes (where the subscription copies existing publisher data to the subscriber), we query the subscriber:
SELECT * FROM products ORDER BY product_id;
product_id | name | base_price | discount_rate | tax_rate | final_price
------------+----------+------------+---------------+----------+-------------
1 | Widget A | 100.00 | 0.10 | 0.08 | 97.20
2 | Widget B | 200.00 | 0.15 | 0.10 | 187.00
3 | Widget C | 50.00 | 0.00 | 0.12 | 56.00All three rows arrived with the correct final_price values, computed on the publisher and applied directly to the subscriber.
Step 5: Test ongoing replication
Back on the publisher, we update a row:
UPDATE products SET base_price = 150.00 WHERE product_id = 1;The publisher recalculates final_price to 145.80 (150.00 * 0.90 * 1.08), and the updated value reaches the subscriber within seconds:
product_id | name | base_price | final_price
------------+----------+------------+-------------
1 | Widget A | 150.00 | 145.80We also insert a new row on the publisher:
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
base_price NUMERIC(10,2) NOT NULL,
discount_rate NUMERIC(5,2) DEFAULT 0,
tax_rate NUMERIC(5,2) DEFAULT 0.10,
final_price NUMERIC(10,2) GENERATED ALWAYS AS
(base_price * (1 - discount_rate) * (1 + tax_rate)) STORED
);0
On the subscriber, we can verify that the new row has been replicated:
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
base_price NUMERIC(10,2) NOT NULL,
discount_rate NUMERIC(5,2) DEFAULT 0,
tax_rate NUMERIC(5,2) DEFAULT 0.10,
final_price NUMERIC(10,2) GENERATED ALWAYS AS
(base_price * (1 - discount_rate) * (1 + tax_rate)) STORED
);1
The subscriber successfully received the recalculated final_price for the updated row and the computed value for the newly inserted row. Both UPDATE and INSERT operations effectively carried the generated column through the replication stream.
Comparing publication modes
To verify all three publication modes, we created publications for each and compared the column lists:
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
base_price NUMERIC(10,2) NOT NULL,
discount_rate NUMERIC(5,2) DEFAULT 0,
tax_rate NUMERIC(5,2) DEFAULT 0.10,
final_price NUMERIC(10,2) GENERATED ALWAYS AS
(base_price * (1 - discount_rate) * (1 + tax_rate)) STORED
);The explicit column list (Option 3) takes precedence over the parameter, allowing you to include a generated column while excluding regular columns such as discount_rate and tax_rate.
Improved conflict monitoring
Diagnosing logical replication conflicts in PostgreSQL 17 or earlier often proved challenging. When the apply worker halted, the server log would indicate that an error occurred, but it would not specify the type of conflict or its frequency, leaving administrators to deduce the cause from limited information.
PostgreSQL 18 enhances this process by adding seven new conflict counter columns to the pg_stat_subscription_stats view. Instead of a single error count, users now receive a detailed breakdown by conflict type:
confl_insert_exists: AnINSERToperation violated a unique constraint (the row already exists).confl_update_origin_differs: AnUPDATEwas made on a row last modified by a different replication origin.confl_update_exists: AnUPDATEviolated a unique constraint.confl_update_missing: AnUPDATEtargeted a row that does not exist on the subscriber.confl_delete_origin_differs: ADELETEwas made on a row last modified by a different origin.confl_delete_missing: ADELETEtargeted a row that does not exist on the subscriber.confl_multiple_unique_conflicts: An operation violated multiple unique constraints simultaneously.
To test this feature, we intentionally caused an INSERT conflict by inserting a row with product_id = 100 directly on the subscriber, followed by an insert with the same primary key on the publisher:
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
base_price NUMERIC(10,2) NOT NULL,
discount_rate NUMERIC(5,2) DEFAULT 0,
tax_rate NUMERIC(5,2) DEFAULT 0.10,
final_price NUMERIC(10,2) GENERATED ALWAYS AS
(base_price * (1 - discount_rate) * (1 + tax_rate)) STORED
);3
When the publisher attempted to replicate the insert, the subscriber already contained a row with product_id = 100. We then checked the stats:
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
base_price NUMERIC(10,2) NOT NULL,
discount_rate NUMERIC(5,2) DEFAULT 0,
tax_rate NUMERIC(5,2) DEFAULT 0.10,
final_price NUMERIC(10,2) GENERATED ALWAYS AS
(base_price * (1 - discount_rate) * (1 + tax_rate)) STORED
);4
The confl_insert_exists counter incremented to 1, clearly identifying the conflict as a duplicate primary key without requiring inspection of the server log. The confl_update_origin_differs counter remained at 0, as origin conflicts only arise in multi-origin topologies such as bidirectional replication and are counted only when track_commit_timestamp is enabled on the subscriber. Note that this conflict halts the apply worker from processing further changes until the issue is resolved. You can address it by deleting the conflicting row on the subscriber (allowing the publisher’s row to be applied) or using ALTER SUBSCRIPTION ... SKIP to bypass the conflicting transaction.
Parallel streaming is now the default
In PostgreSQL 17 and earlier, CREATE SUBSCRIPTION defaulted to streaming = off. This meant that large transactions were fully buffered in memory before being sent to the subscriber, potentially leading to elevated memory consumption proportional to transaction size and increased replication lag until the entire transaction was decoded and transmitted.
PostgreSQL 18 changes this default to streaming = parallel. Large transactions begin replicating before completion, allowing multiple parallel apply workers to process changes simultaneously. No adjustments are necessary to achieve this behavior in new subscriptions.
We verified this by checking the subscription metadata:
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
base_price NUMERIC(10,2) NOT NULL,
discount_rate NUMERIC(5,2) DEFAULT 0,
tax_rate NUMERIC(5,2) DEFAULT 0.10,
final_price NUMERIC(10,2) GENERATED ALWAYS AS
(base_price * (1 - discount_rate) * (1 + tax_rate)) STORED
);5
The value p confirms that the subscription utilizes parallel streaming.
The parallel apply worker pool is governed by two settings:
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
base_price NUMERIC(10,2) NOT NULL,
discount_rate NUMERIC(5,2) DEFAULT 0,
tax_rate NUMERIC(5,2) DEFAULT 0.10,
final_price NUMERIC(10,2) GENERATED ALWAYS AS
(base_price * (1 - discount_rate) * (1 + tax_rate)) STORED
);6
These defaults permit up to 2 parallel apply workers per subscription, drawn from a pool of 4 logical replication workers. For high-throughput workloads, consider increasing these values.
Changing two-phase commit on live subscriptions
PostgreSQL 16 introduced two-phase commit support for logical replication, but the setting could only be specified at the time of subscription creation. To modify it afterward, you had to drop the subscription and recreate it, which would remove the replication slot and necessitate a full resynchronization.
PostgreSQL 18 eliminates this limitation, allowing you to toggle two_phase on an existing subscription using ALTER SUBSCRIPTION.
Note that two-phase commit requires prepared transactions to be enabled on both the publisher and subscriber. In Amazon RDS and Aurora, the max_prepared_transactions parameter governs this. It is a static parameter that necessitates a reboot to take effect, so ensure it is set to a non-zero value (e.g., 10) before testing two-phase commit.
We began by creating a subscription with two_phase disabled:
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
base_price NUMERIC(10,2) NOT NULL,
discount_rate NUMERIC(5,2) DEFAULT 0,
tax_rate NUMERIC(5,2) DEFAULT 0.10,
final_price NUMERIC(10,2) GENERATED ALWAYS AS
(base_price * (1 - discount_rate) * (1 + tax_rate)) STORED
);7
The state d indicates that two-phase commit is disabled. We can now enable it on the existing subscription:
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
base_price NUMERIC(10,2) NOT NULL,
discount_rate NUMERIC(5,2) DEFAULT 0,
tax_rate NUMERIC(5,2) DEFAULT 0.10,
final_price NUMERIC(10,2) GENERATED ALWAYS AS
(base_price * (1 - discount_rate) * (1 + tax_rate)) STORED
);8
The state changes to e (enabled). Because the replication slot was retained, replication resumed from the same position without requiring resynchronization.
Testing prepared transactions
With two_phase enabled, we tested the full lifecycle. On the publisher, we prepared a transaction:
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
base_price NUMERIC(10,2) NOT NULL,
discount_rate NUMERIC(5,2) DEFAULT 0,
tax_rate NUMERIC(5,2) DEFAULT 0.10,
final_price NUMERIC(10,2) GENERATED ALWAYS AS
(base_price * (1 - discount_rate) * (1 + tax_rate)) STORED
);9
At this point, the row does not yet appear on the subscriber. However, the subscriber has created its own prepared transaction, waiting for the publisher’s decision:
INSERT INTO products (name, base_price, discount_rate, tax_rate) VALUES
('Widget A', 100.00, 0.10, 0.08),
('Widget B', 200.00, 0.15, 0.10),
('Widget C', 50.00, 0.00, 0.12);0
When we commit on the publisher:
INSERT INTO products (name, base_price, discount_rate, tax_rate) VALUES
('Widget A', 100.00, 0.10, 0.08),
('Widget B', 200.00, 0.15, 0.10),
('Widget C', 50.00, 0.00, 0.12);1
Widget F’s order appears on the subscriber within seconds:
INSERT INTO products (name, base_price, discount_rate, tax_rate) VALUES
('Widget A', 100.00, 0.10, 0.08),
('Widget B', 200.00, 0.15, 0.10),
('Widget C', 50.00, 0.00, 0.12);2
We also tested ROLLBACK PREPARED to confirm that rolled-back transactions do not propagate to the subscriber:
INSERT INTO products (name, base_price, discount_rate, tax_rate) VALUES
('Widget A', 100.00, 0.10, 0.08),
('Widget B', 200.00, 0.15, 0.10),
('Widget C', 50.00, 0.00, 0.12);3
After rolling back the prepared transaction and querying the subscriber, Widget F’s order was absent, confirming that the rollback propagated through the two-phase stream and the prepared transaction was discarded on both the publisher and subscriber.
Automatic cleanup of idle replication slots
Unattended replication slots often present operational challenges in logical replication. When a subscriber goes offline, its slot continues to retain WAL on the publisher until disk space is exhausted. In PostgreSQL 17 and earlier, administrators had to monitor these slots and drop them manually.
PostgreSQL 18 introduces idle_replication_slot_timeout to mitigate this issue. By setting it to a non-zero value, the next checkpoint invalidates any slot that has remained idle (unused by a replication connection) longer than the specified duration. The invalidated slot still appears in pg_replication_slots, now with an invalidation_reason, but it ceases to hold WAL, allowing the publisher to reclaim disk space.
The parameter defaults to 0, disabling the timeout, and its value is expressed in seconds. Since the setting is applied during configuration reload rather than at startup, you can modify it in your Amazon RDS parameter group or in postgresql.conf without rebooting the instance.
To observe the tracking functionality, we created a manual replication slot and checked its inactive_since field:
INSERT INTO products (name, base_price, discount_rate, tax_rate) VALUES
('Widget A', 100.00, 0.10, 0.08),
('Widget B', 200.00, 0.15, 0.10),
('Widget C', 50.00, 0.00, 0.12);4
This query returns the slot name test_idle_slot with active = false, an inactive_since timestamp, and an idle_duration indicating how long the slot has been inactive. For example, with idle_replication_slot_timeout set to 3600 (one hour), the next checkpoint after a slot has been idle for over an hour invalidates it and releases the WAL it was holding. In Amazon RDS, this configuration is made in your custom parameter group, and the change takes effect without requiring a reboot.
Considerations
Before implementing these features in a production environment, consider the following points:
- Subscriber column type for generated columns: When replicating a STORED generated column, the subscriber must define it as a regular column. If both sides define the column as
GENERATED, the apply process will fail because PostgreSQL cannot write a received value into a generated column. - Only STORED generated columns can be replicated. Virtual generated columns (the new default in PostgreSQL 18) cannot be replicated due to their lack of physical storage. The
publish_generated_columnsparameter uses an enum value specifically to allow for future expansion to virtual columns in later releases. - Origin tracking requires
track_commit_timestamp: Theconfl_update_origin_differsandconfl_delete_origin_differscounters only function whentrack_commit_timestamp = onis set on the subscriber. The other conflict counters operate without this setting. - Idle slot invalidation occurs at checkpoint time, not immediately. There may be a delay of up to
checkpoint_timeoutbetween when the slot exceeds the idle threshold and when it is actually invalidated. You can force a checkpoint manually if immediate cleanup is necessary. - Sequences are not replicated. Logical replication copies the generated ID values as row data, but the sequence counter on the subscriber is not synchronized. If you promote the subscriber to become the new primary, you must manually advance the sequences to avoid duplicate key errors.
- Cross-version replication: If the subscriber runs a version earlier than PostgreSQL 18, the initial table synchronization will not copy generated column values (they will appear as
NULL). However, changes replicated through the ongoing WAL stream after the initial sync do include generated column values. For full support ofpublish_generated_columns, both publisher and subscriber must run PostgreSQL 18.
Cleanup
Once you have completed testing, it is advisable to remove the replication resources to prevent unnecessary costs and WAL accumulation.
Begin by dropping the subscriptions on the subscriber:
INSERT INTO products (name, base_price, discount_rate, tax_rate) VALUES
('Widget A', 100.00, 0.10, 0.08),
('Widget B', 200.00, 0.15, 0.10),
('Widget C', 50.00, 0.00, 0.12);5
Next, drop the publications on the publisher:
INSERT INTO products (name, base_price, discount_rate, tax_rate) VALUES
('Widget A', 100.00, 0.10, 0.08),
('Widget B', 200.00, 0.15, 0.10),
('Widget C', 50.00, 0.00, 0.12);6
Finally, remove the test tables from both the publisher and subscriber:
INSERT INTO products (name, base_price, discount_rate, tax_rate) VALUES
('Widget A', 100.00, 0.10, 0.08),
('Widget B', 200.00, 0.15, 0.10),
('Widget C', 50.00, 0.00, 0.12);7
If you created Amazon RDS instances for this walkthrough, delete them through the Amazon RDS console or the AWS Command Line Interface (AWS CLI) to stop incurring charges. If you modified an existing parameter group, consider reverting rds.logical_replication to 0 if logical replication is no longer needed, followed by a reboot of the instance.
