Real-Time Video Metadata Cache Invalidation With Postgres LISTEN and NOTIFY

At TopVideoHub we aggregate trending video metadata across roughly fourteen Asia-Pacific regions, and the metadata is never still. A title's view count crosses a threshold and it needs a "trending" badge. An editor fixes a mangled Japanese title that our scraper truncated mid-kanji. A video gets region-blocked in Korea but stays visible in Vietnam. Every one of those events has to reach three separate caches within a couple of seconds: the LiteSpeed page cache in front of our PHP 8.4 app, a small in-process metadata map each web node keeps hot, and the local SQLite FTS5 search index (with our CJK tokenizer) that each node queries directly instead of round-tripping to the primary database.

For a long time we closed that gap with polling. A cron job ran every 30 seconds, selected rows where updated_at > last_run, and pushed invalidations. It worked, but it was the wrong shape for the problem. Thirty seconds of staleness is an eternity when a title is spiking; dropping the interval to 5 seconds meant every web node hammered the primary with the same SELECT ... WHERE updated_at > ? scan even when nothing had changed, which was most of the time. We were paying for constant reads to detect rare writes. This post is about how we replaced that with Postgres LISTEN/NOTIFY — a push-based, transaction-safe channel that turns a committed write into a message our listeners receive in single-digit milliseconds — and, just as importantly, the failure mode nobody warns you about. If you want to see the front end this feeds, it is live at TopVideoHub.

Why not Redis pub/sub or a message queue

The obvious objection: we already run infrastructure, why not Redis pub/sub or Kafka? The answer is transactional coupling. The event we care about is "a row in video_metadata was committed." If we publish to Redis from application code, we have two writes that can diverge: the database commit succeeds and the Redis publish fails (or vice versa), and now a cache is stale with no record that it should have been invalidated. You end up rebuilding a transactional outbox to fix it.

Postgres NOTIFY sidesteps this entirely because the notification is part of the transaction. A NOTIFY issued inside a transaction is only delivered if and when that transaction commits, and it is delivered exactly once to each listener that was connected at delivery time. Roll back, and the notification silently disappears with everything else. That single property — the message shares the fate of the data change — is what made it worth adopting. We don't run Postgres as our web-serving store (that's SQLite per node), but we do run it as the metadata system of record, so the pub/sub is free with the database we already trust for writes.

How LISTEN/NOTIFY actually works

The mechanism has three parts. A session issues LISTEN some_channel to subscribe. Any session can issue NOTIFY some_channel, 'payload' (or call pg_notify('some_channel', 'payload'), which is the function form you need when the payload is dynamic). Postgres then delivers the payload asynchronously to every listening session over its existing connection. There is no separate broker process, no port to open, no extra daemon — it rides the connections you already have.

Three constraints shape every design decision that follows:

• The payload is capped at 8000 bytes. Send an identifier and a revision number, not the row. Consumers re-read the authoritative data if they need more.
• Delivery requires an active connection. If a listener is disconnected when the NOTIFY fires, that notification is gone for good. There is no queue that holds it for you. This is the failure mode I'll come back to.
• The listening connection must be dedicated to listening. You cannot use a pooled connection that gets handed back mid-wait, and you shouldn't run heavy queries on it. It blocks, waiting for messages.

Emitting the notification from a trigger

We emit from a trigger rather than from PHP, so the notification is guaranteed to fire for every committed change no matter which code path (web app, admin tool, or a batch importer) made it. The trigger is deliberately narrow: it only fires on the columns that actually invalidate a cache, and it ships a compact JSON payload.

-- One channel, small JSON payloads. Consumers filter on video_id/region.
CREATE OR REPLACE FUNCTION notify_video_change() RETURNS trigger AS $$
DECLARE
  payload json;
BEGIN
  payload := json_build_object(
    'video_id', NEW.video_id,
    'region',   NEW.region,
    'op',       TG_OP,
    'rev',      NEW.revision   -- monotonically increasing per row
  );

  -- pg_notify() is the callable form of NOTIFY; it only delivers
  -- if this transaction commits. On ROLLBACK it vanishes with the row.
  PERFORM pg_notify('video_meta', payload::text);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_video_meta_change
AFTER INSERT OR UPDATE OF title, view_count, region_block, revision
ON video_metadata
FOR EACH ROW EXECUTE FUNCTION notify_video_change();

The revision column is the linchpin of the whole design. It's a per-row counter we bump on every meaningful write (a BEFORE UPDATE trigger sets NEW.revision = OLD.revision + 1). It gives every consumer a total order and a watermark to reconcile against, which we'll need the moment a listener reconnects.

One subtlety worth stating plainly: AFTER UPDATE OF title, view_count, ... means view-count churn from the ingestion pipeline will fire the trigger. That's intentional for us because view counts drive the trending badge. If you don't want high-frequency columns generating notifications, leave them off the trigger's column list — this is a cheap, per-column knob.

