Adopting PostgreSQL UUID v7 Primary Keys for Video Metadata at Scale

Every European evening our ingestion pipeline hits the same wall. A clip goes viral in Warsaw or Lisbon, and within minutes our crawlers are writing tens of thousands of new metadata rows — titles, thumbnail hashes, region-scoped view deltas, GDPR-safe engagement signals — into a single videos table. For two years that table used a uuid primary key generated with UUID v4, and every one of those insert storms was slower than it should have been. Index pages fragmented, the write-ahead log ballooned, and our p99 insert latency climbed from ~4 ms to over 30 ms whenever a trend spiked. This is a write-up of how we moved from random UUID v4 keys to time-ordered UUID v7 on the metadata core at ViralVidVault, with the numbers, runnable code, and the gotchas that bit us.

If you run any high-insert Postgres workload — analytics events, IoT readings, message logs, or in our case viral-video metadata — this is one of the cheapest performance wins available. You change how keys are generated, not your schema shape, and the B-tree stops fighting you.

The real cost of random primary keys

A UUID v4 is 122 bits of randomness. As a primary key that is a beautiful property for uniqueness and an awful one for a B-tree index. Postgres stores the primary key in a B-tree ordered by value. When the key is random, every insert lands at an unpredictable point in the tree.

Here is what that actually costs you at ingestion time:

• Page splits everywhere. A new random key frequently targets a page that is already full, forcing a split. Splits are extra I/O and they leave pages ~50% full, so your index is physically larger than it needs to be.
• Cache thrashing. Sequential keys keep the "hot" right-most leaf pages in shared_buffers. Random keys touch old, cold pages scattered across the whole index, so you evict useful pages and read from disk far more often.
• WAL amplification. With full_page_writes on (the default, and you should keep it on), the first modification of a page after a checkpoint writes the entire 8 KB page to the WAL. Random inserts dirty a huge spread of pages between checkpoints, so the WAL volume explodes. On our old setup a trend spike could push WAL generation from ~20 MB/min to over 200 MB/min — which then slows replication to our read replica and Cloudflare-fronted edge caches.
• Index bloat and vacuum pressure. Half-full pages plus constant churn means autovacuum works harder just to keep the index from degrading.

None of this shows up in a benchmark that inserts 1,000 rows. It shows up at 3 a.m. CET when a football clip detonates across five countries at once and the table already has 400 million rows.

Why UUID v7 and not bigint or v4

The obvious "fix" is a bigserial/bigint identity column. Sequential integers are the friendliest possible B-tree keys — every insert appends to the right-most page, splits are rare, and the WAL stays lean. We rejected them anyway, for reasons specific to a European viral-discovery product:

• They leak business intelligence. A monotonically increasing integer in a URL or API response tells a competitor exactly how many videos you ingest per day. For a discovery product where crawl breadth is a moat, that is a real leak.
• They complicate multi-writer ingestion. We ingest from several regional workers. Coordinating a single global sequence across them, or stitching per-worker ranges back together, is friction we did not want.
• They are painful to generate client-side. We frequently want the ID before the row is written (to build the object graph, enqueue thumbnail jobs, and emit the row to Cloudflare Workers KV in the same request). A DB-assigned integer forces a round trip.

UUID v7 keeps every advantage of a UUID — 128-bit, globally unique, generatable anywhere, no leaked counts — while fixing the one thing that made v4 slow: ordering. A v7 value is prefixed with a millisecond Unix timestamp, so values generated close in time sort close together. Inserts land at the right-hand edge of the B-tree, exactly like a sequence, and all four problems above largely evaporate.

Anatomy of a UUID v7

The layout is defined by RFC 9562 (which finalized the format in 2024). The 128 bits break down like this:

• Bits 0–47 — unix_ts_ms, a 48-bit big-endian Unix timestamp in milliseconds. This is the ordering prefix.
• Bits 48–51 — the version field, fixed to 0111 (7).
• Bits 52–63 — 12 bits of randomness (rand_a).
• Bits 64–65 — the variant field, fixed to 10.
• Bits 66–127 — 62 bits of randomness (rand_b).

That leaves 74 bits of entropy per millisecond — about 1.8 × 10²² values before you should worry about a same-millisecond collision. For our ingestion rate (peaks around 40k inserts/second, so ~40 values per millisecond) the collision risk is not measurable. The timestamp is millisecond-granular, which means values created in the same millisecond are ordered only by their random tail — fine for index locality, but not a guarantee of strict per-row monotonicity. If you need that, RFC 9562 describes an optional method that replaces some random bits with a sub-millisecond counter; we did not need it.

Generating UUID v7 inside PostgreSQL

PostgreSQL 18 ships a native uuidv7() function, so on current servers you write nothing:

-- PostgreSQL 18+ has a native generator
CREATE TABLE videos (
    id          uuid PRIMARY KEY DEFAULT uuidv7(),
    platform    text        NOT NULL,
    region      text        NOT NULL,
    title       text        NOT NULL,
    ingested_at timestamptz NOT NULL DEFAULT now()
);

