Database Sharding Strategies for Growing Video Platforms

Sharding Is Not Your First Problem

Most video platforms can serve millions of rows from a single PostgreSQL instance with proper indexing. Design your schema to be shardable from day one, but shard only when necessary.

TrendVidStream serves 8 regions — UAE, Finland, Czech Republic, Denmark, Belgium, UK, Switzerland, US. The natural partition keys are region and fetched_at.

Strategy 1: PostgreSQL List Partitioning by Region

CREATE TABLE videos (
    id           BIGSERIAL,
    video_id     TEXT NOT NULL,
    title        TEXT NOT NULL,
    region       TEXT NOT NULL,
    category_id  INT,
    view_count   BIGINT DEFAULT 0,
    fetched_at   TIMESTAMPTZ DEFAULT NOW(),
    embed_broken BOOLEAN DEFAULT FALSE,
    title_lang   TEXT,
    PRIMARY KEY (id, region)
) PARTITION BY LIST (region);

CREATE TABLE videos_ae PARTITION OF videos FOR VALUES IN ('AE');
CREATE TABLE videos_fi PARTITION OF videos FOR VALUES IN ('FI');
CREATE TABLE videos_cz PARTITION OF videos FOR VALUES IN ('CZ');
CREATE TABLE videos_dk PARTITION OF videos FOR VALUES IN ('DK');
CREATE TABLE videos_be PARTITION OF videos FOR VALUES IN ('BE');
CREATE TABLE videos_ch PARTITION OF videos FOR VALUES IN ('CH');
CREATE TABLE videos_gb PARTITION OF videos FOR VALUES IN ('GB');
CREATE TABLE videos_us PARTITION OF videos FOR VALUES IN ('US');
CREATE TABLE videos_other PARTITION OF videos DEFAULT;

-- Indexes propagate to all partitions automatically
CREATE INDEX idx_videos_views ON videos (view_count DESC);
CREATE INDEX idx_videos_fetched ON videos (fetched_at DESC);

Strategy 2: Sub-Partition by Date for Event Data

CREATE TABLE video_events (
    id       BIGSERIAL,
    video_id TEXT NOT NULL,
    region   TEXT NOT NULL,
    event    TEXT NOT NULL,
    occurred TIMESTAMPTZ DEFAULT NOW()
) PARTITION BY LIST (region);

CREATE TABLE video_events_ae PARTITION OF video_events
    FOR VALUES IN ('AE')
    PARTITION BY RANGE (occurred);

CREATE TABLE video_events_ae_2026q1 PARTITION OF video_events_ae
    FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');

CREATE TABLE video_events_ae_2026q2 PARTITION OF video_events_ae
    FOR VALUES FROM ('2026-04-01') TO ('2026-07-01');

Strategy 3: Application-Level Shard Routing

import asyncpg

SHARD_MAP = {
    'AE': 'postgresql://user:pass@db-mena/trendvidstream',
    'GB': 'postgresql://user:pass@db-eu-west/trendvidstream',
    'BE': 'postgresql://user:pass@db-eu-west/trendvidstream',
    'CH': 'postgresql://user:pass@db-eu-central/trendvidstream',
    'CZ': 'postgresql://user:pass@db-eu-central/trendvidstream',
    'DK': 'postgresql://user:pass@db-eu-north/trendvidstream',
    'FI': 'postgresql://user:pass@db-eu-north/trendvidstream',
    'US': 'postgresql://user:pass@db-us/trendvidstream',
}

_pools: dict[str, asyncpg.Pool] = {}

async def shard_pool(region: str) -> asyncpg.Pool:
    dsn = SHARD_MAP.get(region, SHARD_MAP['US'])
    if dsn not in _pools:
        _pools[dsn] = await asyncpg.create_pool(dsn, min_size=2, max_size=10)
    return _pools[dsn]

async def trending(region: str, limit: int = 50) -> list[dict]:
    pool = await shard_pool(region)
    async with pool.acquire() as conn:
        rows = await conn.fetch(
            'SELECT video_id, title, thumbnail_url, view_count FROM videos '
            'WHERE region = $1 ORDER BY view_count DESC LIMIT $2', region, limit,
        )
    return [dict(r) for r in rows]

Cross-Shard Global Trending

import asyncio

async def global_trending(limit: int = 20) -> list[dict]:
    regions = list(dict.fromkeys(SHARD_MAP.keys()))
    per_shard = await asyncio.gather(*[trending(r, limit) for r in regions],
                                    return_exceptions=True)
    merged = []
    for result in per_shard:
        if isinstance(result, list):
            merged.extend(result)
    merged.sort(key=lambda v: v['view_count'], reverse=True)
    return merged[:limit]

For TrendVidStream, the global trending page completes in 12ms at current scale. The shard routing layer is ready for when that grows.

Migration Checklist

1. Add region to every table's primary key.
2. Add PostgreSQL list partitions (ATTACH PARTITION is zero-downtime).
3. Move hot partitions to read replicas as read load grows.
4. Introduce shard routing when write throughput demands separate hosts.
5. Never shard prematurely — each step adds operational complexity.

---

This article is part of the Building TrendVidStream series. Check out TrendVidStream to see these techniques in action.