Why We Moved Video Metadata Primary Keys to PostgreSQL UUID v7 at Scale

Last quarter our ingestion pipeline started deadlocking during peak hours. Every 20 minutes, eight regional cron workers wake up, pull fresh streaming titles from provider APIs, and hammer the same three tables with INSERT ... ON CONFLICT. The workers were fighting over the same b-tree pages at the right edge of a BIGSERIAL index. Two regions inserting at once meant two backends both trying to split the rightmost leaf page of the primary key index. Under load, autovacuum couldn't keep up, index bloat climbed to 40%, and our p99 insert latency went from 4ms to 190ms. I run TrendVidStream, a multi-region video discovery service, and the fix that actually stuck was switching our primary keys from monotonic integers to UUID v7. This is the write-up I wish I'd had before I started.

The Problem With Both BIGSERIAL and UUID v4

Before UUID v7 I had exactly two options, and I hated both of them.

BIGSERIAL gives you small, sortable, cache-friendly keys. The downside is the hot right edge. Because every new row gets a key larger than the last, every insert lands on the same index page. With one writer that's fine. With eight concurrent regional writers plus a backfill job, that single page becomes a lock-contention magnet. You also leak information: a competitor can watch your id column increment and estimate exactly how many titles you ingest per day. For a discovery service where catalog size is a competitive signal, that's not nothing.

The classic escape hatch is UUID v4 — fully random 128-bit keys. Random keys spread inserts across the entire index, killing the hot-page contention. But random is a curse for a b-tree. Every insert lands on an unpredictable page, so your working set effectively becomes the entire index. On a table with 40 million video rows, the primary key index is several gigabytes. Random inserts mean constant cache misses, constant page reads from disk, and write amplification as the b-tree shuffles. We measured a 3x increase in shared_buffers churn when we prototyped v4. It solved contention and created an I/O problem.

UUID v7 is the thing that gets you out of the dilemma. Defined in RFC 9562 (the 2024 replacement for RFC 4122), it puts a 48-bit Unix millisecond timestamp in the most significant bits, followed by random data. The result is a 128-bit identifier that is time-ordered like a serial but globally unique like a v4. New rows still land near the right edge of the index — but because the low bits are random and the timestamp advances, concurrent writers spread across a small band of recent pages instead of fighting over one.

Here's what a v7 value looks like decomposed:

• Bits 0–47: Unix timestamp in milliseconds (big-endian)
• Bits 48–51: version field, always 0111 (7)
• Bits 52–63: 12 bits of randomness
• Bits 64–65: variant field, always 10
• Bits 66–127: 62 bits of randomness

That layout is the whole trick: sort a column of v7 UUIDs lexically and you get them in creation order, which is exactly what a b-tree wants.

Generating UUID v7 in PHP 8.4

My application layer is PHP 8.4. PHP core still doesn't ship a UUID generator, so I generate v7 in userland. This matters because I want the key assigned in the app, not the database — it lets me build parent/child relationships in memory before a single row hits Postgres, and it makes idempotent retries trivial since the key is deterministic for a given operation.

Here is the exact generator we run in production. It's dependency-free and works on any 64-bit PHP build:

<?php
declare(strict_types=1);

final class Uuidv7
{
    /**
     * Generate an RFC 9562 UUID v7 as a 36-char canonical string.
     * Timestamp is milliseconds; low bits are CSPRNG random.
     */
    public static function generate(?int $unixMs = null): string
    {
        $unixMs ??= (int) (microtime(true) * 1000);

        // 48-bit timestamp, big-endian, split into two 16-bit + one 16-bit chunk
        $timeHex = str_pad(dechex($unixMs), 12, '0', STR_PAD_LEFT);

        // 10 random bytes fill everything after the timestamp
        $rand = random_bytes(10);

        // Force version (7) into the high nibble of byte 6
        $rand[0] = chr((ord($rand[0]) & 0x0f) | 0x70);
        // Force variant (10xx) into the high bits of byte 8
        $rand[2] = chr((ord($rand[2]) & 0x3f) | 0x80);

        $randHex = bin2hex($rand);

        return sprintf(
            '%s-%s-%s-%s-%s',
            substr($timeHex, 0, 8),
            substr($timeHex, 8, 4),
            substr($randHex, 0, 4),
            substr($randHex, 4, 4),
            substr($randHex, 8, 12)
        );
    }

    /** Extract the creation timestamp back out of a v7 UUID. */
    public static function timestamp(string $uuid): int
    {
        $hex = str_replace('-', '', $uuid);
        return (int) hexdec(substr($hex, 0, 12));
    }
}