The relay: one Go listener, fan-out to nodes

We run a single small Go process — the relay — that holds the one dedicated LISTEN connection and fans each change out to the web nodes over HTTP. Centralizing the listen connection means Postgres sees exactly one listener regardless of how many web nodes we scale to, and the relay is the natural place to handle reconnection and reconciliation once, instead of in every node.

package main

import (
    "context"
    "encoding/json"
    "log"
    "time"

    "github.com/jackc/pgx/v5"
)

type change struct {
    VideoID string `json:"video_id"`
    Region  string `json:"region"`
    Op      string `json:"op"`
    Rev     int64  `json:"rev"`
}

func listen(ctx context.Context, dsn string) error {
    conn, err := pgx.Connect(ctx, dsn)
    if err != nil {
        return err
    }
    defer conn.Close(ctx)

    if _, err := conn.Exec(ctx, "LISTEN video_meta"); err != nil {
        return err
    }
    // Close the gap for anything committed while we were disconnected.
    if n := reconcile(ctx, conn); n > 0 {
        log.Printf("reconciled %d rows on connect", n)
    }
    log.Println("listening on video_meta")

    for {
        note, err := conn.WaitForNotification(ctx) // blocks; no polling
        if err != nil {
            return err // bubble up so the caller reconnects
        }
        var c change
        if err := json.Unmarshal([]byte(note.Payload), &c); err != nil {
            log.Printf("bad payload %q: %v", note.Payload, err)
            continue
        }
        fanout(c)
    }
}

func main() {
    dsn := "postgres://relay@db/tvh?sslmode=disable"
    for {
        if err := listen(context.Background(), dsn); err != nil {
            log.Printf("listener died: %v; reconnecting in 1s", err)
            time.Sleep(time.Second)
        }
    }
}

WaitForNotification is the whole point: it parks the goroutine on the socket and wakes only when a real message arrives. No busy loop, no polling interval, no wasted primary reads. When the connection dies — a database restart, a network blip, a failover — the function returns an error, listen returns, and main reconnects. Crucially, the very next thing the new connection does is reconcile, because everything committed during the outage was never delivered.

The per-node worker: purging LiteSpeed and re-syncing FTS5

Each web node runs a long-lived PHP CLI worker that receives the fan-out (in our setup the relay POSTs to a local endpoint that writes to a Unix socket the worker reads; you can also have each node LISTEN directly if your node count is small). The worker's job is the actual invalidation: drop the page cache entry, re-sync the SQLite FTS5 row so CJK search reflects the corrected title, and tell LiteSpeed to purge its tagged bucket.

<?php
declare(strict_types=1);

// invalidate-worker.php — one long-lived CLI process per web node.
$db = pg_connect('host=db dbname=tvh user=relay') ?: exit("pg_connect failed\n");
pg_query($db, 'LISTEN video_meta');

$sqlite = new SQLite3('/var/tvh/search.db');
$sqlite->busyTimeout(2000);

while (true) {
    $note = pg_get_notify($db, PGSQL_ASSOC);
    if ($note === false) {
        // Nothing pending: block on the socket instead of spinning the CPU.
        $sock = pg_socket($db);
        $r = [$sock]; $w = $e = [];
        stream_select($r, $w, $e, 30); // wake on data or 30s timeout
        continue;
    }

    $c = json_decode($note['payload'], true, flags: JSON_THROW_ON_ERROR);
    invalidate($c, $sqlite);
}

