(opens in a new tab)

UUID v7: Time-Ordered Primary Keys for Databases

August 14, 2026 by Chat2DB Team

Random UUIDs solved a real problem — globally unique identifiers that any node can mint without coordination — and then quietly created another one: primary keys that scatter inserts across the entire breadth of a B-tree index. UUID v7, standardized in RFC 9562, keeps the decentralized-generation property while making identifiers sort by creation time, which is exactly what B-tree-backed databases want. This article explains the v7 bit layout, why v4 keys degrade insert performance, how v7 fixes it, and how to generate and adopt v7 in PostgreSQL and MySQL, including on versions without native support.

What UUID v7 Is

RFC 9562 (published in 2024, obsoleting RFC 4122) defines three new UUID versions; v7 is the one aimed at database keys. A UUID v7 is still a standard 128-bit UUID — same textual format, same uuid column types — but its most significant bits are a Unix timestamp, so lexicographic order approximates creation order.

Bit layout

 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 (48 bits)                  |
+-------------------------------------------------------------+
| unix_ts_ms    | ver=7 |       rand_a (12 bits)              |
+-------------------------------------------------------------+
|var|                   rand_b (62 bits)                      |
+-------------------------------------------------------------+
|                       rand_b (continued)                    |
+-------------------------------------------------------------+

• 48 bits unix_ts_ms — milliseconds since the Unix epoch. 48 bits of milliseconds lasts until roughly the year 10889, so overflow is not a practical concern.
• 4 bits version — always 0111 (7).
• 12 bits rand_a — random, or optionally sub-millisecond timestamp fraction / a monotonic counter to keep IDs ordered within the same millisecond.
• 2 bits variant — 10, as in other RFC UUIDs.
• 62 bits rand_b — random, guaranteeing uniqueness among IDs generated in the same millisecond.

The result: two v7 IDs generated a millisecond apart always sort in generation order, and IDs generated within the same millisecond are unordered among themselves (unless the generator uses the optional counter) but still unique with overwhelming probability.

Why Random UUID v4 Hurts B-tree Indexes

A v4 UUID is 122 random bits. Used as a primary key, each new row lands at a uniformly random position in the key space, and that has cascading physical costs in any B-tree-organized structure — which includes every PostgreSQL btree index and, more severely, InnoDB's clustered primary key, where the table is the index.

Page splits and fragmentation

B-trees store keys in fixed-size pages (8 KB in PostgreSQL, 16 KB in InnoDB). Sequential-ish keys append to the rightmost leaf; when it fills, the database performs an optimized "rightmost" split and keeps packing pages tightly. Random keys instead land in arbitrary leaves. When a random target page is full, it must split in the middle, leaving two half-full pages. Over time a v4-keyed index trends toward substantially lower page fill and therefore more pages for the same data — more disk, more I/O per scan, more WAL/redo generated by the splits themselves.

Cache misses

The working set for inserts under time-ordered keys is a handful of hot pages on the right edge of the tree, which stay in the buffer pool. Under random keys, the working set for inserts is the entire index: any leaf page may be the next target. Once the index outgrows memory, a meaningful fraction of inserts must first read a cold page from disk before modifying it. The same applies to reads: recently created rows — which most applications query disproportionately — are physically co-located under v7 but smeared across the whole table under v4, turning "fetch last 100 orders" from a few page reads into up to a hundred.

These effects are qualitative but structural: they worsen as the table grows, and no configuration flag removes them. Time-ordered keys avoid the entire problem class by making insertion order and key order agree.

What v7 restores

With v7, inserts append near the right edge like a bigserial/AUTO_INCREMENT key would, while retaining what made UUIDs attractive: no central sequence, safe generation in the application tier or across shards, and non-guessable low bits.

Using UUID v7 in PostgreSQL

PostgreSQL 18: native uuidv7()

PostgreSQL 18 ships uuidv7() (alongside uuidv4() as a clearer alias for gen_random_uuid()):

CREATE TABLE orders (
    id         uuid PRIMARY KEY DEFAULT uuidv7(),
    customer   text NOT NULL,
    total      numeric(10,2) NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);
 
INSERT INTO orders (customer, total) VALUES
    ('acme',  120.00),
    ('globex', 89.50),
    ('initech', 42.00);
 
-- IDs sort in insertion order
SELECT id, customer FROM orders ORDER BY id;

Expected output shape (your values will differ, but note the shared timestamp prefix and ascending order matching insertion order):

                  id                  | customer
--------------------------------------+----------
 0198c3f2-6b1a-7c3e-9a11-0f4d2c8b1a01 | acme
 0198c3f2-6b1b-7f02-8c55-77e0a9b3d4e2 | globex
 0198c3f2-6b1b-7f9d-b1c0-3e5f6a7b8c9d | initech

PostgreSQL 18's uuidv7() also fills rand_a with sub-millisecond precision, so IDs generated in a tight loop on one backend still sort correctly. You can extract the embedded timestamp for debugging:

SELECT id, uuid_extract_timestamp(id) FROM orders LIMIT 1;

Older PostgreSQL versions

On PostgreSQL 13–17 you have three workable options:

