You add a UUID primary key to your PostgreSQL table. Everything works great in development. You get to a million rows in production and suddenly your INSERT latency spikes, VACUUM runs longer, and index size is two to three times what you expected. You didn't change anything. What happened?

The problem is UUID v4. Not the concept — the version. UUID v4 is purely random, and purely random identifiers are one of the worst choices you can make for a database primary key. The fix exists, it's been standardized, and almost nobody uses it yet: UUID v7.

I've dealt with this in vatnode.dev and pi-pi.ee — systems where identifier strategy matters because every row is indexed, queried, and sorted. Here's what I learned, and why UUID v7 is the right choice for most production systems in 2026.

What UUID Actually Is

UUID stands for Universally Unique Identifier. The format is always the same: 128 bits, represented as 32 hexadecimal characters split by hyphens into groups of 8-4-4-4-12:

xxxxxxxx-xxxx-Mxxx-Nxxx-xxxxxxxxxxxx
           ^         ^
           version   variant

The M nibble encodes the version (1 through 8). The N nibble encodes the variant — for all modern UUIDs this is 8, 9, a, or b (indicating RFC 4122/9562 format). Everything else depends on the version.

RFC 4122 originally defined versions 1 through 5. RFC 9562 (published May 2024) supersedes it and adds versions 6, 7, and 8. If you see references to "UUID v7" in older codebases, people were using draft specs — RFC 9562 is now the authoritative standard.

All Eight Versions

v1 — Time + MAC address. 60-bit timestamp (100-nanosecond intervals since October 1582), plus a 48-bit node ID derived from your MAC address. Monotonic per node, but leaks your machine's MAC address. Banned in many privacy-sensitive contexts.

v2 — DCE Security. v1 with the lower timestamp bits replaced by a POSIX UID/GID. Practically obsolete. You will not encounter it.

v3 — Name-based, MD5. Deterministic: the same namespace + name always produces the same UUID. Uses MD5, which means collision resistance is weak by modern standards. Still useful for generating stable IDs from names when collision resistance doesn't matter much.

v4 — Random. 122 bits of cryptographically random data. The most widely used version by far. Zero information encoded, maximum uniqueness, minimal collision probability. Also the worst choice for database primary keys.

v5 — Name-based, SHA-1. Like v3 but uses SHA-1. Better collision resistance than v3 for deterministic ID generation. I use this in eu-vat-rates-data for generating stable dataset identifiers from country codes.

v6 — Reordered time. RFC 9562's first new version. Takes the v1 timestamp but reorders the bits so the most significant bits come first — making v6 lexicographically sortable, unlike v1. A transitional format for systems already using v1 that need sortability. No new systems should start with v6 instead of v7.

v7 — Unix timestamp + random. The version you should be using. 48-bit Unix timestamp in milliseconds, followed by 74 bits of random data. Monotonically increasing within the same millisecond, lexicographically sortable, and database-friendly. More on this below.

v8 — Custom. Vendor-specific layout. The standard only mandates the version and variant bits — everything else is up to the implementor. Used for proprietary formats that need to fit the UUID shape.

slug="technical-consultation"
text="Hitting PostgreSQL index bloat or planning a primary key migration on a live table? Book a consultation before you run that ALTER TABLE — the order of operations matters."
/>

Why UUID v4 Destroys Your B-tree Index

PostgreSQL stores table data in 8KB pages and maintains B-tree indexes for primary keys and other indexed columns. When you insert a new row, PostgreSQL finds the correct position in the index for the new key and inserts it there.

With UUID v4, every new ID is random. That means new rows land at random positions throughout the B-tree — not at the end, but anywhere. This causes two things:

Page splits. When a B-tree page is full and a new key needs to be inserted in the middle of it, PostgreSQL splits the page into two half-full pages. With random inserts, this happens constantly. A table that should fit in 100 pages might occupy 150–180 pages because half of them are perpetually half-empty.

Cache misses. PostgreSQL's shared_buffers holds recently accessed pages in memory. With sequential inserts (like auto-increment integers), you're almost always writing to the last few pages — they stay warm in cache. With random UUID v4 inserts, every insert potentially touches a different page. At scale, your effective working set is the entire index, not just the recent tail. You're reading from disk on every insert.

Here's what this looks like measured. On a test table with 5 million rows:

