A PostgreSQL LISTEN/NOTIFY and Durable Outbox Study for Event Wakeups

Key Stats

• 1 version-pinned PostgreSQL instance
• 18 delivery and recovery cases
• 4 disconnect windows

Key Takeaways

• Store work before signaling it.
• Treat a notification as permission to look, not as the event record.
• Recover from an outbox cursor after every reconnect.

The decision under review

This study asks whether a PostgreSQL-backed service may use LISTEN and NOTIFY to wake an event consumer without treating a notification as durable work. The producer writes a synthetic outbox row and calls pg_notify in the same transaction. A listener wakes, queries rows after its durable cursor, claims them, and advances only after the fixture records the chosen processing outcome. The comparison includes polling without notifications, notification-assisted polling, disconnect recovery, duplicate wakes, and consumer replacement.

The distinction matters because a fast signal and a recoverable record solve different problems. A notification can reduce the delay before a consumer looks for work. The outbox row survives a listener restart and gives the consumer something it can query again. The study does not promise exactly-once effects or present LISTEN/NOTIFY as a general message broker. It produces a narrow decision: whether notifications are safe as hints for this service when correctness comes from committed rows, an ordered cursor, and idempotent processing.

Facts that shape the experiment

PostgreSQL documents that LISTEN registers a database session for a named channel and takes effect when its transaction commits. NOTIFY events issued inside a transaction are delivered only if that transaction commits. A listening client receives notifications between transactions, so a listener that stays inside a long transaction can delay delivery. Identical channel and payload combinations issued more than once in one transaction may collapse into one notification. Those rules make a notification unsuitable as the only count of business events.

The documentation also describes a startup race. A client should commit LISTEN first, inspect relevant database state in a new transaction, and then rely on later notifications to prompt another inspection. Early notifications may refer to rows already seen by the initial query. The fixture follows that order and accepts redundant wakes. It rejects any design that listens and then waits without first catching up from durable state, because a transaction can commit before the registration becomes effective or while the client has no active session.

Create the durable fixture

Create an outbox table with a monotonic sequence, event identifier, aggregate identifier, event kind, synthetic payload, committed timestamp, and processing metadata required by the selected ownership model. Create a consumer checkpoint table keyed by consumer name. The fixture uses invented order references and contains no customer records, secrets, or production payloads. A producer transaction changes one synthetic order, inserts its outbox row, and calls pg_notify with a small wake token. The token contains no event body and is never needed to recover the row.

Pin the PostgreSQL version, client library, schema migration, isolation level, connection settings, and application commit. Preserve setup and reset commands plus hashes for producer and consumer code. Run each case from a known empty schema or a recorded checkpoint. The test clock labels observations, but sequence order comes from committed database values rather than wall-clock assumptions. A second producer and two named consumers reveal whether the method accidentally depends on one connection, one process identifier, or one convenient order of callbacks.

Define the consumer loop

The consumer obtains a dedicated session, executes LISTEN, commits that registration, and immediately reads outbox rows after its stored cursor. It then waits for socket activity or a bounded poll interval. Any notification causes the same query; its payload does not select the only row to process. The query uses a deterministic sequence order and a fixed batch limit. After a disconnect, the replacement session repeats LISTEN, commit, catch-up query, and wait. This makes reconnection a normal state transition rather than a special attempt to reconstruct missed messages.

Choose checkpoint semantics before running the fixture. One option advances after each idempotent side effect succeeds. Another claims rows for a bounded lease and records attempts separately. The article does not prescribe one universal outbox processor, but the evidence must show what happens if the process stops before work, during work, after the effect, or before checkpoint commit. A notification handler must remain small. It schedules a drain and coalesces concurrent wake requests instead of starting an unbounded query for every callback.

Exercise commit and rollback boundaries

Begin a producer transaction, insert an outbox row, issue NOTIFY, and hold the transaction open. The consumer must see neither committed row nor delivered wake before commit. Commit and record the row sequence, notification receipt, query start, and processing result. Repeat with rollback. The rolled-back row and its notification must not appear. Then insert two distinct rows with identical notification payloads in one transaction. Even if PostgreSQL folds the duplicate notifications, one drain must retrieve both rows from the table.

Reverse the variation by sending distinct payloads in one transaction and by committing rows from two producer sessions. Record notification order without assuming that one wake equals one row. Hold the listening session in a transaction while a producer commits, then end that listener transaction and observe delivery. This case verifies a documented source of latency. The repair is to keep the listening connection out of long transactions, not to move the business event into a larger notification payload or add sleep calls until the test happens to pass.

Cut the connection in four windows

The first disconnect happens before LISTEN commits while a producer commits a row. The replacement must catch up from its checkpoint. The second happens after registration but before the consumer receives the signal. The third happens after wake receipt but before the outbox query. The fourth happens after the query returns but before the checkpoint commits. Each case restarts with the same algorithm: establish the listener, commit it, inspect durable state, and process everything beyond the durable cursor according to the declared retry rule.

