OLTP OLAP with new Columnar Tables in Postgres

WAL 

The write-ahead log is Postgres’s own internal durability mechanism — it exists for crash recovery, with zero awareness of ELT process (Fivetran, Airbyte, CDC, etc.) or ClickHouse. Every transaction is written to the WAL before the actual table/heap pages are updated (hence “write-ahead”) — that’s what lets Postgres recover to a consistent state if it crashes mid-write. This mechanism predates CDC entirely and would exist even if no downstream replication ever happened.

CDC (Change Data Capture) streaming is a technique that tracks row-level inserts, updates, and deletes in a source database and streams those changes to downstream systems in real time.Instead of querying a database repeatedly or running heavy batch jobs, a CDC stream reads the database’s internal transaction logs (such as PostgreSQL WAL or MySQL binlogs) to capture changes the moment they happen. Tools like Debezium and PeerDB automate this extraction and turn database modifications into live event streams

But new version of Postgres can do the same thing directly using columnar tables.

Building an Append-Only OLTP/OLAP System with Postgres Logical Replication

This design uses two Postgres servers:

a row-based (heap) OLTP server that handles all application writes, and

a columnar OLAP server that receives data automatically and asynchronously via native logical replication.

Nothing is ever updated or deleted — corrections are new rows.

Design principles

  • OLTP side: row storage (heap), receives all writes. Never updated, never deleted from — a correction is a new row.
  • OLAP side: columnar storage, on a separate server, populated automatically and asynchronously from the OLTP side’s WAL. Never written to directly.
  • Surrogate key, not business key: event_id uniquely identifies a row; order_id identifies an order and can legitimately appear more than once.

Step 1 — OLTP server: enable logical replication

# postgresql.conf on the OLTP server — requires a restart
wal_level = logical

Step 2 — OLTP server: create the table

CREATE TABLE orders (
    event_id     bigserial PRIMARY KEY,        -- unique per row, not per order
    order_id     int NOT NULL,                 -- business key; NOT unique — repeats on correction
    customer_id  int NOT NULL,
    order_date   date NOT NULL,
    revenue      numeric NOT NULL,
    recorded_at  timestamptz NOT NULL DEFAULT now(),
    event_type   text NOT NULL DEFAULT 'insert' -- e.g. 'insert', 'correction', 'cancellation'
);

Step 3 — OLTP server: create a replication role and grant access

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD '<choose_a_strong_password>';
GRANT SELECT ON orders TO replicator;
# pg_hba.conf on the OLTP server — allow the OLAP server to connect for replication
host    replication    replicator    <olap_server_ip>/32    scram-sha-256
host    orders_db      replicator    <olap_server_ip>/32    scram-sha-256

(Reload or restart Postgres after editing pg_hba.conf.)

Step 4 — OLTP server: publish the table

CREATE PUBLICATION orders_pub FOR TABLE orders;

Step 5 — OLAP server: enable columnar storage using EXTENSION citus_columnar

CREATE EXTENSION citus_columnar;

Step 6 — OLAP server: create the matching table

Same name (orders) is required for native logical replication to match it up. No PRIMARY KEY here — Citus columnar doesn’t support indexes, so the constraint that made sense on the OLTP side would just error out here.

CREATE TABLE orders (
    event_id     bigint,
    order_id     int,
    customer_id  int,
    order_date   date,
    revenue      numeric,
    recorded_at  timestamptz,
    event_type   text
) USING columnar;

Step 7 — OLAP server: subscribe to the OLTP server’s publication

CREATE SUBSCRIPTION orders_sub
CONNECTION 'host=<oltp_server_ip> port=5432 dbname=orders_db user=replicator password=<the_password>'
PUBLICATION orders_pub;

Step 8 — verify it’s actually working

-- On the OLTP server: confirm the subscriber has connected
SELECT * FROM pg_stat_replication;

-- On the OLAP server: confirm subscription status
SELECT subname, pid, received_lsn, latest_end_lsn FROM pg_stat_subscription;

How you actually use it

Original write (application only ever does this):

INSERT INTO orders (order_id, customer_id, order_date, revenue)
VALUES (1, 101, '2026-08-01', 50.00);

Correction (never an UPDATE — a new row, same order_id):

INSERT INTO orders (order_id, customer_id, order_date, revenue, event_type)
VALUES (1, 101, '2026-08-01', 45.00, 'correction');

Reading current state (on either side — needed because history now has multiple rows per order):

SELECT DISTINCT ON (order_id) *
FROM orders
ORDER BY order_id, recorded_at DESC;

That’s the complete system: OLTP writes land in orders (heap) → the WAL records them → logical replication streams them asynchronously, with zero coupling to the OLTP transaction → they land in the OLAP server’s orders (columnar) → analytical queries run there without ever touching the transactional server.

Loading