A video sitting in the number-three slot of our German trending row was pulled by its uploader at 09:12 UTC. Our German edge node kept serving the thumbnail, the title, the description and a fully rendered watch page for it until 16:04 UTC — six hours and fifty-two minutes of a page whose embed was a grey box. The node was not broken. It was doing exactly what it was built to do: refresh metadata on a seven-hour cron and trust its local database until then. That is the freshness model behind the eight regional edges I run for TrendVidStream, and for two years it was fine, right up until it obviously wasn't.
What replaced it: Postgres LISTEN/NOTIFY fired from the write path, a listener that assumes it will miss messages, and signed HTTPS pushes to eight PHP 8.4 nodes that cannot run a daemon of any kind. Median propagation went from "up to seven hours" to 1.4 seconds. The cron schedule did not change.
Additions can wait, deletions cannot
The first thing worth naming is that staleness is not symmetric, and treating it as one number is why the problem sat unfixed for so long.
- A newly trending video that shows up four hours late costs us a slice of traffic we never see. Annoying, not urgent.
- A video that has been deleted upstream and is still in our index costs a click, a bounce, and a watch page that renders a dead player. Users do not file bug reports about this; they leave.
- A geo-block change is worse than a deletion, because the row is still legitimately alive in six regions and must vanish from two. A global "refresh everything" does not express that.
- SQLite FTS5 is the part that really stings. The row stays in the index, so it keeps ranking in search results, which means our own search actively promotes dead content until the next rebuild.
So the requirement was never "make everything fresher." It was "make removals propagate in seconds while leaving the additions path exactly as it is."
Why we did not just shorten the cron
The obvious move is to run the regional fetchers more often. We priced it out and it does not work.
- Quota. A full eight-region refresh burns a meaningful chunk of the daily YouTube Data API budget. Going from every seven hours to every fifteen minutes multiplies that by 28. There is no version of that which fits.
- The hosts. Edge nodes are shared LiteSpeed accounts with a 180-second PHP-CLI ceiling and 512 MB. A refresh already runs close to the limit; it cannot run every few minutes and also serve traffic.
-
Deploys are FTP. New code and rebuilt database files go out over
lftpin a few minutes. That is fine as a build pipeline and useless as an invalidation channel. - Polling has the wrong shape. To get a p99 of sixty seconds by polling, every node polls every sixty seconds forever, and 99.9% of those polls find nothing. Meanwhile the one system that knows the exact moment a row died — the ingest pipeline that wrote the row — is sitting there with the information and no way to say it.
That last point is the whole design. We needed push, and the push had to originate inside the transaction that caused the change.
Postgres writes, SQLite reads, nothing in between
The topology is deliberately boring. A single Postgres 16 instance on a box I control is the only writer: crawlers, availability probes, and the region-policy engine all commit there. A build step compacts the relevant slice into a per-region SQLite file with an FTS5 external-content index, and ops.sh ships it over FTP. Each edge node is read-only with respect to metadata.
LISTEN/NOTIFY fits this shape unusually well:
- The event source is already a Postgres transaction.
NOTIFYis transactional — if the transaction rolls back, no notification is delivered. You cannot get that from an application-level publish without an outbox. - There is no broker to operate. For roughly 90 invalidations a day, standing up Kafka or even Redis Streams would be more moving parts than the thing they protect.
- It is at-most-once, which sounds like a dealbreaker and is actually just a design constraint you handle explicitly. More on that below.
Emitting the event inside the transaction that caused it
The trigger does two things: append to a durable log table, then notify with nothing but the log id. The payload limit for NOTIFY is 8000 bytes and I have no interest in discovering what happens at 7999 during a takedown wave, so the payload is a single integer.
CREATE TABLE invalidation_log (
id bigserial PRIMARY KEY,
video_id text NOT NULL,
op text NOT NULL CHECK (op IN ('drop','update','region_block')),
regions text[] NOT NULL DEFAULT '{}',
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE OR REPLACE FUNCTION emit_invalidation() RETURNS trigger AS $$
DECLARE
v_op text;
v_regions text[];
v_id bigint;
BEGIN
IF TG_OP = 'DELETE' THEN
INSERT INTO invalidation_log (video_id, op, regions)
VALUES (OLD.video_id, 'drop', OLD.regions)
RETURNING id INTO v_id;
PERFORM pg_notify('vw_invalidate', v_id::text);
RETURN NULL;
END IF;
IF NEW.availability IS DISTINCT FROM OLD.availability THEN
v_op := CASE WHEN NEW.availability = 'gone' THEN 'drop' ELSE 'update' END;
v_regions := NEW.regions;
ELSIF NEW.regions IS DISTINCT FROM OLD.regions THEN
-- Only the regions that LOST access need to hear about this.
v_op := 'region_block';
v_regions := ARRAY(SELECT unnest(OLD.regions) EXCEPT SELECT unnest(NEW.regions));
ELSE
RETURN NULL; -- title/description churn stays on the normal cron
END IF;
INSERT INTO invalidation_log (video_id, op, regions)
VALUES (NEW.video_id, v_op, v_regions)
RETURNING id INTO v_id;
PERFORM pg_notify('vw_invalidate', v_id::text);
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER video_state_invalidate
AFTER UPDATE OR DELETE ON video_state
FOR EACH ROW EXECUTE FUNCTION emit_invalidation();
Note the ELSE RETURN NULL. Without it, every routine view-count update from the crawler would fire a notification, and the channel becomes noise nobody can act on. Deciding what is not an invalidation is most of the work here.
The listener assumes it will miss messages
This is the part people get wrong. NOTIFY delivers to sessions that are connected right now. Restart the listener, drop the connection, deploy a new version — every event in that gap is gone, permanently, with no error anywhere. If your listener treats the notification payload as the data, you have built a system that silently loses removals during exactly the moments you are most likely to be touching it.
So the notification is only a hint that invalidation_log has moved. The listener keeps a watermark on disk and reads the truth from the table.
#!/usr/bin/env python3
"""Bridge Postgres invalidation events to the regional fan-out spool.
Requires psycopg >= 3.2 for notifies(timeout=...).
PG_DSN must be a DIRECT connection -- see the PgBouncer note below.
"""
import json
import logging
import os
import time
import psycopg
DSN = os.environ['PG_DSN']
CHANNEL = 'vw_invalidate'
WATERMARK = '/var/lib/vw/invalidate.watermark'
SPOOL = '/var/spool/vw/invalidate.ndjson'
IDLE_SECONDS = 15.0
def read_watermark() -> int:
try:
with open(WATERMARK) as fh:
return int(fh.read().strip())
except (FileNotFoundError, ValueError):
return 0
def write_watermark(value: int) -> None:
tmp = WATERMARK + '.tmp'
with open(tmp, 'w') as fh:
fh.write(str(value))
os.replace(tmp, WATERMARK) # atomic: we never half-commit progress
def enqueue(event: dict) -> None:
with open(SPOOL, 'a') as fh:
fh.write(json.dumps(event, separators=(',', ':')) + '\n')
def drain(conn: psycopg.Connection, since: int) -> int:
rows = conn.execute(
'SELECT id, video_id, op, regions FROM invalidation_log '
'WHERE id > %s ORDER BY id LIMIT 500',
(since,),
).fetchall()
for row_id, video_id, op, regions in rows:
enqueue({'log_id': row_id, 'video_id': video_id,
'op': op, 'regions': list(regions)})
since = row_id
if rows:
write_watermark(since)
logging.info('drained %d events, watermark now %d', len(rows), since)
return since
def run() -> None:
while True:
try:
with psycopg.connect(DSN, autocommit=True) as conn:
conn.execute(f'LISTEN {CHANNEL}')
# Catch up FIRST. Anything that happened while we were down
# produced a notification nobody was there to receive.
cursor = drain(conn, read_watermark())
while True:
for _ in conn.notifies(timeout=IDLE_SECONDS):
pass # coalesce -- the payload is a hint, not data
cursor = drain(conn, cursor)
except psycopg.OperationalError as exc:
logging.warning('postgres connection lost: %s', exc)
time.sleep(2.0)
if __name__ == '__main__':
logging.basicConfig(level=logging.INFO,
format='%(asctime)s %(levelname)s %(message)s')
run()
Two details that matter more than they look. autocommit=True is mandatory: a listener that opens an implicit transaction and sits on it for hours pins the xmin horizon and quietly blocks autovacuum across the whole database. And the idle timeout is not just a heartbeat — draining on a timer means that even if every notification for an hour is lost, the worst case degrades to a fifteen-second poll rather than to silence.
Fanning out to eight regions that cannot listen
The edge nodes cannot LISTEN themselves. They are shared LiteSpeed accounts: no long-running processes, no persistent outbound connections, no queue runner. Whatever reaches them has to arrive as an ordinary HTTPS request that finishes in under a second.
A small Go worker tails the spool file, batches whatever accumulated in the last 250 ms, and pushes a signed payload per region.
package main
import (
"bytes"
"crypto/hmac"
"crypto/sha256"
"encoding/hex"
"encoding/json"
"fmt"
"log"
"net/http"
"strconv"
"time"
)
type Event struct {
LogID int64 `json:"log_id"`
VideoID string `json:"video_id"`
Op string `json:"op"`
Regions []string `json:"regions"`
}
type Region struct {
Code string
Origin string
Secret []byte
}
// An empty Regions slice means a global drop: everyone gets it.
func filterForRegion(batch []Event, code string) []Event {
out := make([]Event, 0, len(batch))
for _, e := range batch {
if len(e.Regions) == 0 {
out = append(out, e)
continue
}
for _, c := range e.Regions {
if c == code {
out = append(out, e)
break
}
}
}
return out
}
func push(client *http.Client, r Region, batch []Event) error {
body, err := json.Marshal(batch)
if err != nil {
return err
}
ts := strconv.FormatInt(time.Now().Unix(), 10)
mac := hmac.New(sha256.New, r.Secret)
mac.Write([]byte(ts))
mac.Write([]byte{'.'})
mac.Write(body)
sig := hex.EncodeToString(mac.Sum(nil))
req, err := http.NewRequest(http.MethodPost, r.Origin+"/task/invalidate",
bytes.NewReader(body))
if err != nil {
return err
}
req.Header.Set("Content-Type", "application/json")
req.Header.Set("X-VW-Timestamp", ts)
req.Header.Set("X-VW-Signature", sig)
resp, err := client.Do(req)
if err != nil {
return err
}
defer resp.Body.Close()
if resp.StatusCode != http.StatusOK {
return fmt.Errorf("%s: status %d", r.Code, resp.StatusCode)
}
return nil
}
// One slow region must never hold up the other seven.
func fanout(regions []Region, batch []Event) {
client := &http.Client{Timeout: 8 * time.Second}
done := make(chan struct{}, len(regions))
for _, r := range regions {
go func(r Region) {
defer func() { done <- struct{}{} }()
relevant := filterForRegion(batch, r.Code)
if len(relevant) == 0 {
return
}
backoff := 500 * time.Millisecond
for attempt := 1; attempt <= 4; attempt++ {
err := push(client, r, relevant)
if err == nil {
return
}
log.Printf("push %s attempt %d: %v", r.Code, attempt, err)
time.Sleep(backoff)
backoff *= 2
}
log.Printf("giving up on %s, %d events deferred to cron",
r.Code, len(relevant))
}(r)
}
for range regions {
<-done
}
}
The last log line is the safety net, and it is the reason the cron stayed exactly as it was. If a region is unreachable for four attempts, we log it and stop. The next scheduled refresh rebuilds that node's database from Postgres anyway, so the failure mode of the fast path is "you fall back to the old seven-hour behaviour for one region," not "data diverges forever." A fast path you are allowed to abandon is much easier to operate than one you have to guarantee.
Applying it on a PHP 8.4 edge node
On the node this is a single route. It verifies the signature, applies the ops inside one SQLite transaction, and purges the affected page cache files.
<?php
declare(strict_types=1);
namespace App\Controllers;
use PDO;
final class InvalidateController
{
private const int MAX_SKEW = 120;
public function __construct(
private readonly PDO $db,
private readonly string $secret,
private readonly string $pageCacheDir,
) {}
public function handle(): void
{
$body = file_get_contents('php://input') ?: '';
$ts = $_SERVER['HTTP_X_VW_TIMESTAMP'] ?? '';
$sig = $_SERVER['HTTP_X_VW_SIGNATURE'] ?? '';
if (!ctype_digit($ts) || abs(time() - (int) $ts) > self::MAX_SKEW) {
http_response_code(401);
return;
}
$expected = hash_hmac('sha256', $ts . '.' . $body, $this->secret);
if (!hash_equals($expected, $sig)) {
http_response_code(401);
return;
}
$events = json_decode($body, true, 8, JSON_THROW_ON_ERROR);
$touched = [];
$this->db->beginTransaction();
try {
foreach ($events as $event) {
if ($this->apply($event['video_id'], $event['op'])) {
$touched[] = $event['video_id'];
}
}
$this->db->commit();
} catch (\Throwable $e) {
$this->db->rollBack();
error_log('invalidate failed: ' . $e->getMessage());
http_response_code(500); // the Go worker will retry
return;
}
$this->purge($touched);
header('Content-Type: application/json');
echo json_encode(['applied' => count($touched)]);
}
/**
* FTS5 external-content tables never learn about deletions on their own,
* and the 'delete' command needs the OLD column values -- read them first
* or the index silently keeps returning the row.
*/
private function apply(string $videoId, string $op): bool
{
$stmt = $this->db->prepare(
'SELECT rowid, title, description FROM videos WHERE video_id = ?'
);
$stmt->execute([$videoId]);
$video = $stmt->fetch(PDO::FETCH_ASSOC);
if ($video === false) {
return false; // already gone; invalidation is idempotent
}
$this->db->prepare(
"INSERT INTO videos_fts(videos_fts, rowid, title, description)
VALUES ('delete', ?, ?, ?)"
)->execute([$video['rowid'], $video['title'], $video['description']]);
if ($op === 'drop' || $op === 'region_block') {
$this->db->prepare('DELETE FROM videos WHERE rowid = ?')
->execute([$video['rowid']]);
} else {
$this->db->prepare('UPDATE videos SET stale = 1 WHERE rowid = ?')
->execute([$video['rowid']]);
}
return true;
}
private function purge(array $videoIds): void
{
foreach ($videoIds as $id) {
@unlink($this->pageCacheDir . '/watch-' . $id . '.html');
}
// Listing pages are cheap to rebuild and expensive to get wrong.
$listings = glob($this->pageCacheDir . '/{home,category-*}.html', GLOB_BRACE);
foreach ($listings ?: [] as $file) {
@unlink($file);
}
header('X-LiteSpeed-Purge: *');
}
}
The idempotency is load-bearing. Retries, overlapping batches and a cron rebuild that lands mid-flight all mean the same event will be applied more than once. Every branch here tolerates that: a missing row returns false, and an FTS5 delete for a row already removed from the index is a no-op.
Four things that broke
PgBouncer ate the notifications. Our pooler runs in transaction pooling mode. LISTEN registers against a session, and in transaction pooling your session is handed to somebody else the moment your transaction ends. The listener connected fine, reported no errors, and received nothing, ever. The fix is a direct connection for that one process. There is no warning for this; you find it by noticing the watermark only advances on the fifteen-second timer.
A long transaction delayed everything behind it. NOTIFY is delivered at commit, which is the property we wanted, right up until a nightly reconciliation job held a transaction open for forty minutes and every invalidation it generated queued behind it. Removals now happen in short, dedicated transactions; the bulk job writes to a staging table.
The blunt cache purge caused a stampede. The first version deleted every file in data/pagecache/ on any event. Eight regions purging their entire cache within the same second, on shared hosting, during peak — the nodes survived, but response times spiked hard enough to be visible in Search Console. Scoping the purge to the touched watch pages plus the listing pages fixed it.
Clock skew rejected legitimate pushes. One host drifted about four minutes and started returning 401 for everything. A two-minute skew window is a real security control, not a formality, so the answer was NTP on the sender and an explicit alert on repeated 401s rather than widening the window.
Six weeks of numbers
- Propagation from Postgres commit to edge apply: p50 1.4 s, p95 6.2 s, p99 19 s. Before, the number was the region's cron interval — two to seven hours.
- About 90 invalidation events a day across all eight regions. That volume is the entire justification for not running a broker.
- Dead-embed watch pages, sampled hourly: down from roughly 40 concurrent across the fleet to under 3.
- API quota consumption: unchanged. Nothing about the fetch path moved.
- Extra infrastructure: one Python process, one Go process, one Postgres table, one PHP route.
What this is not
This is not a replication system and it should not grow into one. LISTEN/NOTIFY gives you a cheap, transactional wake-up signal with no delivery guarantee; every useful property here comes from the log table and the watermark, not from the notification. If you need ordering across regions, exactly-once delivery, or replay from arbitrary points, you want a real log and you should stop stretching this one. The moment I find myself putting business data in a pg_notify payload, that will be the signal.
Conclusion
The cron was never the problem. Polling is a perfectly good way to keep a read replica roughly current, and ours still does that job on a two-to-seven-hour cadence without complaint. What polling cannot do is express urgency, and "this specific row is dead in these specific regions, right now" is urgent in a way that a scheduled full refresh will never capture. Adding a narrow push channel for exactly that one message class — with the periodic rebuild left intact underneath as the reconciliation layer — cost about 400 lines across four languages and removed the worst failure mode we had. If you already run Postgres on the write side, the bus is sitting there. You mostly just have to accept, in the design rather than in a comment, that it will drop your messages.
Top comments (0)