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

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

In the previous blog, we explored how PostgreSQL 18 supports automatic replication of all stored generated columns using publish_generated_columns = ‘stored’. This approach is simple and ensures that every derived column is replicated without extra effort.

In this blog, we’ll focus on the second approach, fine-grained control using explicit column lists. This method lets you select exactly which columns to replicate, giving you flexibility for analytics, reporting, or optimizing bandwidth and storage.

We’ll also look at the advantages of stored generated columns and some important notes and limitations you should be aware of when using this feature in real-world replication setups.

Method 2: Fine-Grained Control with Explicit Column Lists

If you want precision, list exactly which columns (including generated ones) should be replicated:

CREATE PUBLICATION pub_sales_partial
FOR TABLE sales (id, price, total);

Behavior

Only the specified columns (id, price, total) are replicated.
Unlisted columns (like quantity) are ignored.
Explicit column lists always override publish_generated_columns.

Best for

Analytics/reporting replicas where only derived values matter.
Avoiding replication of bulky or sensitive columns.
Scenarios with strict bandwidth/storage optimization.

Let’s do it:

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 only selected columns:

postgres18=# CREATE PUBLICATION pub_partial
FOR TABLE sales(id, total)
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_partial
CONNECTION 'host=172.31.27.61 dbname=postgres18 user=postgres'
PUBLICATION pub_partial;
NOTICE:  created replication slot "sub_partial" on publisher
CREATE SUBSCRIPTION

Test Case:

Run this loop of inserts on the publisher to simulate continuous activity:

DO $$
BEGIN
    FOR i IN 1..100 LOOP
        INSERT INTO sales (price, quantity) VALUES (i*10, i);
        PERFORM pg_sleep(0.5); 
    END LOOP;
END $$;

Verify on subscriber:

SELECT * FROM sales ORDER BY id;

Should see all new rows replicated automatically.
The total column should be correct (price * quantity) for every row.
Can rerun this test multiple times to ensure replication consistency.

Key Differences

Feature	publish_generated_columns=’stored’	Explicit Column List
Scope	All stored generated columns	Only listed columns
Control	Broad, automatic	Fine-grained
Future columns	Included automatically	Must be added manually
Precedence	Lower (overridden by column list)	Higher (if list given)

When to Use Which Approach?

Use publish_generated_columns=’stored’ when you want minimal configuration and full replication.
Use explicit column lists when you want tight control, selective replication, or optimization.

Advantages

Less work for your app
PostgreSQL automatically calculates and stores the value, so you don’t need to handle it in your application.
Always accurate
The database keeps the value in sync — it’s never wrong or outdated.
Faster queries
Because the value is already stored, PostgreSQL doesn’t need to calculate it every time you query the table.
No triggers or extra code
You don’t need custom triggers or functions to maintain derived values. It’s built into the table itself.
Easy to replicate (PostgreSQL 18+)
Stored generated columns now replicate just like normal columns no manual setup needed on the subscriber.
Works great with indexes
You can create indexes on generated columns to speed up searches and reports.
More secure and consistent
Users can’t change the generated column directly. PostgreSQL always computes it the same way.

Important Notes and Limitations

There’s currently no support for subscriptions comprising several publications where the same table has been published with different column lists. CREATE SUBSCRIPTION disallows creating such subscriptions, but it is still possible to get into that situation by adding or altering column lists on the publication side after a subscription has been created.

This means changing the column lists of tables in publications that are already subscribed to could lead to errors being thrown on the subscriber side. If a subscription is affected by this problem, the only way to resume replication is to adjust one of the column lists on the publication side so that they all match, and then either recreate the subscription or use ALTER SUBSCRIPTION … DROP PUBLICATION to remove one of the offending publications and add it again.

Conclusion

PostgreSQL 18 finally brings full support for stored generated columns in logical replication, solving a long-standing pain point that previously led to extra work, inconsistencies, or outright limitations in many replication scenarios.

In Part 1, we covered how to automatically replicate all stored generated columns using publish_generated_columns = ‘stored’. This simple configuration ensures consistency across replicas with minimal setup.

In Part 2, we explored fine-grained control using explicit column lists, allowing you to replicate only the columns you need — ideal for reporting replicas, analytical workloads, or reducing bandwidth and storage overhead.

With PostgreSQL 18, stored generated columns are now accurate, consistent, and fully manageable in logical replication. Whether you want convenience or control, PG18 gives you the flexibility to choose the right approach for your architecture.

Just remember to review the caveats around changing column lists and handling multiple publications to avoid unexpected replication errors.

Stay tuned! This is just one of the powerful enhancements in PostgreSQL 18. We’ll be diving into more key features in upcoming blog posts, from performance improvements to SQL enhancements and beyond.

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