PostgreSQL 18 UUIDv7: Better Primary Keys

Why this matters now

Postgres 18 finally shipped uuidv7() as a built-in function. It looks like a small addition next to async I/O and OAuth login, but it solves a real production problem that has been costing teams disk space and write throughput for a decade: random UUIDv4 primary keys destroy B-tree locality.

This guide walks through the why and the how — what UUIDv7 is, why it makes a measurable difference on inserts and index size, how to use the new function, how to migrate an existing UUIDv4 table, and the gotchas you only learn after running it in production.

Why UUIDv4 hurts your indexes

UUIDv4 is 122 random bits. Every insert lands at a random position in the B-tree, which causes four compounding problems:

• Page splits everywhere. Each new row forces Postgres to find an arbitrary leaf page and, if full, split it. Split pages leave 50% free space.
• Cold cache hits. Inserts touch random pages, so the working set for writes is your entire index, not just the right edge.
• WAL bloat. Every page split is a full-page image in WAL.
• Larger indexes. Fill factor drifts down toward 70% under sustained load.

Compare to a bigserial: inserts hit the rightmost leaf page, fill it sequentially, and almost never split. UUIDv7 keeps the global-uniqueness property of UUID but recovers most of the locality of a sequence.

What UUIDv7 actually looks like

A UUIDv7 packs three things into 128 bits:

| 48 bits unix_ts_ms | 4 bits version=7 | 12 bits sub-ms rand | 2 bits variant | 62 bits rand |

The leading 48 bits are a millisecond timestamp. That makes UUIDv7 values roughly sortable by creation time. Postgres 18 goes one better: it uses the 12 random bits after the version field as a sub-millisecond counter, so every UUIDv7 generated by the same session within the same millisecond is monotonically increasing.

Net effect: inserts target the right edge of the B-tree, just like a sequence.

How to tell this is your problem

Before changing anything, confirm UUIDv4 is actually hurting you. Two quick checks:

-- 1. Are your hot tables UUID-keyed and write-heavy?
SELECT schemaname, relname, n_tup_ins, n_tup_upd
FROM   pg_stat_user_tables
ORDER  BY n_tup_ins DESC
LIMIT  10;

-- 2. How much of the PK index is wasted space?
SELECT i.relname,
       pg_size_pretty(pg_relation_size(i.indexrelid)) AS size,
       round(100.0 * s.avg_leaf_density, 1) AS leaf_density_pct
FROM   pg_stat_user_indexes i
JOIN   pgstattuple(i.indexrelid) s ON true
WHERE  i.relname LIKE '%_pkey';

If your busiest tables have UUID primary keys and leaf_density_pct sits in the 60–75% range, you are paying the random-key tax. Sequential keys land in the high 80s to low 90s.

Prerequisites

• PostgreSQL 18.0 or later (SELECT version();)
• psql or any client that can issue DDL
• About 5 minutes of free disk for the benchmark in step 4

You do not need the pgcrypto or uuid-ossp extensions anymore. uuidv7(), uuidv4(), and uuid_extract_timestamp() are core in 18.

Step 1 — Verify your server and create a test table

SELECT version();
-- PostgreSQL 18.0 on x86_64-pc-linux-gnu ...

CREATE TABLE events_v7 (
    id          uuid PRIMARY KEY DEFAULT uuidv7(),
    payload     jsonb NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);

That DEFAULT uuidv7() is the whole change. Existing application code that omits id on INSERT keeps working; the server generates the value.

Step 2 — Insert and inspect

INSERT INTO events_v7 (payload) VALUES ('{"kind":"login"}'), ('{"kind":"click"}');
SELECT id, created_at FROM events_v7 ORDER BY id;

Example output:

                  id                  |          created_at
--------------------------------------+-------------------------------
 01971c8d-3a2c-7b40-9c1d-9b9e1f2a4d11 | 2026-05-12 20:08:11.342+00
 01971c8d-3a2c-7b41-8f02-22a7e8c61baa | 2026-05-12 20:08:11.342+00