1. Generate in application code (recommended): mature v7 generators exist for Java, Go, Python, Rust, Node.js, and .NET. The database schema stays a plain uuid column with no default.
2. Extensions: pg_uuidv7 provides a uuid_generate_v7() function if you control the server.
3. A pure-SQL function, good enough for defaults where an extension is not possible. This overlays the current timestamp onto a random v4 UUID:

CREATE OR REPLACE FUNCTION uuid_generate_v7()
RETURNS uuid
LANGUAGE sql
VOLATILE
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;
$$;
 
-- Use it as a column default
CREATE TABLE events (
    id      uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
    payload jsonb
);

(The two set_bit calls force the version nibble to 0111; the overlay writes the 48-bit millisecond timestamp into the first six bytes.)

Using UUID v7 in MySQL

MySQL (through 8.4) has no native v7 generator, and the storage decisions matter more here because InnoDB clusters rows by primary key.

Store as BINARY(16), not CHAR(36)

Text UUIDs waste 20 bytes per key, and every secondary index repeats the primary key. Use BINARY(16) with UUID_TO_BIN/BIN_TO_UUID for conversion:

CREATE TABLE orders (
    id       BINARY(16) PRIMARY KEY,
    customer VARCHAR(100) NOT NULL,
    total    DECIMAL(10,2) NOT NULL
);
 
-- Application supplies a v7 UUID string; store its bytes as-is:
INSERT INTO orders (id, customer, total)
VALUES (UUID_TO_BIN('01890a5d-ac96-774b-bcce-b302099a8057'), 'acme', 120.00);
 
SELECT BIN_TO_UUID(id) AS id, customer FROM orders;

The swap flag: for v1, not v7

UUID_TO_BIN(uuid, 1) exists to fix version 1 UUIDs, which store their timestamp low-bits-first; the swap flag reorders the fields so the binary value sorts by time. Do not use the swap flag with v7. A v7 UUID already leads with its timestamp in string order, so UUID_TO_BIN(v7_value) — swap flag absent or 0 — preserves time ordering. Applying the swap flag to a v7 value scrambles the layout and destroys exactly the locality you adopted v7 for. If you must use MySQL's built-in UUID() (which generates v1), then UUID_TO_BIN(UUID(), 1) is the time-ordered choice; for v7 generated in the application, pass the bytes straight through.

Migration Considerations

Moving an existing system toward v7 keys is usually incremental:

• New tables first. v4 and v7 values coexist safely in the same uuid/BINARY(16) column type; nothing about v7 requires schema changes beyond the default expression.
• Switching defaults on an existing table is instant (ALTER TABLE ... ALTER COLUMN id SET DEFAULT uuidv7(); in PostgreSQL). Old rows keep their v4 keys; new rows cluster at the right edge from then on. Existing fragmentation does not heal by itself — a REINDEX/pg_repack (PostgreSQL) or OPTIMIZE TABLE (InnoDB) rebuild reclaims it.
• Do not rewrite existing primary keys. Foreign keys, URLs, caches, and event logs reference them; the payoff is not worth the blast radius.
• Keep created_at. The embedded timestamp is a physical-layout optimization, not a substitute for an explicit, indexed, timezone-aware column with defined semantics.
• Sorting caveat: ordering by a v7 key is millisecond-granular and generator-dependent within a millisecond; use it for clustering, use created_at (with a tiebreaker) for user-visible ordering guarantees.
• Inspecting mixed tables during a migration — checking which rows are v4 versus v7, or eyeballing timestamp prefixes — is easier in a client with good uuid rendering; Chat2DB (opens in a new tab) works with both PostgreSQL and MySQL, so you can validate the same v7 generation logic across both engines from one interface.

Privacy Note: Timestamp Leakage

A v7 identifier discloses, to anyone who sees it, the millisecond the row was created. If IDs appear in URLs, API responses, or logs shared with third parties, that reveals account signup times, order times, or message times — and comparing two IDs reveals which came first and by how much. Often this is harmless, but it can be sensitive (e.g., health or HR records). Standard mitigations: keep v7 for internal keys but expose a separate random public identifier; or accept the leak explicitly after review. Unlike UUID v1, v7 contains no MAC address, so the exposure is limited to timing — but timing alone can matter.

FAQ

Is UUID v7 as unique as v4? Effectively yes for practical purposes. It carries 74 random bits per millisecond (v4 has 122 total); collisions require generating enormous volumes within a single millisecond. High-throughput generators can also use rand_a as a counter per RFC 9562's monotonicity guidance.

Should I still prefer bigint sequences? If you never need decentralized generation, non-enumerable IDs, or merge-across-shards semantics, a bigint sequence remains smaller (8 vs 16 bytes) and equally well-ordered. v7 is the right choice when you want UUID properties and index locality.

Does v7 help non-clustered tables too? Yes. PostgreSQL heaps are not clustered by key, but the primary key's btree index still suffers random-insert page splits and cache misses under v4, and secondary effects (WAL volume, index bloat) still improve under v7 — the benefit is simply largest for clustered storage like InnoDB.

Can I derive the creation time from a v7 ID? Yes — the first 48 bits are milliseconds since the epoch; PostgreSQL 18 exposes uuid_extract_timestamp() for exactly this. That convenience is the same property discussed in the privacy section, so decide deliberately where such IDs are exposed.