Building a Multi-Tenant Video Platform Schema That Scales in PostgreSQL

Six months into running video discovery for multiple regional front-ends, I hit the wall every multi-tenant system eventually hits: a single SELECT * FROM videos query started leaking rows from one tenant's catalog into another tenant's homepage. Nothing exploded, no alarm fired — a German-region feed just quietly showed three videos that belonged to a Japan-region tenant. That is the worst kind of bug, because it is silent, it is a data-isolation breach, and it is almost always a schema design problem rather than an application bug.

I run DailyWatch, a free video discovery platform, and the original stack was deliberately boring: PHP 8.4, SQLite with FTS5 for search, LiteSpeed in front, Cloudflare at the edge. SQLite is wonderful until you need real concurrent writes across many tenants and one shared analytics surface. When we outgrew a single-file database, PostgreSQL was the obvious move — but "just add a tenant_id column" is where most teams design themselves into a corner. This article is the schema I wish I'd designed on day one: how to model tenants, videos, channels, playback events, and search in PostgreSQL so that isolation is enforced by the database, not by a developer remembering to add a WHERE clause.

The three multi-tenancy models, and why I picked shared-schema

There are three ways to isolate tenants in PostgreSQL, and the trade-off is always the same: isolation strength versus operational cost.

• Database-per-tenant. Strongest isolation, but 500 tenants means 500 databases to migrate, back up, and monitor. Connection pooling becomes a nightmare because each pooled connection is bound to one database. Great for a handful of enterprise customers, terrible for a discovery platform with many small regional tenants.
• Schema-per-tenant. One database, one PostgreSQL schema per tenant. Migrations must run N times, and search_path juggling is error-prone. The catalog table pg_class bloats once you pass a few thousand schemas.
• Shared-schema with a tenant_id column. One set of tables, every row tagged with its owner. Cheapest to operate, easiest to run cross-tenant analytics on, and — critically — it can be made just as safe as the other two using PostgreSQL Row-Level Security (RLS).

For a video discovery platform where tenants are regional catalogs sharing the same code and the same deploy, shared-schema is the right call. The danger is isolation, and RLS closes that gap. Let's build it.

The core tables

Start with the tenant as a first-class entity, not an afterthought column. Every tenant-owned table carries tenant_id as part of its identity, and I make it the leading column of the primary key on the hot tables so that index locality follows tenant access patterns.

