Streaming Live Video View Counts With Server-Sent Events on LiteSpeed

The number on every page was stale by design

Every watch page on TopVideoHub renders a view count, and for months that number was quietly wrong. Our stack — PHP 8.4 behind LiteSpeed, fronted by Cloudflare — caches aggressively because it has to. We aggregate trending video across a dozen Asia-Pacific regions, and origin CPU is the real constraint, not bandwidth. A watch page might sit in edge cache for ten minutes. So the "1,204 views" a visitor in Osaka saw was frozen at render time, sometimes hours old, while the real counter kept climbing in SQLite, untouched by anyone.

The naive fix is polling: have the browser hit /api/views?v=abc every few seconds and repaint the number. We tried it. With a few thousand concurrent watchers across TopVideoHub, that turned into a steady drizzle of tiny requests that punched straight through Cloudflare (uncacheable by design), woke a PHP worker, opened SQLite, ran one SELECT, and closed. Multiply by every open tab and you are paying full request overhead to move a single integer that usually did not even change.

Server-Sent Events solve this cleanly. One long-lived HTTP connection, server pushes only when the count actually moves, and the browser's built-in EventSource handles reconnection and message ordering for you. This post walks through the exact design we run: a debounced write path, a PHP SSE endpoint for the single-server case, and a small Go pub/sub broker for when one box is no longer enough. Every code block below is runnable.

Why SSE and not WebSockets or polling

The three real options are long-polling, WebSockets, and SSE. For a one-directional counter that only ever flows server → client, SSE is the right-sized tool:

• It is plain HTTP. No Upgrade handshake, no separate protocol for Cloudflare or LiteSpeed to mishandle. It rides the same TLS, the same headers, the same WAF rules you already trust.
• The client is free. EventSource is built into every browser. It reconnects automatically, tracks the last event ID, and re-sends it on reconnect so you can resume without gaps.
• It is cheap on the wire. After the initial response headers, each update is a few bytes of data: framing. No per-message HTTP overhead like polling, no frame masking like WebSockets.
• It degrades sanely. If a proxy buffers or a NAT idles the connection, you send periodic comment frames and the browser silently reconnects. You never have to hand-roll a ping/pong protocol.

WebSockets win when you need genuine bidirectional, low-latency traffic — chat, collaborative editing, live cursors. A view counter is not that. Pushing view counts over a WebSocket means writing a framing layer, a heartbeat, and a reconnect strategy you would otherwise get for free. Save WebSockets for when the client actually talks back.

Coalescing writes before they touch SQLite

Before streaming anything, fix the write path. A popular video during a K-drama finale gets thousands of view pings a minute. You do not want thousands of individual UPDATE statements fighting over the same row — under SQLite's single-writer model that is a recipe for SQLITE_BUSY and latency spikes.

The trick is to coalesce. Raw view events land in an in-memory buffer, and a background flusher applies them as one batched increment per video per interval. SQLite's WAL mode plus a sane busy_timeout handles the rest. Here is the increment primitive, using SQLite's UPSERT and RETURNING (both stable since 3.35) so a bump returns the new total in a single round trip:

<?php
// view_counter.php — hot-path increment on WAL-mode SQLite
final class ViewCounter
{
    private \PDO $db;

    public function __construct(string $path)
    {
        $this->db = new \PDO('sqlite:' . $path);
        $this->db->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
        $this->db->exec('PRAGMA journal_mode=WAL');
        $this->db->exec('PRAGMA busy_timeout=3000');
        $this->db->exec(
            'CREATE TABLE IF NOT EXISTS view_counts (
                 video_id TEXT PRIMARY KEY,
                 views    INTEGER NOT NULL DEFAULT 0
             )'
        );
    }

    /** Apply a batched delta and return the new total. */
    public function bump(string $videoId, int $delta = 1): int
    {
        $stmt = $this->db->prepare(
            'INSERT INTO view_counts (video_id, views) VALUES (:id, :d)
             ON CONFLICT(video_id) DO UPDATE SET views = views + :d
             RETURNING views'
        );
        $stmt->execute([':id' => $videoId, ':d' => $delta]);
        return (int) $stmt->fetchColumn();
    }
}

