Why We Moved Video Metadata Primary Keys From Bigserial To UUID v7

Our nightly ingest window is 90 minutes. Nine Asia-Pacific regions, roughly a dozen upstream feeds each, ~180,000 rows of video metadata upserted into a single Postgres table that already held 41 million rows. Last November that job started running 70 minutes, then 88, then it blew through the window and collided with the morning cache warm. Nothing had changed in the query plan. What had changed was the size of the primary key index relative to shared_buffers: 2.9 GB of B-tree holding nothing but random 128-bit values, at roughly 65% page fill, thrashing every single insert against a different page.

That table backs TopVideoHub, a multi-language trending-video aggregator built on PHP 8.4 with a Postgres system of record and per-region SQLite FTS5 edge indexes for CJK search. This post is what we actually did about it — moving from random UUID v4 to RFC 9562 UUID v7 — including the generator code in three languages, the numbers we measured, and the four things that bit us.

Why v4 was the wrong default for append-heavy metadata

We picked UUID v4 for the usual reasons. Video rows are created by workers in three regions, ids need to be generatable client-side before the row exists (we build the FTS payload and the CDN purge list in the same transaction), and we did not want a central sequence in the hot path. All of that is still true. Randomness was never the requirement — decentralized generation was, and we conflated the two.

The cost of full randomness in a B-tree is specific and measurable:

• Page splits everywhere. Every insert lands in an unpredictable leaf page. Postgres splits a full leaf 50/50, and those halves rarely fill again. Steady-state fill factor on our v4 PK index sat around 65%.
• Full-page writes in WAL. After a checkpoint, the first write to any page writes the whole page into WAL. Random inserts touch thousands of distinct pages per batch instead of one hot page, so the WAL volume per ingest was dominated by FPWs.
• Zero cache locality. The working set of a random index is the whole index. On a 16 GB box with 4 GB shared_buffers, a 2.9 GB index that is only ever touched randomly evicts everything else.
• No usable ordering. ORDER BY id was meaningless, so every feed query needed a separate published_at index and an OFFSET-based paginator.

UUID v7 fixes the first three by construction and hands you the fourth for free.

What UUID v7 actually encodes

RFC 9562 (May 2024) standardized v7 as a 128-bit layout:

• bits 0–47 — Unix timestamp in milliseconds, big-endian, unsigned
• bits 48–51 — version, always 0111
• bits 52–63 — rand_a, 12 bits the RFC lets you use as a sub-millisecond counter
• bits 64–65 — variant, always 10
• bits 66–127 — rand_b, 62 random bits

The important consequence: because Postgres compares uuid values as a plain 16-byte memcmp, and SQLite compares BLOB the same way, byte order equals time order. Sorting by the key sorts by creation time in both stores, with no extra column and no extra index.

The 12-bit counter is what makes ordering stable within a millisecond. If you leave rand_a random, ids created in the same millisecond shuffle relative to each other, which breaks keyset pagination in exactly the way that is hardest to reproduce in staging. Uniqueness never depends on the counter — that is the job of the 62 random bits — so the counter can wrap or repeat without any practical collision risk.

Generating v7 in PHP 8.4

ramsey/uuid 4.7+ has Uuid::uuid7() and symfony/uid has UuidV7, and both are fine choices. We ship our own for two reasons: we want the raw 16-byte form without a value object allocation per row (we generate ~180k per night inside a tight loop), and we want to control the monotonic counter so it survives a backwards clock step.

<?php
declare(strict_types=1);

final class UuidV7
{
    private static int $lastMs = 0;
    private static int $counter = 0;

    /** Returns the raw 16 bytes, not a formatted string. */
    public static function binary(): string
    {
        $ms = (int) (microtime(true) * 1000);

        if ($ms === self::$lastMs) {
            self::$counter++;
            if (self::$counter > 0x0FFF) {
                // >4096 ids in one ms: borrow the next millisecond
                self::$lastMs = ++$ms;
                self::$counter = 0;
            }
        } elseif ($ms < self::$lastMs) {
            // NTP stepped us backwards: hold the line, keep counting
            $ms = self::$lastMs;
            self::$counter++;
        } else {
            self::$lastMs = $ms;
            // seed low so a busy ms has headroom before it borrows
            self::$counter = random_int(0, 0x00FF);
        }

        // 48-bit ts | 4-bit version (0111) | 12-bit counter
        $hi = pack('J', ($ms << 16) | 0x7000 | (self::$counter & 0x0FFF));

        $lo = random_bytes(8);
        $lo[0] = chr((ord($lo[0]) & 0x3F) | 0x80); // variant 10

        return $hi . $lo;
    }

    public static function format(string $bin): string
    {
        $h = bin2hex($bin);
        return substr($h, 0, 8) . '-' . substr($h, 8, 4) . '-'
             . substr($h, 12, 4) . '-' . substr($h, 16, 4) . '-'
             . substr($h, 20, 12);
    }