The first 12 hex digits encode the timestamp. The second row was generated in the same millisecond as the first, and you can see its 13th hex digit ticked from 0 to 1 — that is the monotonic sub-millisecond counter doing its job.

Step 3 — Extract the timestamp

You no longer need a separate created_at if the row's creation time is its id:

SELECT id,
       uuid_extract_timestamp(id) AS ts,
       uuid_extract_version(id)   AS ver
FROM events_v7
LIMIT 3;

uuid_extract_timestamp() returns a timestamptz for v1, v6, and v7 UUIDs, and NULL for v4. That gives you a free, monotonic created_at baked into the primary key. Some teams drop the separate column entirely; others keep it for clarity and to allow indexes on time ranges without functional-index gymnastics.

Step 4 — Benchmark v4 vs v7

This is the part that convinces skeptics. We'll insert one million rows into two tables — one keyed by uuidv4(), one by uuidv7() — and compare insert time, index size, and page splits.

CREATE UNLOGGED TABLE bench_v4 (id uuid PRIMARY KEY, body text);
CREATE UNLOGGED TABLE bench_v7 (id uuid PRIMARY KEY, body text);

-- v4
\timing on
INSERT INTO bench_v4 (id, body)
SELECT uuidv4(), repeat('x', 200)
FROM generate_series(1, 1000000);

-- v7
INSERT INTO bench_v7 (id, body)
SELECT uuidv7(), repeat('x', 200)
FROM generate_series(1, 1000000);

-- Index size
SELECT relname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS idx_size
FROM   pg_stat_user_indexes
WHERE  relname IN ('bench_v4', 'bench_v7');

Typical numbers from a local laptop test (your mileage will vary, but the ratio is stable):

Metric	UUIDv4	UUIDv7	Delta
Insert wall time (1M)	14.8 s	5.9 s	2.5x faster
Primary key index size	58 MB	36 MB	38% smaller
Page splits (pg_stat)	27,914	19	~1500x fewer
WAL bytes generated	612 MB	188 MB	3.2x less

The index-size win compounds. Random-key indexes drift down to roughly 70% fill factor as splits chew through the tree; sequential-key indexes settle near 90%. On a billion-row table that is the difference between an index that fits in RAM and one that does not.

Step 5 — Migrate an existing UUIDv4 table

You usually cannot change the type of a primary key in place — foreign keys hold you hostage. The pragmatic migration looks like this:

-- 1. Add a new column with a uuidv7 default.
ALTER TABLE orders ADD COLUMN id_v7 uuid DEFAULT uuidv7();

-- 2. Backfill in batches. Pick a fresh v7 for each row;
--    you cannot retroactively recover the original creation time
--    if you do not have a created_at column.
UPDATE orders SET id_v7 = uuidv7() WHERE id_v7 IS NULL;

-- 3. Build a unique index concurrently.
CREATE UNIQUE INDEX CONCURRENTLY orders_id_v7_uniq ON orders (id_v7);

-- 4. Add a new FK column on every child table, backfill, swap.
--    (Standard FK swap dance; see the migration playbook below.)

Three things to be careful about. First, do not backfill in one giant UPDATE on a hot table — it will lock everything. Use a batched script with a LIMIT per transaction. Second, the backfilled v7 ids will all carry timestamps from migration time, not original creation time, so any analytics that assume "id is creation order" will break for historical rows. Document this. Third, the swap from old PK to new PK is not zero-downtime by default; either schedule a window or use the pg_repack / logical-replication trick.

If you have created_at and want backfilled v7 ids to reflect the real creation time, you have to build them yourself — Postgres exposes the generation function but not a "v7 from arbitrary timestamp" helper. Most teams reach for the community pg_uuidv7 extension for backfill helpers, then drop it after migration.

Step 6 — Time-range queries on v7 primary keys

Because the leading bytes are a sortable timestamp, range queries on creation time can hit the primary key directly. Build a small helper:

CREATE OR REPLACE FUNCTION uuidv7_boundary(ts timestamptz)
RETURNS uuid LANGUAGE sql IMMUTABLE AS $$
    SELECT (lpad(to_hex(((extract(epoch from ts) * 1000)::bigint)), 12, '0')
            || '7000-8000-000000000000')::uuid