The :d delta is what makes this batchable. A thousand pings collapse into one bump($id, 1000), which is a single writer transaction instead of a thousand. Everything downstream — including the SSE stream — reads the coalesced total, so viewers see a smooth, monotonic number instead of a stampede.

The PHP SSE endpoint

For a single origin box, you can serve SSE straight from PHP. The endpoint holds the connection open, checks the counter, and emits a frame only when the value changed. The important details are all in the headers and the flush discipline:

<?php
// sse_views.php — stream view-count updates for one video
header('Content-Type: text/event-stream');
header('Cache-Control: no-cache, no-transform');
header('Connection: keep-alive');
header('X-Accel-Buffering: no'); // ask proxies not to buffer

$videoId = preg_replace('/[^A-Za-z0-9_-]/', '', $_GET['v'] ?? '');
if ($videoId === '') {
    http_response_code(400);
    exit;
}

set_time_limit(0);
ignore_user_abort(false);
while (ob_get_level() > 0) {
    ob_end_flush(); // tear down any output buffering LiteSpeed set up
}

$db = new PDO('sqlite:/var/data/backlink.db');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$read = $db->prepare('SELECT views FROM view_counts WHERE video_id = ?');

$last     = -1;
$deadline = time() + 300; // recycle the worker every 5 minutes

while (time() < $deadline) {
    if (connection_aborted()) {
        break;
    }

    $read->execute([$videoId]);
    $views = (int) ($read->fetchColumn() ?: 0);

    if ($views !== $last) {
        $last = $views;
        echo 'id: ' . $views . "\n";
        echo "event: views\n";
        echo 'data: ' . json_encode(['v' => $videoId, 'views' => $views]) . "\n\n";
    } else {
        echo ": keep-alive\n\n"; // comment frame; keeps the pipe warm
    }

    flush();
    sleep(2);
}

A few things worth calling out:

• X-Accel-Buffering: no is the single most important line. Reverse proxies love to buffer a response until it looks "complete," which for a stream means forever. This header tells LiteSpeed (and nginx) to pass bytes through immediately.
• The comment frame (: keep-alive\n\n) is a no-op the browser ignores, but it keeps Cloudflare and intermediate NATs from deciding the idle connection is dead.
• The 5-minute deadline matters. A PHP-FPM or LiteSpeed worker pinned to one client forever is a worker you cannot reuse. Recycling every few minutes lets EventSource transparently reconnect and frees the slot. Combined with set_time_limit(0), you control the lifetime rather than letting the SAPI kill you mid-frame.

This works, and for a small deployment it is all you need. But notice the flaw: every connection polls SQLite on its own timer. That is fine for hundreds of viewers and miserable for tens of thousands — you have just moved the polling from the browser to the origin. When one box stops coping, you push the poll out of the request path entirely.

Fanning out with a small Go broker

The scale-out design flips the model from pull to push. Instead of each connection reading the database, a single publisher writes the coalesced total once and fans it out to every subscriber over an in-memory pub/sub hub. Go is a natural fit — cheap goroutines, real concurrency, and http.Flusher for streaming. One goroutine per connection, a shared hub keyed by video ID:

// broker.go — pub/sub fan-out for SSE view counts
package main

import (
    "encoding/json"
    "fmt"
    "net/http"
    "sync"
)

type Hub struct {
    mu   sync.RWMutex
    subs map[string]map[chan int]struct{} // videoID -> subscriber channels
}

func NewHub() *Hub {
    return &Hub{subs: make(map[string]map[chan int]struct{})}
}

func (h *Hub) subscribe(video string) chan int {
    ch := make(chan int, 8)
    h.mu.Lock()
    if h.subs[video] == nil {
        h.subs[video] = make(map[chan int]struct{})
    }
    h.subs[video][ch] = struct{}{}
    h.mu.Unlock()
    return ch
}