    public static function createdAt(string $bin): float
    {
        ['hi' => $hi] = unpack('Jhi', substr($bin, 0, 8));
        return ($hi >> 16) / 1000.0;
    }
}

One note on the pack('J', ...) line, since it looks like it should overflow. Current epoch milliseconds are about 1.77e12, which is under 2^41; shifting left 16 gives a value under 2^57, comfortably inside PHP's signed 64-bit int. This code stops being correct in the year 10889, which we have accepted as technical debt.

The counter is static, which means it is per-process. Under LiteSpeed's PHP SAPI each worker holds its own, and workers on different machines obviously do not coordinate. That is fine: two workers can produce ids in the same millisecond with the same counter value, and the 62 random bits still separate them. The counter only guarantees ordering within a generator, which is the property keyset pagination actually needs.

Making Postgres agree

Postgres 18 ships uuidv7() and uuid_extract_timestamp() in core, which is the whole story if you are already there. We are on 17 in production, so the defaults come from a SQL function. This is the widely-circulated overlay() trick, and it is worth reading closely because it is doing bit surgery on the output of gen_random_uuid():

CREATE OR REPLACE FUNCTION uuid_generate_v7() RETURNS uuid AS $$
  SELECT encode(
    set_bit(
      set_bit(
        overlay(
          uuid_send(gen_random_uuid())
          PLACING substring(
            int8send((extract(epoch FROM clock_timestamp()) * 1000)::bigint)
            FROM 3
          )
          FROM 1 FOR 6
        ),
        52, 1
      ),
      53, 1
    ),
    'hex')::uuid;
$$ LANGUAGE sql VOLATILE;

CREATE TABLE videos (
    id            uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
    provider      text        NOT NULL,
    provider_ref  text        NOT NULL,
    region        char(2)     NOT NULL,
    title         text        NOT NULL,
    title_lang    text        NOT NULL DEFAULT 'und',
    duration_s    integer     NOT NULL,
    published_at  timestamptz NOT NULL,
    ingested_at   timestamptz NOT NULL DEFAULT now(),
    stats         jsonb       NOT NULL DEFAULT '{}'::jsonb,
    UNIQUE (provider, provider_ref)
) WITH (fillfactor = 90);

-- PG < 18: pull the timestamp back out of the key
CREATE OR REPLACE FUNCTION uuid_v7_ts(u uuid) RETURNS timestamptz AS $$
  SELECT to_timestamp(
    ('x0000' || substring(u::text, 1, 8) || substring(u::text, 10, 4))::bit(64)::bigint
    / 1000.0
  );
$$ LANGUAGE sql IMMUTABLE STRICT;

The database default is a safety net, not the main path — 99% of our ids arrive from the application because we need them before the INSERT. But having a correct default means a psql session, a migration, or an ops script never accidentally introduces a v4 into a table whose whole design assumes v7.

Store it as uuid, never as text. The uuid type is 16 bytes and compares with memcmp; the canonical string form is 37 bytes with a varlena header and compares with collation-aware machinery. On 41 million rows that difference alone was over 800 MB of heap.

Time-range scans without a timestamp index

Because the leading 48 bits are a timestamp, a range of ids is a range of time. You can build the bounds without any function call on the column, which keeps the plan on a plain index scan:

-- everything ingested in the last 6 hours, straight off the PK
SELECT id, title, region
FROM videos
WHERE id >= (
        lpad(to_hex((extract(epoch FROM now() - interval '6 hours') * 1000)::bigint), 12, '0')
        || '7000-8000-000000000000'
      )::uuid
ORDER BY id DESC
LIMIT 200;

That is not a replacement for published_at — upstream publish time and our ingest time are different things, and we index both. But it removed an entire class of "what did the 04:00 run actually touch" operational query from needing an index at all, and it made keyset pagination trivial: WHERE id < :cursor ORDER BY id DESC LIMIT 40, with the cursor being the last id on the previous page. No OFFSET, no ties, no second sort column.

Bulk ingest from the Go workers

Our regional fetchers are Go binaries that normalize provider payloads and hand batches to Postgres over COPY. Generating ids in the worker means the FTS payload and the Cloudflare purge list can be built in the same pass, before the transaction commits.

package ingest

import (
    "context"
    "crypto/rand"
    "encoding/binary"
    "sync"
    "time"

    "github.com/jackc/pgx/v5"
    "github.com/jackc/pgx/v5/pgxpool"
)

type V7Gen struct {
    mu     sync.Mutex
    lastMs int64
    seq    uint16
}

