Replication is a core part of most production PostgreSQL setups. It covers high availability, disaster recovery, read scaling, and workload distribution. As distributed database deployments grow more common, consistency across nodes becomes harder to take for granted.

PostgreSQL 18 brings several improvements to logical replication. One of the most requested among them is the ability to logically replicate STORED generated columns. This post covers what changed, how to configure it, and what the performance numbers look like from our testing.

Related reading: PostgreSQL Replication: Setup, Types and Best Practices | PostgreSQL 18 Docs: Logical Replication

Introduction to Generated Columns

Generated columns are computed automatically from other columns using a defined expression. PostgreSQL classifies them into two types based on how and when the value is produced.

STORED generated columns are computed at insert or update time, and the value is physically written to the table. Reads are fast because no recalculation happens at runtime. The trade-off is that they occupy additional storage space.

VIRTUAL generated columns are computed only when a query reads them. The value is never stored. This saves storage but adds computation overhead at read time.

For replication purposes, only STORED generated columns carry a physical value that can be sent over the wire. Virtual columns have nothing to replicate.

Logical Replication of STORED Generated Columns

This has been one of the most requested features in PostgreSQL replication. Before PostgreSQL 18, generated columns were excluded from publications by default. Subscribers had to recompute the values locally, which created real risks: if the subscriber's expression differed from the publisher's, or if the dependency was missing entirely, the column would silently remain NULL or produce incorrect data. PostgreSQL 18 resolves this by sending the already-computed value directly from the publisher.

Scenario 1: Publication Behavior in Older Versions (PostgreSQL 16)

To confirm the old behavior, we set up a test on PostgreSQL 16.

Publisher setup (PostgreSQL 16) - Create a table with a STORED generated column:‍


CREATE TABLE bills (
    id      INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    amount  NUMERIC(10,2),
    tax     NUMERIC(4,2),
    total   NUMERIC(10,2)
            GENERATED ALWAYS AS (amount + (amount * tax)) STORED
);
 Insert values
INSERT INTO bills (amount, tax) VALUES
(100.00, 10.00),
(200.00, 20.00),
(50.00, 5.00);

Output:‍


SELECT * FROM bills;
 id | amount |  tax  | total  
----+--------+-------+--------
  1 | 100.00 | 10.00 | 110.00
  2 | 200.00 | 20.00 | 220.00
  3 |  50.00 |  5.00 |  55.00
(3 rows)

Create a standard publication (no new option needed or available):‍


CREATE PUBLICATION nongenerated_columns
FOR TABLE bills;

SELECT attnames
FROM pg_publication_tables
WHERE pubname = 'nongenerated_columns';

Output:‍


 attnames     
-----------------
 {id,amount,tax}
(1 row)

Observation: The generated column total does not appear in the publication. PostgreSQL excludes STORED generated columns by default, and this behavior applies to all versions prior to PostgreSQL 18. It also remains the default in PostgreSQL 18 unless explicitly changed.

Subscriber setup:‍


CREATE TABLE bills (
    id      INTEGER PRIMARY KEY,
    amount  NUMERIC(10,2),
    tax     NUMERIC(10,2),
    total   NUMERIC(10,2)
);
Create subscription:
CREATE SUBSCRIPTION bills_sub
CONNECTION 'host=127.0.0.1 dbname=testdb user=postgres'
PUBLICATION nongenerated_columns;

Query replicated data:‍


SELECT * FROM bills;
 id | amount |  tax  | total 
----+--------+-------+-------
  1 | 100.00 | 10.00 |      
  2 | 200.00 | 20.00 |      
  3 |  50.00 |  5.00 |      
(3 rows)

Observations: Columns id, amount, and tax replicate correctly. The total column is not sent by the publisher, and since it is a regular column on the subscriber side, it stays NULL. No error is raised.

Scenario 2: Enabling Logical Replication for Generated Columns (PostgreSQL 18)

PostgreSQL 18 introduces the ability to replicate STORED generated columns using logical replication. We switched to PostgreSQL 18 to test this behavior.

Reference: PostgreSQL 18 Docs: Generated Column Replication | PostgreSQL 18 Docs: CREATE PUBLICATION

Publisher setup (PostgreSQL 18) - Create table with a STORED generated column:‍


CREATE TABLE bills (
id SERIAL PRIMARY KEY,
amount NUMERIC(10,2) NOT NULL,
tax NUMERIC(10,2) NOT NULL,
total NUMERIC(10,2)
GENERATED ALWAYS AS (amount + tax) STORED
);
CREATE TABLE

INSERT INTO bills (amount, tax) VALUES
(100.00, 10.00),
(200.00, 20.00),
(50.00, 5.00);
INSERT 0 3

Output:‍


SELECT * FROM bills;
 id | amount |  tax  | total  
----+--------+-------+--------
  1 | 100.00 | 10.00 | 110.00
  2 | 200.00 | 20.00 | 220.00
  3 |  50.00 |  5.00 |  55.00
(3 rows)

