Designing a Video Streaming with PostgreSQL

Designing a Video Streaming Platform with PostgreSQL

Building a video streaming service demands a robust, scalable, and highly performant data layer. PostgreSQL, with its rich feature set—including advanced indexing, full-text search, JSON support, table partitioning, and powerful analytical capabilities—stands out as an exceptional choice for the backbone of such a platform. This tutorial walks you through designing a complete PostgreSQL schema for a video streaming application, from core metadata storage to user management, watch history, analytics, and performance optimization strategies.

What It Is

A video streaming database design encompasses the structured storage of video assets, user profiles, subscriptions, watch histories, playlists, comments, ratings, and playback analytics. Rather than storing raw video files directly in PostgreSQL (which is technically possible but rarely optimal), the database serves as the metadata and relationship layer that points to media files stored in object storage services like Amazon S3, Cloudflare R2, or a distributed file system. PostgreSQL manages everything around the video—its title, description, tags, encoding profiles, thumbnails, user interactions, and real-time analytics—while the heavy binary payloads live elsewhere.

The core components of such a design include:

• Video catalog tables — storing metadata, status, and references to media files
• User management tables — handling authentication, profiles, and subscription tiers
• Interaction tables — tracking watch history, likes, comments, and playlists
• Analytics tables — capturing playback events for business intelligence
• Encoding job tables — managing the video processing pipeline
• Monetization tables — handling billing, ad insertions, and revenue tracking

Why PostgreSQL Matters for Video Streaming

Choosing PostgreSQL over other database systems for a video streaming backend brings several concrete advantages:

• Full-text search — PostgreSQL's built-in tsvector and tsquery types enable fast, relevance-ranked search across video titles, descriptions, and transcripts without relying on an external search engine like Elasticsearch during early stages
• JSONB columns — allow flexible metadata schemas where each video can carry different sets of attributes (e.g., episodic metadata for TV shows vs. standalone metadata for films) without schema migrations
• Table partitioning — watch history and analytics tables grow at enormous rates; declarative partitioning by date or user hash lets you manage billions of rows efficiently
• BRIN indexes — perfect for time-series event data, offering tiny index sizes and fast range scans over massive tables
• Materialized views — precompute dashboard statistics like trending videos or aggregate watch counts, refreshing on a schedule to avoid expensive real-time queries
• Row-level security — enforces multi-tenancy or user-specific data access rules directly at the database level
• Advisory locks and LISTEN/NOTIFY — coordinate encoding worker processes and notify other services when a video finishes processing
• Proven reliability — ACID compliance ensures that billing transactions and subscription changes are never lost or corrupted

Step-by-Step Database Schema Design

Let's build the schema incrementally. All examples use PostgreSQL 15+ syntax. We'll start with the foundational tables and progressively add features.

1. The Users and Profiles Foundation

Every streaming platform begins with users. Here we separate authentication data from profile data, allowing multiple profiles per account (a common pattern in services like Netflix).

CREATE TABLE users (
    user_id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email           TEXT NOT NULL UNIQUE,
    password_hash   TEXT NOT NULL,
    subscription_tier TEXT NOT NULL DEFAULT 'free'
        CHECK (subscription_tier IN ('free', 'basic', 'premium', 'enterprise')),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    last_login_at   TIMESTAMPTZ
);