CREATE TABLE tenant (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    slug         text NOT NULL UNIQUE,
    display_name text NOT NULL,
    region       text NOT NULL DEFAULT 'US',
    created_at   timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE channel (
    tenant_id   bigint NOT NULL REFERENCES tenant(id),
    id          bigint GENERATED ALWAYS AS IDENTITY,
    youtube_id  text NOT NULL,
    title       text NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (tenant_id, id),
    UNIQUE (tenant_id, youtube_id)
);

CREATE TABLE video (
    tenant_id    bigint NOT NULL REFERENCES tenant(id),
    id           bigint GENERATED ALWAYS AS IDENTITY,
    channel_id   bigint NOT NULL,
    youtube_id   text NOT NULL,
    title        text NOT NULL,
    description  text,
    duration_s   integer NOT NULL DEFAULT 0,
    published_at timestamptz,
    view_count   bigint NOT NULL DEFAULT 0,
    created_at   timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (tenant_id, id),
    UNIQUE (tenant_id, youtube_id),
    FOREIGN KEY (tenant_id, channel_id) REFERENCES channel(tenant_id, id)
);

Two things are doing a lot of work here. First, the composite foreign key (tenant_id, channel_id) REFERENCES channel(tenant_id, id). This is the single most important line in the whole schema. It makes it physically impossible for a video in tenant A to reference a channel in tenant B, because the referenced pair must match on tenant_id. A plain channel_id FK cannot express that constraint, and cross-tenant references are exactly how data leaks start.

Second, the UNIQUE (tenant_id, youtube_id) on video. The same YouTube video can legitimately exist in the US catalog and the Japan catalog as two independent rows with independent view counts and moderation state. A global unique on youtube_id would be wrong. Scoping uniqueness to the tenant is almost always what you want in shared-schema designs.

Enforcing isolation with Row-Level Security

Composite keys stop structural leaks. RLS stops query leaks — the forgotten WHERE tenant_id = ?. The idea: set a session variable to the current tenant when a request begins, and let PostgreSQL append the tenant filter to every query automatically.

ALTER TABLE video   ENABLE ROW LEVEL SECURITY;
ALTER TABLE channel ENABLE ROW LEVEL SECURITY;

-- Force RLS even for the table owner (default policies are bypassed by owners)
ALTER TABLE video   FORCE ROW LEVEL SECURITY;
ALTER TABLE channel FORCE ROW LEVEL SECURITY;

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

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

Now a query that forgets its tenant filter returns zero rows instead of everyone's rows. That is the correct failure mode: a missing filter should fail closed, not fail open. The FORCE ROW LEVEL SECURITY line matters more than people realize — without it, the role that owns the tables (often the same role your app connects as) bypasses the policy entirely, and your carefully written RLS does nothing in production.

The one rule you must never break: set app.tenant_id at the start of every request and reset it at the end. If you use a connection pool (you should), a connection carrying tenant 7's setting can be handed to a request for tenant 12. Use transaction-scoped settings so the value dies with the transaction.

Here is the PHP 8.4 side. I wrap every request in a transaction and use SET LOCAL, which is automatically scoped to the current transaction and cleaned up on commit or rollback.

<?php
declare(strict_types=1);

final class TenantContext
{
    public function __construct(private \PDO $pdo) {}

    /**
     * Runs $work inside a transaction with app.tenant_id pinned.
     * SET LOCAL is transaction-scoped, so a pooled connection can
     * never leak this tenant's setting into the next request.
     */
    public function scoped(int $tenantId, callable $work): mixed
    {
        $this->pdo->beginTransaction();
        try {
            $stmt = $this->pdo->prepare(
                "SET LOCAL app.tenant_id = :tid"
            );
            // set_config is safer than string-interpolating the id
            $this->pdo->prepare(
                "SELECT set_config('app.tenant_id', :tid, true)"
            )->execute(['tid' => (string) $tenantId]);

            $result = $work($this->pdo);
            $this->pdo->commit();
            return $result;
        } catch (\Throwable $e) {
            $this->pdo->rollBack();
            throw $e;
        }
    }
}

// Usage in a controller
$context = new TenantContext($pdo);
$videos = $context->scoped($currentTenantId, function (\PDO $db): array {
    // No WHERE tenant_id needed — RLS enforces it.
    $stmt = $db->query(
        "SELECT id, title, view_count
           FROM video
          ORDER BY published_at DESC
          LIMIT 40"
    );
    return $stmt->fetchAll(\PDO::FETCH_ASSOC);
});

Notice the query has no WHERE tenant_id = ... at all. The policy adds it. That is the whole point: isolation stops being a discipline problem and becomes a schema guarantee. set_config(..., true) with the third argument true means "local to transaction," the same lifetime as SET LOCAL, and it accepts a bound parameter so you avoid interpolating an integer into SQL.

Playback events: the table that will dwarf everything else

On a discovery platform, the catalog is small and the event stream is enormous. Every play, pause, and completion is a row, and this table grows by two orders of magnitude faster than video. Modeling it wrong means your analytics queries slow down for every tenant as any tenant grows. The fix is native range partitioning by time, combined with the tenant column for pruning.

CREATE TABLE playback_event (
    tenant_id  bigint NOT NULL,
    video_id   bigint NOT NULL,
    event_type text   NOT NULL,   -- 'start' | 'complete' | 'skip'
    occurred_at timestamptz NOT NULL,
    session_id uuid NOT NULL,
    PRIMARY KEY (tenant_id, occurred_at, session_id, video_id)
) PARTITION BY RANGE (occurred_at);

CREATE TABLE playback_event_2026_07 PARTITION OF playback_event
    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

CREATE TABLE playback_event_2026_08 PARTITION OF playback_event
    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

-- Composite index that serves the common per-tenant time-window query
CREATE INDEX ON playback_event_2026_07 (tenant_id, video_id, occurred_at);

Partitioning by month gives you three wins at once. Dropping old data becomes an instant DROP TABLE playback_event_2026_01 instead of a multi-hour DELETE that bloats the table and thrashes autovacuum. Queries that filter on a date range only touch the relevant partitions (partition pruning). And each partition's indexes stay small enough to live in memory. The tenant_id-leading index means one tenant's analytics scans never read another tenant's event pages off disk.

RLS applies to partitioned tables too — enable it on the parent and every partition inherits the policy. Don't forget to ENABLE ROW LEVEL SECURITY on playback_event, or your largest, most sensitive table becomes the one with no isolation.

Creating next month's partition should be automated. Here is the Python job I run from cron; it is idempotent, so running it twice is harmless.

import psycopg
from datetime import date
from dateutil.relativedelta import relativedelta

def ensure_partition(conn, months_ahead: int = 2) -> None:
    """Create playback_event partitions up to N months ahead."""
    today = date.today().replace(day=1)
    with conn.cursor() as cur:
        for offset in range(months_ahead + 1):
            start = today + relativedelta(months=offset)
            end = start + relativedelta(months=1)
            name = f"playback_event_{start:%Y_%m}"
            cur.execute(
                """
                CREATE TABLE IF NOT EXISTS {name}
                PARTITION OF playback_event
                FOR VALUES FROM (%s) TO (%s)
                """.format(name=name),
                (start.isoformat(), end.isoformat()),
            )
            cur.execute(
                f"CREATE INDEX IF NOT EXISTS {name}_tvo "
                f"ON {name} (tenant_id, video_id, occurred_at)"
            )
    conn.commit()

if __name__ == "__main__":
    with psycopg.connect("dbname=discovery") as conn:
        ensure_partition(conn, months_ahead=2)
        print("partitions ensured")

Run it on the first of every month with a couple of months of runway, so you never wake up to inserts failing because the target partition doesn't exist yet. Table names can't be bound parameters, so I build them from a date format string — safe here because the value is machine-generated, never user input.

Search without SQLite FTS5

On the original stack, SQLite's FTS5 handled search beautifully for a single catalog. In PostgreSQL, the equivalent is a tsvector column with a GIN index, and the multi-tenant twist is that your search index must also respect tenant boundaries. RLS handles the boundary for free once the base table has a policy, but you still want the index laid out so a search never scans across tenants.

ALTER TABLE video ADD COLUMN search tsvector
    GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(description, '')), 'B')
    ) STORED;

