UUIDs in PostgreSQL

The column#

CREATE TABLE orders (
    id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    created_at timestamptz NOT NULL DEFAULT now()
);

-- PostgreSQL 18 and later
CREATE TABLE events (
    id uuid PRIMARY KEY DEFAULT uuidv7()
);

gen_random_uuid() is built in since PostgreSQL 13 and returns a v4. PostgreSQL 18 added uuidv7(), which is the one you want for a primary key. On older versions, generate v7 in the application; the column type is the same either way.

Why the version matters to the index#

A B-tree writes a new row next to the keys it sorts near. With v4 keys, every insert lands somewhere else, so the write set is the whole index: pages are read, dirtied, and written back all over the tree, WAL grows with full-page images, and the buffer cache fills with pages nothing will read again. With v7 keys, inserts land in the rightmost leaf, which stays hot.

RFC 9562 § 6.13 · DBMS and Database ConsiderationsWhat the standard says about database considerations

Never store a UUID as text#

A uuid column is 16 bytes and compares as two 64-bit words. varchar(36) is 36 bytes plus the length header, compares character by character, and lets two spellings of one identifier live in the same column. The type also validates on write, which is a free correctness check the text column does not give you.

Practical notes#

• uuid_generate_v4() comes from the uuid-ossp extension and predates gen_random_uuid(); new code needs neither the extension nor that function.
• PostgreSQL indexes carry only their own columns plus a pointer into the heap, so a UUID primary key does not widen the secondary indexes the way it does in InnoDB. What it does widen is the primary key index itself and every foreign key column pointing at it.
• For partitioned tables, a v7 key partitions by time without a separate column.
• The output format is canonical lower case; input accepts braces, the urn prefix and bare hex.

• PostgreSQL: the uuid data typeAccepted input forms and storage
• PostgreSQL: UUID functionsgen_random_uuid and, from 18, uuidv7