How I Structure a Multi-Tenant Video Platform Schema in PostgreSQL

Last quarter I merged four separate video-discovery databases into one PostgreSQL cluster. Each brand had its own trending feed, its own set of European audience segments, and its own retention rules. The old setup was four SQLite files behind a PHP 8.4 app on LiteSpeed — fast, but a nightmare to keep consistent once we started sharing a viral-scoring pipeline across brands. The moment two brands needed to query "videos trending in DE and FR this hour, excluding anything a GDPR erasure request touched," the single-file-per-tenant model fell apart. I run ViralVidVault, and this post is the schema I landed on after three rewrites, plus the mistakes that forced each one.

Multi-tenancy sounds like an architecture decision you make once. It isn't. It's a decision you re-make at every table, because the cost of getting tenant_id wrong compounds silently until a cross-tenant leak shows up in a support ticket.

The three isolation models, and why I picked shared-schema

There are exactly three ways to isolate tenants in Postgres, and every vendor blog blurs them:

• Database-per-tenant. One physical database per brand. Total isolation, trivial GDPR deletion (drop the database), but connection pooling becomes hell and cross-tenant analytics require dblink or an ETL job. Migrations run N times.
• Schema-per-tenant. One Postgres schema (namespace) per brand inside one database. Shared connection pool, but search_path juggling is error-prone and 40 tenants × 30 tables = 1,200 tables that pg_dump and the planner have to reason about.
• Shared-schema with a tenant_id column. Every row carries its tenant. One set of tables, one migration, cheap cross-tenant queries. The risk is that a single missing WHERE tenant_id = ? leaks data.

For a video platform where the whole point is cross-tenant trend analysis — "what's viral across all our European feeds right now" — shared-schema is the only model that doesn't fight the product. The leak risk is real, but it's a solvable engineering problem via Row-Level Security. Database-per-tenant would have made our core query impossible without a fan-out.

So the rule for the rest of this post: every tenant-scoped table has a tenant_id, and RLS enforces isolation at the engine level, not in application code.

The tenant and identity backbone

Start with the tenant registry itself. This is the one table that is not tenant-scoped — it defines the tenants.

CREATE TABLE tenant (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    slug         text NOT NULL UNIQUE,            -- 'viralvidvault', 'dailywatch'
    display_name text NOT NULL,
    region       text NOT NULL DEFAULT 'eu',      -- data residency marker
    created_at   timestamptz NOT NULL DEFAULT now(),
    settings     jsonb NOT NULL DEFAULT '{}'::jsonb
);

-- Every tenant-scoped table follows this shape.
CREATE TABLE video (
    tenant_id    bigint NOT NULL REFERENCES tenant(id),
    id           bigint GENERATED ALWAYS AS IDENTITY,
    external_id  text NOT NULL,                   -- YouTube/source id
    title        text NOT NULL,
    channel_id   bigint,
    duration_s   int,
    published_at timestamptz NOT NULL,
    region_codes text[] NOT NULL DEFAULT '{}',    -- ['DE','FR','GB']
    ingested_at  timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (tenant_id, id),
    UNIQUE (tenant_id, external_id)
);

Two details that matter more than they look:

The primary key is (tenant_id, id), composite, in that order. Putting tenant_id first means every index built on the PK is already partitioned by tenant on its leading column. Range scans for one tenant stay physically clustered. If you make id alone the PK and bolt tenant_id on as a plain column, you'll add a second index for it and the planner will sometimes pick the wrong one.

UNIQUE (tenant_id, external_id) not UNIQUE (external_id). The same YouTube video can legitimately exist in two of my brands' feeds. Uniqueness is per tenant. I've seen teams enforce a global unique constraint here and then wonder why importing the same viral clip into a second brand throws a duplicate key error.

Row-Level Security is the actual isolation boundary

Application-code filtering (WHERE tenant_id = $current) is not isolation. It's a convention, and conventions get forgotten in the one reporting query written at 2 a.m. Postgres RLS moves the boundary into the engine so a forgotten WHERE clause returns zero rows instead of everyone's rows.

ALTER TABLE video ENABLE ROW LEVEL SECURITY;
ALTER TABLE video FORCE ROW LEVEL SECURITY;  -- applies even to the table owner

CREATE POLICY tenant_isolation ON video
    USING (tenant_id = current_setting('app.tenant_id')::bigint);

