Postgres LISTEN and NOTIFY for Real-Time Video Metadata Invalidation

An editor renames a trending video in our admin panel, swaps the thumbnail, and fixes a typo in the description. Thirty minutes later they ping me: the old title is still showing on the watch page, the category listing, and three CDN edges. Everything looked correct in the database. The problem was every layer between Postgres and the browser — a PHP page cache, a LiteSpeed public cache, a Cloudflare edge cache, and a SQLite FTS5 search mirror — was happily serving a snapshot from before the edit. On DailyWatch we run a free video discovery platform where metadata changes constantly (titles get corrected, videos get flagged, view counts roll over), and stale reads are the single most common support complaint we get. This post is how we killed the staleness window using Postgres LISTEN/NOTIFY instead of the polling loop we started with.

Why Polling Was the Wrong Answer

Our first fix was the obvious one: a cron job that ran every 60 seconds, selected every row in video_metadata where updated_at > last_run, and purged the relevant caches. It worked, sort of. But it had three problems that got worse as the catalog grew past a few hundred thousand videos.

First, latency. A 60-second poll means the average stale window is 30 seconds and the worst case is a full minute. For a video that just got featured on the home page, a minute of wrong metadata is a lot of impressions.

Second, waste. Ninety-nine percent of poll cycles found zero changes. We were hammering the primary with a WHERE updated_at > ? query that had to scan a growing index on every tick, across multiple worker nodes, forever, mostly to learn that nothing happened.

Third, the tightening trap. The natural instinct is to lower the interval to 10 seconds, then 5, then 1. Each step multiplies the query load while the stale window only shrinks linearly. You are paying exponentially more to asymptotically approach a problem you never actually solve.

What we actually wanted was the database telling us the instant something changed, not us asking it repeatedly. That is exactly what LISTEN/NOTIFY provides, and it has shipped in Postgres for well over a decade.

How LISTEN/NOTIFY Actually Works

NOTIFY sends a named message, optionally with a text payload, to any session that has run LISTEN on that channel name. The delivery is asynchronous and pushed over the existing connection — no polling, no new query per check. A session parks on the socket and Postgres wakes it when a notification arrives.

The part that makes it genuinely useful for cache invalidation is that notifications are transactional. If you call pg_notify() inside a transaction and that transaction rolls back, the notification is never sent. If it commits, listeners see the notification after the commit is visible. You will never get told a row changed and then read the old value — the ordering guarantee is real and it is the reason this beats an application-level message bus for database-derived events.

The channel name and payload are both plain text. The payload has a hard limit of 8000 bytes, which shapes the whole design: you send identifiers, not row contents. Listeners react to "video 48213 changed" by re-reading what they need, they do not try to receive the new state in the message itself.

The critical caveat, which I will come back to, is that NOTIFY is fire-and-forget. There is no durable queue. If your listener is disconnected when a notification fires, it is gone. That single fact drives the reconciliation design at the end of this post — treat LISTEN/NOTIFY as a low-latency hint, not a system of record.

Emitting Notifications From a Trigger

We do not want application code to remember to call pg_notify on every write path — that is exactly the kind of thing someone forgets in a hotfix. Instead we put the emission in a trigger, so any write to video_metadata, from anywhere, produces exactly one notification.

CREATE OR REPLACE FUNCTION notify_video_metadata_changed()
RETURNS trigger AS $$
DECLARE
  affected_id bigint;
  op text := lower(TG_OP);
BEGIN
  -- On DELETE the new row is null, so fall back to OLD.
  affected_id := COALESCE(NEW.id, OLD.id);

  -- Payload is a compact JSON string: id, operation, and the
  -- category so a listener can decide which listings to purge
  -- without an extra round trip.
  PERFORM pg_notify(
    'video_metadata_changed',
    json_build_object(
      'id',       affected_id,
      'op',       op,
      'category', COALESCE(NEW.category_slug, OLD.category_slug),
      'ts',       extract(epoch from clock_timestamp())
    )::text
  );

  RETURN NULL; -- AFTER trigger, return value is ignored
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_video_metadata_changed
AFTER INSERT OR UPDATE OR DELETE ON video_metadata
FOR EACH ROW
EXECUTE FUNCTION notify_video_metadata_changed();

Two deliberate choices here. It is an AFTER trigger, so the notification only queues once the row change is real; combined with the transactional delivery guarantee, listeners never see a phantom change. And we keep the payload to a handful of scalar fields — id, operation, category, timestamp — which is nowhere near the 8000-byte ceiling even for a burst of edits. If you ever find yourself tempted to stuff the whole row in there, stop: send the id and let the listener read the row.

One genuine gotcha with the trigger approach: a bulk UPDATE touching 50,000 rows fires 50,000 notifications, one per row. That is usually fine for our workload because real edits are one row at a time, but for batch jobs (say, a nightly view-count recalc) we disable the trigger inside the batch transaction and emit a single pg_notify('video_metadata_bulk', '...') at the end, which the listener treats as "invalidate everything."