-- Partial-free GIN over the generated column; RLS still filters tenant
CREATE INDEX video_search_idx ON video USING gin (search);
CREATE INDEX video_tenant_pub ON video (tenant_id, published_at DESC);

A generated tsvector column keeps the index in sync automatically — no triggers, no application code that can forget to reindex on update. Title matches weighted A rank above description matches weighted B, which is exactly the relevance ordering a discovery front-end wants.

The search query, again with no explicit tenant filter because RLS supplies it:

SELECT id, title, ts_rank(search, query) AS rank
  FROM video, websearch_to_tsquery('english', 'space documentary') query
 WHERE search @@ query
 ORDER BY rank DESC
 LIMIT 20;

websearch_to_tsquery is the function to reach for on a public site: it accepts the kind of input real users type — quoted phrases, or, leading minus for exclusion — without throwing syntax errors on characters that to_tsquery chokes on. Behind LiteSpeed and Cloudflare, the actual latency users feel is dominated by the edge cache anyway, but a GIN-indexed tsvector keeps origin search under a few milliseconds even when the cache misses.

Connection pooling is where RLS quietly breaks

Everything above is correct and still fails in production if you get pooling wrong. The failure looks like this: you use a session-level SET app.tenant_id (not SET LOCAL), a pooler like PgBouncer in transaction mode hands the connection to another request mid-session, and now tenant 7 is reading tenant 12's data with tenant 12's setting still attached. This is a real, exploitable isolation bug, and it passes every test that runs on a single connection.