The app sets one session variable per request instead of threading tenant_id through every query:

<?php
// PHP 8.4 — called once at the start of each request, right after we
// resolve which brand the incoming Host header maps to.
final class TenantContext
{
    public function __construct(private \PDO $pdo) {}

    public function bind(int $tenantId): void
    {
        // set_config with is_local=true scopes the value to the current
        // transaction, so a pooled connection can't leak it to the next request.
        $stmt = $this->pdo->prepare(
            "SELECT set_config('app.tenant_id', :tid, true)"
        );
        $stmt->execute(['tid' => (string) $tenantId]);
    }
}

// Usage inside the request lifecycle:
$pdo->beginTransaction();
(new TenantContext($pdo))->bind($resolvedTenantId);
// From here every SELECT/UPDATE/DELETE on `video` is auto-filtered by RLS.
// A query with no tenant clause at all returns only this tenant's rows.

The is_local = true flag on set_config is the part people miss. With connection pooling (PgBouncer in transaction mode, or a LiteSpeed persistent PDO pool), a session-scoped variable set by request A can survive into request B on the same physical connection. Transaction-local scope guarantees the value dies with the transaction. Set it wrong and you have the exact cross-tenant leak RLS was supposed to prevent — now with a false sense of security.

One caveat: BYPASSRLS roles (superusers, replication) ignore policies. Your migration user needs it; your application user must never have it. Check with SELECT rolname, rolbypassrls FROM pg_roles; before you ship.

Time-series analytics without drowning the write path

A video platform's real load isn't the video table — it's the events. Every play, every 25/50/75/100% completion mark, every share. At ViralVidVault that's the table that decides what "viral" means, and it grows by millions of rows a day across brands.

Putting those events in a flat table and running COUNT(*) ... GROUP BY hour on read is how you get 8-second dashboards. The fix is native range partitioning by time, combined with pre-aggregation.

CREATE TABLE video_event (
    tenant_id   bigint NOT NULL,
    video_id    bigint NOT NULL,
    event_type  smallint NOT NULL,        -- 1=play 2=q25 3=q50 4=q75 5=complete 6=share
    occurred_at timestamptz NOT NULL,
    country     char(2),                  -- ISO region, coarse-grained on purpose
    session_hash bytea                    -- salted, NOT a raw device id (GDPR)
) PARTITION BY RANGE (occurred_at);

-- One partition per day. A cron job creates tomorrow's ahead of time.
CREATE TABLE video_event_2026_07_01
    PARTITION OF video_event
    FOR VALUES FROM ('2026-07-01') TO ('2026-07-02');

CREATE INDEX ON video_event_2026_07_01 (tenant_id, video_id, occurred_at);

Partitioning buys three things at once for a European analytics workload:

• Retention is a metadata operation. Our GDPR policy caps raw event retention at 90 days. Deleting is DROP TABLE video_event_2026_04_01 — instant, no DELETE scanning millions of rows, no bloat, no autovacuum storm.
• Query pruning. "Trending in the last 6 hours" only touches today's and yesterday's partitions. The planner skips the other 88 tables entirely.
• Cheaper indexes. Each partition's index is small enough to stay in cache.

Raw events answer "what happened." They're terrible at answering "what's trending now," because that needs a rolling aggregate. I keep a separate hourly rollup that the scoring job reads:

CREATE TABLE video_score_hourly (
    tenant_id    bigint NOT NULL,
    video_id     bigint NOT NULL,
    bucket       timestamptz NOT NULL,    -- truncated to the hour
    plays        int NOT NULL DEFAULT 0,
    completes    int NOT NULL DEFAULT 0,
    shares       int NOT NULL DEFAULT 0,
    velocity     numeric(10,4) NOT NULL DEFAULT 0,  -- computed viral score
    PRIMARY KEY (tenant_id, video_id, bucket)
);

Computing the viral score in one pass

The rollup is filled by a worker, not by a trigger. Triggers on the hot event table would serialize writes and murder ingest throughput. Instead I batch: read the last hour of new events, aggregate in Go, upsert into the rollup. Go, because the worker runs outside the PHP request path and I want tight control over batching and backpressure.

package scoring

