Designing a Multi-Tenant Video Aggregation Schema in PostgreSQL

When you run several video sites off one codebase, the first thing that breaks is not the frontend — it is the schema. At TopVideoHub we aggregate trending videos across the Asia-Pacific region for four separate brands, each with its own domain, its own language mix (Japanese, Korean, Traditional Chinese, Thai), and its own trending window. Early on every brand had its own SQLite file. That worked until we needed a single trending query that spanned Tokyo and Seoul, a shared moderation queue, and per-tenant analytics that a marketing person could actually run. The moment those cross-tenant needs showed up, isolated databases turned into a synchronization nightmare. This post is how I redesigned the whole thing onto a single PostgreSQL cluster using row-level security, tenant-scoped indexes, and a partitioning strategy tuned for high-churn trending data. If you want to see the aggregator these decisions power, it is live at TopVideoHub.

The concrete problem: shared reads, isolated writes

Multi-tenant is a vague phrase, so let me pin down what we actually needed:

• Hard isolation on writes. Brand A must never overwrite Brand B's curated ordering, even through a buggy query.
• Soft sharing on reads. The video catalog itself is shared — a YouTube video trending in Japan is the same row whether TopVideoHub or a sister brand surfaces it. We do not want four copies of the same 2 GB metadata table.
• Per-tenant ranking. Each brand computes its own trending score from region weights. The ranking is tenant-specific; the video is global.
• CJK search that stays fast. Our search layer historically used SQLite FTS5 with a custom CJK tokenizer. Moving the source of truth to Postgres meant search had to keep up across four tenants without a full-scan penalty.

The naive answers — database-per-tenant or schema-per-tenant — fail the "shared reads" requirement. A shared catalog with four physical copies wastes storage and, worse, means four separate ingestion pipelines drifting out of sync. So the design is a hybrid: globally shared reference tables, tenant-scoped association and ranking tables, and row-level security to enforce the boundary.

Core schema: separate the global from the tenant-scoped

The single most important modeling decision is deciding, for every table, whether a row belongs to everyone or to one tenant. Get this wrong and you either duplicate data or leak it.

-- Global reference data: one row per real-world video, shared by all tenants
CREATE TABLE video (
    id            BIGGENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    provider      TEXT        NOT NULL,          -- 'youtube', 'vimeo'
    provider_id   TEXT        NOT NULL,
    title         TEXT        NOT NULL,
    lang          TEXT        NOT NULL,          -- BCP-47: 'ja', 'ko', 'zh-Hant'
    duration_s    INT         NOT NULL,
    published_at  TIMESTAMPTZ NOT NULL,
    fetched_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (provider, provider_id)
);

-- Tenants (brands)
CREATE TABLE tenant (
    id      INT  GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    slug    TEXT NOT NULL UNIQUE,               -- 'tvh', 'dwv'
    domain  TEXT NOT NULL UNIQUE,
    default_lang TEXT NOT NULL DEFAULT 'en'
);

-- Tenant-scoped: which videos this brand surfaces, and how it ranks them
CREATE TABLE tenant_video (
    tenant_id   INT         NOT NULL REFERENCES tenant(id),
    video_id    BIGINT      NOT NULL REFERENCES video(id),
    region      TEXT        NOT NULL,           -- 'JP', 'KR', 'TW'
    trend_score NUMERIC(10,4) NOT NULL DEFAULT 0,
    curated_rank INT,                            -- NULL = algorithmic only
    added_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (tenant_id, video_id, region)
);

A few deliberate choices here:

• video has no tenant_id. It is genuinely global. Deduplicating on (provider, provider_id) means one physical row even when all four brands trend the same clip.
• tenant_video carries the composite primary key (tenant_id, video_id, region). Putting tenant_id first in the PK matters — Postgres builds the underlying B-tree in that column order, so every tenant-scoped range scan starts from a tight prefix instead of filtering a global index.
• curated_rank lets editors override the algorithm without a separate table. NULL means "trust the score."

Typo watch for anyone copy-pasting: it is BIGINT GENERATED ALWAYS AS IDENTITY, not the mashed token above — I have literally shipped that typo to staging.

Enforce isolation with row-level security, not application code

Application-level WHERE tenant_id = ? is not isolation. It is a convention, and conventions get forgotten in the one reporting query written under deadline pressure. PostgreSQL row-level security (RLS) moves the boundary into the database so a missing predicate fails closed instead of leaking.

ALTER TABLE tenant_video ENABLE ROW LEVEL SECURITY;
ALTER TABLE tenant_video FORCE ROW LEVEL SECURITY;

-- The app connects as a role that is subject to RLS
CREATE POLICY tenant_isolation ON tenant_video
    USING      (tenant_id = current_setting('app.tenant_id')::int)
    WITH CHECK (tenant_id = current_setting('app.tenant_id')::int);