function invalidate(array $c, SQLite3 $sqlite): void
{
    $id = (string) $c['video_id'];

    // 1) Drop the PHP file page cache entry for this watch page.
    @unlink("/var/tvh/pagecache/watch_{$id}.html");

    // 2) Re-sync the FTS5 index so the CJK tokenizer re-indexes the new title.
    //    external-content FTS5: delete the old shadow row, re-insert current.
    $del = $sqlite->prepare('INSERT INTO video_fts(video_fts, rowid, title)
                             VALUES (\'delete\', :rid, :old)');
    // ... bind rowid + previous title, execute (omitted for brevity)
    $sqlite->exec("INSERT INTO video_fts(rowid, title)
                   SELECT rowid, title FROM videos WHERE video_id = '{$id}'");

    // 3) Purge the tagged LiteSpeed cache bucket for this video.
    //    Our watch pages emit: header('X-LiteSpeed-Tag: vid_' . $id);
    file_put_contents('/tmp/lscache_purge', "tag=vid_{$id}\n", FILE_APPEND);
}

A few things earn their keep here. stream_select on pg_socket() is what keeps this worker from being a busy loop — pg_get_notify is non-blocking, so without the select we'd pin a core spinning on "nothing yet." The 30-second timeout is a safety valve so the loop periodically wakes even if the socket layer misses a wakeup. And the LiteSpeed piece leans on tag-based purging: every watch page sends an X-LiteSpeed-Tag: vid_<id> header, so a single purge-by-tag drops exactly the pages containing that video without touching the rest of the cache. That is the difference between invalidating one page and cold-starting your entire cache during a traffic spike.

The FTS5 re-sync matters more than it looks for a CJK site. Because our tokenizer segments Japanese, Korean, and Chinese text into searchable units, a corrected title genuinely changes which queries match. Leaving the FTS5 shadow row stale means someone searching the corrected kanji still can't find the video — the page is fixed but search is lying. The invalidation has to hit search, not just the page cache.

The failure mode: notifications you never received

Here is the part that bites everyone eventually. NOTIFY has no durable queue. If your listener is disconnected — even for 800 milliseconds during a failover — every change committed in that window is delivered to nobody and is gone. Reconnecting does not replay them. If all you do is LISTEN again, you now have caches that are permanently stale for exactly the rows that changed during the blip, and no error anywhere to tell you.

This is why the revision column exists and why the Go relay calls reconcile immediately on every connect, before it processes a single live notification. Reconciliation is a watermark sweep: remember the highest revision you've successfully processed, and on reconnect, replay everything with a higher revision straight from the table.

# reconcile.py — run on every (re)connect, BEFORE processing live events.
# NOTIFY is fire-and-forget: anything committed while we were
# disconnected was delivered to nobody. Close the gap with a watermark.
import pathlib
import psycopg

WATERMARK = pathlib.Path("/var/tvh/last_rev")

def last_seen() -> int:
    return int(WATERMARK.read_text()) if WATERMARK.exists() else 0

def reconcile(conn, fanout) -> int:
    since = last_seen()
    with conn.cursor() as cur:
        cur.execute(
            "SELECT video_id, region, revision "
            "FROM video_metadata "
            "WHERE revision > %s ORDER BY revision",
            (since,),
        )
        rows = cur.fetchall()

    for video_id, region, rev in rows:
        fanout({"video_id": video_id, "region": region,
                "op": "SYNC", "rev": rev})

    if rows:
        WATERMARK.write_text(str(rows[-1][2]))  # advance the watermark
    return len(rows)

We also advance the watermark during steady-state processing (persisting the rev from each notification we finish handling), so a reconnect after hours of uptime only replays the handful of rows that changed during the actual outage — not the entire table. The reconciliation query hits an index on revision, so even a long outage is a cheap range scan.

The combination is what makes the system trustworthy: LISTEN/NOTIFY gives you low-latency delivery in the common case, and the watermark sweep gives you at-least-once semantics across disconnects. Neither alone is enough. Push without reconciliation loses data on every blip; reconciliation without push is just polling again. Together they are a push system with a poll-shaped safety net that only runs when something actually went wrong.

Operational notes and gotchas

A handful of things we learned running this in production across our regions:

• Keep payloads to identifiers. We ship video_id + revision and nothing else. The 8000-byte cap is real, and small payloads keep the internal notify queue from filling under a burst of writes. Consumers re-read the table when they need the full row.
• Watch pg_notification_queue_usage(). Postgres buffers pending notifications in a shared queue. A listener that stalls (a slow fanout, a wedged HTTP call) lets that queue grow, and a full queue blocks the committing transactions. We alert at 25% usage and keep the per-message handler fast, offloading anything slow to a worker pool.
• One dedicated connection per listener, always. Never LISTEN on a pooled connection. pgBouncer in transaction mode will break LISTEN outright — the connection you listened on isn't the one you get back. Give the relay its own direct connection.
• Cloudflare sits above LiteSpeed. For pages Cloudflare edge-caches, the local LiteSpeed purge isn't enough; the same invalidate() path also fires a Cloudflare cache-purge-by-tag API call for the handful of high-traffic watch pages we let the edge cache. Most pages are no-store at the edge and only cached at LiteSpeed, so this stays cheap.
• Test the reconnect path deliberately. Bugs here are invisible in normal operation and only surface as mystery stale data after a failover. We have a smoke test that kills the relay's connection mid-write and asserts the change still lands in every node's SQLite index within a second of reconnect.

Conclusion

Swapping polling for LISTEN/NOTIFY changed the economics of cache invalidation for us: we went from every web node scanning the primary every few seconds to detect rare writes, to a single relay that sleeps until a real commit wakes it. Median propagation from a committed metadata change to a purged LiteSpeed page and a re-synced FTS5 row dropped from tens of seconds to under 200 milliseconds, and the primary stopped absorbing constant detection reads.

The feature that makes it safe rather than merely fast is the transactional guarantee — a notification lives and dies with its transaction — paired with a revision watermark that turns "we were briefly disconnected" from silent data corruption into a bounded, self-healing catch-up. If you already run Postgres as a system of record and you're polling it to invalidate caches downstream, this is a small amount of code that removes a whole class of staleness. Just don't ship the happy path without the reconnect sweep; the notifications you never received are the ones that will hurt.