import (
    "context"
    "time"

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

// RollUp aggregates one hour of raw events into video_score_hourly.
// velocity weights recent completions and shares more heavily than raw plays,
// which is what actually correlates with a clip going viral in our feeds.
func RollUp(ctx context.Context, pool *pgxpool.Pool, tenantID int64, hour time.Time) error {
    const q = `
        INSERT INTO video_score_hourly
            (tenant_id, video_id, bucket, plays, completes, shares, velocity)
        SELECT
            $1,
            video_id,
            date_trunc('hour', occurred_at) AS bucket,
            count(*) FILTER (WHERE event_type = 1) AS plays,
            count(*) FILTER (WHERE event_type = 5) AS completes,
            count(*) FILTER (WHERE event_type = 6) AS shares,
            -- weighted velocity: completions x3, shares x5, plays x1
            (count(*) FILTER (WHERE event_type = 1)
             + 3 * count(*) FILTER (WHERE event_type = 5)
             + 5 * count(*) FILTER (WHERE event_type = 6))::numeric
             / GREATEST(extract(epoch FROM (now() - min(occurred_at))) / 3600, 1)
        FROM video_event
        WHERE tenant_id = $1
          AND occurred_at >= $2
          AND occurred_at <  $2 + interval '1 hour'
        GROUP BY video_id, date_trunc('hour', occurred_at)
        ON CONFLICT (tenant_id, video_id, bucket) DO UPDATE SET
            plays     = EXCLUDED.plays,
            completes = EXCLUDED.completes,
            shares    = EXCLUDED.shares,
            velocity  = EXCLUDED.velocity;`

    _, err := pool.Exec(ctx, q, tenantID, hour)
    return err
}

Dividing by hours-since-first-event gives velocity a decay: a clip with 10k plays spread over a week scores lower than one with 3k plays in 40 minutes. That ratio is the difference between "popular" and "viral," and it's the whole reason the platform exists. The ON CONFLICT upsert makes the job idempotent — rerun it after a crash and you get the same numbers, not doubled ones.

The trending endpoint then reads only the small pre-aggregated table:

SELECT v.external_id, v.title, s.velocity
FROM video_score_hourly s
JOIN video v ON v.tenant_id = s.tenant_id AND v.id = s.video_id
WHERE s.bucket >= now() - interval '6 hours'
  AND 'DE' = ANY(v.region_codes)
ORDER BY s.velocity DESC
LIMIT 40;

RLS still applies here — both video_score_hourly and video carry the same policy, so the tenant_id join condition is belt-and-suspenders, not the actual security boundary.

GDPR shaped the schema more than performance did

Operating in the European market means the schema has to make three GDPR obligations cheap, because expensive compliance is compliance that gets skipped:

• Data minimization. Notice there's no user table in any of the above. We don't need accounts to rank videos, so we don't collect them. The session_hash in video_event is a salted hash rotated daily, not a stable identifier — it can count unique-ish sessions within a day without being personal data you must delete on request. If you don't store it, you don't have to protect it, delete it, or explain it to a regulator.
• Right to erasure. The 90-day partition drop is our erasure mechanism for behavioural data. There's no per-user delete because there's no per-user data. The heavy lifting is a DROP TABLE.
• Data residency. The region column on tenant and a region_codes array on video let us keep everything provably inside EU processing. Our edge sits on Cloudflare Workers, which terminate and cache close to the user; the origin only ever sees already-anonymised aggregates for the public trending feed.

A design smell I now watch for: if your "delete this user's data" story requires an UPDATE or DELETE that scans a hot table, you've built a compliance liability into your write path. Push deletion to partition boundaries or don't collect the data.

What I'd tell my past self

Three corrections, in the order the pain arrived:

1. Composite PK with tenant_id first, from day one. Retrofitting it means rebuilding every index on your largest tables. Do it before the tables are large.
2. RLS with FORCE and transaction-local set_config, not app-layer filtering. The engine never forgets a WHERE clause. Your busiest reporting query will.
3. Separate raw events from rollups, and never trigger between them. The write path and the read path have opposite needs; a trigger forces them to share a lock. Batch the aggregation in a worker instead.

The old four-SQLite-files setup wasn't wrong — SQLite in WAL mode behind LiteSpeed is genuinely fast for single-tenant reads, and I still run smaller brands that way. But the moment the product is cross-tenant — comparing what's viral across every European feed at once — the data has to live together, and shared-schema Postgres with RLS is the model that lets it live together without leaking. Design the isolation boundary into the engine, make deletion a metadata operation, and keep the hot path free of anything you'll later have to explain to a regulator. Everything else is just columns.