Stream Changes with Logical Decoding

Your application silently writes to PostgreSQL, but nobody outside the database knows about it. You've built a cache that goes stale, a search index that lags, and a slow poller that hammers the database every few seconds. The solution isn't another background job — it's streaming changes with logical decoding, a built-in PostgreSQL feature that turns your database into a real-time event source. In this lesson, you'll learn what logical decoding is, why it beats polling for most change-data-capture (CDC) scenarios, and how to set it up step by step. By the end, you'll be able to capture every INSERT, UPDATE, and DELETE as a stream of readable events and feed them straight into your application or analytics pipeline.

The problem this lesson solves

Imagine you run an e-commerce site. Every time a user places an order, you want to:

• Update a search index
• Invalidate a cache
• Send a notification
• Push the order to a data warehouse

The naive approach is to write that logic into every transaction. That couples your application to side effects, slows down commits, and makes it nearly impossible to add new consumers without touching code. The slightly better approach — polling the updated_at column every few seconds — works, but it's inefficient and misses DELETEs entirely. You also run the risk of missing changes that happen between polls.

Core problem: You need a reliable, decoupled way to know what changed in your database without tightly coupling your app or wasting resources on polling.

Enter logical decoding. It's PostgreSQL's built-in mechanism that reads the write-ahead log (WAL) and turns each committed change into a structured, logical event — like INSERT or UPDATE — that you can consume in real time. You no longer have to ask the database "what changed?" — the database tells you the moment it happens.

Core concept / mental model

Think of PostgreSQL as a stone-cold scribe. It writes every change to a journal — the write-ahead log (WAL) — before it touches the actual data files. This journal is what makes crash recovery possible. But the same journal can also serve another purpose: logical decoding reads that WAL and translates the raw binary records into a logical, human-readable form.

Here's the analogy: the WAL is like a store's surveillance tape — it captures every action, but it's not formatted for a manager to read. Logical decoding is like a smart assistant who watches the tape and types up a report: "At 9:14, a new order was placed for customer X with total $29.99."

Key terms:

• Replication slot — A named bookmark that tells PostgreSQL where you are in the WAL stream. It prevents the server from discarding WAL segments you haven't consumed yet.
• Output plugin — A library that formats the decoded changes. The most common one is test_decoding, which outputs simple, readable text. wal2json outputs JSON, which is often more practical for applications.
• Logical decoding — The process of turning WAL entries into logical change events (inserts, updates, deletes, and also DDL, depending on the plugin).

Because logical decoding is a logical view, it's independent of physical storage — you can replicate to a different PostgreSQL version, or stream to a non-PostgreSQL consumer like Kafka.

Mental model: WAL = event source. Replication slot = your personal bookmark. Output plugin = translator that turns binary into readable events.

How it works step by step

To stream changes with logical decoding, you need to:

1. Enable logical replication in postgresql.conf — set wal_level = logical and restart the server.
2. Create a replication slot for a specific output plugin (e.g., test_decoding or wal2json).
3. Start consuming the changes using either SQL functions (for a quick test) or a client library (for production).
4. Process each change — your code will receive a memory chunk that contains all decoded changes from that transaction.

The core functions you'll use in SQL are:

• pg_create_logical_replication_slot(slot_name, plugin) — creates the slot.
• pg_logical_slot_get_changes(slot_name, upto_lsn, upto_nchanges, options) — consumes and acknowledges changes, so they can be discarded.
• pg_logical_slot_peek_changes(...) — peeks at changes without consuming them (useful for dry-run).

The process is: you call pg_create_logical_replication_slot, then you run pg_logical_slot_get_changes in a loop. Each call returns new changes, and you process them. The slot remembers its position, so you don't miss anything, even if your consumer restarts.

Hands-on walkthrough

Let's set up logical decoding on a local PostgreSQL instance. First, ensure your server allows logical replication. In postgresql.conf:

# postgresql.conf
wal_level = logical
max_replication_slots = 5   # default is 10, but 5 is fine for testing

Restart PostgreSQL, then connect to your database and create a test table:

-- Step 1: Create a test table
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer TEXT NOT NULL,
    total NUMERIC(10,2) NOT NULL,
    status TEXT DEFAULT 'new'
);

Now create a logical replication slot using the test_decoding plugin (built-in):

-- Step 2: Create a logical replication slot
SELECT * FROM pg_create_logical_replication_slot('orders_slot', 'test_decoding');

Output:

  slot_name  | lsn
-------------+-----  orders_slot | 0/16B2B20
(1 row)

Now make some changes to the table:

-- Step 3: Make some changes
INSERT INTO orders (customer, total) VALUES ('Alice', 25.00);
UPDATE orders SET status = 'shipped' WHERE customer = 'Alice';
DELETE FROM orders WHERE id = 1;

Now consume the changes from the slot:

-- Step 4: Stream the changes
SELECT * FROM pg_logical_slot_get_changes('orders_slot', NULL, NULL);