The PHP Listener Daemon

Our main app is PHP 8.4, so the listener is a long-running PHP CLI process supervised by systemd. This is not a request handler — it is a daemon that holds one dedicated Postgres connection open and blocks on notifications. The one thing you must not do is run this through PgBouncer in transaction-pooling mode; LISTEN registrations are tied to a backend connection, and transaction pooling will silently hand your LISTEN to a connection you never see again. Connect directly, or use session pooling.

<?php
declare(strict_types=1);

// Dedicated, direct connection. No PgBouncer transaction pooling here.
$dsn  = 'host=127.0.0.1 port=5432 dbname=dailywatch user=cacheworker';
$conn = pg_connect($dsn, PGSQL_CONNECT_FORCE_NEW);
if ($conn === false) {
    fwrite(STDERR, "cannot connect to postgres\n");
    exit(1);
}

pg_query($conn, 'LISTEN video_metadata_changed');
pg_query($conn, 'LISTEN video_metadata_bulk');

$debounce = [];              // id => last-seen epoch, for coalescing bursts
$flushAfter = 0.25;          // seconds to batch rapid edits to one row

while (true) {
    // Block on the socket instead of busy-looping. pg_socket()
    // exposes the underlying fd so we can select() on it.
    $sock = pg_socket($conn);
    $read = [$sock];
    $write = $except = [];
    // 1s timeout so we periodically wake to flush the debounce buffer.
    stream_select($read, $write, $except, 1);

    while (($note = pg_get_notify($conn, PGSQL_ASSOC)) !== false) {
        if ($note['message'] === 'video_metadata_bulk') {
            purge_everything();
            $debounce = [];
            continue;
        }
        $payload = json_decode($note['payload'], true);
        if (is_array($payload) && isset($payload['id'])) {
            $debounce[(int) $payload['id']] = microtime(true);
        }
    }

    // Flush any ids that have been quiet for $flushAfter seconds.
    $now = microtime(true);
    foreach ($debounce as $id => $seenAt) {
        if (($now - $seenAt) >= $flushAfter) {
            invalidate_video($id);
            unset($debounce[$id]);
        }
    }

    // Detect a dropped connection and let systemd restart us.
    if (pg_connection_status($conn) !== PGSQL_CONNECTION_OK) {
        fwrite(STDERR, "connection lost, exiting for restart\n");
        exit(1);
    }
}

function invalidate_video(int $id): void {
    // 1) Drop the PHP file page cache for this watch page.
    @unlink("/var/cache/dailywatch/watch_{$id}.html");
    // 2) Purge the LiteSpeed public cache tag for this video.
    litespeed_purge_tag("video_{$id}");
    // 3) Rebuild the SQLite FTS5 row so search reflects the new title.
    rebuild_fts_row($id);
    // 4) Purge the Cloudflare edge by URL.
    cloudflare_purge(["https://dailywatch.video/watch/{$id}"]);
    error_log("invalidated video {$id}");
}

The stream_select call is what turns this from a CPU-burning loop into a process that sits at essentially zero load until something happens. We give it a one-second timeout not because we are polling the database — Postgres pushes to us — but so the loop wakes periodically to flush the debounce buffer even during a quiet spell.

Debouncing Edit Bursts

The debounce buffer solves a real problem we hit in production. An editor fixing a video does not make one change; they fix the title, tab to the thumbnail, tab to the description, and hit save on each. That is three UPDATEs in two seconds, three notifications, and naively three full invalidation cycles — three Cloudflare API calls, three FTS rebuilds — for what the user experiences as one edit.

By keeping a map of id => last-seen time and only firing the invalidation once an id has been quiet for 250 ms, we coalesce that burst into a single cycle. Cloudflare's purge API in particular is rate-limited, so collapsing redundant purges is not just tidy, it keeps us under the ceiling during busy editorial windows. The trade-off is a deliberate 250 ms of extra latency, which is invisible to users and well worth it.

Handling Reconnects and Missed Notifications

Here is the failure mode that will bite you if you treat LISTEN/NOTIFY as a durable queue. Your listener process restarts for a deploy. During the two seconds it is down, an editor saves a change. That notification fired into a channel nobody was listening on. It is gone. The cache for that video is now stale forever, because nothing will ever tell the listener to purge it.

The fix is a reconciliation sweep that runs on every listener startup and periodically thereafter. It does the boring, reliable thing polling did — compares updated_at against a high-water mark — but only as a safety net on process boundaries, not as the primary mechanism. This gives you the best of both: sub-second latency from NOTIFY in the common case, and guaranteed eventual consistency across restarts and network blips.

#!/usr/bin/env python3
"""Reconciliation sweep. Runs once at listener startup and every
5 minutes as a cron safety net. Catches anything NOTIFY dropped
while the listener was disconnected."""
import psycopg
from pathlib import Path

HIGH_WATER = Path("/var/lib/dailywatch/reconcile_hwm")

