PostgreSQL Logical Replication#

Overview#

Sequin is built entirely on PostgreSQL logical replication. Every change event Sequin delivers starts as a WAL (Write-Ahead Log) entry decoded via the pgoutput plugin and streamed to Sequin's SlotProducer process over a dedicated replication connection 1.

Three components must exist on the source database before Sequin can operate:

1. wal_level = 'logical' (requires a server restart if changed)
2. A logical replication slot using the pgoutput plugin
3. A publication scoping which tables are replicated

Prerequisites & Configuration#

wal_level#

wal_level must be 'logical'. Sequin validates this at connection time via check_replication_permissions/1, which queries both current_setting('wal_level') = 'logical' and current_setting('max_replication_slots')::int > 0. A validation error is returned if either check fails, with a note that a server restart may be required if the setting was recently changed 2.

Replication Slot#

Slots must be created with the pgoutput plugin. Sequin's create_replication_slot/2 uses create_if_not_exists semantics — it checks pg_replication_slots before calling pg_create_logical_replication_slot. This is invoked by the CreateReplicationSlotWorker Oban job when create_if_not_exists: true is set in config 3.

Publication#

Publications scope which tables enter the replication stream. Sequin's default creates a publication for all tables 4:

CREATE PUBLICATION name FOR ALL TABLES WITH (publish_via_partition_root = true);

create_publication/3 follows the same create_if_not_exists pattern. Custom init_sql is supported but must be a CREATE PUBLICATION statement whose name matches the supplied publication name.

Replica Identity#

Each replicated table needs a replica identity 5:

• DEFAULT — replicates only the primary key for updates/deletes (default)
• FULL — replicates the full old row; required for complete change records but increases WAL volume

How Sequin Connects: SlotProducer#

SlotProducer 6 is a GenStage producer that owns the replication connection. On startup it:

1. Connects using Postgrex.Protocol in replication mode (replication: "database") 7
2. Fetches restart_lsn from pg_replication_slots to initialize its WAL cursor
3. Issues START_REPLICATION SLOT ... LOGICAL 0/0 (proto_version '1', publication_names '...', messages 'true')

Replication always starts from 0/0 because Sequin filters messages client-side based on a cursor stored separately, fast-forwarding through WAL already processed 8. For PG ≥ 14, messages 'true' is appended to enable pg_logical_emit_message support for heartbeats; PG < 14 omits this 9.

LSN acknowledgments are sent on a 5-second interval (default) via standby status messages, advancing the slot's confirmed_flush_lsn and allowing Postgres to reclaim WAL 10.

The low-level protocol lives in Sequin.Postgres.ReplicationConnection — vendored from Postgrex — which handles the gen_statem state machine, socket buffering, and reconnect logic 11.

SlotSupervisor 12 is a DynamicSupervisor that manages the SlotProducerSupervisor, SlotProcessorServer, and per-consumer SlotMessageStoreSupervisor for each replication slot. Runtime.Supervisor starts and monitors SlotSupervisor instances at boot.

Slot Health Monitoring#

Sequin runs a periodic Oban job (CheckPostgresReplicationSlotWorker) that 13:

• Verifies the slot exists and is in the correct database
• Checks wal_level and replication permissions
• Measures replication lag via pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)

Full slot verification is in Databases.verify_slot/2, which chains: publication exists → slot exists → slot is in correct database → replication permissions OK.

SlotProcessorServer 14 issues heartbeats and verifies receipt. On PG ≥ 14 this uses pg_logical_emit_message; on older versions, a dedicated logical_messages table. If no heartbeat is received within 10 minutes, SlotProcessorServer terminates and triggers a pipeline restart 15.

Diagnostic query for replication lag:

SELECT slot_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag
FROM pg_replication_slots;

Operational Failure Modes#

ERROR 55006 (object_in_use) — Slot held by dead PID#

Log pattern: [SlotProducer] replication connect failed: ERROR 55006 (object_in_use) replication slot "..." is active for PID ...

Occurs when Sequin's replication connection dies uncleanly (e.g., unexpected EOF on standby connection) and PostgreSQL hasn't yet released the slot. Sequin retries every ~10 seconds but will loop indefinitely if the old backend process persists 16.

Workaround: Terminate the stale backend:

SELECT pg_terminate_backend(active_pid)
FROM pg_replication_slots
WHERE slot_name = 'your_slot';

If pg_terminate_backend doesn't resolve it, the issue is likely a supervisor restart loop rather than a stale PID — see below.

SlotProducer Crash on :restart_wal_cursor Timeout#

SlotProducer calls SlotProcessorServer every 10 seconds to determine the safe WAL position to acknowledge 17. If SlotProcessorServer is blocked — by slow database writes, downstream sink latency, or the MessageHandler.put_messages retry loop — the 5-second GenServer.call times out, crashing SlotProducer 18.

The resulting cascade:

1. SlotProducer terminates on the :restart_wal_cursor timeout
2. SlotSupervisor tries to restart; SlotProcessorServer calls :monitor_message_store (120s timeout) — this requires SlotMessageStore to finish loading persisted messages from the database
3. If the internal DB connection pool (default: 10 connections) is saturated, initialization hangs and SlotSupervisor startup fails 19
4. The old replication connection still holds the slot → ERROR 55006 on every retry

Mitigations:

• Increase PG_POOL_SIZE (e.g., PG_POOL_SIZE=30) if pool saturation is observed during startup
• Increase the per-consumer max_memory_mb (default: 128 MB; configurable via PATCH /api/sinks/<name>) to delay buffer pressure
• Set load_shedding_policy: discard_on_full on the sink consumer to prevent blocking put_messages retry loops at the cost of dropping messages

WAL Accumulation / Unbounded Replication Lag#

The slot's confirmed_flush_lsn only advances when Sequin sends acknowledgments. If Sequin stops acknowledging — due to crashes, pipeline stalls, or heartbeat timeout — Postgres cannot reclaim WAL and the slot lag grows without bound. Real-world cases have reached 50–100 GB+ 20.

Because replication always starts from LOGICAL 0/0, recovering from a large WAL backlog requires Sequin to stream and client-side filter the entire backlog before catching up, compounding recovery time 21. In the worst case, dropping and recreating the slot is required to reset to current LSN.

Heartbeat Stall#

SlotProcessorServer logs Heartbeat verification failed (no messages or heartbeat received in last 10 min) then terminates, triggering a pipeline restart. This often surfaces before slot lag becomes critical and is the first visible symptom in the health dashboard 16.

Key Source Files#