Create a publication with generated columns enabled:‍


CREATE PUBLICATION generated_columns
FOR TABLE bills
WITH (publish_generated_columns = stored);

Verify published columns:

SELECT attnames FROM pg_publication_tables WHERE pubname = 'generated_columns';

Output:‍

attnames        
-----------------------
 {id,amount,tax,total}
(1 row)

The generated column total is now included in the replication set.

Subscriber setup:

Create subscriber table
CREATE TABLE bills (
    id      INTEGER PRIMARY KEY,
    amount  NUMERIC(10,2),
    tax     NUMERIC(10,2),
    total   NUMERIC(10,2)
);
Create subscription
CREATE SUBSCRIPTION billstable
CONNECTION 'host=localhost port=5432 dbname=testdb user=postgres password=StrongPassword123'
PUBLICATION generated_columns;

Query replicated data:‍


select * from bills;
 id | amount |  tax  | total  
----+--------+-------+--------
  1 | 100.00 | 10.00 | 110.00
  2 | 200.00 | 20.00 | 220.00
  3 |  50.00 |  5.00 |  55.00
(3 rows)

Observations: The total column is replicated from the publisher as a precomputed value. The subscriber does not recalculate it. Data consistency is maintained across both sides.

Note on subscriber table definition: The subscriber table must define total as a plain NUMERIC column, not as a generated column. If the receiving column also carries a generation expression, the apply worker will raise an error when the publisher tries to write the incoming value into it.

Replication Flow: Missing Dependencies vs Full Sync

PG 16 (Older)

Publisher

{id, amount, tax, total}

total (dropped)

Subscriber

{id, amount, tax, NULL}

PG 18 (New)

Publisher

{id, amount, tax, total}

{ ..., total: 110.00 }

Subscriber

{id, amount, tax, total}

Scenario 3: Default Behavior in PostgreSQL 18

PostgreSQL 18 preserves backward compatibility. If publish_generated_columns is not set, the default is none, meaning generated columns are excluded from the replication stream, matching the behavior of all prior versions.

Publisher setup:‍


CREATE PUBLICATION bills_generated_columns_default
FOR TABLE bills WITH (publish_generated_columns = none);
CREATE PUBLICATION

INSERT INTO bills (amount, tax) VALUES
(100.00, 10.00),
(200.00, 20.00),
(50.00, 5.00);

select * from bills;

Output:‍


 id | amount |  tax  | total  
----+--------+-------+--------
  4 | 100.00 | 10.00 | 110.00
  5 | 200.00 | 20.00 | 220.00
  6 |  50.00 |  5.00 |  55.00
(3 rows)

The generated column total is calculated only on the publisher.

Subscriber setup:‍


CREATE SUBSCRIPTION bills_gencol_sub
CONNECTION 'host=127.0.0.1 port=5432 dbname=testdb user=postgres password=StrongPassword123'
PUBLICATION bills_generated_columns_default;

SELECT * from bills;

Output:‍


 id | amount |  tax  | total 
----+--------+-------+-------
  4 | 100.00 | 10.00 |      
  5 | 200.00 | 20.00 |      
  6 |  50.00 |  5.00 |      
(3 rows)

Observation: The generated column total is not updated on the subscriber. No error or warning is raised. See also: PostgreSQL 18 Docs: Column Lists

Publication Parameter: publish_generated_columns

Default Setup

publish_generated_columns = 'none'

Maintains backward compatibility. Generated columns are excluded from the replication stream.

✖

total

PG 18 Feature

publish_generated_columns = 'stored'

Explicitly includes STORED generated columns in the logical replication publication.

total

Performance Testing with Sysbench

To measure the practical impact of querying a precomputed STORED column versus computing on the fly, we ran 10,000 query executions for both column types using sysbench with a single thread.

Queries tested:

Non-generated column:

SELECT SUM(val1 * val2) FROM normal_table WHERE val1 * val2 > 50;

Generated column:

SELECT SUM(computed) FROM generated_table WHERE computed > 50;

Sysbench commands used:‍


 sysbench normal_test.lua \
--db-driver=pgsql \
--pgsql-host=localhost \
--pgsql-user=postgres \
--pgsql-db=postgres \
--threads=1 \
--events=10000 \
--time=0 \
run

CPU monitoring commands:

pidstat -C postgres 1 > cpu_normal.txt
pidstat -C postgres 1 > cpu_generated.txt

awk '{sum+=$8} END {print "Average CPU:", sum/NR "%"}' cpu_normal.txt
awk '{sum+=$8} END {print "Average CPU:", sum/NR "%"}' cpu_generated.txt

Output:‍‍

Sysbench test for non-generated Columns

mydbops@ip-172-31-3-181:~/script$ sysbench normal_test.lua --db-driver=pgsql --pgsql-host=localhost --pgsql-user=postgres --pgsql-db=postgres --threads=1 --events=10000 --time=0 run
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
Initializing worker threads...
Threads started!
SQL statistics:
    queries performed:
        read:                            10000
        write:                           0
        other:                           0
        total:                           10000
    transactions:                        10000  (7.51 per sec.)
    queries:                             10000  (7.51 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)