UUID v7 behaves almost identically to BIGINT SERIAL from the database's perspective, because the inserts are time-ordered. You get the benefits of an ordered key (no page splits, cache-friendly, predictable VACUUM) while keeping the globally unique, opaque identifier that distributed systems need.

UUID v7 in Detail

The 128-bit layout of UUID v7, per RFC 9562:

 0                   1                   2                   3
 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|                           unix_ts_ms                          |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|          unix_ts_ms           |  ver  |       rand_a          |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|var|                        rand_b                             |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|                            rand_b                             |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

• Bits 0–47: Unix timestamp in milliseconds. Covers dates until the year 10889. No Y2K38 problem.
• Bits 48–51: Version field, always 0111 (7).
• Bits 52–63: rand_a — 12 bits of random or sub-millisecond precision data. RFC 9562 allows implementations to use this for monotonicity within the same millisecond.
• Bits 64–65: Variant bits, always 10.
• Bits 66–127: rand_b — 62 bits of random data.

The critical property: because the timestamp occupies the most significant 48 bits, UUID v7 values sort in the same order they were generated. Two UUIDs generated 1ms apart are guaranteed to sort correctly. Two UUIDs generated within the same millisecond sort in whatever order the random rand_a portion dictates — most implementations increment a counter for same-millisecond generation to preserve monotonicity.

A real UUID v7 looks like this:

0195d3a2-f8c0-7b4e-8f32-1a2b3c4d5e6f
^^^^^^^^^
timestamp prefix — ms since Unix epoch, base-16

The first 8 characters encode the timestamp. You can actually read when a UUID v7 was generated, which is useful for debugging.

Generating UUID v7 in Node.js

npm install uuidv7

import { uuidv7 } from "uuidv7";

// Generate a single UUID v7
const id = uuidv7();
// → "0195d3a2-f8c0-7b4e-8f32-1a2b3c4d5e6f"

// Parse the timestamp back out — useful for debugging
import { UUIDv7 } from "uuidv7";

function extractTimestamp(uuid: string): Date {
  const parsed = UUIDv7.parse(uuid);
  return new Date(parsed.unixTimeMs);
}

const ts = extractTimestamp("0195d3a2-f8c0-7b4e-8f32-1a2b3c4d5e6f");
// → 2026-03-11T...

The uuidv7 package also handles the within-millisecond monotonicity guarantee — if you call uuidv7() multiple times in the same millisecond, each call returns a value that sorts after the previous one:

import { uuidv7 } from "uuidv7";

// Generate a batch — guaranteed monotonic even within the same ms
const ids = Array.from({ length: 1000 }, () => uuidv7());

// Verify they're in order (they always are)
const sorted = [...ids].sort();
console.log(JSON.stringify(ids) === JSON.stringify(sorted)); // true

Using UUID v7 with Drizzle ORM

// packages/db/src/schema.ts
import { pgTable, text, timestamp, integer } from "drizzle-orm/pg-core";
import { uuidv7 } from "uuidv7";

export const orders = pgTable("orders", {
  // Generate UUID v7 in application code, not database
  // PostgreSQL's gen_random_uuid() generates v4 — don't use it here
  id: text("id")
    .primaryKey()
    .$defaultFn(() => uuidv7()),
  customerId: text("customer_id").notNull(),
  amount: integer("amount").notNull(),
  createdAt: timestamp("created_at").notNull().defaultNow(),
});

One practical note: store the UUID as text or uuid in PostgreSQL. The uuid type is 16 bytes (binary) vs. 36 bytes for text with the hyphen representation. At scale, uuid type saves space and compares faster. Drizzle ORM's uuid column type handles this correctly:

import { pgTable, uuid, integer, timestamp } from "drizzle-orm/pg-core";
import { uuidv7 } from "uuidv7";

export const orders = pgTable("orders", {
  id: uuid("id")
    .primaryKey()
    .$defaultFn(() => uuidv7()),
  amount: integer("amount").notNull(),
  createdAt: timestamp("created_at").notNull().defaultNow(),
});

ULID: The Alternative Worth Knowing

ULID (Universally Unique Lexicographically Sortable Identifier) predates UUID v7 and solves the same problem differently. It's also timestamp-prefixed and sortable, but uses a different format:

01ARZ3NDEKTSV4RRFFQ69G5FAV
^^^^^^^^^^ ^^^^^^^^^^^^^^^^
10-char ts  16-char random
(48-bit ms) (80-bit random)

