Scaling Video Viewership Analytics with TimescaleDB Hypertables

On DailyWatch we log a row every time someone starts a clip, scrubs the timeline, or finishes a video. For a free discovery platform that surfaces trending content across dozens of countries, that firehose adds up fast. Our original setup was a single view_events table in SQLite behind PHP 8.4 and LiteSpeed, fronted by Cloudflare. It worked beautifully at a few million rows. Then it didn't.

The query that broke us was the most boring one imaginable: "how many views did each video get, bucketed by hour, over the last 30 days?" At 40 million rows that query went from 300ms to roughly 12 seconds. The dashboard timed out, the cron that pre-warmed our trending caches started overlapping itself, and the SQLite file locked long enough that ingestion writes began backing up. Analytics workloads and transactional workloads had collided in the same file, and the analytics side lost.

This is the exact shape of problem TimescaleDB hypertables solve. Not "Postgres is faster than SQLite" — that's not the point. The point is that time-series ingest-and-aggregate workloads have a specific access pattern, and a hypertable is a storage engine tuned for it. This article walks through how we migrated the viewership analytics off SQLite and onto TimescaleDB, what actually moved the needle, and the mistakes worth skipping.

Why a regular table stops scaling for view events

A view event is append-only, timestamped, and almost always queried by a time range. You never UPDATE a view from three weeks ago. You never ask "show me event id 84,113,902." You ask "give me the last 24 hours," "bucket this month by day," "compare this week to last week."

A normal B-tree table doesn't know any of that. As it grows:

• The index that keeps range queries fast grows with it, and eventually the working set no longer fits in memory. Every range scan starts hitting disk.
• Inserts touch a single ever-growing index, so index maintenance cost climbs even though you're only ever appending recent timestamps.
• Deleting old data (retention) means a giant DELETE ... WHERE ts < ... that bloats the table and triggers expensive vacuuming.

A TimescaleDB hypertable looks like one table to your SQL but is physically many chunks, each covering a time range (say, one day). This partitioning is what changes the economics:

• A query for "last 24 hours" touches one or two chunks and ignores the rest — chunk exclusion. The planner never even opens the other 340 chunks.
• Each chunk has its own small index, so recent inserts hit a small, cache-resident index.
• Retention becomes DROP CHUNK, which is a metadata operation, not a row-by-row delete. No bloat, no vacuum storm.

You keep writing normal SQL. The engine handles the physical layout.

Designing the events table

Here's the schema we landed on. The key decisions: pick the time column that queries actually filter on, keep the row narrow, and store the dimensions you group by as plain columns rather than joining out to lookup tables on every analytics query.

-- Requires the timescaledb extension
CREATE EXTENSION IF NOT EXISTS timescaledb;

CREATE TABLE view_events (
    ts          TIMESTAMPTZ      NOT NULL,
    video_id    BIGINT           NOT NULL,
    country     CHAR(2)          NOT NULL,   -- ISO-3166 alpha-2
    device      SMALLINT         NOT NULL,   -- 0=web 1=ios 2=android 3=tv
    event_type  SMALLINT         NOT NULL,   -- 0=start 1=progress 2=complete
    watch_ms    INTEGER          NOT NULL DEFAULT 0,
    session_id  UUID             NOT NULL
);

-- Turn it into a hypertable, 1-day chunks.
SELECT create_hypertable(
    'view_events',
    by_range('ts', INTERVAL '1 day')
);

-- The composite index that matches our #1 query pattern:
-- "this video, over this time range".
CREATE INDEX ON view_events (video_id, ts DESC);

A few things worth calling out:

• Chunk interval matters. The rule of thumb from the Timescale docs is that a single chunk's indexes plus data should fit comfortably in about 25% of your shared_buffers / RAM available to Postgres. Too large and you lose the memory-locality benefit; too small and you drown in chunk metadata. We started at 1 day, measured, and never needed to change it.
• No surrogate primary key. We deliberately don't have a SERIAL id. A monotonically increasing PK on an append-heavy table is a hot-spot and buys us nothing — we never look up a single event by id.
• Dimensions are denormalized on purpose. country and device live in the fact row so aggregation never needs a join. Storage is cheap; join-per-aggregate is not.

Ingesting from PHP without melting the write path

Our app is PHP 8.4. The temptation is to fire one INSERT per event inside the request that serves the video page. Don't. Two reasons: it couples user-facing latency to the analytics DB, and single-row inserts waste most of Postgres's throughput on round-trips.