func (g *V7Gen) Next() [16]byte {
    g.mu.Lock()
    ms := time.Now().UnixMilli()
    switch {
    case ms == g.lastMs:
        g.seq++
        if g.seq > 0x0FFF {
            g.lastMs++
            ms, g.seq = g.lastMs, 0
        }
    case ms < g.lastMs:
        ms = g.lastMs
        g.seq++
    default:
        g.lastMs, g.seq = ms, 0
    }
    seq := g.seq & 0x0FFF
    g.mu.Unlock()

    var id [16]byte
    binary.BigEndian.PutUint64(id[0:8], uint64(ms)<<16|0x7000|uint64(seq))
    rand.Read(id[8:16])
    id[8] = id[8]&0x3F | 0x80
    return id
}

type Video struct {
    Provider, Ref, Region, Title, Lang string
    Duration                          int32
    PublishedAt                       time.Time
    ID                                [16]byte
}

func Bulk(ctx context.Context, pool *pgxpool.Pool, g *V7Gen, rows []Video) (int64, error) {
    src := pgx.CopyFromSlice(len(rows), func(i int) ([]any, error) {
        rows[i].ID = g.Next()
        r := rows[i]
        return []any{r.ID, r.Provider, r.Ref, r.Region, r.Title, r.Lang, r.Duration, r.PublishedAt}, nil
    })

    return pool.CopyFrom(ctx,
        pgx.Identifier{"videos_stage"},
        []string{"id", "provider", "provider_ref", "region", "title", "title_lang", "duration_s", "published_at"},
        src)
}

We copy into an unlogged videos_stage table and then do a single INSERT ... SELECT ... ON CONFLICT (provider, provider_ref) DO UPDATE. Because the staged ids are already in ascending order, the merge into the real table appends to the right edge of the PK index instead of scattering.

The numbers

Measured on our primary — 4 vCPU, 16 GB RAM, NVMe, Postgres 17.4, shared_buffers = 4GB, 41.3M rows — comparing the v4 table against the rebuilt v7 table with identical data:

• PK index size: 2.91 GB → 1.68 GB (−42%). Most of that is fill factor: the v7 index settles near 90% occupancy because it only ever appends.
• COPY + merge of a 180k-row batch: 41.2s → 12.6s.
• WAL generated per nightly ingest: 6.2 GB → 1.9 GB. Fewer distinct pages touched after each checkpoint means far fewer full-page writes.
• PK index buffer hit ratio during ingest: 71% → 99.3%.
• VACUUM on the videos table: 14 min → 5 min, mostly because there is less index to scan.

The end-to-end result was the one we cared about: the ingest window went from 88 minutes back to 31, and the morning cache warm stopped colliding with it.

One honest caveat on these numbers. A rebuilt index always looks good against a production index that has been churning for two years; some of that 42% is "we ran REINDEX", not "we switched to v7". Three months later the v7 index is at 1.79 GB and the fill factor has held, which is the part that actually proves the point — the v4 index was back over 2.4 GB three months after its last rebuild.

Feeding the SQLite FTS5 edge indexes

Each region gets a read-only SQLite file shipped to the edge, holding a trimmed projection of the video table plus an FTS5 index. CJK is the reason we use SQLite here at all: with a trigram tokenizer we get usable substring matching for Japanese, Korean and Chinese titles without a word segmenter, which Postgres full-text would need.

The same 16 bytes carry across. SQLite stores them as a BLOB and orders them with memcmp, so the cursor pagination logic is literally the same query shape on both sides:

<?php
declare(strict_types=1);

