UUID as a primary key in Postgres: use uuidv7(), store it as uuid, and measure

Step 01: Check your Postgres version

select version();
select uuidv7() as v7, uuidv4() as v4;

If uuidv7() exists, you are on 18 or newer. It builds a 48-bit Unix timestamp in milliseconds, a sub-millisecond fraction and random bits, per the PostgreSQL 18 docs. uuidv4() is the same as gen_random_uuid().

Step 02: Declare the key as uuid with a uuidv7() default

create table orders (
  id uuid primary key default uuidv7(),
  created_at timestamptz not null default now(),
  total_cents bigint not null
);

Two choices matter here. The type: uuid stores 16 bytes; a text column stores the 36-character string plus a header, and in our test that made the primary key index 729 MB against 389 MB for the same v4 values as uuid. The version: v7 starts with the time, so new keys arrive in order.

On PostgreSQL 17 or older there is no built-in v7. Generate it in your app (we tested 10 libraries and some fail to keep order) or install pg_uuidv7 by Florian Boulnois, a small MPL-2.0 extension.

Step 03: Insert without passing an id

Let the default fill the key and ask for it back with RETURNING:

Our psql session, Oct 25 2026, 5:24 PM ET. Three IDs made within 2 milliseconds still sort in creation order.

The three IDs share their first 12 hex digits, the millisecond timestamp, and still sort in insert order because Postgres fills the next bits with a sub-millisecond fraction.

Step 04: Measure the table and the index

create extension if not exists pgstattuple;
select pg_size_pretty(pg_relation_size('orders'))      as heap,
       pg_size_pretty(pg_relation_size('orders_pkey')) as pk_index;
select avg_leaf_density, leaf_fragmentation from pgstatindex('orders_pkey');

This is where random keys show their cost. A B-tree (the index behind every primary key) keeps keys sorted in pages. Ordered keys always go on the last page, which fills to the 90% default and moves on. Random keys land on any page; when one is full it splits in two half-empty pages.

The text-v4 table from our third run, measured right after the inserts. Its index leaves are 69.7% full; the uuidv7() index measured 90.0%. Oct 25 2026.

WAL is what your replicas, backups and disk all have to carry. Random keys wrote 1.64x the WAL of uuidv7(); random text keys 4.35x. Our test, Oct 25 2026, median of 3 runs.

Every split writes extra pages to the write-ahead log, and after each checkpoint the first change to a page logs the whole page. Scatter writes over thousands of pages and that adds up: the same 10 million rows cost 2.99 GB of WAL with v4 keys and 1.82 GB with v7.

Step 05: Read the creation time when you need it

select id, uuid_extract_timestamp(id), uuid_extract_version(id) from orders;

A v7 key is also a timestamp, which is handy for debugging and pagination. Postgres's docs warn the extracted time "is not necessarily exactly equal to the time the UUID was generated" when another system made the ID. Our timestamp guide shows where it lies.

Step 06: Switch an existing v4 table to v7

alter table orders alter column id set default uuidv7();
-- optional, to compact the old random part of the index:
reindex index concurrently orders_pkey;

Old rows keep their v4 IDs, so nothing that references them breaks; v7 and v4 values live happily in one uuid column. New rows now append to the right edge of the index. REINDEX CONCURRENTLY rebuilds the index packed without blocking writes, at the cost of a second copy on disk while it runs.