// Two IDs generated in the same request sort in creation order.
$a = Uuidv7::generate();
usleep(2000);
$b = Uuidv7::generate();
var_dump($a < $b); // bool(true)
var_dump(date('c', intdiv(Uuidv7::timestamp($a), 1000)));

The timestamp() helper is more useful than it looks. Because the creation time is embedded in the key, I can answer "how many titles did region EU-West ingest between 14:00 and 14:20" without a separate created_at index — I range-scan the primary key directly. That deleted an entire index from my schema.

The Postgres Schema

On the database side I store keys as the native uuid type, never as text or varchar(36). The native type is 16 bytes; the string form is 37 bytes and forces byte-by-byte collation comparisons. Getting this wrong doubles your index size and wrecks comparison speed.

CREATE TABLE video (
    id           uuid PRIMARY KEY,
    provider     text        NOT NULL,
    external_id  text        NOT NULL,
    title        text        NOT NULL,
    region       text        NOT NULL,
    duration_s   integer     NOT NULL,
    published_at timestamptz NOT NULL,
    metadata     jsonb       NOT NULL DEFAULT '{}',
    UNIQUE (provider, external_id)
);

-- Postgres 18 can generate v7 server-side if you ever want a DB default.
-- We keep app-side generation but this is the fallback:
-- ALTER TABLE video ALTER COLUMN id SET DEFAULT uuidv7();

CREATE TABLE video_region_stat (
    video_id  uuid REFERENCES video(id) ON DELETE CASCADE,
    region    text NOT NULL,
    rank      integer NOT NULL,
    views_24h bigint  NOT NULL,
    PRIMARY KEY (video_id, region)
);

A few schema decisions worth calling out:

• Keep the UNIQUE (provider, external_id) constraint. The UUID is the internal identity; the provider's ID is the external identity used for upserts. Conflating them is a classic mistake — you want to upsert on the natural key and let the surrogate v7 key be assigned once, on first insert.
• The metadata jsonb column holds the region-specific fields that vary per provider. This is where FTS lives (more below).
• Foreign keys reference uuid, so joins compare 16-byte integers, not strings.

Measuring the Difference

I don't trust migrations I can't measure, so I ran the same 10-million-row insert benchmark three ways on identical hardware (Postgres 16, 8 vCPU, shared_buffers=2GB). Eight concurrent writers, batches of 500, mimicking our regional cron fan-out.

Key type	Inserts/sec	Index bloat after run	p99 insert latency
BIGSERIAL	41,000	38%	190ms (under contention)
UUID v4	22,500	9%	71ms
UUID v7	39,800	6%	9ms

UUID v7 gave me nearly the raw throughput of BIGSERIAL with the low bloat of random keys and dramatically better tail latency, because writers spread across a band of recent pages instead of colliding on one. The v4 numbers show exactly the I/O penalty I described — throughput nearly halved because the b-tree working set exploded.

Here's the Python harness I used, in case you want to reproduce it against your own hardware. It's deliberately close to how our real ingest workers behave:

import os
import time
import secrets
import psycopg
from concurrent.futures import ThreadPoolExecutor

DSN = os.environ["PG_DSN"]
BATCH = 500
WRITERS = 8
ROWS_PER_WRITER = 250_000


def uuid7() -> str:
    unix_ms = int(time.time() * 1000)
    rand = bytearray(secrets.token_bytes(10))
    rand[0] = (rand[0] & 0x0F) | 0x70  # version 7
    rand[2] = (rand[2] & 0x3F) | 0x80  # variant 10
    h = f"{unix_ms:012x}{rand.hex()}"
    return f"{h[0:8]}-{h[8:12]}-{h[12:16]}-{h[16:20]}-{h[20:32]}"


def worker(region: str) -> None:
    with psycopg.connect(DSN) as conn:
        with conn.cursor() as cur:
            rows = []
            for i in range(ROWS_PER_WRITER):
                rows.append((
                    uuid7(), "yt", f"{region}-{i}",
                    f"Title {i}", region, 240,
                ))
                if len(rows) >= BATCH:
                    cur.executemany(
                        """INSERT INTO video
                           (id, provider, external_id, title, region,
                            duration_s, published_at)
                           VALUES (%s,%s,%s,%s,%s,%s, now())
                           ON CONFLICT (provider, external_id) DO NOTHING""",
                        rows,
                    )
                    conn.commit()
                    rows.clear()


if __name__ == "__main__":
    regions = ["us", "gb", "de", "fr", "in", "br", "au", "ca"]
    start = time.time()
    with ThreadPoolExecutor(max_workers=WRITERS) as pool:
        list(pool.map(worker, regions))
    total = WRITERS * ROWS_PER_WRITER
    print(f"{total / (time.time() - start):,.0f} inserts/sec")