$edge = new PDO('sqlite:' . $path, options: [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$edge->exec('PRAGMA journal_mode = WAL');
$edge->exec('PRAGMA synchronous = NORMAL');

$edge->exec('CREATE TABLE IF NOT EXISTS videos (
    rowid  INTEGER PRIMARY KEY,
    uuid   BLOB NOT NULL UNIQUE,
    title  TEXT NOT NULL,
    lang   TEXT NOT NULL,
    ts_ms  INTEGER NOT NULL
)');

$edge->exec("CREATE VIRTUAL TABLE IF NOT EXISTS videos_fts USING fts5(
    title,
    lang UNINDEXED,
    content='videos',
    content_rowid='rowid',
    tokenize='trigram case_sensitive 0'
)");

$ins = $edge->prepare(
    'INSERT INTO videos (uuid, title, lang, ts_ms) VALUES (:u, :t, :l, :ts)
     ON CONFLICT(uuid) DO UPDATE SET title = excluded.title, lang = excluded.lang'
);

$edge->beginTransaction();
foreach ($batch as $row) {
    $bin = $row['id']; // raw 16 bytes straight from Postgres
    $ins->bindValue(':u', $bin, PDO::PARAM_LOB);
    $ins->bindValue(':t', $row['title']);
    $ins->bindValue(':l', $row['title_lang']);
    $ins->bindValue(':ts', (int) (UuidV7::createdAt($bin) * 1000), PDO::PARAM_INT);
    $ins->execute();
}
$edge->commit();

// keyset page, newest first — identical shape to the Postgres query
$page = $edge->prepare(
    'SELECT uuid, title FROM videos WHERE uuid < :cursor ORDER BY uuid DESC LIMIT 40'
);

The ts_ms column is redundant — it is derivable from the uuid — but SQLite has no cheap way to slice a BLOB prefix into an integer in an index, so we materialize it for range filters. That is a deliberate 8 bytes per row.

Backfilling 41 million rows

The migration ran as an online dual-write: add id_v7 uuid, backfill in batches, swap the constraint, drop the old column. The interesting part is what value to assign to a historical row.

Our first attempt used clock_timestamp() during the backfill, which gave every legacy row an id timestamped to migration night. Technically valid; operationally useless, because it destroyed the one property we had just paid for. The fix was to derive the timestamp from published_at:

import os
import struct
from uuid import UUID

import psycopg

BATCH = 20_000


def uuid7_at(ms: int, seq: int) -> UUID:
    hi = (ms << 16) | 0x7000 | (seq & 0x0FFF)
    lo = bytearray(os.urandom(8))
    lo[0] = (lo[0] & 0x3F) | 0x80
    return UUID(bytes=struct.pack('>Q', hi) + bytes(lo))


with psycopg.connect(os.environ['PG_DSN']) as conn:
    total = 0
    while True:
        with conn.cursor() as cur:
            cur.execute(
                '''SELECT legacy_id, (EXTRACT(EPOCH FROM published_at) * 1000)::bigint
                   FROM videos
                   WHERE id_v7 IS NULL
                   ORDER BY published_at
                   LIMIT %s''',
                (BATCH,),
            )
            rows = cur.fetchall()
            if not rows:
                break

            payload = [
                (uuid7_at(int(ms), i), legacy_id)
                for i, (legacy_id, ms) in enumerate(rows)
            ]
            cur.executemany(
                'UPDATE videos SET id_v7 = %s WHERE legacy_id = %s', payload
            )
        conn.commit()
        total += len(rows)
        print(f'{total} rows backfilled', flush=True)

Batches are ordered by published_at and the enumerate index becomes the counter, so ids come out monotonic across the whole backfill. The counter wraps every 4096 rows within a batch of 20,000 — that is intentional and harmless, since uniqueness lives in the 62 random bits and rows sharing a millisecond have no meaningful order anyway.

The backfill took eleven hours in 20k-row batches with a two-second sleep between them, throttled to keep replica lag under five seconds. We did not attempt to preserve any relationship between old and new ids; instead we kept legacy_id as a plain indexed column for six months so old cached URLs and partner integrations kept resolving.

Four things that bit us

A v7 id publishes its own creation time. Anyone holding an id knows to the millisecond when the row was created. For public video pages we route on slugs and never expose the uuid, so the Cloudflare cache key and the LiteSpeed page cache path contain no ids at all. But we had one internal admin endpoint leaking ids into an HTML data attribute, which meant a scraper could have reconstructed our exact ingest schedule. Fixed, but it is exactly the kind of thing that does not show up in a schema review.

Never use a v7 id as a secret. Only 62 bits are random and the rest is guessable. Session tokens, password reset links, unsubscribe links — those stay v4, or better, random_bytes(32). Sequential-ish keys and unguessable tokens are different jobs.

ORDER BY id is ingest order, not publish order. A video published in 2019 that we first saw last week sorts as last week. We hit this the day after the cutover when the "newest" feed for one region filled with old catalog rows that had just been backfilled. Feeds sort by published_at; only internal tooling sorts by id.

The right edge of the index is now a hot spot. Every insert targets the same rightmost leaf page, so at high write concurrency you trade random-page thrash for buffer contention on one page. At our peak of about 2,000 inserts/sec this is nowhere near a problem, and the WAL savings dwarf it. If you are writing tens of thousands of rows per second from many connections, benchmark it rather than assuming — that is the one workload where v4's randomness is genuinely an advantage.

Conclusion

UUID v7 is not a clever trick, it is a correction of a mismatch: we needed decentralized id generation and accidentally bought full randomness along with it. Putting a millisecond timestamp in the leading 48 bits gives back everything a B-tree wants — append-only inserts, high fill factor, cache locality, cheap WAL — while keeping the property we actually chose UUIDs for.

If you are on Postgres 18, uuidv7() is already there and the migration is a DEFAULT change plus a backfill. On 17 or older, the SQL function above plus an application-side generator gets you the same behavior. Either way, derive backfilled ids from a real event timestamp rather than migration time, keep the id out of anything user-facing, and measure your index fill factor three months later rather than the day of the rebuild. That last number is the one that tells you whether it worked.