Tracking Row-Level Changes with Postgres Logical Replication

Galaxy Glossary

How do I track row-level changes in PostgreSQL using logical replication?

PostgreSQL logical replication lets you stream INSERT, UPDATE, and DELETE events to other databases or external services so you can track row-level changes in near real-time.

Sign up for the latest in SQL knowledge from the Galaxy Team!
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

Description

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.

Why Tracking Row-Level Changes with Postgres Logical Replication is important

Modern data stacks demand real-time visibility into database mutations. Logical replication eliminates fragile trigger-based CDC, reduces ETL latency from hours to seconds, and offloads heavy analytics queries from the primary instance. Mastering it lets data engineers build scalable, event-driven systems without abandoning PostgreSQL.

Tracking Row-Level Changes with Postgres Logical Replication Example Usage


CREATE PUBLICATION accounting_pub FOR TABLE invoices, payments;

Tracking Row-Level Changes with Postgres Logical Replication Syntax



Common Mistakes

Frequently Asked Questions (FAQs)

Is logical replication the same as physical replication?

No. Physical replication streams binary disk changes (blocks), while logical replication streams row-level INSERT/UPDATE/DELETE events. Logical is better for CDC and multi-way replication.

Can I monitor logical replication from Galaxy's SQL editor?

Yes. You can query pg_publication, pg_subscription, and pg_replication_slots from Galaxy, then share those diagnostics with teammates via Collections.

What happens if a subscriber is offline for days?

The replication slot will retain WAL files until the subscriber catches up, potentially filling disk space. Monitor pg_replication_slots and drop or move stale slots.

Do I need to restart Postgres to enable logical replication?

You must restart when changing wal_level from replica to logical, but later publication and subscription changes are hot-swappable.

Want to learn about other SQL terms?