Tracking Row-Level Changes with Postgres Logical Replication

Need to capture every INSERT, UPDATE, or DELETE as soon as it happens? Postgres logical replication lets you stream row-level changes directly from the database engine, providing an audit-friendly, low-latency alternative to batch ETL and trigger-based solutions.

This article walks through how logical replication works under the hood, why it matters, how to configure it, and the best practices (and pitfalls) you need to know.

Why Track Row-Level Changes?

• Real-time analytics — power dashboards and anomaly detection with fresh data.
• Event-driven micro-services — publish database changes to Kafka, Kinesis, or Webhooks.
• Compliance & auditing — maintain an immutable history of sensitive records.
• Data warehouse sync — push incremental updates to Snowflake, BigQuery, or Redshift instead of nightly dumps.
• Cache invalidation — evict or update Redis keys when the source of truth mutates.

Postgres Logical Replication in a Nutshell

When wal_level is set to logical, PostgreSQL writes extra metadata to its WAL (Write-Ahead Log). A replication slot holds the position of each subscriber so that WAL files aren’t recycled until all downstream consumers have read them. A publication defines which tables and operations should be streamed, and a subscription on another Postgres instance (or a custom decoder) receives those changes.

High-Level Flow

1. Enable wal_level = logical and restart the primary.
2. Create a publication specifying tables (FOR TABLE) or everything (FOR ALL TABLES).
3. Create a subscription on the replica. Postgres will automatically take a snapshot of the current data and then switch to streaming incremental changes.
4. Optionally, write a custom output plugin (e.g., wal2json) to emit JSON for Kafka, Pulsar, or webhooks.

Detailed Configuration Walkthrough

1. Server-Side Settings

-- postgresql.conf (primary)
wal_level = logical -- default: replica
max_replication_slots = 10 -- at least 1 per downstream consumer
max_wal_senders = 10 -- concurrent streaming processes


After editing these values, restart Postgres.

2. Allow Replication Connections

# pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
host replication rep_user 10.0.0.0/24 md5


3. Create Replication Role

CREATE ROLE rep_user WITH REPLICATION LOGIN PASSWORD 'secret';


4. Define a Publication

-- Stream only the important tables
CREATE PUBLICATION accounting_pub FOR TABLE invoices, payments;
-- Or everything
-- CREATE PUBLICATION all_pub FOR ALL TABLES;


5. Create Subscription (Replica)

CREATE SUBSCRIPTION accounting_sub
CONNECTION 'host=10.0.0.1 port=5432 user=rep_user password=secret dbname=app'
PUBLICATION accounting_pub;


Postgres copies existing rows (the initial snapshot) and then streams new changes.

6. Decoding WAL to JSON

If you need to pipe events to a message queue, install wal2json or pgoutput and use a connector like debezium:

# Example Debezium config
"database.server.name": "app",
"plugin.name": "wal2json",
"slot.name": "debezium_slot",
"publication.autocreate.mode": "filtered"


Best Practices

• Add primary keys to all replicated tables. Logical decoding relies on a stable row identifier.
• Use partial publications to limit traffic to what you actually need.
• Monitor slot lag via pg_replication_slots. If a consumer stalls, disk usage can skyrocket.
• Separate maintenance traffic by dedicating a network interface or VPC endpoint for replication.
• Version your schema—breaking changes (e.g., dropping columns) can break consumers.

Common Misconceptions

• “Logical replication is the same as physical streaming.” — Logical works at the row level and understands SQL semantics; physical copies disk blocks.
• “Triggers are easier.” — Triggers add latency to every write path and are hard to scale; logical replication runs outside the transaction, preserving OLTP performance.
• “I can ignore WAL retention.” — Unconsumed slots prevent WAL recycling and can fill your disk.

Real-World Example

A fintech startup needs to stream ledger updates into Redshift for compliance reporting. By configuring a publication on ledger_entries and subscribing via Debezium + Kafka Connect, they achieve sub-second replication without hammering the OLTP cluster with batch queries or triggers.

How Galaxy Fits In

Because Galaxy is a modern SQL editor, you can:

• Create publications and inspect pg_replication_slots directly from the editor.
• Use Galaxy’s AI Copilot to generate the replication SQL shown above or diagnose slot lag.
• Share your logical replication setup queries via Galaxy Collections, allowing team endorsement and version control of replication scripts.

While Galaxy doesn’t run the replication process itself, it accelerates development and collaboration around the necessary SQL.

Conclusion

Postgres logical replication provides a first-class, low-latency way to track row-level changes, power event-driven architectures, and simplify data pipelines. By following the configuration steps and best practices outlined above—and avoiding common pitfalls like missing primary keys or unmanaged slots—you can build robust, scalable change-data-capture pipelines directly on top of PostgreSQL.