func (h *Hub) unsubscribe(video string, ch chan int) {
    h.mu.Lock()
    delete(h.subs[video], ch)
    if len(h.subs[video]) == 0 {
        delete(h.subs, video)
    }
    h.mu.Unlock()
    close(ch)
}

func (h *Hub) publish(video string, views int) {
    h.mu.RLock()
    for ch := range h.subs[video] {
        select {
        case ch <- views: // non-blocking: a slow client just drops a frame
        default:
        }
    }
    h.mu.RUnlock()
}

func (h *Hub) stream(w http.ResponseWriter, r *http.Request) {
    video := r.URL.Query().Get("v")
    if video == "" {
        http.Error(w, "missing v", http.StatusBadRequest)
        return
    }
    fl, ok := w.(http.Flusher)
    if !ok {
        http.Error(w, "streaming unsupported", http.StatusInternalServerError)
        return
    }
    w.Header().Set("Content-Type", "text/event-stream")
    w.Header().Set("Cache-Control", "no-cache")
    w.Header().Set("X-Accel-Buffering", "no")

    ch := h.subscribe(video)
    defer h.unsubscribe(video, ch)

    for {
        select {
        case <-r.Context().Done():
            return // client left; goroutine and channel are cleaned up
        case views := <-ch:
            data, _ := json.Marshal(map[string]int{"views": views})
            fmt.Fprintf(w, "id: %d\nevent: views\ndata: %s\n\n", views, data)
            fl.Flush()
        }
    }
}

func main() {
    hub := NewHub()
    http.HandleFunc("/sse", hub.stream)
    http.HandleFunc("/publish", func(w http.ResponseWriter, r *http.Request) {
        var msg struct {
            V     string `json:"v"`
            Views int    `json:"views"`
        }
        if json.NewDecoder(r.Body).Decode(&msg) == nil {
            hub.publish(msg.V, msg.Views)
        }
    })
    http.ListenAndServe("127.0.0.1:8090", nil)
}

The select/default in publish is the load-bearing detail. A subscriber whose buffered channel is full simply misses that frame instead of blocking the publisher and every other subscriber behind it. For a monotonically increasing counter, a dropped intermediate value is harmless — the next frame carries a newer, higher total anyway. This is back-pressure done right: you protect the fast path and let slow clients self-correct.

Feeding the broker from the write path

Something has to call /publish with the coalesced totals. That is where the debounced flusher lives. In our pipeline it is a small async Python service: it accepts raw view pings, buffers them, and once a second applies the batched delta to SQLite and forwards the new total to the Go broker.

# flusher.py — coalesce raw view pings, push batched totals to the broker
import asyncio
import sqlite3
import httpx

DB = "/var/data/backlink.db"
BROKER = "http://127.0.0.1:8090/publish"
FLUSH_INTERVAL = 1.0  # seconds

pending: dict[str, int] = {}
lock = asyncio.Lock()


async def record(video_id: str) -> None:
    # called on every raw view; cheap, in-memory
    async with lock:
        pending[video_id] = pending.get(video_id, 0) + 1


async def flusher() -> None:
    db = sqlite3.connect(DB, isolation_level=None)
    db.execute("PRAGMA journal_mode=WAL")
    db.execute("PRAGMA busy_timeout=3000")

    async with httpx.AsyncClient(timeout=2.0) as client:
        while True:
            await asyncio.sleep(FLUSH_INTERVAL)

            async with lock:
                if not pending:
                    continue
                batch = dict(pending)
                pending.clear()

            for vid, delta in batch.items():
                (total,) = db.execute(
                    "INSERT INTO view_counts (video_id, views) VALUES (?, ?) "
                    "ON CONFLICT(video_id) DO UPDATE SET views = views + ? "
                    "RETURNING views",
                    (vid, delta, delta),
                ).fetchone()
                try:
                    await client.post(BROKER, json={"v": vid, "views": total})
                except httpx.HTTPError:
                    pass  # broker restart shouldn't lose the DB write