ULID uses Crockford Base32 encoding (uppercase letters + numbers, excluding I, L, O, U to avoid ambiguity). This makes them shorter to display — 26 characters vs. 36 for UUID — and URL-safe without encoding.

npm install ulid

import { ulid, decodeTime } from "ulid";

const id = ulid();
// → "01ARZ3NDEKTSV4RRFFQ69G5FAV"

// Extract the timestamp
const ts = decodeTime(id);
// → Unix timestamp in ms

// Monotonic factory — guarantees sort order within the same ms
import { monotonicFactory } from "ulid";
const ulidMonotonic = monotonicFactory();

const ids = Array.from({ length: 100 }, () => ulidMonotonic());
// Each one sorts after the previous, even within the same ms

ULID's weakness: it's not a formal RFC standard. The spec lives in a GitHub repository. UUID v7 is RFC 9562 — more likely to have native database support, ORM support, and long-term ecosystem stability.

NanoID: Fast, Small, Not for Primary Keys

NanoID is not a timestamp-based identifier. It generates cryptographically random strings of configurable length using a URL-safe alphabet. Default is 21 characters.

npm install nanoid

import { nanoid, customAlphabet } from "nanoid";

// Default: 21-character URL-safe ID
const id = nanoid();
// → "V1StGXR8_Z5jdHi6B-myT"

// Custom alphabet and length
const shortId = customAlphabet("0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ", 12);
shortId(); // → "4F3K9B2M7X1P"

NanoID is the right choice for IDs that users see and type: invitation codes, short links, public-facing resource handles. It's smaller (21 chars vs. 36 for UUID), URL-safe without encoding, and has no timestamp information leakage — which is sometimes exactly what you want.

Do not use NanoID as a database primary key. The random distribution causes the same index fragmentation problem as UUID v4. It's not designed for that use case.

Side-by-Side Comparison

When to Use What

UUID v7 — your default for primary keys in relational databases. You're building a SaaS, an API, an e-commerce backend — anything with PostgreSQL, MySQL, or SQLite at its core. The index performance difference is real and measurable. Switch from v4 as soon as your table grows beyond a few hundred thousand rows.

UUID v4 — when you need to generate IDs in contexts where you have no control over the library (legacy systems, third-party integrations) or when you explicitly do not want timestamp information in the ID. Also acceptable for low-volume tables where index performance is not a concern.

