Real-Time Video Metadata Cache Invalidation with Postgres LISTEN NOTIFY

At TopVideoHub we aggregate trending videos across nine Asia-Pacific regions — Japan, Korea, Taiwan, Singapore, Vietnam, Thailand, Hong Kong and more — and every four hours an ingestion cron rewrites a chunk of our videos table. The problem is not writing that data. The problem is that the rest of the stack does not know the data changed. A watch page cached at the LiteSpeed edge, a Cloudflare-cached fragment, a PHP file cache, and an SQLite FTS5 search index with a CJK tokenizer all keep serving yesterday's title, yesterday's view count, and — worse — a video that was region-blocked in Korea an hour ago. When we localize a Japanese title or a channel gets pulled, we were leaving stale copies live for up to six hours. This is the story of how we killed most of that lag with Postgres LISTEN/NOTIFY, the free real-time pub/sub that ships inside the database that powers TopVideoHub.

Why short TTLs and polling both failed us

The obvious fix is "just lower the TTL." We tried. Dropping a six-hour watch-page TTL to fifteen minutes multiplied our origin PHP requests by roughly 24x on the long tail, and it still had a fifteen-minute lie window. For a site whose whole value is "what is trending right now in Seoul," a quarter-hour of wrongness on a region-block is unacceptable, and paying 24x origin cost to still be wrong is the worst of both worlds.

The second option is polling: a worker that runs SELECT video_id FROM videos WHERE updated_at > :last_seen every few seconds. This works, but it forces a trade-off between latency and load. Poll every second and you hammer the database with mostly-empty queries; poll every minute and you are back to a minute of staleness. Polling also needs a reliable updated_at high-water mark, which gets awkward with clock skew and with rows touched inside long transactions.

LISTEN/NOTIFY sidesteps both. The database itself tells you the instant a row changes, inside the same transaction that changed it, over a connection you already hold open. No extra polling load, sub-second latency, and the signal is emitted by a trigger so no application code path can forget to fire it.

How LISTEN/NOTIFY actually works

Three primitives:

• LISTEN channel — the current session subscribes to a named channel.
• NOTIFY channel, 'payload' (or the function pg_notify(channel, payload)) — sends a text payload to every session listening on that channel.
• The client library exposes an async socket you can select() on, so a listener process sleeps until a notification arrives.

A few properties that matter in production:

• Notifications are transactional. A NOTIFY issued inside a transaction is only delivered when that transaction commits. If it rolls back, no one hears it. That is exactly the guarantee you want for cache invalidation — you never purge a cache for a write that did not land.
• Delivery is at-most-once and only to currently-connected listeners. If your listener is down when the notify fires, that message is gone. So LISTEN/NOTIFY is a fast path, not a durable queue. You still need a periodic reconciliation sweep as a backstop.
• The payload is capped at 8000 bytes. Send identifiers, not documents.
• Duplicate notifications with identical channel+payload inside one transaction are collapsed. Handy, but do not rely on it across transactions.

Why not Redis pub/sub or a message broker?

The honest answer is scope. We already run Postgres for the canonical videos table. Adding Redis pub/sub means a second daemon to deploy, monitor, secure and keep alive across the fleet, plus the application now has to publish to Redis and write to Postgres in the same logical operation — a dual-write with no shared transaction, so a crash between the two leaves cache and database disagreeing. Kafka is even more machinery for what is fundamentally "tell me when a row changed." With a trigger-driven NOTIFY, the publish is inside the database transaction. There is exactly one write path, one thing to keep alive, and no dual-write consistency hole. The ceiling is real — NOTIFY runs through a single shared queue and is not the tool for millions of messages a second — but for cache invalidation on a metadata table that changes a few thousand rows every few hours, it is comfortably within budget, and it is free.

Designing the payload

Because of the 8KB cap and the at-most-once nature, the payload should be a pointer to what changed, not the changed data itself. The listener re-reads the canonical row from Postgres and pushes it wherever it needs to go. Our payload is a small JSON object:

• op — INSERT / UPDATE / DELETE, so a delete can drop the row from the search index.
• video_id — the natural key.
• region and lang — so we can scope which regional pages and which CJK-language search shards to invalidate without a second query.
• changed_at — an epoch timestamp for debounce and lag metrics.

Emitting the signal from a trigger

The cleanest place to fire the notification is a trigger, so every writer — the cron, an admin edit, a manual SQL fix — emits it automatically. We only fire on the columns that actually affect rendered output, which keeps notification volume down:

CREATE OR REPLACE FUNCTION notify_video_change() RETURNS trigger AS $$
DECLARE
  payload json;
BEGIN
  payload := json_build_object(
    'op', TG_OP,
    'video_id', COALESCE(NEW.video_id, OLD.video_id),
    'region', COALESCE(NEW.region, OLD.region),
    'lang', COALESCE(NEW.lang, OLD.lang),
    'changed_at', extract(epoch from now())
  );
  PERFORM pg_notify('video_metadata', payload::text);
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER video_metadata_change
AFTER INSERT OR UPDATE OF title, view_count, region_blocked, lang
OR DELETE ON videos
FOR EACH ROW EXECUTE FUNCTION notify_video_change();

Firing AFTER (not BEFORE) matters: you want the notify tied to the committed row. Scoping UPDATE OF title, view_count, region_blocked, lang means a bulk update that only touches, say, an internal last_checked_at column does not wake every listener on the planet.

A resilient PHP listener

Our worker fleet is PHP 8.4, so the listener is PHP too. The subtlety is that you must not busy-loop: you stream_select() on the connection's socket and sleep until Postgres wakes you. The other subtlety is reconnection — a database restart or a network blip silently kills your subscription, and a listener that does not notice will go quiet forever.

<?php
// listener.php — run supervised (systemd / supervisord)
$conn = pg_connect("host=127.0.0.1 dbname=videos user=cache_worker");
if (!$conn) {
    fwrite(STDERR, 'connect failed' . PHP_EOL);
    exit(1);
}

pg_query($conn, "LISTEN video_metadata");
$sock = pg_socket($conn); // stream resource usable with stream_select()

while (true) {
    // Block up to 30s; the wakeup doubles as a heartbeat.
    $read = [$sock];
    $write = $except = [];
    if (@stream_select($read, $write, $except, 30) === false) {
        continue;
    }

    pg_consume_input($conn);
    while ($note = pg_get_notify($conn, PGSQL_ASSOC)) {
        $payload = json_decode($note['payload'], true);
        if (!is_array($payload)) {
            continue;
        }
        handleInvalidation($payload);
    }

    // Detect a dropped backend and re-subscribe.
    if (pg_connection_status($conn) !== PGSQL_CONNECTION_OK) {
        pg_close($conn);
        $conn = reconnectWithBackoff();     // sleeps 1s,2s,4s... capped
        pg_query($conn, "LISTEN video_metadata");
        $sock = pg_socket($conn);
    }
}

The stream_select($read, $write, $except, 30) call blocks for up to thirty seconds. When a notification arrives, the socket becomes readable and we drain every pending message with pg_get_notify(). Each loop we also check pg_connection_status() and, if the backend died, reconnect with exponential backoff and re-issue LISTEN. Without that re-LISTEN after reconnect you get the nastiest failure mode there is — a process that looks healthy, holds a connection, and receives nothing.

Debouncing notification storms

Our four-hourly cron can update several thousand rows in a couple of minutes. If each row triggers an immediate CDN purge, we would fire thousands of Cloudflare API calls and blow through rate limits. The fix is a small debounce: collect changed IDs for a short window, then flush one batched purge. A dedicated Go worker handles this cleanly because Go's select over channels makes the collect-then-flush loop trivial:

package main

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

    "github.com/lib/pq"
)

type change struct {
    VideoID string `json:"video_id"`
    Region  string `json:"region"`
    Lang    string `json:"lang"`
}

func main() {
    const dsn = "postgres://cache_worker@127.0.0.1/videos?sslmode=disable"
    l := pq.NewListener(dsn, 10*time.Second, time.Minute, func(_ pq.ListenerEventType, err error) {
        if err != nil {
            log.Printf("listener event: %v", err)
        }
    })
    if err := l.Listen("video_metadata"); err != nil {
        log.Fatal(err)
    }

    // Collect IDs for 250ms, de-dup through a map, flush one batch.
    pending := map[string]struct{}{}
    tick := time.NewTicker(250 * time.Millisecond)
    for {
        select {
        case n := <-l.Notify:
            if n == nil { // reconnect signalled
                continue
            }
            var c change
            if json.Unmarshal([]byte(n.Extra), &c) == nil {
                pending[c.VideoID] = struct{}{}
            }
        case <-tick.C:
            if len(pending) == 0 {
                continue
            }
            ids := make([]string, 0, len(pending))
            for id := range pending {
                ids = append(ids, id)
            }
            pending = map[string]struct{}{}
            flushPurge(ids) // batched cache + CDN purge
        }
    }
}

