PG18 Hacktober:31 Days of New Features- Logical Replication of Stored Generated Columns - Part I

Welcome to the 11th blog post in the “logical replication” segment of PG18 Hacktober!

Stored Generated Columns are now fully supported in logical replication.

This enhancement removes a common headache for DBAs and application developers replicating derived values that were previously excluded from replication. With PostgreSQL 18, not only can you replicate these columns, you also get two different ways to control how they’re published:

Automatic replication with publish_generated_columns = ‘stored’
Fine-grained control with explicit column lists

Quick Recap: What are Stored GENERATED columns?

A stored generated column automatically calculates its value based on other columns, and PostgreSQL physically stores it on disk.

Example:

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    price NUMERIC NOT NULL,
    quantity INT NOT NULL,
    total NUMERIC GENERATED ALWAYS AS (price * quantity) STORED
);

Here:

total is always derived as price * quantity.
The value is stored, not recalculated on every query.
This saves CPU cycles and ensures consistency across replicas.

In PostgreSQL ≤17, these columns were ignored by logical replication. Now in PG18, they’re supported in replication.

Method 1: Replicating all Stored GENERATED columns automatically

You can configure a publication to include all stored generated columns:

CREATE PUBLICATION pub_sales
FOR TABLE sales
WITH (publish_generated_columns = 'stored');

Or update an existing publication:

ALTER PUBLICATION pub_sales
SET (publish_generated_columns = 'stored');

Behavior

All stored generated columns are replicated.
Any new stored-generated columns added in the future are included automatically.

Best for

Full table replication (including derived values).
Reducing DBA overhead — no manual updates to publication when the schema evolves.

Here’s an easy example to understand:

On Publisher

postgres18=# CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    price NUMERIC NOT NULL,
    quantity INT NOT NULL,
    total NUMERIC GENERATED ALWAYS AS (price * quantity) STORED
);
CREATE TABLE
postgres18=#

postgres18=# INSERT INTO sales (price, quantity) VALUES
    (100, 2),
    (50, 4);
INSERT 0 2
postgres18=#

Replicate all columns (including total):

postgres18=# CREATE PUBLICATION pub_all FOR TABLE sales WITH (publish_generated_columns = 'stored');
CREATE PUBLICATION
postgres18=#

On Subscriber

postgres18=# CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    price NUMERIC NOT NULL,
    quantity INT NOT NULL,
    total NUMERIC GENERATED ALWAYS AS (price * quantity) STORED
);
CREATE TABLE
postgres18=#

postgres18=# CREATE SUBSCRIPTION sub_all
CONNECTION 'host=172.31.27.61 dbname=postgres18 user=postgres'
PUBLICATION pub_all;
NOTICE:  created replication slot "sub_all" on publisher
CREATE SUBSCRIPTION

Now:

pub_all → replicates base + generated columns.

Conclusion

PostgreSQL 18 introduces full support for logical replication of stored generated columns, solving a limitation that previously caused extra work and inconsistencies.

You can choose automatic replication of all stored generated columns using publish_generated_columns = ‘stored’.
This approach gives DBAs and developers flexibility, simpler maintenance, and consistent replicas.

In Part 2, we’ll see the second approach, i.e, selectively replicating only the columns you need with explicit column lists, and also look at the advantages, important notes, and limitations of stored generated columns.

PG18 Hacktober:31 Days of New Features- Logical Replication of Stored Generated Columns – Part I