if __name__ == "__main__":
    asyncio.run(flusher())

Now the shape is clear: pings come in fast and cheap, get batched once a second into a single SQLite write, and the resulting total is pushed once to the broker, which fans it out to every open connection. The database is written a bounded number of times per second regardless of traffic, and no SSE connection ever touches it. That is the whole point — decouple write frequency from read frequency from broadcast frequency.

The browser side

The client is almost anticlimactic, which is exactly why SSE is pleasant. EventSource does the reconnection and Last-Event-ID bookkeeping; you just listen for the named event and repaint:

// live-views.js — attach live counting to any watch page
function liveViews(videoId, el) {
  const url = `/sse?v=${encodeURIComponent(videoId)}`;
  const es = new EventSource(url);

  es.addEventListener("views", (e) => {
    const { views } = JSON.parse(e.data);
    el.textContent = new Intl.NumberFormat().format(views);
  });

  es.onerror = () => {
    // EventSource retries on its own and resends Last-Event-ID.
    // Only intervene if the browser has truly given up.
    if (es.readyState === EventSource.CLOSED) {
      setTimeout(() => liveViews(videoId, el), 5000);
    }
  };

  // Don't hold a connection open for a backgrounded tab.
  document.addEventListener("visibilitychange", () => {
    if (document.hidden) {
      es.close();
    } else if (es.readyState === EventSource.CLOSED) {
      liveViews(videoId, el);
    }
  });
}

liveViews(
  document.body.dataset.videoId,
  document.getElementById("view-count"),
);

Two habits worth adopting. First, close the connection when the tab is hidden. A user with fifteen background tabs should not pin fifteen goroutines; reopening on visibilitychange costs nothing thanks to automatic reconnect. Second, trust EventSource before writing your own retry logic — it already implements exponential-ish backoff and event-ID resumption per the WHATWG spec. The manual setTimeout is only a floor for the rare CLOSED state, not a replacement.

Reconnection, ordering, and back-pressure

A few production lessons that are easy to miss:

• Use the count itself as the event ID. Because the counter is monotonic, setting id: to the view total gives you free ordering: the browser resends Last-Event-ID on reconnect, and you can skip re-emitting anything not greater than it. No sequence table required.
• Cloudflare will happily hold a stream open as long as bytes keep flowing. The comment-frame heartbeat every couple of seconds is what keeps it and any intermediary from reaping an "idle" connection. Do not remove it to save bytes.
• Cap connection lifetime on the server, not just the client. Recycling workers or goroutines on a timer bounds your resource ceiling and turns a memory-leak risk into a self-healing reconnect. The client never notices.
• Drop, don't block. For a counter, a missed intermediate value is invisible because the next frame supersedes it. Design your fan-out so a slow consumer degrades its own experience and no one else's.
• Never cache the stream. Cache-Control: no-cache, no-transform plus X-Accel-Buffering: no are mandatory. On a Cloudflare-fronted origin, one stray cache rule on the SSE path will serve one user's frozen stream to everyone.

What changed in production

After moving from polling to this design, origin request volume for view counts dropped by roughly two orders of magnitude — thousands of tiny uncacheable requests per minute became a handful of long-lived connections plus one batched DB write per second. SQLite SQLITE_BUSY errors on the counter table went to zero, because the single-writer flusher is the only thing that ever writes it. And the number on the page finally tells the truth: when a video trends across our Asia-Pacific regions, watchers see it climb in real time instead of discovering a stale figure trapped behind a ten-minute edge cache.

The larger lesson is architectural, not about SSE specifically. Real-time UX and aggressive caching are not in conflict once you stop trying to serve live data through the cache. Keep the cacheable HTML cacheable, and open a separate, deliberately uncacheable stream for the handful of values that must be live. SSE is the least-effort way to open that stream: no new protocol, no handshake, no client library, and reconnection you get for free. Start with the PHP endpoint on a single box, and reach for the Go broker only when your connection count outgrows per-connection polling. Both are in this post, and both are running behind that view count right now.