ULID — when you need the same sortable timestamp property as UUID v7, but your system predates RFC 9562 (ULID has been around since 2017), or you need shorter string representation (26 vs. 36 characters). It has slightly better random entropy (80 bits vs. 62 bits in UUID v7's rand_b).

NanoID — user-visible identifiers: invitation links, short tokens, public slugs, coupon codes. Any context where the ID is meant to be typed, shared, or embedded in a URL without percent-encoding.

What Not to Do With UUID v7

Do not use gen_random_uuid() in PostgreSQL migrations if you want v7. PostgreSQL 13+ ships gen_random_uuid() built in. It generates UUID v4. If you use it as a column default, you get v4 despite all your intentions. Generate v7 in application code and pass it explicitly.

Do not mix versions in the same column. If you have a legacy table with UUID v4 primary keys and start inserting UUID v7, you lose the sort guarantee — old v4 rows have random positions, new v7 rows are ordered, and queries sorting by ID will not produce meaningful results. Migrate fully or not at all.

Do not rely on UUID v7 for global ordering across distributed nodes without considering clock skew. UUID v7 is monotonic within a single process. Across multiple nodes, clock drift can cause an ID from Node A to sort before an ID from Node B even if Node A generated it later. For most use cases this is acceptable — the window is milliseconds. For strict distributed ordering, you need a coordination layer (like a centralized sequence or HLC).

Migrating an Existing Table

If you have a production table with UUID v4 primary keys and want to move to v7:

-- 1. Add a new column for the v7 ID (do not replace in place)
ALTER TABLE orders ADD COLUMN new_id uuid;

-- 2. Backfill — existing rows get v4 equivalents (you can't recover original insertion order)
UPDATE orders SET new_id = gen_random_uuid() WHERE new_id IS NULL;

-- 3. Set not null constraint
ALTER TABLE orders ALTER COLUMN new_id SET NOT NULL;

-- 4. Create new primary key on the new column
ALTER TABLE orders ADD CONSTRAINT orders_new_pk PRIMARY KEY (new_id);

-- 5. Drop the old primary key (after confirming all FKs are updated)
-- ALTER TABLE orders DROP CONSTRAINT orders_pkey;

The honest tradeoff: existing rows in the migrated table will have random-looking identifiers (because they were backfilled with v4-equivalent random values). New rows will be ordered. The index efficiency improvement will compound over time — eventually the old rows become a smaller and smaller fraction of the table, and the index starts behaving more like a v7 table. On vatnode, I chose to spin up the new schema with v7 from day one rather than migrate — new systems should not start with v4 in 2026.

Results in Practice

In vatnode.dev, all tables use UUID v7 via the uuidv7 package with Drizzle ORM. With 11 tables and growing validation volume, index sizes are consistent with what I'd expect from an ordered key — no bloat, VACUUM completes predictably, and the dashboard queries that sort by creation time are using the primary key index directly without a separate created_at index.

In pi-pi.ee, covering 32 EU markets with 6 payment methods and orders that span Stripe, SEPA, and Multibanco, every order and invoice record uses UUID v7. The timestamp embedded in the ID is genuinely useful during debugging: I can look at an order ID and immediately know when it was created without querying the created_at column.

items={[
{
q: "Why is UUID v4 bad for PostgreSQL performance?",
a: "UUID v4 is purely random, so each new row lands at a random position in the B-tree index rather than appending to the end. This causes constant page splits — pages that should be full end up half-empty — and cache misses as every insert touches a different page. At 5 million rows, UUID v4 produces indexes roughly 2.7x larger than UUID v7 and INSERT latency roughly 4x slower.",
},
{
q: "What is UUID v7 and how is it different from UUID v4?",
a: "UUID v7 is an RFC 9562 standard that embeds a 48-bit Unix millisecond timestamp in the most significant bits. This makes UUIDs sortable and time-ordered, so database inserts append near the end of the index rather than scattering randomly. You keep the global uniqueness and opacity of a UUID while gaining the index performance of an auto-incrementing integer.",
},
{
q: "Should I use UUID v7 or ULID?",
a: "Both solve the same index fragmentation problem. UUID v7 is the RFC 9562 standard with growing ORM and database native support. ULID predates it, uses Crockford Base32 encoding (26 chars vs 36 for UUID), and has slightly higher random entropy. For new projects in 2026, UUID v7 is the safer default due to formal standardization. ULID is a reasonable choice if you need shorter string representation or if your stack already uses it.",
},
{
q: "Does PostgreSQL support UUID v7 natively?",
a: "PostgreSQL's built-in gen_random_uuid() generates UUID v4, not v7. Generate v7 in application code using the uuidv7 npm package and pass it explicitly as the default value. In Drizzle ORM, use .$defaultFn(() => uuidv7()) on the column definition. Never use gen_random_uuid() as a column default if you want v7 ordering.",
},
{
q: "When should I use NanoID instead of UUID v7?",
a: "NanoID is designed for user-visible identifiers: invitation codes, short links, coupon codes, public-facing resource handles in URLs. It is shorter (21 chars), URL-safe, and has no timestamp leakage. Do not use NanoID as a database primary key — its random distribution causes the same index fragmentation problem as UUID v4.",
},
]}
/>

If you're building a system that will grow — SaaS, e-commerce, API — getting the identifier strategy right from day one is one of those decisions that pays off quietly over time. You'll never know how many page splits you avoided. But you won't be debugging index bloat at 3 AM either.

If you're working on EU e-commerce or SaaS infrastructure and want someone who's thought through these decisions in production — get in touch. I'm available for freelance projects and long-term engagements. If you need technical consultation on a schema migration or a broader MVP development engagement where these patterns apply from day one, that's the work I do.

Further reading:

• RFC 9562 — Universally Unique IDentifiers (UUIDs)
• uuidv7 npm package
• ULID specification
• NanoID repository
• Vatnode VAT validation SaaS — production system where UUID v7 is in use
• Pi-Pi B2B e-commerce — 32-market platform with UUID v7 across all order records
• Data Format Comparison: JSON, YAML, TOML, CSV, Protobuf — the adjacent decision: what format to use between those tables
• Technical SEO I Build Into Every Project — the same "build it right the first time" principle, applied to indexing