-- A separate role for the ingestion pipeline that legitimately writes all tenants
CREATE POLICY ingest_bypass ON tenant_video
    TO ingest_role
    USING (true) WITH CHECK (true);

The mechanism is current_setting('app.tenant_id'). Every request sets this session variable once, and from then on the database itself refuses to return or write rows outside that tenant. FORCE ROW LEVEL SECURITY is the part people miss: without it, the table owner bypasses policies, and your migrations run as the owner, so you get a false sense of security in testing.

The critical detail is scoping the setting to the transaction, not the connection. In a pooled environment (PgBouncer, or LiteSpeed's persistent PHP workers) a connection is reused across requests. If you SET app.tenant_id at connection time, request two inherits request one's tenant. Use SET LOCAL inside a transaction instead — it resets automatically at commit.

Wiring the tenant context in PHP 8.4

Our app is PHP behind LiteSpeed with Cloudflare in front. Tenant resolution happens from the Host header, then we open a transaction and pin the tenant for its lifetime. PHP 8.4's property hooks and asymmetric visibility make the context object clean:

<?php
declare(strict_types=1);

final class TenantContext
{
    // PHP 8.4 asymmetric visibility: readable everywhere, writable only here
    public private(set) int $tenantId;

    private const DOMAIN_MAP = [
        'topvideohub.com' => 2,
        'dailywatch.video' => 1,
    ];

    public function __construct(private \PDO $pdo, string $host)
    {
        $host = strtolower(preg_replace('/^www\./', '', $host));
        $this->tenantId = self::DOMAIN_MAP[$host]
            ?? throw new \RuntimeException("Unknown tenant host: {$host}");
    }

    /**
     * Run $work inside a transaction with app.tenant_id pinned via SET LOCAL,
     * so RLS scopes every query and the setting resets on commit/rollback.
     */
    public function scoped(callable $work): mixed
    {
        $this->pdo->beginTransaction();
        try {
            // SET LOCAL can't be parameterised; tenantId is an int, so cast-and-inline is safe
            $this->pdo->exec('SET LOCAL app.tenant_id = ' . $this->tenantId);
            $result = $work($this->pdo);
            $this->pdo->commit();
            return $result;
        } catch (\Throwable $e) {
            $this->pdo->rollBack();
            throw $e;
        }
    }
}

// Usage in the request bootstrap
$ctx = new TenantContext($pdo, $_SERVER['HTTP_HOST']);
$trending = $ctx->scoped(function (\PDO $db): array {
    $stmt = $db->query(
        'SELECT v.title, v.lang, tv.trend_score
           FROM tenant_video tv
           JOIN video v ON v.id = tv.video_id
          WHERE tv.region = \'JP\'
          ORDER BY COALESCE(tv.curated_rank, 2147483647), tv.trend_score DESC
          LIMIT 40'
    );
    return $stmt->fetchAll(\PDO::FETCH_ASSOC);
});

Note there is no tenant_id in that WHERE clause. RLS injects it. If someone later refactors this query and forgets the tenant filter, the database still returns only the current tenant's rows. That is the whole point — isolation you cannot accidentally remove. The one place you must be disciplined is inlining the tenant id, because SET LOCAL does not accept bound parameters; casting to int first closes the injection hole.

Partitioning trending data so churn stays cheap

Trending data is the highest-churn table in the system. Every ingestion cycle rewrites trend_score for tens of thousands of rows and expires yesterday's leaders. On a monolithic table that means bloat, vacuum pressure, and index fragmentation. We partition tenant_video — or in the highest-volume version, a separate trend_snapshot table — by tenant using declarative list partitioning:

CREATE TABLE trend_snapshot (
    tenant_id   INT         NOT NULL,
    video_id    BIGINT      NOT NULL,
    region      TEXT        NOT NULL,
    trend_score NUMERIC(10,4) NOT NULL,
    snapshot_at TIMESTAMPTZ NOT NULL DEFAULT now()
) PARTITION BY LIST (tenant_id);

CREATE TABLE trend_snapshot_tvh PARTITION OF trend_snapshot FOR VALUES IN (2);
CREATE TABLE trend_snapshot_dwv PARTITION OF trend_snapshot FOR VALUES IN (1);

-- Per-partition index; each tenant's ranking scan touches only its own partition
CREATE INDEX ON trend_snapshot_tvh (region, trend_score DESC);

List-by-tenant partitioning gives us three things:

• Partition pruning. With app.tenant_id constant per request, the planner touches exactly one partition. A brand's trending query never scans another brand's rows, even physically.
• Cheap expiry. Refreshing a tenant's trending set becomes TRUNCATE trend_snapshot_tvh — instant, no per-row DELETE, no vacuum debt. That is a huge win when you rebuild trending every couple of hours.
• Isolated bloat. A misbehaving ingest on one tenant cannot fragment another's index.

One caveat: partition-by-tenant only scales to tens of tenants comfortably. At thousands, the catalog overhead of one partition per tenant hurts, and you would switch to hash partitioning or back to RLS-only. For a handful of brands, list partitioning is the sweet spot.

Keeping CJK search fast across tenants

Our search story used to be SQLite FTS5 with a bespoke CJK tokenizer, because standard tokenizers split Japanese and Chinese on whitespace that does not exist. Postgres full-text search has the same weakness out of the box: the default parser treats a run of Han characters as one giant token. The fix is bigram tokenization via pg_bigm, which indexes overlapping two-character windows — the same principle our FTS5 tokenizer used.

Because search is a read and the catalog is global, we index video directly and let RLS govern only the join to tenant_video. Here is the tenant-aware search path, written in Go for our ingestion-side API where latency matters:

package search

import (
    "context"
    "database/sql"
)

type Result struct {
    VideoID int64
    Title   string
    Lang    string
}

// Search runs inside a tx that has already issued SET LOCAL app.tenant_id.
// pg_bigm handles CJK; the JOIN to tenant_video is RLS-scoped automatically.
func Search(ctx context.Context, tx *sql.Tx, region, query string) ([]Result, error) {
    const q = `
        SELECT v.id, v.title, v.lang
          FROM video v
          JOIN tenant_video tv ON tv.video_id = v.id AND tv.region = $1
         WHERE v.title LIKE '%' || $2 || '%'
         ORDER BY tv.trend_score DESC
         LIMIT 30`

    rows, err := tx.QueryContext(ctx, q, region, query)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var out []Result
    for rows.Next() {
        var r Result
        if err := rows.Scan(&r.VideoID, &r.Title, &r.Lang); err != nil {
            return nil, err
        }
        out = append(out, r)
    }
    return out, rows.Err()
}

The LIKE '%term%' looks like a full-scan trap, and normally it is — but with a pg_bigm GIN index it is not:

CREATE EXTENSION IF NOT EXISTS pg_bigm;
CREATE INDEX idx_video_title_bigm ON video
    USING gin (title gin_bigm_ops);

Now title LIKE '%東京%' uses the bigram index instead of scanning, and it works identically for Japanese, Korean, and Traditional Chinese because bigrams are script-agnostic. Because the index lives on the global video table, all four tenants share one index structure — no per-tenant duplication, and the tenant boundary is enforced purely through the RLS-governed join.

Migration and operational lessons

Moving from four SQLite files to one Postgres cluster surfaced a handful of lessons worth stealing:

• Backfill video first, dedupe hard. We found ~38% of rows across brands were the same physical video. Collapsing them cut catalog storage by more than a third and made cross-brand analytics trivial.
• Test RLS by trying to break it. Write an integration test that sets tenant A, then attempts to read and write tenant B rows, and asserts zero rows and a failed insert. If that test ever goes green-to-red silently, you have a leak.
• Watch connection pooling and SET LOCAL. Under PgBouncer in transaction mode, SET LOCAL is safe; session-level SET is a landmine. Verify your pool mode before trusting either.
• Keep the read cache tenant-keyed. We still cache rendered pages at the edge via Cloudflare and LiteSpeed, but the cache key must include the tenant, or brand A serves brand B's trending list. A single forgotten tenant dimension in the cache key undoes all the database-level isolation above.
• Partition maintenance is a cron job. Creating and pruning partitions is not automatic. Script it, and alert if a tenant's partition is missing before ingestion runs.

Conclusion

The schema that finally held up was not the most clever one — it was the one that made the tenant boundary a property of the database rather than a habit of the developers. Global reference tables for the shared catalog, tenant-scoped tables for ranking, row-level security so isolation fails closed, list partitioning so high-churn trending data stays cheap to rebuild, and a bigram index so CJK search keeps up across every brand. Each piece is boring on its own. Together they let one PostgreSQL cluster serve four regional video brands without the four-way synchronization tax that isolated databases would have imposed. If you are staring at a pile of per-tenant SQLite files wondering how to unify them, start by classifying every table as global or tenant-scoped — the rest of the design follows from that one honest answer.

Comments

[Mads Hansen]

Nice separation of global identity from tenant-specific ranking. One boundary I’d make explicit is write authority on the shared video table. RLS on tenant_video prevents cross-tenant ranking edits, but any request role that can update video.title, lang, or provider metadata changes what every brand sees. I’d revoke DML on the global catalog from tenant-facing roles, give ingestion a narrowly scoped role or procedure, retain source/provenance and fetched version, and add a tenant override table for brand-specific editorial text. RLS tests should also run as the exact deployed app role, table owner, migration role, and any role with BYPASSRLS, because their behavior differs. That makes “shared reads, isolated writes” an enforceable role-and-schema contract rather than only a table classification.