Why We Moved Video Metadata Primary Keys to PostgreSQL UUID v7

Last quarter our videos table crossed 40 million rows across eight regions, and our slowest query stopped being a search or a join. It was INSERT. Every cron cycle we ingest fresh discovery data from streaming platforms in the US, GB, DE, FR, IN, BR, JP and AU, and each batch was landing on the same few 8KB B-tree pages at the right edge of a BIGSERIAL index. That's fine until it isn't. Once the hot leaf pages no longer fit in shared_buffers under concurrent writers, insert latency at the p99 went from 4ms to 90ms and autovacuum started thrashing. At TrendVidStream we run lean infrastructure — PHP 8.4 on the edge, SQLite FTS5 for local search caches, and a central PostgreSQL cluster — so I couldn't just throw a bigger box at the problem. We migrated the primary key strategy to UUID v7, and this is the honest write-up of why, how, and what broke.

The Problem With Both BIGSERIAL and UUID v4

Engineers usually pick between two defaults for primary keys, and both are wrong for a high-ingest, multi-region video catalog.

BIGSERIAL gives you small, monotonic keys — great for index locality when reading, terrible for write concurrency. Every writer contends on the same rightmost B-tree pages. Worse, a sequential integer is a business fact you leak: anyone scraping our public watch pages can diff two video IDs and estimate exactly how many titles we index per day. For a discovery product, that catalog velocity is competitive information I'd rather not hand out for free.

Random UUID v4 fixes the enumeration problem and spreads writes beautifully — too beautifully. Because the 128 bits are fully random, consecutive inserts scatter across the entire index. On a 40M-row table the index no longer has any temporal locality, so a range of "videos ingested this hour" touches thousands of unrelated leaf pages. Cache hit ratio collapses, and your index bloats because Postgres keeps splitting pages in the middle rather than appending. We measured a v4 index at nearly 2.3x the size of the equivalent BIGINT index for the same row count.

UUID v7 is the compromise that actually works. The first 48 bits are a Unix millisecond timestamp, so keys generated close in time sort close together. The remaining bits are random, so they're still unguessable and still spread within a millisecond. You get v4's opacity and write-distribution safety with something close to BIGSERIAL's index locality. For time-ordered data like video ingest events, that's the whole game.

What a UUID v7 Actually Looks Like

The layout is defined in RFC 9562. Bytes 0–5 hold a big-endian millisecond timestamp, then version and variant bits are stamped into fixed positions, and everything else is random. Here's a generator in Go that makes the bit-twiddling explicit, because "just use a library" hides the part you need to understand when you debug it at 2am:

package main

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

// NewUUIDv7 builds an RFC 9562 UUIDv7 from a millisecond timestamp.
func NewUUIDv7(t time.Time) ([16]byte, error) {
    var u [16]byte

    // 48-bit big-endian Unix millisecond timestamp in bytes 0..5.
    ms := uint64(t.UnixMilli())
    var tmp [8]byte
    binary.BigEndian.PutUint64(tmp[:], ms)
    copy(u[0:6], tmp[2:8]) // low 48 bits

    // Fill the trailing 10 bytes with randomness.
    if _, err := rand.Read(u[6:]); err != nil {
        return u, err
    }

    // Set version (7) in the high nibble of byte 6.
    u[6] = (u[6] & 0x0F) | 0x70
    // Set variant (10xx) in the two high bits of byte 8.
    u[8] = (u[8] & 0x3F) | 0x80

    return u, nil
}

func format(u [16]byte) string {
    s := hex.EncodeToString(u[:])
    return fmt.Sprintf("%s-%s-%s-%s-%s", s[0:8], s[8:12], s[12:16], s[16:20], s[20:32])
}

func main() {
    u, _ := NewUUIDv7(time.Now())
    fmt.Println(format(u)) // e.g. 0190f3a1-...-7...-8...
}

The important detail: two UUID v7 values generated in the same millisecond sort by their random tails, and across milliseconds they sort by time. That monotonic-ish ordering is what keeps the B-tree happy.

Generating v7 Inside PostgreSQL

Postgres 18 ships a native uuidv7() function, and if you're on 18+ you should just use it. But most production clusters aren't there yet — ours ran 16 during the migration — so I wrote a SQL function that works on 13+ with pgcrypto. It's worth having even after you upgrade, because it lets you backfill and test deterministically.

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE OR REPLACE FUNCTION uuid_generate_v7()
RETURNS uuid AS $$
DECLARE
  unix_ms bytea;
  rand_bytes bytea;