General statistics:
    total time:                          1331.5248s
    total number of events:              10000
Latency (ms):
         min:                                   96.04
         avg:                                  133.15
         max:                                  327.49
         95th percentile:                      161.51
         sum:                              1331484.47
Threads fairness:
    events (avg/stddev):           10000.0000/0.00
    execution time (avg/stddev):   1331.4845/0.00
mydbops@ip-172-31-3-181:~/script$ awk '{sum+=$8} END {print "Average CPU:", sum/NR,"%"}' cpu_normal.txt
Average CPU: 13.9017 %

Sysbench test for generated columns 

mydbops@ip-172-31-3-181:~/script$ sysbench generated_test.lua --db-driver=pgsql --pgsql-host=localhost --pgsql-user=postgres --pgsql-db=postgres --threads=1 --events=10000 --time=0 run
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
Initializing worker threads...
Threads started!
SQL statistics:
    queries performed:
        read:                            10000
        write:                           0
        other:                           0
        total:                           10000
    transactions:                        10000  (8.00 per sec.)
    queries:                             10000  (8.00 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)
General statistics:
    total time:                          1249.4682s
    total number of events:              10000
Latency (ms):
         min:                                   90.43
         avg:                                  124.94
         max:                                  266.76
         95th percentile:                      137.35
         sum:                              1249426.89
Threads fairness:
    events (avg/stddev):           10000.0000/0.00
    execution time (avg/stddev):   1249.4269/0.00
mydbops@ip-172-31-3-181:~/script$ awk '{sum+=$8} END {print "Average CPU:", sum/NR,"%"}' cpu_generated.txt
Average CPU: 14.8632 %

IO

mydbops@ip-172-31-3-181:~/script$ awk '{read+=$5; write+=$6; count++} END {print "Avg Read:", read/count, "KB/s, Avg Write:", write/count, "KB/s"}' io_normal.txt
Avg Read: 0.000801272 KB/s, Avg Write: 0.000405043 KB/s
mydbops@ip-172-31-3-181:~/script$ awk '{read+=$5; write+=$6; count++} END {print "Avg Read:", read/count, "KB/s, Avg Write:", write/count, "KB/s"}' io_generated.txt
Avg Read: 0.00088592 KB/s, Avg Write: 0.000545802 KB/s

Sysbench Results Summary

Interpretation:

Generated columns completed the same 10,000 queries roughly 82 seconds faster, with average latency dropping from 133 ms to 124 ms. CPU usage is marginally higher for generated columns because queries finish faster, keeping the processor busier per unit of time. Read and write IO is slightly higher for generated columns because the precomputed values are stored on disk and must be read from storage during query execution. Non-generated columns compute in memory at query time, so they produce less disk IO.

Overall, generated columns allow faster query execution and lower latency, which matters in replication-heavy environments where subscribers need consistent, ready-to-read data.

Summary

Logical replication of STORED generated columns is now fully supported in PostgreSQL 18 using the publish_generated_columns = stored option. Publishers send precomputed values directly, removing the need for subscribers to recompute them and eliminating the risk of logic mismatches or silent NULL failures that existed in earlier versions.

Backward compatibility is preserved. The default remains none, so existing replication setups are not affected by upgrading to PostgreSQL 18. The publish_generated_columns parameter gives administrators explicit control over this behavior on a per-publication basis.

Performance testing confirms that STORED generated columns reduce total query time and average latency compared to columns that compute at read time. This improvement is consistent and meaningful in environments with high read throughput or replication-heavy workloads. The trade-off is marginally higher disk IO, which in most cases is acceptable given the latency gains.

For more details, refer to the official release notes: PostgreSQL 18 Release Notes

Further Reading

Internal resources from Mydbops:

• PostgreSQL Replication: Setup, Types and Best Practices
• Bidirectional Logical Replication in PostgreSQL 16
• PostgreSQL 17: Convert Streaming Replica to Logical Replication with pg_createsubscriber
• PostgreSQL WAL File Retention
• Cascading Replication in PostgreSQL
• Convert Your PostgreSQL Database to a Patroni Cluster for High Availability
• PostgreSQL Performance Tuning Best Practices
• PostgreSQL 16: A Comprehensive Overview of New Features
• PostgreSQL 17: Incremental Backup with pg_basebackup and pg_combinebackup

PostgreSQL official documentation:

• Generated Column Replication (Section 29.6)
• CREATE PUBLICATION
• Generated Columns (DDL)
• Logical Replication (Chapter 29)
• Column Lists (Section 29.5)
• PostgreSQL 18 Release Notes

Ready to Improve Your PostgreSQL Setup?

Running PostgreSQL in production? Mydbops supports you with replication setup, version upgrades, 24/7 monitoring, migration planning, remote DBA services, and security audits so your team can stay focused on core priorities.