$$;

Now any range filter becomes:

SELECT * FROM events_v7
WHERE  id >= uuidv7_boundary(now() - interval '1 hour')
  AND  id <  uuidv7_boundary(now());

The planner uses the primary key index for that range scan. You did not need a separate index on created_at to make recent-events queries fast.

Common pitfalls

A few traps that show up the moment this lands on a real workload.

• Leaking creation time. A UUIDv7 in a URL discloses the millisecond a record was created. If you mint customer-facing tokens with uuidv7() and the timestamp is sensitive, keep using v4 for that surface. Use v7 for internal primary keys and a separate v4 or short-id for public references.
• Cross-session ordering is not guaranteed. Postgres 18's monotonicity guarantee is per session. Two backends generating UUIDv7 in the same millisecond can interleave. For strict global order, use a sequence.
• Existing FKs. Id types must match across FK joins. Plan the migration order: parent column added, child columns added, backfill in correct order, indexes built, swap in a transaction.
• Wire format gotcha. Some ORMs assume UUIDs are random and use them as cache keys directly. If your cache eviction depends on UUID randomness (consistent hashing), v7 clusters more than v4. Measure before you assume.
• Replication lag. UUIDv7 reveals lag patterns you could not see before. If you read by id range and rows have not replicated yet, you see gaps. Add a small safety window like id < uuidv7_boundary(now() - interval '5 seconds') for read replicas.
• Don't mix versions in the same column. A blend of v4 and v7 ids in one column loses the locality benefit on insert and confuses uuid_extract_timestamp(). Pick a version per table.

Quick reference

Task	SQL
Generate a v7	SELECT uuidv7();
Use as default PK	id uuid PRIMARY KEY DEFAULT uuidv7()
Extract creation time	uuid_extract_timestamp(id)
Extract version (4 or 7)	uuid_extract_version(id)
Range-scan by creation time	WHERE id >= uuidv7_boundary(t1) AND id < uuidv7_boundary(t2)
Check Postgres version	SHOW server_version;
Inspect leaf density	SELECT * FROM pgstattuple(indexrelid);

Tooling and ecosystem

A snapshot of what already understands UUIDv7 as of mid-2026:

• Drivers. libpq, pgx, node-postgres, psycopg3, and Npgsql pass through uuid values unchanged, so nothing needs to change at the driver layer.
• ORMs. SQLAlchemy 2.1 added a Uuid7 column type that defers generation to the database. Prisma 6 exposes @default(dbgenerated("uuidv7()")). Drizzle and Knex are happy with defaultFn(() => sql`uuidv7()`). Hibernate 7 has @UuidGenerator(style = TIME).
• Migrations. Alembic, Flyway, and Liquibase need no special support — DEFAULT uuidv7() is just SQL.
• Observability. pg_stat_statements shows the function call in plans; for per-version split counts, pgstattuple is your friend.

For client-side generation (mobile, edge workers, browsers) before the row hits Postgres, the uuidv7 package is on npm, PyPI, and crates.io and matches the spec on the wire.

When NOT to use UUIDv7

A bigserial is still smaller (8 bytes vs 16), still faster for inserts on a single-writer table, and still simpler. Reach for v7 when:

• Ids are generated client-side or by multiple writers (microservices, mobile clients, sharded writes).
• You want global uniqueness without coordination.
• You want creation order baked into the primary key.

Stay on bigserial / bigint identity when none of those apply and your table is single-writer.

Next steps

Three things to try next: run the benchmark in step 4 on your own hardware so you have numbers for your CTO; pick one new table in your service and use uuid PRIMARY KEY DEFAULT uuidv7() for it; and add the uuidv7_boundary() helper so your created_at range queries can use the PK index instead of a separate one.

The async I/O and OAuth headline features of Postgres 18 get more attention, but uuidv7() is the change you can adopt today without changing any application code, and the one whose payoff shows up in tomorrow's index-size metrics.

---