def last_mark() -> float:
    try:
        return float(HIGH_WATER.read_text().strip())
    except (FileNotFoundError, ValueError):
        return 0.0

def reconcile(conn) -> int:
    since = last_mark()
    with conn.cursor() as cur:
        cur.execute(
            """SELECT id, extract(epoch from updated_at) AS ts
                 FROM video_metadata
                WHERE extract(epoch from updated_at) > %s
                ORDER BY updated_at""",
            (since,),
        )
        rows = cur.fetchall()

    newest = since
    for vid, ts in rows:
        invalidate_video(vid)      # same purge path as the listener
        newest = max(newest, ts)

    if newest > since:
        HIGH_WATER.write_text(str(newest))
    return len(rows)

if __name__ == "__main__":
    with psycopg.connect("host=127.0.0.1 dbname=dailywatch user=cacheworker") as conn:
        n = reconcile(conn)
        print(f"reconciled {n} video(s) missed since last mark")

The high-water mark is a single float on disk. On a clean run where the listener never dropped anything, the sweep finds zero rows and costs one indexed range query every five minutes — orders of magnitude cheaper than the per-second polling we started with, because it is a backstop rather than the delivery path. Make sure video_metadata(updated_at) is indexed or this sweep degrades into a full scan as the catalog grows.

Scaling the Listener With Go

PHP holds up fine at our volume, but if you are pushing tens of thousands of notifications a minute — or you want the listener to be a tiny static binary with no runtime — a Go listener using lib/pq's pq.Listener is a clean fit. It handles reconnect-with-backoff for you and exposes a channel you range over, which maps naturally onto a worker pool for the actual purge fan-out.

package main

import (
    "database/sql"
    "encoding/json"
    "log"
    "time"

    "github.com/lib/pq"
)

type change struct {
    ID       int64   `json:"id"`
    Op       string  `json:"op"`
    Category string  `json:"category"`
    Ts       float64 `json:"ts"`
}

func main() {
    connStr := "host=127.0.0.1 dbname=dailywatch user=cacheworker sslmode=disable"

    // pq.Listener reconnects automatically with backoff and reports
    // connection state changes through the callback.
    listener := pq.NewListener(connStr, 2*time.Second, time.Minute,
        func(ev pq.ListenerEventType, err error) {
            if err != nil {
                log.Printf("listener event %d: %v", ev, err)
            }
            // On reconnect, kick a reconciliation sweep to catch
            // anything dropped while we were disconnected.
            if ev == pq.ListenerEventReconnected {
                go reconcile(connStr)
            }
        })

    if err := listener.Listen("video_metadata_changed"); err != nil {
        log.Fatal(err)
    }
    log.Println("listening for video metadata changes")

    for n := range listener.Notify {
        if n == nil { // reconnect signal, no payload
            continue
        }
        var c change
        if err := json.Unmarshal([]byte(n.Extra), &c); err != nil {
            log.Printf("bad payload %q: %v", n.Extra, err)
            continue
        }
        invalidateVideo(c.ID) // fan out to your worker pool here
    }
}

func invalidateVideo(id int64) { /* purge caches, rebuild FTS row */ }
func reconcile(connStr string) { _ = sql.Drivers /* startup sweep */ }

The key line is the ListenerEventReconnected handler kicking a reconciliation sweep. pq.Listener transparently reconnects after a network drop, but it cannot replay notifications that fired while it was gone — so a reconnect is precisely the moment you must reconcile. Wire that in and the durability gap closes itself.

The Gotchas Worth Repeating

A few things I wish someone had put in bold before I started:

• No delivery guarantee. Missed while disconnected means gone. The reconciliation sweep is not optional; it is the thing that makes the whole design correct rather than merely fast.
• PgBouncer transaction pooling breaks LISTEN. Your registration lands on a connection the pooler recycles. Use a direct connection or session pooling for the listener only.
• 8000-byte payload cap. Send ids, not rows. This also keeps you honest — the listener re-reads current state, so it can never act on a stale in-flight payload.
• Duplicate and out-of-order notifications are possible. Make invalidate_video idempotent. Purging a cache twice is harmless; that property is what lets you stop worrying about exactly-once delivery.
• Bulk writes fan out per row. Disable the trigger inside batch transactions and emit one summary notification instead.

Conclusion

Swapping a per-second polling loop for LISTEN/NOTIFY took our metadata staleness window from up to a minute down to roughly 300 milliseconds — the debounce delay — while cutting the load on the primary to a five-minute reconciliation query that almost always returns zero rows. The mental model that made it click was refusing to treat NOTIFY as a message queue. It is a low-latency hint that says "go look, something changed," backed by a cheap reconciliation sweep that guarantees correctness across the failures NOTIFY cannot survive. If you have a caching layer sitting on top of Postgres and you are still polling for changes, this is one of the highest-leverage changes you can make: less database load, dramatically fresher reads, and a design that degrades gracefully instead of silently going stale.