-- On PostgreSQL 14–17, add a small PL/pgSQL fallback.
-- Requires the pgcrypto extension for gen_random_bytes().
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE OR REPLACE FUNCTION uuidv7() RETURNS uuid AS $$
DECLARE
    ts_ms      bigint := floor(extract(epoch FROM clock_timestamp()) * 1000)::bigint;
    uuid_bytes bytea;
BEGIN
    -- 6 timestamp bytes (low 48 bits of the bigint) + 10 random bytes
    uuid_bytes := substring(int8send(ts_ms) FROM 3 FOR 6)
                  || gen_random_bytes(10);
    -- set version to 7 in the high nibble of byte 6
    uuid_bytes := set_byte(uuid_bytes, 6,
                  (get_byte(uuid_bytes, 6) & 15) | 112);
    -- set the variant to 10xx in byte 8
    uuid_bytes := set_byte(uuid_bytes, 8,
                  (get_byte(uuid_bytes, 8) & 63) | 128);
    RETURN encode(uuid_bytes, 'hex')::uuid;
END;
$$ LANGUAGE plpgsql VOLATILE;

The substring(... FROM 3 FOR 6) trick keeps the low 48 bits of the 8-byte big-endian integer. The two set_byte calls stamp the version and variant. Postgres accepts a 32-character hex string directly as a uuid, so no dashes are required in the cast.

Generating them in the application layer with PHP 8.4

Much of our stack is PHP 8.4 fronted by LiteSpeed. We generate the ID in the application so we can reference it before the insert, enqueue the thumbnail fetch, and write a summary row to Cloudflare Workers KV in the same request cycle. No external library needed:

<?php
declare(strict_types=1);

function uuidv7(): string
{
    // 48-bit Unix timestamp in milliseconds
    $unixTsMs = (int) floor(microtime(true) * 1000);

    // 10 random bytes for version, variant and the random tail
    $rand = random_bytes(10);

    // pack the timestamp as a 64-bit big-endian value, keep the low 48 bits
    $bytes  = substr(pack('J', $unixTsMs), 2, 6);
    $bytes .= $rand;

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

    $hex = bin2hex($bytes);
    return sprintf(
        '%s-%s-%s-%s-%s',
        substr($hex, 0, 8),
        substr($hex, 8, 4),
        substr($hex, 12, 4),
        substr($hex, 16, 4),
        substr($hex, 20, 12)
    );
}

// e.g. 0190f8c2-8a3d-7b41-9e02-4c7f1a2b3c4d
echo uuidv7(), PHP_EOL;

The only subtlety is pack('J', ...), which produces an unsigned 64-bit big-endian integer; slicing bytes 2–7 gives the 48-bit timestamp prefix. random_bytes() is cryptographically secure, so the tail has real entropy — do not swap it for mt_rand() to shave microseconds.

Python and Go crawlers

Our regional crawlers are a mix of Python and Go, and both need to mint the same key format so the ingestion API can trust IDs generated at the edge. In Python:

import os
import time
import uuid

def uuidv7() -> uuid.UUID:
    unix_ts_ms = int(time.time() * 1000)
    ts_bytes = unix_ts_ms.to_bytes(6, byteorder="big")
    tail = bytearray(os.urandom(10))
    # version 7 in the high nibble of byte 6 (tail[0])
    tail[0] = (tail[0] & 0x0F) | 0x70
    # variant 10xx in byte 8 (tail[2])
    tail[2] = (tail[2] & 0x3F) | 0x80
    return uuid.UUID(bytes=bytes(ts_bytes) + bytes(tail))

if __name__ == "__main__":
    print(uuidv7())

Python 3.14 added uuid.uuid7() to the standard library, so on newer runtimes you can delete the helper entirely. We keep the explicit version for workers still pinned to 3.12. The Go crawlers use an equivalent 16-byte builder:

package main

import (
    "crypto/rand"
    "encoding/hex"
    "fmt"
    "time"
)

func UUIDv7() (string, error) {
    var b [16]byte
    if _, err := rand.Read(b[6:]); err != nil {
        return "", err
    }

    ms := uint64(time.Now().UnixMilli())
    b[0] = byte(ms >> 40)
    b[1] = byte(ms >> 32)
    b[2] = byte(ms >> 24)
    b[3] = byte(ms >> 16)
    b[4] = byte(ms >> 8)
    b[5] = byte(ms)

    b[6] = (b[6] & 0x0f) | 0x70 // version 7
    b[8] = (b[8] & 0x3f) | 0x80 // variant 10xx

    return fmt.Sprintf("%s-%s-%s-%s-%s",
        hex.EncodeToString(b[0:4]),
        hex.EncodeToString(b[4:6]),
        hex.EncodeToString(b[6:8]),
        hex.EncodeToString(b[8:10]),
        hex.EncodeToString(b[10:16]),
    ), nil
}

