PostgreSQL 18 continues the tradition of delivering meaningful enhancements to logical replication, a feature that has become increasingly critical for distributed database architectures. In my previous posts about Postgres 18's Async I/O and Skip Scan features, I explored performance improvements at the storage and indexing layers. Today, I'll turn my attention to two significant replication improvements:

• replication of generated columns

• substantially enhanced conflict monitoring capabilities

These features address real-world pain points that many of us have encountered when building distributed systems. Whether you're working with multi-region deployments, replicating to analytical databases, or building active-active architectures with solutions like pgEdge Distributed Postgres, these enhancements will make your life considerably easier.

Generated Column Replication: Finally Here

Prior to Postgres 18, generated columns were completely invisible to logical replication. If you had a table with a generated column on the publisher, that column was simply skipped during replication. This created several problems:

• Subscribers couldn't receive calculated values from the publisher, limiting data availability.

• Replicating to non-Postgres databases, which might not support generated columns, required workarounds.

• You had to recreate the same calculations on each subscriber node, creating redundancy.

• Cross-version replication scenarios became unnecessarily complex due to these limitations.

Postgres 18 addresses these issues with a new publication parameter:

publish_generated_columns

Currently, the parameter accepts two values:

• NONE : The default; generated columns are skipped during replication, maintaining backward compatibility with Postgres 17 and earlier behavior.

• STORED: STORED generated columns are published in the same way as regular columns.

This parameter gives you explicit control over whether generated columns are replicated.

How It Works

Let's demonstrate this with a practical example. Consider an e-commerce application where you track product pricing in a table created with:

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,
    -- Stored generated column for final price
    final_price NUMERIC(10,2) GENERATED ALWAYS AS
        (base_price * (1 - discount_rate) * (1 + tax_rate)) STORED);

In Postgres 17 and earlier, creating a publication would automatically skip the final_price column in our table. With Postgres 18, you now have three options:

● -- Option 1: Default behavior (skip generated columns)
CREATE PUBLICATION pub_products FOR TABLE products;
● -- Option 2: Include all stored generated columns
CREATE PUBLICATION pub_products_with_generated
FOR TABLE products WITH (publish_generated_columns = stored);
● -- Option 3: Selectively include specific columns (including generated)
CREATE PUBLICATION pub_products_selective
FOR TABLE products (product_id, name, base_price, final_price);

There are several critical rules to understand when working with generated column replication:

• Postgres 18 supports replication only for STORED generated columns. Virtual generated columns (the new default in Postgres 18) cannot be replicated because they exist only at query time and have no physical storage. The publish_generated_columns parameter uses an enum value specifically to allow for future expansion, possibly to support virtual columns in later releases.

• The subscriber must have regular columns; when you publish a stored generated column, the subscriber must receive it into a regular (non-generated) column. If the subscriber also defines the column as GENERATED, the apply process will error.

This is the corrected subscriber table definition:

-- Subscriber table (note: final_price is NOT generated)
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    base_price NUMERIC(10,2) NOT NULL,
    final_price NUMERIC(10,2)  -- Regular column, not generated);

Implementation Details and Design Decisions

The implementation of generated column replication required careful consideration of the logical decoding infrastructure. The feature was developed through the collaborative efforts of Shubham Khanna, Vignesh C, Zhijie Hou, Shlok Kyal, and Peter Smith, representing contributions from multiple organizations in the Postgres community.

The development team chose to use an enumerated value for the publish_generated_columns parameter rather than a simple boolean. This design decision, documented in the release notes, allows for future enhancements—most notably, potential support for virtual generated columns in subsequent releases.

This extensible design reflects the Postgres community's forward-thinking approach to feature development, leaving the door open for supporting virtual generated columns in future releases (the new default in Postgres 18) once the technical challenges around on-demand computation during replication are resolved.

This design decision ensures that replicated generated values can be stored persistently on the subscriber without introducing conflicts between the replication stream and local computation. It also enables scenarios where subscribers might not support generated columns at all, such as when replicating to non-Postgres databases.