Run it, then check bloat with pgstattuple on the primary key index. The difference between v4 and v7 is stark on any spinning-disk or network-attached storage where cache misses hurt.

Where UUID v7 Actually Pays Off Beyond Inserts

The throughput win was what forced the migration, but the properties that keep me happy day to day are downstream.

Keyset pagination is trivial. Because keys are time-ordered, "give me the next 50 titles after this one" is a plain WHERE id > $last ORDER BY id LIMIT 50. No OFFSET, no separate sort column, and it stays fast at page 10,000. Our discovery feed is one giant keyset scan over v7 IDs.

Merging regional shards is deterministic. Each of our eight regions ingests independently. When I need a global "newest across all regions" view, I merge eight per-region streams. Because every ID encodes its millisecond timestamp, a simple k-way merge on the ID gives correct chronological order across shards with no coordination and no global sequence.

Idempotency for retries. Our workers deploy over FTP to LiteSpeed hosts and sometimes get killed mid-batch by a timeout. Since the app assigns the key and we upsert on (provider, external_id), a retried batch is a no-op instead of a duplicate. The v7 key generated on retry differs, but ON CONFLICT DO NOTHING on the natural key means the first-write key wins and stays stable.

Debuggability. When something looks wrong, I can eyeball an ID and know roughly when the row was created. SELECT id FROM video WHERE id > '018f...' is a time filter in disguise. That has saved me during incident triage more than once.

Combining v7 Keys With SQLite FTS5 on the Edge

Here's a wrinkle specific to my stack. Postgres is my system of record, but each edge node runs SQLite with FTS5 for local full-text search — it's tiny, embeddable, and ships as a single file to every host. The question was whether v7 UUIDs would play nicely as external content-table row identifiers in FTS5.

They do, with one caveat: FTS5 wants an integer rowid internally, so I keep an INTEGER PRIMARY KEY alias locally and store the v7 UUID as a regular indexed column that maps back to Postgres. The v7 UUID remains the cross-system join key; the local integer is just an FTS5 implementation detail.

-- Edge node SQLite schema
CREATE TABLE video_local (
    rowid_alias INTEGER PRIMARY KEY,
    uuid        TEXT NOT NULL UNIQUE,   -- the v7 key from Postgres
    title       TEXT NOT NULL,
    region      TEXT NOT NULL
);

CREATE VIRTUAL TABLE video_fts USING fts5(
    title,
    content='video_local',
    content_rowid='rowid_alias'
);

-- Search returns the v7 UUID, which the app resolves against Postgres.
SELECT v.uuid, v.title
FROM video_fts f
JOIN video_local v ON v.rowid_alias = f.rowid
WHERE video_fts MATCH 'documentary'
ORDER BY rank
LIMIT 20;

The lesson: v7 UUIDs are a fine global identity, but respect each engine's preferences for its physical row identity. SQLite is happiest with a 64-bit integer rowid; Postgres is happiest with a native uuid. Store the v7 value in both, use it as the join key across systems, and don't force either engine to use it as its physical clustering key against its will.

Migration Notes From the Trenches

Migrating a live 40-million-row table without downtime took planning. The approach that worked:

• Add a nullable uuid column alongside the existing BIGSERIAL, backfill it in batches ordered by the old id (so the v7 timestamps roughly track true creation order), then add the unique index concurrently.
• Dual-write during transition. For a week, new rows got both a serial and a v7 UUID. This let me validate every foreign-key rewrite before committing.
• Rewrite foreign keys in dependency order, children before parents, using the mapping table. Do this with CREATE INDEX CONCURRENTLY so you never take a long lock.
• Flip the primary key last, in a single fast transaction, once every FK references the UUID column.
• Keep the old serial column for a release as a safety net, then drop it once you're confident.

The one thing I'd stress: backfill the v7 values ordered by the original serial id so historical rows retain approximately correct chronological ordering. If you generate them in random order during backfill, you lose the time-ordering property for your existing data and your keyset pagination over old rows breaks.

Conclusion

UUID v7 is not a silver bullet, but for a write-heavy, multi-writer, multi-region metadata store it hits a genuinely rare sweet spot: the insert locality of a sequence, the uniqueness and non-enumerability of a random UUID, and free time-ordering that collapses a whole category of secondary indexes and pagination logic. My insert p99 dropped from 190ms to 9ms, index bloat fell from 38% to 6%, and I deleted a created_at index I no longer needed. If you're running concurrent writers against a BIGSERIAL hot edge — or you already fled to v4 and got punished by I/O — v7 is very likely the key type you actually wanted. Generate it in your app for idempotency, store it as native uuid in Postgres, and respect each engine's physical rowid preferences on the edge.