skip to content

In PostgreSQL, a cache-invalidation listener reconnects after a one-minute outage; which NOTIFY signals sent meanwhile does it receive, and how should it resynchronise?

level: middleimportance: must knowfreq 54%

answer

  1. no mailbox for absent sessions
  2. queue serves current listeners only
  3. pg_notify directory wiped at startup
  4. LISTEN, commit, then read
  5. notification is a hint, table is truth

basics

~20 s

None: PostgreSQL delivers a notification only to sessions listening when it commits and never stores it for anyone else. On reconnect the listener must run LISTEN, commit, then reload or re-validate its cache from the tables.

solid answer

~40 s

PostgreSQL keeps a notification only until every **current** listener has read it; a disconnected session is not a listener, so everything committed during the outage is gone for it. Restarts and failovers lose signals too: the `pg_notify` directory is cleared at startup and is never WAL-logged, so a promoted standby has nothing, and `LISTEN` cannot even run on a hot standby. In PostgreSQL 18.6, as since 18.2 and 17.8, an error while a backend absorbs a notification closes the session with FATAL, another reason to treat every reconnect as a possible gap. The resync follows the `LISTEN` manual's rule: run `LISTEN price_changed` and commit, then in a new transaction reload or re-validate the cache, then rely on notifications. Invalidation must be idempotent, because the first notifications may repeat what the reload saw.

code

sql · 8 lines
sql
-- on every (re)connect, in autocommit mode
LISTEN price_changed;  -- committed: registration is live from here

-- a new transaction: rebuild the cache from the table
SELECT sku, amount, version FROM price;

-- per notification with payload 'SKU-1042': reread that row only
SELECT sku, amount, version FROM price WHERE sku = 'SKU-1042';

go deeper

for a junior

Know that notifications are never stored for a session that is not listening, so a reconnecting client has missed them.

for a middle

Explain the loss cases, disconnect, restart and failover, and the LISTEN, commit, then read sequence on reconnect.

for a senior

Design the resync: idempotent invalidation, a version check or full reload per reconnect, and no reliance on id order in change logs.

for a principal

Decide where a signal is enough and where events must be durable rows, and make that boundary explicit for every consumer.

## Signals, not stored messages PostgreSQL's notification queue exists to hand committed notifications to sessions that are **listening at that moment**. A session that is disconnected, or has not yet committed its `LISTEN`, is not a listener, and nothing is kept for it. There is no per-listener mailbox, no acknowledgement and no redelivery. A listener that comes back after an outage starts clean: it receives only notifications committed after its new `LISTEN` took effect. ## Every way a signal goes missing | Event | What is lost | Why | |---|---|---| | Listener disconnected | everything sent while it was away | an absent session is not a listener | | Server restart or crash | everything not yet read | the `pg_notify` directory is emptied at startup; it is never WAL-logged or fsynced | | Failover to a standby | everything not yet read | notifications are not in WAL, so the standby never had them; `LISTEN` cannot run on a hot standby | | Error while a backend absorbs a notification | whatever that session had not yet delivered | since 18.2 and 17.8 the session is closed with FATAL instead of silently skipping the message | | Queue full | the writer's whole transaction | the notifying commit fails, so the change and its signal both vanish | The last row is a different failure, the full-queue mechanism, but it belongs on the same list because the signal never arrives. The 18.2 and 17.8 change deserves a sentence of its own. Before it, a backend that hit an error while absorbing a notification advanced past that message and reported the error, so the client had no reliable way to know a notification had been lost. Closing the session turns the loss into a disconnect, which the reconnect rule below already handles. A connection pooler that hands the listener's server connection to other clients breaks listening in a similar way; pooling modes are a PgBouncer topic. ## The reconnect rule The `LISTEN` reference page spells out the only safe start-up order: 1. Run `LISTEN price_changed` and **commit** it; in autocommit mode it commits by itself. 2. In a **new** transaction, read the state the cache needs. 3. From then on, rely on notifications to learn about later changes. The registration becomes effective at an instant during the commit of the `LISTEN`, slightly later than any snapshot that transaction could have taken. Reading inside the same transaction leaves a window in which a change is neither in the read nor notified. Committing first closes that window. The price is that the first few notifications may describe changes the reload already saw, which is harmless when invalidation is idempotent. ## Designing pricing cache invalidation - **Payload = key.** Send the SKU, not the price; the listener rereads the `price` row for that key. - **Idempotent handling.** Dropping or reloading an entry twice is fine; duplicates are expected after every reconnect. - **Treat every reconnect as a gap.** On any connection loss, rerun the reconnect rule and then either flush the cache or compare a per-row `version` column with what the cache holds. - **Do not trust `id > last_seen` on a change-log table.** Identity and sequence values are assigned when a row is inserted, not when its transaction commits, so a lower id can become visible after a higher one was already read and be skipped forever. - **A backstop.** A cache TTL bounds the damage if some gap is still missed. ## When the signal must not be lost If a consumer must handle every event, the event has to live in a table and the notification only wakes the consumer up; work-queue tables, the outbox pattern and WAL-based change streams are separate topics. The point to take away here is that `NOTIFY` carries no durability of its own, so anything that needs durability must already be in a committed row before the signal is sent. ## What a strong answer sounds like The question tests whether a candidate treats `NOTIFY` like a broker. A strong answer: - says *none* without hedging; - names restart, crash and failover as loss cases, not only disconnects; - describes the LISTEN-commit-read sequence and explains the window it closes; - makes invalidation idempotent and treats the table, not the signal, as the source of truth. Weak answers promise that the server will replay notifications from WAL or keep them until the listener returns. PostgreSQL does neither.

  • In PostgreSQL, why must a listener commit LISTEN before reading the state it caches?
    `LISTEN` takes effect at an instant during its transaction's commit, later than any snapshot that transaction took. A read in the same transaction can miss a change that committed in between and was never notified. Committing first, then reading in a new transaction, closes that gap at the cost of some duplicate notifications.
  • Would reading a change-log table WHERE id > last_seen_id close every gap after a PostgreSQL reconnect?
    No. Identity values are handed out at insert time, not commit time, so a transaction holding id 101 can commit after id 102 is already visible and read. The reader moves past 101 and never sees it. Use a full reload, a per-row version comparison, or an overlap window instead.
  • Does keeping a LISTEN connection on a PostgreSQL streaming standby protect against failover?
    No. `LISTEN` is refused during recovery, and notifications are never written to WAL, so a standby has none to hand over. After promotion, listeners connect to the new primary, rerun `LISTEN` and resynchronise like after any other reconnect.

  • PostgreSQL keeps notifications for a listener until it reconnects and drains them.
  • Notifications are WAL-logged, so a promoted standby redelivers the pending ones.
  • Listeners can run LISTEN on a hot standby to offload the primary.
  • Reading the initial state in the same transaction as LISTEN closes every race.
  • A missed notification always shows up later as a duplicate, never as a gap.

Assumes PostgreSQL 18.6; closing the session with FATAL on an error while absorbing a notification arrived in the 18.2 and 17.8 minor releases.