Instead we buffer events (in this simplified version, a batch handed to a worker) and insert them with a multi-row INSERT inside one transaction. This is the single biggest ingestion win — batched inserts are often 10-20x the throughput of row-at-a-time.

<?php
declare(strict_types=1);

final class ViewEventWriter
{
    public function __construct(private readonly PDO $pdo) {}

    /**
     * @param list<array{ts:string,video_id:int,country:string,
     *   device:int,event_type:int,watch_ms:int,session_id:string}> $events
     */
    public function writeBatch(array $events): int
    {
        if ($events === []) {
            return 0;
        }

        $cols = ['ts','video_id','country','device','event_type','watch_ms','session_id'];
        $placeholders = [];
        $bind = [];

        foreach ($events as $i => $e) {
            $row = [];
            foreach ($cols as $c) {
                $param = ":{$c}_{$i}";
                $row[] = $param;
                $bind[$param] = $e[$c];
            }
            $placeholders[] = '(' . implode(',', $row) . ')';
        }

        $sql = 'INSERT INTO view_events (' . implode(',', $cols) . ') VALUES '
             . implode(',', $placeholders);

        $this->pdo->beginTransaction();
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($bind);
            $this->pdo->commit();
        } catch (\Throwable $ex) {
            $this->pdo->rollBack();
            throw $ex;
        }

        return count($events);
    }
}

// Wiring: keep analytics on its own connection, not the app's main DB handle.
$pdo = new PDO(
    'pgsql:host=analytics-db;port=5432;dbname=metrics',
    'ingest',
    getenv('ANALYTICS_DB_PASS'),
    [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_EMULATE_PREPARES   => false,
    ]
);

In production the buffer lives in a lightweight queue (we use a Redis list; a table would work too) and a cron worker drains it every few seconds in batches of a few thousand. The user-facing request just does an LPUSH and returns. Cloudflare and LiteSpeed never wait on Postgres.

One caveat on batch size: bind parameters are limited (Postgres caps at 65,535 parameters per statement). With 7 columns that's ~9,300 rows per statement. We cap batches at 2,000 rows, which stays well under the limit and keeps each transaction short enough that a failure only costs a small retry.

Continuous aggregates: computing the dashboard once, not per request

The original 12-second query was recomputing hourly buckets from raw rows every single time the dashboard loaded. That's insane when the answer for "3pm yesterday" never changes once the hour is over.

TimescaleDB's continuous aggregates are materialized views that incrementally refresh. You define the rollup once; the engine keeps it up to date as new data lands, only recomputing the recent, still-changing buckets.

-- Hourly rollup per video and country.
CREATE MATERIALIZED VIEW views_hourly
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', ts)                       AS bucket,
    video_id,
    country,
    count(*)                                        AS events,
    count(*) FILTER (WHERE event_type = 0)          AS starts,
    count(*) FILTER (WHERE event_type = 2)          AS completes,
    sum(watch_ms)                                   AS total_watch_ms
FROM view_events
GROUP BY bucket, video_id, country
WITH NO DATA;

-- Refresh policy: keep the last 3 days fresh, refreshing every 30 min.
-- Buckets older than 3 days are already final and never recomputed.
SELECT add_continuous_aggregate_policy('views_hourly',
    start_offset => INTERVAL '3 days',
    end_offset   => INTERVAL '1 hour',
    schedule_interval => INTERVAL '30 minutes');

Now the dashboard query reads from views_hourly, which has maybe a few thousand rows per day instead of tens of millions of raw events:

SELECT bucket, sum(starts) AS starts
FROM views_hourly
WHERE video_id = $1
  AND bucket >= now() - INTERVAL '30 days'
GROUP BY bucket
ORDER BY bucket;