Collecting for 250ms and de-duplicating through a map turns a burst of 3,000 row updates into a handful of batched purges. During a normal cron run this cut our outbound purge calls by well over 90%.

Wiring invalidation into SQLite FTS5, LiteSpeed and Cloudflare

The last mile is the handler that actually invalidates things. It re-reads the canonical row from Postgres, updates the SQLite FTS5 index (the one with our CJK tokenizer that makes Japanese and Chinese search work), drops the PHP file page cache, and purges both the LiteSpeed public cache and the Cloudflare edge. FTS5 has no UPSERT, so the correct pattern is delete-then-insert:

<?php
function handleInvalidation(array $p): void
{
    $videoId = (string) $p['video_id'];

    $sqlite = new PDO('sqlite:/var/www/data/search.db');
    $sqlite->exec('PRAGMA busy_timeout = 3000'); // live readers share this file

    // FTS5 has no UPSERT: delete then (re)insert.
    $sqlite->prepare('DELETE FROM videos_fts WHERE video_id = ?')
           ->execute([$videoId]);

    $row = ($p['op'] === 'DELETE') ? null : fetchVideoFromPostgres($videoId);
    if ($row !== null) {
        $ins = $sqlite->prepare(
            'INSERT INTO videos_fts(video_id, title, channel, lang)
             VALUES(:id, :title, :channel, :lang)'
        );
        $ins->execute([
            ':id' => $videoId,
            ':title' => $row['title'],
            ':channel' => $row['channel'],
            ':lang' => $row['lang'],
        ]);
    }

    // Drop the PHP file page cache for the watch page.
    @unlink('/var/www/data/pagecache/watch_' . md5($videoId) . '.html');

    // Purge LiteSpeed by cache tag, and Cloudflare by URL.
    purgeLiteSpeed('video:' . $videoId);
    purgeCloudflare(['https://topvideohub.com/watch/' . $videoId]);
}

A few production notes baked into that code:

• PRAGMA busy_timeout = 3000 because the same SQLite file is being read by live request handlers; without it you get SQLITE_BUSY under load.
• We purge by cache tag (video:<id>) on LiteSpeed rather than by URL, so every fragment that referenced the video — the watch page, the channel page, the trending row — dies together.
• The Cloudflare purge is URL-scoped and batched by the Go debouncer above; the free plan allows purge-by-URL, which is all we need.

Transactional gotchas we hit

• Do not NOTIFY before the write. If you notify and then the transaction rolls back, the listener purges a cache and re-reads the old row, effectively re-caching stale data. AFTER triggers on commit avoid this.
• LISTEN/NOTIFY is not durable. We run a lightweight reconciliation every ten minutes: SELECT video_id FROM videos WHERE updated_at > now() - interval '15 minutes' and re-invalidate. It catches anything a disconnected listener missed. Fast path for latency, slow path for correctness.
• Connection-pooler transaction mode breaks it. Behind PgBouncer in transaction pooling mode your LISTEN connection gets recycled and you stop receiving. The listener must hold a dedicated session connection (session pooling or a direct connection), separate from your request-path pool.
• One channel per concern, not per row. We use a single video_metadata channel and put the id in the payload. Creating a channel per video would be unmanageable and gains nothing.

What we measured

After rolling this out across the fleet:

• Metadata-change-to-live-update latency dropped from up to six hours (worst-case TTL) to a p95 of about 900 milliseconds, most of which is the Cloudflare purge round-trip.
• Origin PHP request volume did not increase, unlike the short-TTL experiment — we invalidate precisely instead of expiring everything on a timer.
• Region-block propagation, our most sensitive case, went from "eventually" to effectively immediate, which matters for compliance in several APAC markets.

Conclusion

LISTEN/NOTIFY is one of those Postgres features that quietly replaces a whole category of infrastructure — you do not need Redis pub/sub, a message broker, or a polling cron just to know a row changed. Fire a notification from an AFTER trigger, hold one dedicated listener connection per worker, debounce the bursts, and treat it as a fast path in front of a periodic reconciliation sweep for durability. For a read-heavy, edge-cached, multi-region site the payoff is large: sub-second invalidation without paying the origin-load tax of short TTLs. It has been running in front of the CJK search and regional caches on our fleet for months, and the biggest compliment I can give it is that I have stopped thinking about cache staleness at all.