← Back to DevBytes
Database July 07, 2026 9 min read

Designing a Video Streaming with Database Sharding

Designing a Video Streaming Platform with Database Sharding

Building a video streaming service that serves millions of users requires handling massive amounts of data: user profiles, watch history, video metadata, comments, recommendations, and more. As the user base grows, a single monolithic database becomes a bottleneck. Database sharding is a proven strategy to scale horizontally by splitting data across multiple independent databases. This tutorial walks you through the entire process—from understanding the concept to implementing a sharded architecture for a video streaming application, complete with practical code examples.

What Is Database Sharding?

Database sharding is the process of partitioning a large database into smaller, faster, more manageable pieces called shards. Each shard is a separate database instance running on its own server or cluster. Data is distributed based on a chosen shard key, ensuring that rows related to a particular key (for example, a user or a video) reside on the same shard. This allows horizontal scaling, as you can add more shards to handle increased load, rather than endlessly upgrading a single server (vertical scaling).

In a video streaming context, sharding often targets tables that grow extremely large—user activity logs, comment threads, playback sessions, and video metadata. By splitting these tables across shards, query performance remains fast and the system can handle millions of concurrent users.

Why Sharding Matters for Video Streaming

Choosing a Shard Key

The shard key determines how data is distributed. For a video streaming service, two common approaches exist:

Many real-world implementations use a hybrid model: user-facing transactional data sharded by user ID, and content metadata sharded by video ID. This tutorial will demonstrate both patterns with practical schema and code.

Designing the Sharded Schema

Let’s design tables for a video streaming service and decide how to shard them. We’ll use PostgreSQL as the database engine, but the principles apply to MySQL, CockroachDB, and others.

We’ll define three logical databases (shards) for user-centric data, and three for video-centric data. In production, each logical shard runs as a separate physical PostgreSQL instance. For development, we simulate multiple shards using different schemas or databases on the same server.

User-Centric Shard (Shard Key: user_id)

Tables stored in this shard:

The shard number is derived by hashing the user ID and applying modulo to the total shard count. For example:

-- Determine shard: hash user_id and mod by number of user shards
-- Shard index = hash('user_123') % 3   (assuming 3 shards)

Video-Centric Shard (Shard Key: video_id)

Tables stored here:

Shard index calculated similarly:

-- Shard index = hash('video_987') % 3

Implementing Sharding Logic in Application Code

Your application server (e.g., a Node.js, Python, or Java backend) must route each query to the correct shard. A common pattern is to use a shard router that resolves the shard connection based on the shard key present in the request.

Below is a Python example using raw database connections and a simple hash-based shard resolver. In production, you’d use connection pooling and possibly a sharding framework, but this illustrates the core logic clearly.

import hashlib
import psycopg2

# Configuration: mapping of shard index to connection parameters
SHARD_CONFIG = {
    0: {'host': 'shard0.example.com', 'dbname': 'video_user_shard0'},
    1: {'host': 'shard1.example.com', 'dbname': 'video_user_shard1'},
    2: {'host': 'shard2.example.com', 'dbname': 'video_user_shard2'},
}

VIDEO_SHARD_CONFIG = {
    0: {'host': 'shard-video0.example.com', 'dbname': 'video_content_shard0'},
    1: {'host': 'shard-video1.example.com', 'dbname': 'video_content_shard1'},
    2: {'host': 'shard-video2.example.com', 'dbname': 'video_content_shard2'},
}

def get_user_shard_index(user_id: str, num_shards: int = 3) -> int:
    """Hash the user_id and map to a shard index."""
    hash_bytes = hashlib.sha256(user_id.encode()).digest()
    # Convert first 8 bytes to an integer for modulo operation
    hash_int = int.from_bytes(hash_bytes[:8], 'big')
    return hash_int % num_shards

def get_video_shard_index(video_id: str, num_shards: int = 3) -> int:
    """Hash the video_id for content shard routing."""
    hash_bytes = hashlib.sha256(video_id.encode()).digest()
    hash_int = int.from_bytes(hash_bytes[:8], 'big')
    return hash_int % num_shards

def get_user_shard_connection(user_id: str):
    index = get_user_shard_index(user_id)
    config = SHARD_CONFIG[index]
    conn = psycopg2.connect(**config)
    return conn

def get_video_shard_connection(video_id: str):
    index = get_video_shard_index(video_id)
    config = VIDEO_SHARD_CONFIG[index]
    conn = psycopg2.connect(**config)
    return conn

With these helpers, every data access layer method picks the appropriate connection. For example, saving a watch history entry:

def save_watch_event(user_id: str, video_id: str, timestamp: float, progress_seconds: int):
    conn = get_user_shard_connection(user_id)
    cursor = conn.cursor()
    # Table: watch_history (user_id, video_id, timestamp, progress_seconds)
    cursor.execute(
        "INSERT INTO watch_history (user_id, video_id, timestamp, progress_seconds) VALUES (%s, %s, %s, %s)",
        (user_id, video_id, timestamp, progress_seconds)
    )
    conn.commit()
    cursor.close()
    conn.close()

And fetching comments for a video goes to the video shard:

def get_comments_for_video(video_id: str, limit: int = 50):
    conn = get_video_shard_connection(video_id)
    cursor = conn.cursor()
    cursor.execute(
        "SELECT comment_id, user_id, text, created_at FROM comments WHERE video_id = %s ORDER BY created_at DESC LIMIT %s",
        (video_id, limit)
    )
    rows = cursor.fetchall()
    cursor.close()
    conn.close()
    return rows

Handling Cross-Shard Queries

Some queries naturally span multiple shards. For example, “show me all videos I’ve watched recently” is straightforward because it resides entirely on the user’s shard. But “show the top 10 trending videos globally” requires aggregating data from all video shards. This is a scatter-gather operation.

Implementation pattern using Python asyncio to query all shards concurrently:

import asyncio
import asyncpg  # async PostgreSQL driver

async def get_trending_videos_across_shards(limit=10):
    # Query each video shard in parallel
    shard_tasks = []
    for shard_idx in range(3):
        config = VIDEO_SHARD_CONFIG[shard_idx]
        task = asyncpg.connect(**config)
        shard_tasks.append(task)
    connections = await asyncio.gather(*shard_tasks)
    
    # Execute query on all shards
    query = "SELECT video_id, score FROM video_stats ORDER BY score DESC LIMIT %s"
    results = await asyncio.gather(*[
        conn.fetch(query, limit) for conn in connections
    ])
    
    # Merge results, sort globally, and take top limit
    merged = []
    for result in results:
        merged.extend(result)
    merged.sort(key=lambda row: row['score'], reverse=True)
    final = merged[:limit]
    
    # Close connections
    for conn in connections:
        await conn.close()
    
    return final

This approach works well for moderate numbers of shards. For hundreds of shards, you might use a dedicated analytics pipeline (like Spark or a materialized view) rather than live scatter-gather.

Sharding the Video File Storage (CDN Integration)

While database sharding handles metadata, the actual video files (mp4, HLS segments) are typically stored on a content delivery network (CDN) or object storage like S3. However, sharding logic can still apply to how you map video IDs to storage buckets to avoid hotspotting. For example, you can hash the video ID to choose an S3 bucket prefix:

def get_video_storage_prefix(video_id: str) -> str:
    # Use first few characters of SHA256 to create a distributed path
    hash_hex = hashlib.sha256(video_id.encode()).hexdigest()
    prefix = f"videos/{hash_hex[:3]}/{hash_hex[3:6]}/{video_id}"
    return prefix

This ensures even distribution across the object store namespace, preventing thousands of files landing in a single partition that could slow down listing operations.

Best Practices for Sharding a Video Streaming Database

Example: Building a Shard-Aware Repository in TypeScript

Here’s a simplified TypeScript snippet for a Node.js backend using a shard router with a connection pool per shard. It demonstrates the repository pattern isolating shard selection.

import crypto from 'crypto';
import { Pool } from 'pg';

// Shard connection pools (in real code, read from config)
const userShardPools: Map = new Map([
  [0, new Pool({ host: 'shard0', database: 'user_shard0' })],
  [1, new Pool({ host: 'shard1', database: 'user_shard1' })],
  [2, new Pool({ host: 'shard2', database: 'user_shard2' })],
]);

function getUserShardIndex(userId: string): number {
  const hash = crypto.createHash('sha256').update(userId).digest();
  // Take first 4 bytes as unsigned integer
  const num = hash.readUInt32BE(0);
  return num % userShardPools.size;
}

export class UserWatchRepository {
  async addWatchRecord(userId: string, videoId: string, timestamp: number, progress: number): Promise {
    const shardIndex = getUserShardIndex(userId);
    const pool = userShardPools.get(shardIndex)!;
    const client = await pool.connect();
    try {
      await client.query(
        `INSERT INTO watch_history (user_id, video_id, timestamp, progress_seconds) VALUES ($1, $2, $3, $4)`,
        [userId, videoId, timestamp, progress]
      );
    } finally {
      client.release();
    }
  }

  async getRecentWatchHistory(userId: string, limit: number = 20): Promise {
    const shardIndex = getUserShardIndex(userId);
    const pool = userShardPools.get(shardIndex)!;
    const client = await pool.connect();
    try {
      const res = await client.query(
        `SELECT video_id, timestamp, progress_seconds FROM watch_history WHERE user_id = $1 ORDER BY timestamp DESC LIMIT $2`,
        [userId, limit]
      );
      return res.rows;
    } finally {
      client.release();
    }
  }
}

This repository hides shard complexity from the rest of the application. Controllers simply call repo.addWatchRecord(...) without caring about shard location.

Conclusion

Database sharding transforms a video streaming platform from a fragile monolithic database into a resilient, horizontally scalable system. By carefully selecting shard keys based on dominant access patterns, implementing a clean routing layer, and following best practices like consistent hashing and aggressive caching, you can serve millions of concurrent users while maintaining low latency. The code examples in this tutorial—from Python shard routing to TypeScript repositories—provide a solid foundation you can adapt to your own stack. Remember, sharding is not a silver bullet; it introduces complexity. But for a growing video service, mastering it is essential to keep your data architecture as performant and reliable as your content delivery.

🚀 Need a reliable AI agent for your project?

Deploy Hermes Agent in 10 minutes. Managed hosting, zero DevOps.

Get Started — $23.99/mo
← Back to all articles