Column List Override Behavior

When determining which columns to publish, an explicitly specified column list takes precedence over the publish_generated_columns parameter. This allows fine-grained control: you might enable generated column replication at the publication level but selectively exclude certain generated columns from specific tables.

Backward Compatibility

If your subscriber is running Postgres 17 or earlier, initial table synchronization will not copy generated columns even if the publisher enables this option. For full support, both publisher and subscriber should be running Postgres 18. However, the feature is designed to fail gracefully—older subscribers simply won't receive the generated column data, maintaining compatibility with mixed-version deployments.

Real-World Use Cases

The ability to replicate generated columns opens up several practical scenarios:

Cross-Database Replication

If you're replicating from Postgres to a data warehouse or analytical database that doesn't support generated columns, you can now send the pre-calculated values directly. This is particularly useful when working with specialized databases that excel at queries but lack Postgres's expression capabilities. The release notes specifically call out this use case as a primary motivation for the feature.

Performance-Critical Subscribers

In read-heavy environments where subscribers handle most query traffic, replicating calculated values can significantly reduce CPU overhead on subscriber nodes. Rather than recalculating the same expression millions of times during queries, the work is done once on the publisher.

Distributed Postgres Architectures

For those running pgEdge Distributed Postgres or similar multi-master architectures, generated column replication becomes even more valuable. In a distributed system where different nodes may serve different geographic regions, replicating calculated values ensures consistency across the cluster without requiring identical generated column definitions on every node. This is especially important when dealing with currency conversions, localized tax calculations, or region-specific business rules where the calculation logic might vary by jurisdiction but you still want to replicate the final computed values.

Example: The difference between Postgres 17 and Postgres 18

The functionality for replicating generated columns is demonstrated below using a simple example; the example shows that the generated column changes aren’t replicated in Postgres 17, but are replicated using the new functionality in Postgres 18.

-- Postgres 17

-- Created table on publisher side with generated column

CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
subtotal NUMERIC(10,2) NOT NULL,
tax_rate NUMERIC(4,2) DEFAULT 0.10,
-- Generated column that calculates total
total NUMERIC(10,2) GENERATED ALWAYS AS (subtotal * (1 + tax_rate)) STORED
);

INSERT INTO orders (subtotal, tax_rate) VALUES
(100.00, 0.10),  -- total should be 110.00
(200.00, 0.15),  -- total should be 230.00
(50.00, 0.08);   -- total should be 54.00

-- Created publication

CREATE PUBLICATION pub_orders FOR TABLE orders;

-- Query below show that generated column is not selected for replication

SELECT attnames FROM pg_publication_tables WHERE pubname = 'pub_orders';
           attnames           
------------------------------
 {order_id,subtotal,tax_rate}

-- Created table on subscriber side with regular column

CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
subtotal NUMERIC(10,2) NOT NULL,
tax_rate NUMERIC(4,2) DEFAULT 0.10,
total NUMERIC(10,2)  -- Regular column (NOT generated)
);

-- Create subscriber

CREATE SUBSCRIPTION sub_orders
    CONNECTION 'host=localhost port=5433 dbname=postgres user=pgedge password=pgedge110'
    PUBLICATION pub_orders;

-- The generated column value is not replicated

 select * from orders;
 order_id | subtotal | tax_rate | total 
----------+----------+----------+-------
        1 |   100.00 |     0.10 |      
        2 |   200.00 |     0.15 |      
        3 |    50.00 |     0.08 |      
(3 rows)

-- Run update on publisher

UPDATE orders SET subtotal = 150.00 WHERE order_id = 1;
UPDATE 1
postgres=# select * from orders;
 order_id | subtotal | tax_rate | total  
----------+----------+----------+--------
        2 |   200.00 |     0.15 | 230.00
        3 |    50.00 |     0.08 |  54.00
        1 |   150.00

-- Value for generated column not replicated to subscriber