Output (format may vary slightly by version):

  lsn    | xid | data
---------+-----+--------------------------------------------------
 0/16B2C0 | 731 | BEGIN 731
 0/16B2C0 | 731 | table public.orders: INSERT: id[integer]:1 customer[text]:'Alice' total[numeric]:25.00 status[text]:'new'
 0/16B2C0 | 731 | COMMIT 731
 0/16B2D0 | 732 | BEGIN 732
 0/16B2D0 | 732 | table public.orders: UPDATE: id[integer]:1 customer[text]:'Alice' total[numeric]:25.00 status[text]:'shipped'
 0/16B2D0 | 732 | COMMIT 732
 0/16B2E0 | 733 | BEGIN 733
 0/16B2E0 | 733 | table public.orders: DELETE: id[integer]:1
 0/16B2E0 | 733 | COMMIT 733

That's it — you've streamed changes from PostgreSQL! The test_decoding plugin gives you a readable text format. For JSON, you'd install the wal2json plugin (which is not bundled by default), but the concept is identical.

Here's a more production-oriented example using Python with the psycopg2 library, which supports logical replication protocol natively:

import psycopg2
import psycopg2.extras

# Connection string for logical replication (requires replication permission)
conn = psycopg2.connect(
    dbname='mydb',
    user='replicator',
    password='secret',
    host='localhost',
    port=5432,
    connection_factory=psycopg2.extras.LogicalReplicationConnection
)

cur = conn.cursor()
cur.create_replication_slot('orders_slot_py', output_plugin='test_decoding')
cur.start_replication(slot_name='orders_slot_py')

def consume(msg):
    print(msg.payload)  # your processing logic goes here
    cur.send_feedback(flush_lsn=msg.data_start)  # acknowledge progress

cur.consume_stream(consume)

# This will run forever, streaming changes as they happen.
# Stop with Ctrl+C.

In this code, consume_stream blocks and invokes consume for each change. The send_feedback call tells PostgreSQL that you've processed up to that point, which is essential to avoid unlimited WAL growth.

Pro tip: Always send feedback after processing, even if you're just logging to a file. If you don't, your replication slot will grow indefinitely and your disk will fill up.

Compare options / when to choose what

Logical decoding is powerful, but it's not the only way to track changes. Here's how it stacks up against the alternatives:

When to choose logical decoding over the rest: - You need real-time or near-real-time events. - You want to keep your application code decoupled from side effects. - You're building an event-driven architecture. - You need to replay changes from a point in time (even if your schema changes, logical decoding can handle it).

When to choose polling or triggers: - Your change rate is very low and "eventually consistent" is fine. - You need a simple audit trail within the same database. - You don't want to manage replication slots.

Troubleshooting & edge cases

Logical decoding can be tricky. Here are common issues and how to fix them:

1. ERROR: logical decoding requires wal_level >= logical

Solution: Set wal_level = logical in postgresql.conf and restart. Also check max_replication_slots — create a slot only if the server allows it.

2. Replication slot grows indefinitely

If you create a slot but never consume it, PostgreSQL will accumulate WAL forever. Check active slots with pg_replication_slots. Drop unused slots with pg_drop_replication_slot. In production, always monitor slot lag.

3. test_decoding doesn't show the exact SQL statement

The output is a logical decoding representation, not literal SQL. For INSERTs you see column values, for UPDATEs you see new values (and optionally old ones if you set include-old-data). If you need the exact UPDATE statement, consider using wal2json with include-type-oids or similar options.

4. Changes happen on a standby server?

Logical decoding works only on the primary or a hot standby. If you're connecting to a standby, it must be configured as hot_standby_feedback and have the slot on the primary — this gets complex. For simplicity, connect to the primary.

5. pg_logical_slot_get_changes returns no changes even after you make them

If you use pg_logical_slot_peek_changes earlier, you may have peeked but not consumed — that's fine. But if you used pg_logical_slot_get_changes already, those changes are gone. Also, changes are only visible after the transaction commits.

6. WAL file retention fills the disk

If you have a lagging consumer, pg_wal will grow. Always set a max_slot_wal_keep_size or monitor pg_replication_slots to avoid filling the disk. Also consider using pg_logical_slot_get_binary_changes in newer versions for efficiency.

What you learned & what's next

You've now grasped the core idea behind streaming changes with logical decoding. You learned that the WAL is the underlying event log, and logical decoding transforms it into readable change events. You practiced creating a logical replication slot, consuming changes via SQL, and even started streaming with a Python client. You can now explain how this approach beats polling for real-time data synchronization.

What's next in the track? Now that you can capture changes, the natural next step is learning how to apply those changes to a different PostgreSQL instance — that's called logical replication. In the next lesson, you'll set up a primary and standby with logical replication, explore conflict resolution, and handle schema changes. That's the bridge between capturing changes and building a multi-database architecture.

Keep experimenting: try creating a wal2json slot and stream to a JSON file. The patterns you've learned here will appear in every CDC pipeline you build from now on.