BEGIN
  -- 48-bit millisecond timestamp, big-endian, as 6 bytes.
  unix_ms := substring(
    int8send((extract(epoch FROM clock_timestamp()) * 1000)::bigint)
    FROM 3 FOR 6
  );

  -- 10 random bytes for the version/variant + entropy tail.
  rand_bytes := gen_random_bytes(10);

  -- Stamp version 7 (0x70) into the first random byte's high nibble.
  rand_bytes := set_byte(rand_bytes, 0,
    (get_byte(rand_bytes, 0) & 15) | 112);
  -- Stamp variant (0x80) into the third random byte's high bits.
  rand_bytes := set_byte(rand_bytes, 2,
    (get_byte(rand_bytes, 2) & 63) | 128);

  RETURN encode(unix_ms || rand_bytes, 'hex')::uuid;
END;
$$ LANGUAGE plpgsql VOLATILE;

Use clock_timestamp(), not now() — now() returns the transaction start time, so a batch insert inside one transaction would stamp every row with an identical 48-bit prefix and defeat the temporal spread within the batch. clock_timestamp() advances per row.

Then the table definition is unremarkable, which is the point:

CREATE TABLE videos (
    id           uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
    platform     text NOT NULL,
    region       char(2) NOT NULL,
    external_id  text NOT NULL,
    title        text NOT NULL,
    duration_s   integer,
    ingested_at  timestamptz NOT NULL DEFAULT now(),
    UNIQUE (platform, external_id)
);

-- Time-range scans on the PK now have locality for free.
CREATE INDEX videos_region_idx ON videos (region, id);

That (region, id) index is quietly powerful: because id embeds time, a query for "newest videos in region DE" walks a contiguous index range instead of sorting by a separate ingested_at column. You can drop a created_at index you were previously maintaining.

Extracting the Timestamp Back Out

A UUID v7 is not just an ID — it's a timestamp you can read without a join. This is genuinely useful for our multi-region cron: each region's ingest job can ask "what's the newest key I've seen?" and derive an ingest watermark from the ID alone. Here's the extraction in PHP 8.4, which is what our edge deploy actually runs:

<?php
declare(strict_types=1);

/**
 * Recover the millisecond timestamp embedded in a UUIDv7.
 */
function uuidv7_timestamp(string $uuid): DateTimeImmutable
{
    $hex = str_replace('-', '', $uuid);
    if (strlen($hex) !== 32) {
        throw new InvalidArgumentException('not a 128-bit uuid');
    }

    // First 12 hex chars = 48-bit millisecond timestamp.
    $msHex = substr($hex, 0, 12);
    $ms = hexdec($msHex); // safe: 48 bits fits a PHP int on 64-bit

    $seconds = intdiv($ms, 1000);
    $micros  = ($ms % 1000) * 1000;

    return DateTimeImmutable::createFromFormat(
        'U.u',
        sprintf('%d.%06d', $seconds, $micros)
    );
}

$id = '0190f3a1-8c2d-7b41-9e0a-1f2b3c4d5e6f';
$when = uuidv7_timestamp($id);
echo $when->format('Y-m-d H:i:s.v'), PHP_EOL;

We use this to build the SQLite FTS5 mirror on each edge node. The cron pulls rows from central Postgres, and instead of tracking a separate cursor column we page by id > :last_seen_uuid. Because v7 sorts chronologically, that keyset pagination is both correct and index-friendly. No OFFSET, no missed rows if a slow insert commits late, because we advance the watermark only past keys we've durably written locally.

The Migration, Honestly

You cannot just ALTER COLUMN id TYPE uuid on a live 40M-row table with foreign keys pointing at it. Here's the path that worked without downtime, run region by region during each region's low-traffic window:

1. Add the new column as nullable: ALTER TABLE videos ADD COLUMN id_v7 uuid;. This is a metadata-only change on modern Postgres, instant even on a huge table.
2. Backfill in batches so you don't hold one giant transaction or bloat WAL. We backfilled 20k rows per statement with a short sleep between batches, deriving the v7 timestamp from the existing ingested_at so the new keys' embedded time matched reality.
3. Dual-write from the application for the overlap window — new inserts populate both id and id_v7.
4. Rebuild foreign keys against the new column using NOT VALID constraints first, then VALIDATE CONSTRAINT separately (that split keeps the validation scan from holding a strong lock).
5. Swap in a single short transaction: drop the old PK, rename columns, promote id_v7.

The backfill deserves its own snippet because the batching is where people blow up production:

-- Derive v7 keys whose embedded timestamp matches the real ingest time,
-- so historical ordering is preserved after the swap.
CREATE OR REPLACE FUNCTION uuid_v7_at(ts timestamptz)
RETURNS uuid AS $$
  SELECT encode(
    substring(int8send((extract(epoch FROM ts) * 1000)::bigint) FROM 3 FOR 6)
    || set_byte(set_byte(gen_random_bytes(10), 0,
         (get_byte(gen_random_bytes(10), 0) & 15) | 112), 2,
         (get_byte(gen_random_bytes(10), 2) & 63) | 128),
    'hex')::uuid;
$$ LANGUAGE sql VOLATILE;

DO $$
DECLARE
  rows_done integer;
BEGIN
  LOOP
    UPDATE videos
       SET id_v7 = uuid_v7_at(ingested_at)
     WHERE id_v7 IS NULL
       AND ctid IN (
         SELECT ctid FROM videos WHERE id_v7 IS NULL LIMIT 20000
       );
    GET DIAGNOSTICS rows_done = ROW_COUNT;
    EXIT WHEN rows_done = 0;
    COMMIT;              -- release WAL pressure each batch
    PERFORM pg_sleep(0.2);
  END LOOP;
END $$;

One caveat on that backfill function: calling gen_random_bytes three times means the version and variant nibbles get stamped onto different random bytes than the tail. For a backfill where you only care about time-ordering and uniqueness that's harmless, but if you want strictly conforming bytes, generate the 10 random bytes once into a variable in a plpgsql function like the earlier uuid_generate_v7. I kept the inline SQL version because it runs faster in a tight backfill loop and our downstream code never validates the variant bits on historical rows.

What Actually Improved, With Numbers

After the swap on our largest region shard:

• Insert p99 dropped from ~90ms back to ~5ms under the same concurrent cron load. The rightmost-page contention of BIGSERIAL is gone because inserts spread across the current millisecond's worth of pages, but not so wide that cache dies.
• Primary key index shrank ~40% versus the UUID v4 prototype we'd briefly tested, and sat only about 15% larger than the old BIGINT index — an acceptable tax for opacity and write spread.
• Autovacuum settled down. Fewer mid-page splits meant less index bloat, so vacuum had less to do and stopped competing with ingest for I/O.
• We deleted a column and an index. The created_at timestamp and its dedicated index became redundant because the PK carries the time. On 40M rows that's real disk back.

The wins that don't show up in a dashboard mattered too. Keyset pagination between central Postgres and the edge SQLite caches became trivial and correct. Debugging got easier: paste any video ID into the PHP helper and instantly know when it was ingested and which cron window produced it. And our public IDs stopped leaking catalog growth rate to anyone counting.

Trade-offs You Should Weigh First

UUID v7 is not free lunch. Be clear-eyed:

• 16 bytes vs 8. Every foreign key, every index, every row is twice the key size of a BIGINT. On a wide fact table with many child tables this adds up. Measure your total index footprint before committing.
• Millisecond timestamp leakage. v7 intentionally embeds creation time. If your IDs are public and creation time is sensitive (it isn't for us — video ingest time is not a secret), v7 leaks it. v4 doesn't.
• No cross-node global ordering guarantee. Two nodes generating v7s in the same millisecond can interleave. For a total order you still need something else. We only need approximate temporal locality, which v7 delivers.
• Text representation is bulky. If you serialize IDs as 36-char strings everywhere (JSON APIs, logs), that's more bytes on the wire than an integer. Store as native uuid, not text — the text mistake doubles storage and kills index performance, and I've seen it in more than one codebase.
• Clock dependence. A backwards clock jump can produce out-of-order keys. NTP hygiene matters; a monotonic clock source for the timestamp portion is safer if your platform offers one.

My rule of thumb: reach for UUID v7 when you have high-concurrency inserts into time-ordered data, want opaque public IDs, and can absorb the 16-byte key size. That describes a video discovery catalog almost perfectly. It does not describe a small config table with a dozen rows — keep BIGSERIAL there and don't overthink it.

Conclusion

Switching primary key strategy sounds like the kind of foundational decision you make once at CREATE TABLE and never revisit. In practice it's a lever you can pull under load, carefully, region by region, and the payoff for a write-heavy multi-region catalog is large: insert latency back under control, a leaner index, redundant columns deleted, and public IDs that don't hand competitors your ingest rate. UUID v7 earns its place because it stops treating "random" and "ordered" as a binary choice — it gives you enough order for the B-tree and enough randomness for concurrency and opacity. If you're running high-ingest time-series-shaped data on Postgres 13 or newer, write the generator function, benchmark it against your real insert pattern for an afternoon, and let the p99 numbers decide. Ours did.