postgres=# select * from orders;
 order_id | subtotal | tax_rate | total 
----------+----------+----------+-------
        2 |   200.00 |     0.15 |      
        3 |    50.00 |     0.08 |      
        1 |   150.00 |     0.10 |      
(3 rows)

Now, we'll run the same example with Postgres 18:

-- Postgres 18

-- Create publication on the publisher side with publish_generated_columns set to stored

CREATE PUBLICATION pub_orders_with_gen 
    FOR TABLE orders 
    WITH (publish_generated_columns = stored);

-- Notice in PG-18, the generated column is seen in publication attributes

SELECT attnames FROM pg_publication_tables WHERE pubname = 'pub_orders_with_gen';
              attnames              
------------------------------------
 {order_id,subtotal,tax_rate,total}

-- Create the subscription in the subscriber

CREATE SUBSCRIPTION sub_orders
    CONNECTION 'host=localhost port=5432 dbname=postgres user=pgedge password=pgedge110'
    PUBLICATION pub_orders_with_gen;
NOTICE:  created replication slot "sub_orders" on publisher
CREATE SUBSCRIPTION

-- The generated column value is replicated

select * from orders;
 order_id | subtotal | tax_rate | total  
----------+----------+----------+--------
        1 |   100.00 |     0.10 | 110.00
        2 |   200.00 |     0.15 | 230.00
        3 |    50.00 |     0.08 |  54.00
(3 rows)

-- Do the update in the publisher side

UPDATE orders SET subtotal = 150.00 WHERE order_id = 1;
UPDATE 1

-- Changes for the generated column are reflected on the subscirber

postgres=# select * from orders;
 order_id | subtotal | tax_rate | total  
----------+----------+----------+--------
        2 |   200.00 |     0.15 | 230.00
        3 |    50.00 |     0.08 |  54.00
        1 |   150.00 |     0.10 | 165.00
(3 rows)

Enhanced Replication Monitoring and Conflict Detection

The second major improvement in Postgres 18 relates to how we monitor and troubleshoot logical replication. Anyone who has debugged replication conflicts knows the frustration of limited visibility into what went wrong. Postgres 18 addresses this head-on with comprehensive conflict logging and enhanced statistics tracking, work primarily driven by Zhijie Hou and Nisha Moond with contributions from the broader community.

The pg_stat_subscription_stats View

While pg_stat_subscription_stats was introduced in Postgres 15, version 18 significantly enhances it with detailed conflict tracking columns:

SELECT subname,
       apply_error_count,
       sync_error_count,
       confl_insert_exists,
       confl_update_origin_differs,
       confl_update_exists,
       confl_update_missing,
       confl_delete_origin_differs,
       confl_delete_missing,
       confl_multiple_unique_conflicts
FROM pg_stat_subscription_stats;

Each of these conflict types provides specific insight into what's happening in your replication topology:

• confl_insert_exists counts INSERT operations that violate unique constraints.

• confl_update_origin_differs identifies UPDATE conflicts where rows were modified by different origins.

• confl_update_exists tracks UPDATEs that violate unique constraints.

• confl_update_missing counts attempts to UPDATE rows that don't exist.

• confl_delete_origin_differs captures DELETE operations on rows modified by another origin.

• confl_delete_missing tracks DELETE attempts on non-existent rows.

• confl_multiple_unique_conflicts detects scenarios where multiple unique constraints are violated simultaneously.

Implications for pgEdge Distributed Postgres

These Postgres 18 improvements align particularly well with distributed Postgres architectures. pgEdge Distributed Postgres, which builds on logical replication through its Spock extension, benefits from these core enhancements.

Calculated Column Distribution

Generated column replication is particularly valuable in distributed systems where you want to centralize calculation logic but distribute the results. For example, in a global e-commerce platform using pgEdge, you might calculate currency conversions or tax rates on designated primary nodes and replicate those calculated values to regional nodes. This reduces computation overhead across the cluster while ensuring consistency.