Record whether each row is unseen, attempted, completed, repeated, or left uncertain. The fixture passes recovery when every committed row reaches an allowed terminal outcome and no rolled-back row is processed. A repeated attempt is not automatically a defect, because a crash after an external effect but before checkpoint commit can make the next consumer see the row again. The effect boundary therefore needs an idempotency key or another explicit reconciliation method. LISTEN/NOTIFY cannot close that application-level ambiguity.

Test duplicate wakes and competing consumers

Send several notifications for one committed row, send one notification for a batch, and send a notification when no new row exists. The drain should tolerate every case. Keep counters for wakes, drain schedules, rows fetched, attempts, successful outcomes, repeats, and empty drains. The counters describe behavior but do not establish correctness by themselves. Case-level evidence must connect each committed event identifier to its outbox row, processing attempts, checkpoint movement, and final disposition.

Run two instances under the intended consumer model. If both represent the same logical subscription, use a reviewed claim or partition rule so they do not perform the same non-idempotent effect concurrently. If each represents a different subscriber, give each its own checkpoint and expected outcome. Notifications reach listening sessions; they do not assign exclusive ownership of a row. Stop one instance during a claimed batch and prove that the other can recover work after the documented lease or reconciliation boundary without skipping the remaining sequence.

Inspect queue pressure and operational limits

PostgreSQL keeps notifications in a queue until listening sessions can process them. The documentation notes that a listener left in a transaction can prevent cleanup, and pg_notification_queue_usage reports the occupied fraction. Add a controlled case with one stalled listener and a bounded notification volume. Observe queue usage, server warnings available to the test operator, producer commit results, and recovery after the listener leaves its transaction. Do not attempt to fill a shared environment or turn this into an exhaustion test without an isolated approved database.

Operational checks include listener connection state, reconnect attempts, last successful catch-up, oldest unprocessed outbox age, checkpoint lag, drain duration, batch saturation, repeated attempts, dead-letter or review outcomes, and notification queue usage. Alerts should focus on durable lag and failed processing, not merely the absence of notifications. A quiet channel can mean there is no work. A healthy stream of wakes can coexist with a stuck cursor. The database owner sets queue and connection limits; the service owner sets lag and retry thresholds.

Challenge the preferred design

Run the consumer with notifications disabled while bounded polling remains active. All rows should still complete, with higher wake latency allowed by the test. Then disable periodic catch-up and drop the listener connection during a commit. The seeded defect must leave a row unprocessed until another wake or restart exposes it. This pair demonstrates what notifications improve and what they cannot guarantee. If both variants appear equally reliable under every disconnect, the harness may not be cutting the connection at the intended boundary.

Test a tempting alternative that places the full synthetic event in the notification payload and omits the outbox insert. Disconnect the listener, commit the producer, and show that the application has no queryable record from which to recover that event. Keep this negative case isolated from the acceptable implementation. Also test a consumer that assumes one callback per NOTIFY. Identical notifications in one transaction may collapse, so the row count must come from the table, not from callback arithmetic.

Handoff, limits, and decision rule

The handoff contains database and client versions, schema and code hashes, channel ownership, producer transaction sequence, consumer state machine, checkpoint rule, case matrix, raw event timeline, disconnect controls, duplicate-wake results, queue observations, idempotency boundary, access scope, rollback, and named reviewers. A second engineer reproduces the startup sequence, rollback case, collapsed-wake case, one disconnect before query, one crash after effect, and recovery with notifications disabled. The review uses synthetic data and a restricted test database.

Pass requires every committed outbox row to remain discoverable after restart, no rolled-back row to be processed, seeded disconnects to recover from durable state, redundant notifications to be harmless, checkpoint movement to follow the declared effect rule, and bounded behavior under empty or repeated wakes. Conditional pass names uncertain external-effect or failover paths and their owner. Fail preserves the smallest missing, skipped, or concurrently duplicated case. The conclusion applies only to the pinned topology and says that LISTEN/NOTIFY may accelerate a durable outbox consumer, not replace it.

Sources checked for this study

PostgreSQL's current LISTEN documentation defines session registration, commit behavior, and the startup race that requires registration before the initial state inspection. The current NOTIFY documentation defines transaction delivery, duplicate folding, ordering, payload limits, and notification queue behavior. The current libpq asynchronous-notification documentation explains how a client consumes pending notifications and integrates socket input with PQnotifies. These sources define database and client mechanisms; the outbox schema, failure cases, and decision thresholds are DeveloperOffshore.com analysis for a bounded handoff.

The study does not prove cross-region failover, logical replication, connection-pool proxy behavior, operating-system socket timing, every client driver, or external side-effect exactly-once semantics. A managed database may expose different monitoring and connection controls. Re-run after a PostgreSQL upgrade, driver or pool change, schema change, failover design change, checkpoint change, new subscriber model, or revised processing effect. Treat an untested disconnect window as unknown rather than inferring recovery from a successful steady-state run.

Evidence table

Good distributed work is observable at the handoff: the result, evidence, limitations, and next owner are all explicit.

Frequently asked questions

Related Research

• Data pipeline development
• Legacy application maintenance
• Research library