CREATE TABLE profiles (
    profile_id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id         BIGINT NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    display_name    TEXT NOT NULL,
    avatar_url      TEXT,
    age_rating_limit TEXT NOT NULL DEFAULT 'all'
        CHECK (age_rating_limit IN ('all', 'teen', 'adult')),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- One user can have many profiles; enforce a reasonable limit
CREATE INDEX idx_profiles_user_id ON profiles(user_id);

-- Quick lookup by email for login
CREATE INDEX idx_users_email ON users(email);

2. The Video Catalog Core

The videos table holds every piece of content. We use JSONB for flexible metadata and an ENUM-like CHECK constraint for processing status. The actual video files, thumbnails, and subtitle tracks are referenced by URLs pointing to object storage.

CREATE TABLE videos (
    video_id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title               TEXT NOT NULL,
    description         TEXT,
    slug                TEXT NOT NULL UNIQUE,
    duration_seconds    INTEGER,  -- NULL until encoding finishes
    thumbnail_url       TEXT,
    -- JSONB stores arbitrary metadata: cast, genre tags, release year, content ratings
    metadata            JSONB NOT NULL DEFAULT '{}',
    owner_user_id       BIGINT NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    visibility          TEXT NOT NULL DEFAULT 'private'
        CHECK (visibility IN ('private', 'unlisted', 'public')),
    processing_status   TEXT NOT NULL DEFAULT 'uploading'
        CHECK (processing_status IN (
            'uploading', 'queued', 'transcoding', 'ready', 'failed'
        )),
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    published_at        TIMESTAMPTZ  -- when visibility changed to 'public'
);

-- Full-text search index on title and description
CREATE INDEX idx_videos_fts ON videos
    USING GIN (to_tsvector('english', title || ' ' || description));

-- Index for browsing by owner
CREATE INDEX idx_videos_owner ON videos(owner_user_id, created_at DESC);

-- Index for public content listing
CREATE INDEX idx_videos_public ON videos(published_at DESC)
    WHERE visibility = 'public';

-- JSONB index for common metadata queries (e.g., filtering by genre)
CREATE INDEX idx_videos_metadata_gin ON videos USING GIN (metadata jsonb_path_ops);

3. Video Files and Encoding Profiles

A single video is transcoded into multiple resolutions and bitrates. We model this with a separate table that tracks each rendition, along with the encoding pipeline's progress.

CREATE TABLE video_files (
    file_id             BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    video_id            BIGINT NOT NULL REFERENCES videos(video_id) ON DELETE CASCADE,
    resolution          TEXT NOT NULL,  -- e.g., '1080p', '720p', '480p'
    bitrate_kbps        INTEGER,
    codec               TEXT,           -- e.g., 'h264', 'h265', 'av1'
    container           TEXT DEFAULT 'mp4',
    file_url            TEXT,
    file_size_bytes     BIGINT,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_video_files_video_id ON video_files(video_id);

-- Encoding job queue (can be consumed by worker processes)
CREATE TABLE encoding_jobs (
    job_id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    video_id        BIGINT NOT NULL REFERENCES videos(video_id) ON DELETE CASCADE,
    source_file_url TEXT NOT NULL,
    target_profile  TEXT NOT NULL,  -- JSON describing output specs
    status          TEXT NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending', 'in_progress', 'completed', 'failed')),
    worker_id       TEXT,           -- which worker claimed this job
    error_message   TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    completed_at    TIMESTAMPTZ
);

CREATE INDEX idx_encoding_jobs_status ON encoding_jobs(status, created_at);

4. Watch History — The High-Volume Table

Watch history is the largest table in a streaming platform. Users may generate millions of events daily. We use range partitioning by timestamp (monthly) and a BRIN index to keep things manageable.

CREATE TABLE watch_history (
    profile_id      BIGINT NOT NULL,
    video_id        BIGINT NOT NULL,
    progress_seconds INTEGER NOT NULL DEFAULT 0,  -- how far the user watched
    total_duration  INTEGER,                       -- total video duration at time of event
    event_type      TEXT NOT NULL DEFAULT 'play'
        CHECK (event_type IN ('play', 'pause', 'resume', 'complete', 'seek')),
    client_ip       INET,
    device_type     TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
)
PARTITION BY RANGE (created_at);

-- Create monthly partitions (automate this with pg_partman or a cron job)
CREATE TABLE watch_history_2025_01
    PARTITION OF watch_history
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE watch_history_2025_02
    PARTITION OF watch_history
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

-- BRIN index: tiny footprint, excellent for time-range queries
CREATE INDEX idx_watch_history_time ON watch_history USING BRIN (created_at);

-- Composite index for per-profile lookups within a time window
CREATE INDEX idx_watch_history_profile ON watch_history (profile_id, created_at DESC);

To automate partition creation, use PostgreSQL's built-in pg_partman extension or schedule a function like this:

CREATE OR REPLACE FUNCTION create_watch_history_partition(
    target_month DATE
) RETURNS void AS $$
DECLARE
    start_date TEXT;
    end_date   TEXT;
    table_name TEXT;
BEGIN
    start_date := to_char(target_month, 'YYYY-MM-01');
    end_date   := to_char(
        (target_month + INTERVAL '1 month')::date, 'YYYY-MM-01'
    );
    table_name := 'watch_history_' || to_char(target_month, 'YYYY_MM');
    
    EXECUTE format(
        'CREATE TABLE %I PARTITION OF watch_history
         FOR VALUES FROM (%L) TO (%L)',
        table_name, start_date, end_date
    );
END;
$$ LANGUAGE plpgsql;

5. Playlists and User-Generated Collections

Playlists link videos together in a user-defined order. We use a junction table with a position column for ordering.

CREATE TABLE playlists (
    playlist_id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    profile_id      BIGINT NOT NULL REFERENCES profiles(profile_id) ON DELETE CASCADE,
    title           TEXT NOT NULL,
    description     TEXT,
    visibility      TEXT NOT NULL DEFAULT 'private'
        CHECK (visibility IN ('private', 'shared', 'public')),
    thumbnail_url   TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE playlist_videos (
    playlist_id     BIGINT NOT NULL REFERENCES playlists(playlist_id) ON DELETE CASCADE,
    video_id        BIGINT NOT NULL REFERENCES videos(video_id) ON DELETE CASCADE,
    position        INTEGER NOT NULL DEFAULT 0,
    added_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (playlist_id, video_id)
);

CREATE INDEX idx_playlist_videos_order
    ON playlist_videos(playlist_id, position);

6. Comments and Social Interactions

A threaded comment system enables community engagement. We use a parent_comment_id self-referencing foreign key for replies.

CREATE TABLE video_comments (
    comment_id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    video_id            BIGINT NOT NULL REFERENCES videos(video_id) ON DELETE CASCADE,
    profile_id          BIGINT NOT NULL REFERENCES profiles(profile_id) ON DELETE CASCADE,
    parent_comment_id   BIGINT REFERENCES video_comments(comment_id) ON DELETE CASCADE,
    body_text           TEXT NOT NULL,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    is_edited           BOOLEAN NOT NULL DEFAULT false
);

-- Fetch top-level comments sorted by recency
CREATE INDEX idx_comments_video_top
    ON video_comments(video_id, created_at DESC)
    WHERE parent_comment_id IS NULL;

-- Fetch replies for a specific comment
CREATE INDEX idx_comments_parent ON video_comments(parent_comment_id, created_at);

-- User likes/dislikes with a unique constraint to prevent duplicates
CREATE TABLE video_reactions (
    profile_id      BIGINT NOT NULL REFERENCES profiles(profile_id) ON DELETE CASCADE,
    video_id        BIGINT NOT NULL REFERENCES videos(video_id) ON DELETE CASCADE,
    reaction_type   TEXT NOT NULL CHECK (reaction_type IN ('like', 'dislike', 'love')),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (profile_id, video_id)
);

7. Subscriptions and Monetization

For paid tiers, we track subscription periods, payments, and ad delivery rules for free-tier users.

CREATE TABLE subscription_plans (
    plan_id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name            TEXT NOT NULL,
    price_monthly_cents INTEGER NOT NULL,
    max_profiles    INTEGER DEFAULT 1,
    video_quality   TEXT DEFAULT '1080p',
    concurrent_streams INTEGER DEFAULT 1,
    features        JSONB NOT NULL DEFAULT '{}'
);

CREATE TABLE user_subscriptions (
    subscription_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id         BIGINT NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    plan_id         BIGINT NOT NULL REFERENCES subscription_plans(plan_id),
    started_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    expires_at      TIMESTAMPTZ NOT NULL,
    status          TEXT NOT NULL DEFAULT 'active'
        CHECK (status IN ('active', 'canceled', 'expired', 'past_due')),
    payment_method_id BIGINT
);

CREATE INDEX idx_subscriptions_user ON user_subscriptions(user_id, status);

-- Track ad insertions for free-tier content
CREATE TABLE ad_breaks (
    ad_break_id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    video_id        BIGINT NOT NULL REFERENCES videos(video_id) ON DELETE CASCADE,
    position_seconds INTEGER NOT NULL,  -- when in the video the ad plays
    ad_asset_url    TEXT NOT NULL,
    duration_seconds INTEGER NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_ad_breaks_video ON ad_breaks(video_id, position_seconds);

Full-Text Search for Video Discovery

PostgreSQL's full-text search allows users to find videos by title, description, or transcript content without external dependencies. Here's how to build a comprehensive search function:

-- Add a dedicated search vector column for better performance
ALTER TABLE videos ADD COLUMN search_vector tsvector;

-- Populate it from title and description (and optionally transcript)
UPDATE videos SET search_vector = 
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(description, '')), 'B');

-- Trigger to keep search_vector updated automatically
CREATE OR REPLACE FUNCTION videos_search_update() RETURNS trigger AS $$
BEGIN
    NEW.search_vector := 
        setweight(to_tsvector('english', coalesce(NEW.title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(NEW.description, '')), 'B');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_videos_search
    BEFORE INSERT OR UPDATE ON videos
    FOR EACH ROW EXECUTE FUNCTION videos_search_update();

-- Create index on the search vector
CREATE INDEX idx_videos_search ON videos USING GIN (search_vector);

-- Search function with ranking
CREATE OR REPLACE FUNCTION search_videos(
    query_text TEXT,
    result_limit INTEGER DEFAULT 50
) RETURNS TABLE(
    video_id BIGINT,
    title TEXT,
    description TEXT,
    rank REAL
) AS $$
BEGIN
    RETURN QUERY
    SELECT v.video_id, v.title, v.description,
           ts_rank(v.search_vector, plainto_tsquery('english', query_text)) AS rank
    FROM videos v
    WHERE v.visibility = 'public'
      AND v.search_vector @@ plainto_tsquery('english', query_text)
    ORDER BY rank DESC
    LIMIT result_limit;
END;
$$ LANGUAGE plpgsql STABLE;

Analytics and Materialized Views

Real-time aggregation over billions of watch events is expensive. Materialized views precompute dashboard data on a schedule (e.g., every hour). This approach keeps dashboards fast while accepting slightly stale data.

CREATE MATERIALIZED VIEW trending_videos AS
SELECT v.video_id,
       v.title,
       count(wh.profile_id) AS unique_viewers_last_24h,
       avg(wh.progress_seconds) AS avg_watch_progress
FROM videos v
JOIN watch_history wh ON wh.video_id = v.video_id
WHERE wh.created_at > now() - INTERVAL '24 hours'
  AND v.visibility = 'public'
GROUP BY v.video_id, v.title
ORDER BY count(wh.profile_id) DESC
LIMIT 100;

-- Refresh on schedule (call this from pg_cron or application scheduler)
-- REFRESH MATERIALIZED VIEW CONCURRENTLY trending_videos;

CREATE UNIQUE INDEX idx_trending_videos_id ON trending_videos(video_id);

For user-specific analytics, a summary table updated via triggers or batch jobs keeps per-video aggregate counts:

CREATE TABLE video_stats (
    video_id            BIGINT NOT NULL REFERENCES videos(video_id) ON DELETE CASCADE PRIMARY KEY,
    total_views         BIGINT NOT NULL DEFAULT 0,
    total_likes         BIGINT NOT NULL DEFAULT 0,
    total_comments      BIGINT NOT NULL DEFAULT 0,
    avg_watch_percentage NUMERIC(5,2),
    last_calculated_at  TIMESTAMPTZ,
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);

Performance Optimization Strategies

Using BRIN Indexes for Time-Series Data

For tables like watch_history that grow to billions of rows, traditional B-tree indexes become too large and slow to maintain. BRIN (Block Range INdex) indexes store summary data about contiguous blocks of rows, resulting in dramatically smaller indexes—often 1000x smaller than equivalent B-tree indexes. They work best when data arrives in roughly chronological order.

-- BRIN index on the timestamp column
CREATE INDEX idx_watch_history_brin_time
    ON watch_history USING BRIN (created_at)
    WITH (pages_per_range = 32);

-- BRIN index for a combination of columns
CREATE INDEX idx_watch_history_brin_profile_time
    ON watch_history USING BRIN (profile_id, created_at)
    WITH (pages_per_range = 64);

Partition Pruning for Query Acceleration

When querying partitioned tables, always include the partition key in WHERE clauses. PostgreSQL automatically eliminates partitions that cannot contain matching rows:

-- This query only scans the relevant monthly partition
SELECT video_id, progress_seconds
FROM watch_history
WHERE created_at BETWEEN '2025-02-15' AND '2025-02-20'
  AND profile_id = 42;

Connection Pooling with PgBouncer

A video streaming platform handles thousands of concurrent connections. Use PgBouncer in transaction mode between your application servers and PostgreSQL to multiplex connections efficiently. Configure your application to use a pooled connection string:

-- pgbouncer.ini excerpt
[databases]
streaming_db = host=localhost port=5432 dbname=streaming pool_size=50

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25

Hot Standby for Read Scaling

Separate write operations (uploads, comments, reactions) from read-heavy operations (browsing, search, loading watch history). Direct read queries to hot standby replicas:

-- Application-side routing pseudo-configuration
-- Write connection: primary-host:5432/streaming_db
-- Read connection:  replica-host:5432/streaming_db

-- On the replica, set:
-- ALTER SYSTEM SET hot_standby = on;
-- SELECT pg_reload_conf();

Handling Concurrent Writes Safely

Watch progress updates happen frequently and must not overwrite each other. Use INSERT ... ON CONFLICT (upsert) for idempotent progress tracking:

-- Dedicated table for current watch position per profile-video pair
CREATE TABLE watch_progress (
    profile_id          BIGINT NOT NULL,
    video_id            BIGINT NOT NULL,
    progress_seconds    INTEGER NOT NULL DEFAULT 0,
    last_updated_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (profile_id, video_id)
);

-- Upsert: insert if new, update if exists and progress is greater
INSERT INTO watch_progress (profile_id, video_id, progress_seconds, last_updated_at)
VALUES (42, 1001, 360, now())
ON CONFLICT (profile_id, video_id)
DO UPDATE SET
    progress_seconds = GREATEST(watch_progress.progress_seconds, EXCLUDED.progress_seconds),
    last_updated_at = EXCLUDED.last_updated_at;

Using LISTEN/NOTIFY for Encoding Pipeline Coordination

When a video finishes uploading, notify encoding workers without polling:

-- Function that fires when a video status changes to 'queued'
CREATE OR REPLACE FUNCTION notify_encoding_job() RETURNS trigger AS $$
BEGIN
    IF NEW.processing_status = 'queued' AND OLD.processing_status = 'uploading' THEN
        PERFORM pg_notify(
            'encoding_queue',
            json_build_object(
                'video_id', NEW.video_id,
                'source_url', NEW.metadata->>'source_file_url'
            )::text
        );
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_video_queued
    AFTER UPDATE ON videos
    FOR EACH ROW EXECUTE FUNCTION notify_encoding_job();

-- Workers listen: LISTEN encoding_queue;

Best Practices

• Separate media from metadata — Store video files, thumbnails, and subtitles in object storage (S3, GCS, R2) and keep only URLs in PostgreSQL. This keeps backups small and performance predictable
• Partition early, not late — Design watch history and analytics tables as partitioned from day one. Retrofitting partitioning on a 10-billion-row table is painful and requires downtime
• Use BRIN indexes for append-only time-series data — They stay compact even as tables grow to terabytes. Reserve B-tree indexes for columns that need exact lookups or unique constraints
• Leverage JSONB sparingly — JSONB is powerful for flexible metadata, but overusing it for core relational data defeats PostgreSQL's strengths. Keep frequently queried fields (title, status, duration) as dedicated columns
• Materialize aggregates — Don't run COUNT(*) over billion-row tables on every page load. Use materialized views, summary tables, or a caching layer (Redis) fed by batch jobs
• Implement soft deletion — Instead of actually deleting videos or comments, add a deleted_at column. This preserves referential integrity for existing watch history entries and enables undo functionality
• Use advisory locks for encoding jobs — When multiple worker processes compete for encoding tasks, use pg_try_advisory_lock() to ensure exactly one worker claims each job
• Plan for backup and point-in-time recovery — Use pg_basebackup and WAL archiving. For large databases, consider partial restores of only critical tables using the pg_dump -t option combined with partitioned table backups
• Monitor query performance relentlessly — Enable auto_explain with log_min_duration_statement set to catch slow queries early. Use EXPLAIN (ANALYZE, BUFFERS) to understand where time goes
• Index foreign keys consistently — Every foreign key column that appears in JOIN or WHERE clauses needs an index. Unindexed foreign keys cause sequential scans on related tables during deletes and cascading operations

Conclusion

Designing a video streaming platform's data layer with PostgreSQL is a rewarding engineering challenge that leverages the full depth of the database system. By combining thoughtful schema design, table partitioning, BRIN indexes, full-text search, materialized views, and LISTEN/NOTIFY coordination, you can build a backend that handles millions of users and billions of watch events while remaining maintainable and performant. The key insight is to treat PostgreSQL as the metadata and relationship engine—not a file server—and to plan for massive scale from the start through partitioning and appropriate indexing strategies. With the patterns laid out in this tutorial, you have a production-ready blueprint that scales from a prototype to a platform serving global audiences.