Neon’s logical replication lets you stream database changes to subscribers, but it’s not just about sending data; it’s about capturing the intent of each change as it happens.
Here’s a snapshot of a Neon logical replication slot and its associated publisher and subscriber:
{
"replication_slot_name": "my_logical_slot",
"plugin": "pgoutput",
"slot_type": "logical",
"datoid": "12345",
"conninfo": "host=your_neon_host.neon.tech port=5432 user=your_user dbname=your_db",
"active": true,
"wal_status": "running",
"active_pid": 12345,
"extra_attributes": {
"proto_version": "1.0",
"publication_names": ["my_publication"]
}
}
This shows a logical slot named my_logical_slot using the pgoutput plugin, actively streaming changes from a database (datoid: 12345) to a publication named my_publication. The conninfo details how a subscriber would connect to this Neon database.
The core problem logical replication solves is enabling near real-time data synchronization between a PostgreSQL source (the publisher) and one or more consumers (subscribers) without requiring full table scans or complex ETL processes. This is crucial for scenarios like:
- Change Data Capture (CDC): Feeding downstream systems (data warehouses, other databases, microservices) with an exact log of what happened in the source database.
- Microservice Data Sharing: Allowing independent services to subscribe to specific datasets they need from a central database.
- Database Migration/Upgrades: Gradually migrating data to a new system while keeping it in sync.
- Caching Invalidation: Triggering cache updates when data changes.
Internally, Neon logical replication works by leveraging PostgreSQL’s Write-Ahead Log (WAL). When a transaction commits, its changes are first written to the WAL. The logical replication system then reads these WAL records, interprets them based on a defined "publication" (which specifies which tables and operations to track), and formats them into a stream of logical changes. This stream is then sent to any connected subscribers via a dedicated replication connection. The subscriber consumes these changes and applies them to its own data.
The key components you’ll interact with are:
- Publications: These are server-side objects that define what data changes to replicate. You create them using
CREATE PUBLICATION. You can specify which tables to include and whether to replicateINSERT,UPDATE, andDELETEoperations.CREATE PUBLICATION my_publication FOR TABLE users, orders; - Replication Slots: On the publisher side, a replication slot acts as a bookmark. It tells the WAL sender process how far along it has sent changes and ensures that WAL records needed for replication are not removed prematurely. You create them using
CREATE_LOGICAL_REPLICATION_SLOT.SELECT pg_create_logical_replication_slot('my_logical_slot', 'pgoutput'); - Subscribers: These are the systems or applications that connect to the publisher and consume the logical replication stream. They typically use a PostgreSQL client library capable of logical decoding or dedicated replication tools.
The pgoutput plugin is the standard output plugin for logical replication in PostgreSQL. It decodes the WAL records into a format that subscribers can understand, typically representing changes as rows being inserted, updated, or deleted.
When you’re setting up replication, understanding the proto_version is important. It indicates the version of the logical decoding protocol the publisher is using. Subscribers need to be compatible with this version. proto_version 1.0 is the most common and widely supported.
The wal_status field in the JSON output tells you if the WAL sender process associated with the slot is active and running. If it’s not running, changes aren’t being sent.
The active_pid is the process ID of the backend process on the publisher that is currently managing this replication slot.
The extra_attributes field, specifically publication_names, confirms which publications this slot is associated with, ensuring that only changes from those specified tables and operations are streamed.
What most people don’t realize is that a logical replication slot doesn’t just track WAL; it actively prevents WAL files from being purged by the system. If a replication slot is lagging or has become inactive, but not dropped, its associated WAL files will continue to accumulate on the publisher, consuming disk space indefinitely until the slot is either advanced or dropped. This is a critical operational consideration for managing disk usage and preventing the publisher from running out of space due to unpurged WAL.
The next hurdle you’ll likely encounter is handling schema changes on the publisher and ensuring they are propagated correctly to the subscribers, often requiring manual intervention or more sophisticated tooling.