The rules that keep it safe:

• Always use transaction-scoped settings (SET LOCAL or set_config(..., true)), never session-scoped ones. The setting must die when the transaction ends.
• Wrap the tenant setting and the queries in one transaction. If the SET LOCAL and the query land in different transactions on a transaction-pooled connection, the query runs with no tenant set and RLS returns zero rows — annoying, but at least it fails closed.
• Never cache a connection with a tenant pinned to it at the application layer.

Here is a Go version of the same pattern, using pgx and a transaction so the setting can't outlive the request. Go's explicit transaction handling makes the lifetime boundary hard to get wrong.

package tenant

import (
    "context"
    "fmt"

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

// Scoped runs fn inside a transaction with app.tenant_id set locally,
// so RLS filters every query and the setting never escapes the tx.
func Scoped(
    ctx context.Context,
    pool *pgxpool.Pool,
    tenantID int64,
    fn func(pgx.Tx) error,
) error {
    tx, err := pool.Begin(ctx)
    if err != nil {
        return fmt.Errorf("begin: %w", err)
    }
    defer tx.Rollback(ctx) // no-op after a successful commit

    // set_config(..., true) == transaction-local, parameterized.
    _, err = tx.Exec(ctx,
        "SELECT set_config('app.tenant_id', $1, true)",
        fmt.Sprintf("%d", tenantID),
    )
    if err != nil {
        return fmt.Errorf("set tenant: %w", err)
    }

    if err := fn(tx); err != nil {
        return err
    }
    return tx.Commit(ctx)
}

The defer tx.Rollback(ctx) is a cheap safety net: after a successful Commit, the rollback is a no-op, but on any early return the transaction — and its app.tenant_id — is torn down immediately. That is the invariant you are protecting: the tenant setting and the transaction share exactly one lifetime.

A few decisions I'd defend

bigint identity, not uuid, for internal keys. UUIDs as primary keys hurt index locality and bloat the enormous playback_event table. I keep uuid only for session_id, where it comes from the client and randomness is the point. Tenant and video ids are sequential bigint.

timestamptz, always, never timestamp. A global platform has events from every timezone. Store everything in UTC with timestamptz and convert at the edge. timestamp without a zone is a bug waiting for the next daylight-saving transition.

Uniqueness scoped to tenant. UNIQUE (tenant_id, youtube_id) lets the same source video live independently in multiple catalogs. Reach for a global unique only on genuinely global things like the tenant.slug.

Composite foreign keys everywhere a child references a parent. They are slightly more verbose, and they make cross-tenant references structurally impossible. That trade is always worth it.

Wrapping up

Multi-tenancy fails silently when isolation depends on every developer remembering a WHERE clause. The schema here moves that guarantee into the database: composite primary and foreign keys make cross-tenant references impossible to write, Row-Level Security makes cross-tenant reads impossible to forget, transaction-scoped settings make pooled connections safe, and time-based partitioning keeps the event firehose from dragging down the catalog. Start with FORCE ROW LEVEL SECURITY and transaction-local set_config, verify with a test that a missing tenant setting returns zero rows rather than everyone's rows, and you have a shared-schema platform that is as isolated as database-per-tenant at a fraction of the operational cost. The silent leak that started this whole migration hasn't recurred since — not because we got more careful, but because the schema no longer lets it happen.