That query dropped from ~12 seconds to single-digit milliseconds. Crucially, TimescaleDB does real-time aggregation: if you query a continuous aggregate for a time range that includes the last hour (which isn't materialized yet), it transparently unions the materialized buckets with a live aggregate over the raw recent chunk. You get correct, up-to-the-second numbers without recomputing history.

Reading the analytics from a service

The dashboard API that fans these numbers out is a small Go service — it handles the concurrent dashboard load better than spinning up PHP-FPM workers for pure JSON, and it sits behind the same Cloudflare edge. Here's the trending endpoint reading straight from the continuous aggregate:

package analytics

import (
    "context"
    "time"

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

type TrendingRow struct {
    VideoID int64
    Starts  int64
    WatchMs int64
}

// Trending returns the most-started videos in a country over a window,
// reading the pre-aggregated hourly rollup rather than raw events.
func Trending(ctx context.Context, pool *pgxpool.Pool, country string, window time.Duration, limit int) ([]TrendingRow, error) {
    const q = `
        SELECT video_id,
               sum(starts)          AS starts,
               sum(total_watch_ms)  AS watch_ms
        FROM views_hourly
        WHERE country = $1
          AND bucket >= now() - $2::interval
        GROUP BY video_id
        ORDER BY starts DESC
        LIMIT $3`

    rows, err := pool.Query(ctx, q, country, window.String(), limit)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    out := make([]TrendingRow, 0, limit)
    for rows.Next() {
        var r TrendingRow
        if err := rows.Scan(&r.VideoID, &r.Starts, &r.WatchMs); err != nil {
            return nil, err
        }
        out = append(out, r)
    }
    return out, rows.Err()
}

Because the underlying view is tiny and indexed, this endpoint stays fast even under the concurrent load of a homepage refresh, and Cloudflare caches the JSON at the edge for a short TTL on top of that. We use context deadlines so a slow query can never pin a connection from the pool — a discipline worth keeping regardless of the database.

Compression and retention: paying for storage you actually use

Raw view events are the bulk of the data and the least valuable per-row once they've been rolled up. TimescaleDB's columnar compression is built for exactly this: compress old chunks, keep recent ones row-oriented for fast ingest.

-- Segment by the columns we filter on, order by time within each segment.
ALTER TABLE view_events SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'video_id, country',
    timescaledb.compress_orderby   = 'ts DESC'
);

-- Compress chunks older than 7 days automatically.
SELECT add_compression_policy('view_events', INTERVAL '7 days');

-- Drop raw events older than 90 days — the rollups already have the history.
SELECT add_retention_policy('view_events', INTERVAL '90 days');

What this buys you:

• Compression ratios of 90%+ on our event data. The segmentby choice is the lever: grouping rows by video_id, country means each compressed batch holds many events for the same video, which compress extremely well and let range queries skip whole segments.
• Retention as metadata. add_retention_policy drops whole chunks past 90 days. No DELETE, no bloat. And because our continuous aggregates already captured the rollups, dropping raw events loses nothing the dashboards need.
• Recent data stays writable. Only chunks older than 7 days get compressed, so hot ingest never touches compressed storage.

A subtle but important interaction: set your continuous aggregate start_offset shorter than your retention window, and make sure the aggregate has materialized a chunk before retention drops it. Otherwise you retain-drop raw data that the rollup hadn't captured yet. We keep 90 days of raw and refresh aggregates within 3 days, so there's a comfortable margin.

What actually moved the needle

After the migration, ranked by impact:

1. Continuous aggregates. The dashboard was slow because it recomputed history on every load. Materializing hourly rollups turned 12s into milliseconds. This alone justified the project.
2. Batched inserts on a dedicated connection. Decoupling ingestion from the user request and batching 2,000 rows per transaction removed the write contention that had been backing up under load.
3. Chunk exclusion. "Last 24 hours" and "last 7 days" queries got fast for free because the planner skips irrelevant chunks. No query rewrite needed.
4. Compression + DROP CHUNK retention. Cut storage by an order of magnitude and made retention a non-event instead of a nightly vacuum battle.

Things that were not worth it: I spent an afternoon hand-tuning the chunk interval before I had numbers. Don't. Start at 1 day, load real data, then measure chunks_detailed_size and adjust only if a chunk overflows memory. I also over-indexed early — every extra index slows ingest, so add them from observed query plans, not speculation.

When you don't need this

Be honest about scale. SQLite with FTS5 still runs plenty of our platform — search, content metadata, the stuff that fits in a file and gets read far more than written. Hypertables earn their keep specifically for high-volume, append-only, time-ranged data where you aggregate constantly and retain selectively. If your events table is under a few million rows and a GROUP BY time_bucket returns in under a second, you have a scaling headroom problem you don't have yet — solve it when it's real.

Conclusion

The migration wasn't about swapping databases; it was about matching the storage engine to the access pattern. View events are append-only and time-ranged, and a hypertable plus continuous aggregates is purpose-built for that shape. The wins compound: chunk exclusion makes range queries cheap, continuous aggregates make dashboards instant, compression makes storage affordable, and DROP CHUNK retention makes cleanup free. We kept the rest of the stack — PHP 8.4, LiteSpeed, Cloudflare, SQLite where it still fits — and moved only the workload that had outgrown a single file. If your analytics queries are creeping past a second and your event table only ever grows, this is the pattern to reach for before you start sharding anything.