func main() {
    id, _ := UUIDv7()
    fmt.Println(id)
}

All three implementations produce byte-identical layouts, which matters: the same clip crawled by a Python worker in Frankfurt and validated by a Go service in the API tier must round-trip through Postgres without surprises.

Migrating without downtime

We could not take the videos table offline, so we did an online swap. The shape:

• Add a new column id_v7 uuid DEFAULT uuidv7(). New inserts get a v7 key immediately; old rows are backfilled lazily.
• Backfill in bounded batches so autovacuum keeps up and we never hold a long transaction that pins the xmin horizon.
• Build the unique index CONCURRENTLY so reads and writes continue, then promote it to the primary key using that index.

-- 1. add the new column; new rows get a v7 key right away
ALTER TABLE videos ADD COLUMN id_v7 uuid DEFAULT uuidv7();

-- 2. backfill old rows in batches (run repeatedly until 0 rows affected)
UPDATE videos SET id_v7 = uuidv7()
WHERE id IN (
    SELECT id FROM videos WHERE id_v7 IS NULL LIMIT 50000
);

-- 3. build the unique index without blocking traffic
CREATE UNIQUE INDEX CONCURRENTLY videos_id_v7_uidx ON videos (id_v7);

-- 4. swap the primary key to the new column, reusing the index
ALTER TABLE videos
    DROP CONSTRAINT videos_pkey,
    ADD CONSTRAINT videos_pkey PRIMARY KEY USING INDEX videos_id_v7_uidx;

Foreign keys were the real work, not the table itself. We ran the old and new keys in parallel for a week, updated referencing tables to carry both columns, cut foreign keys over one relationship at a time, and only then dropped the legacy id. The application read a feature flag so we could roll back the ID used in URLs instantly if anything looked wrong at the Cloudflare edge.

What the numbers looked like

On our staging replica of the production videos table (~380 million rows, same hardware class as production), inserting a fresh batch of 5 million rows into a freshly built table:

• Insert throughput rose from roughly 41k rows/sec (v4) to 78k rows/sec (v7) — a bit under 1.9× on this box.
• Primary-key index size after the load dropped ~28%, because pages stayed near-full instead of splitting to half-full.
• WAL generated for the batch fell by about 35%, which we felt downstream as calmer replication lag during spikes.
• p99 insert latency during a simulated trend spike went from ~31 ms back down to ~6 ms.

Your mileage will vary with row width, fillfactor, checkpoint settings, and how random your v4 workload really was, but the direction is reliable: ordered keys are strictly kinder to a B-tree than random ones. The best part is that nothing about query patterns changed — point lookups by primary key are identical, and range scans over the ordering prefix ("everything ingested in the last hour") are now essentially free because the key itself is time-ordered.

Gotchas worth knowing before you commit

UUID v7 is not a free lunch. The things that surprised us:

• The timestamp is not a secret. Anyone holding a v7 value can read the creation time to the millisecond. That is usually fine, but if an ID is exposed to untrusted users and the creation time is sensitive, do not use v7 there. For public video IDs we decided the ingest time is not confidential; for internal audit records that were more sensitive, we kept random v4.
• Ordering is by millisecond, not per row. Two rows created in the same millisecond can sort in either order. If you rely on the key for strict event ordering, add an explicit ingested_at timestamptz plus a tiebreaker, or use the monotonic sub-millisecond counter variant.
• Clock skew across workers matters. Because the prefix is wall-clock time, a worker with a badly skewed clock will emit keys that sort out of place and slightly hurt insert locality. Run NTP everywhere; we alert if any crawler drifts more than 250 ms.
• Storage is still 16 bytes. A bigint is 8. If key size in every downstream index is your bottleneck, v7 does not fix that — it fixes ordering, not width. Store it as native uuid, never as text, or you double the size and lose the fast comparison path.
• Do not v7-ify everything reflexively. Small lookup tables, config, and low-write reference data gain nothing. This pays off specifically on high-insert, ever-growing tables.

Conclusion

Moving the video-metadata core from UUID v4 to UUID v7 was the rare change that is small in code, large in effect, and low in risk. We kept every property we liked about UUIDs — client-side generation, no leaked row counts, multi-writer ingestion with no coordination — and gave the B-tree the sequential-ish key it wanted all along. Insert throughput nearly doubled, the primary-key index shrank by more than a quarter, and our worst-case latency during viral spikes dropped back into single-digit milliseconds.

If you are on Postgres 18, uuidv7() is one DEFAULT clause away. If you are on 14–17, the ten-line PL/pgSQL function above (or generating in your app, as we do) gets you there today. Measure your own WAL volume and index bloat first so you can prove the win, migrate the primary key online with CREATE INDEX CONCURRENTLY, and keep v4 around for the handful of tables where a readable creation timestamp is a leak rather than a feature.