How One Forgotten psql Session Filled Postgres' NOTIFY Queue and Stalled Every Checkout Write

03:47 UTC. PagerDuty fires on checkout-api: write latency on POST /orders has gone from a steady 40ms p99 to over 4 seconds, and climbing. Nothing in the deploy log for the last nine hours. No traffic spike, Saturday night in most of the timezones that matter. Whatever is wrong is already in the database, waiting for someone to notice it.

the scramble

First theory: a bad deploy. There wasn't one. Second theory: autovacuum running a long table scan and starving I/O. pg_stat_activity shows one autovacuum worker, on a small lookup table, nowhere near the orders path. Third theory: a lock. Someone runs the standard blocking-lock query against the orders and inventory tables:

  pid  | wait_event_type |     wait_event      |            query
-------+------------------+----------------------+------------------------------
 48221 | Lock             | transactionid        | UPDATE inventory SET ...
 48230 | Lock             | transactionid        | INSERT INTO orders ...
 48244 | Client           | ClientRead           | LISTEN inventory_cache
 48190 | Lock             | transactionid        | UPDATE inventory SET ...

Dozens of backends stuck on wait_event_type = Lock, specifically transactionid, all running ordinary single-row updates against inventory. Nothing here looks like a deadlock between two of our own transactions, there's no cycle, just everyone waiting on something that never seems to release. The row-level locks resolve instantly when checked individually. The queries aren't fighting each other. They're fighting something else.

the hunt

The inventory table has an AFTER UPDATE trigger that's been running in production for over a year, part of a cache-invalidation setup that predates anyone currently on call. Every time a product's stock count changes, the trigger fires pg_notify inside the same transaction as the update, so app servers holding an in-memory inventory cache can invalidate the row the moment it commits, without a separate cache-busting round trip:

CREATE OR REPLACE FUNCTION notify_inventory_change() RETURNS trigger AS $$
BEGIN
  PERFORM pg_notify('inventory_cache', json_build_object(
    'sku', NEW.sku, 'stock', NEW.stock
  )::text);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER inventory_notify AFTER UPDATE ON inventory
  FOR EACH ROW EXECUTE FUNCTION notify_inventory_change();

That's the first real lead: every write to inventory, including the ones on the checkout path, now routes through pg_notify. Postgres exposes a function for exactly this situation, and it's the next thing anyone checks:

 pg_notification_queue_usage
------------------------------
                       0.9814

The shared NOTIFY queue is 98% full. Postgres keeps one queue per database, not one per channel, and every backend calling NOTIFY or pg_notify writes into the same bounded structure. A message only gets trimmed from that queue once every session that has ever run LISTEN has acknowledged it, which happens automatically the next time that session isn't inside a transaction. If one listening session stops checking in, the oldest unacknowledged message can't be removed, and nothing behind it in the queue can be removed either. The queue doesn't fail gracefully once that happens. Backends calling pg_notify start blocking, waiting for space, which is exactly the lock wait the first query showed, just not the kind of lock anyone was expecting to chase.

pg_stat_activity had already shown the culprit, it just didn't look like one at first: pid 48244, state idle in transaction, last query LISTEN inventory_cache, backend_start six hours earlier. Someone had opened a psql session on a bastion host that afternoon to eyeball the cache-invalidation channel while debugging an unrelated ticket, run BEGIN; out of habit before LISTEN, confirmed the messages were flowing, and closed the laptop lid without ever running COMMIT or \q. The SSH connection to the bastion stayed alive. The transaction never ended. Postgres had been faithfully holding a place in the queue for a session that was never coming back to read from it.

the find

A debug session left LISTEN'd inside an open transaction prevented the shared NOTIFY queue from ever trimming, because Postgres can't discard a message until every listening session has acknowledged it, and acknowledgment only happens between transactions. Six hours of normal inventory write volume, maybe a few thousand notifications, filled a queue that's supposed to drain itself continuously. Once it was full, every backend in the database calling pg_notify started blocking on queue space, and because the inventory trigger fires inside the same transaction as the write itself, that meant every checkout order that touched inventory stock was blocking on a connection nobody remembered opening.

the fix

The immediate fix is one line, terminate the offending backend and let the queue drain:

 pg_terminate_backend
-----------------------
 t

Latency on POST /orders dropped back under 50ms within about ninety seconds, roughly the time it took the backlog of blocked writes to clear once new pg_notify calls stopped waiting on queue space. The real fix is making sure a forgotten terminal can't do this again:

# kill any session sitting idle inside an open transaction
idle_in_transaction_session_timeout = '60s'

With that set, a session that runs BEGIN; LISTEN channel; and then goes quiet for a minute gets terminated automatically, whether it's a debug session, a stuck application connection, or a connection pooler that failed to reset state between checkouts. The setting is cluster-wide and safe for this workload, nothing in the application holds a transaction open intentionally for more than a few hundred milliseconds.

A second change moved the actual cache invalidation off the hot write path. The trigger still fires pg_notify, but a lightweight consumer now re-emits invalidations through Redis pub/sub for the app servers to subscribe to, so a slow or disconnected cache listener downstream can never again apply back-pressure to the table that processes orders:

import select
import psycopg2

conn = psycopg2.connect(DATABASE_URL)
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
cur = conn.cursor()
cur.execute("LISTEN inventory_cache;")

while True:
    if select.select([conn], [], [], 5) == ([], [], []):
        continue
    conn.poll()
    while conn.notifies:
        note = conn.notifies.pop(0)
        redis_client.publish("inventory_cache", note.payload)

That consumer runs under a supervisor that restarts it if it ever dies, and it's the only process allowed to hold a long-lived LISTEN against that channel. Anyone debugging the channel by hand now does it against Redis, where an idle subscriber costs nothing and can't block a write anywhere.

the aftermath

No orders were lost and no data was corrupted, every blocked write eventually completed once the queue drained, but checkout write latency stayed above 1 second for roughly 35 minutes before the terminated backend was identified, long enough that a handful of customers on slower connections saw their checkout spinner time out and had to retry.

idle_in_transaction_session_timeout existed in this cluster's config file the whole time, just never set. The queue didn't fail because the feature was obscure. It failed because a timeout that should have been a default from day one was left to the honor system, and the honor system doesn't account for someone closing a laptop lid mid-debug on a Saturday afternoon.