UUID in PostgreSQL: the uuid type, v7 vs v4, bigint vs UUID

← Back to the section

UUID is the standard way to hand out identifiers when they are issued not by one database but by several services at once. Yet even on a single PostgreSQL, three decisions — which column type to use, which UUID version, and who generates it — visibly change insert speed and index size.

The uuid type, not varchar(36)

The first time a UUID goes into a database, the instinct is to store it as a string: 550e8400-e29b-41d4-a716-446655440000 is 36 characters, so varchar(36). Don't. PostgreSQL has its own uuid type, and it keeps the value as 16 bytes of binary, not as text.

-- wrong
CREATE TABLE customer_bad (
    id   varchar(36) PRIMARY KEY,
    name text NOT NULL
);

-- right
CREATE TABLE customer (
    id   uuid PRIMARY KEY,
    name text NOT NULL
);

varchar(36) takes 37 bytes — 36 characters plus one byte of string header. But size is not the whole story: the uuid type normalises the spelling, a string does not.

live example

SELECT '550e8400-e29b-41d4-a716-446655440000'::uuid
         = '550E8400-E29B-41D4-A716-446655440000'::uuid AS uuid_same,
       '550e8400-e29b-41d4-a716-446655440000'
         = '550E8400-E29B-41D4-A716-446655440000'       AS text_same;

For the uuid type that is one value; for a string it is two different ones. Let one service write the identifier in upper case and another look it up in lower case, and the row is simply not found. The uuid type also validates the format on insert: 'not-a-uuid' will be rejected by a uuid column and stored silently by varchar(36).

On a schema with dozens of tables and foreign keys, the size difference adds up to tens of gigabytes. Indexes grow with the data and get slower to read. So: the uuid type only, no varchar(36), char(36), or text.

UUID v4 makes a poor primary key

UUID v4 is fully random. Each new identifier lands at an arbitrary spot on the number line. For a database, that is a problem.

A primary key in PostgreSQL is a btree index, and a btree keeps keys in sorted order, laid out in pages. When you insert rows with UUID v4, every new insert lands in a random place in that tree:

Three consecutive inserts, three different pages: a random key scatters them across the whole index, and none of those pages stays in the cache.

Three consequences follow. First, every insert may have to pull a separate page off disk, and the buffer cache is evicted all the time — there are no hot pages, the whole tree is hot. Second, an insert into the middle of a full page forces Postgres to split it in half, so pages sit 30–60 % full and the index takes up roughly twice the space it could. Third, the primary key cannot answer "the last N records" — identifiers are scattered, and such a query needs a separate index on the creation time.

On a 100-million-row table this shows up as a two- to fivefold slowdown in inserts and as dips until the cache warms up.

UUID v7 — the same UUID, but with time inside

UUID v7 is built differently: the first 48 bits are a timestamp in milliseconds, the rest are random bits. Which means values created one after another differ only in the tail:

live example

SELECT id,
       to_timestamp(('x' || substr(replace(id::text, '-', ''), 1, 12))::bit(48)::bigint / 1000.0) AS created_at
FROM (VALUES
        ('0199c1a2-3b4c-7a1e-8f00-2b7d9c4e1a55'::uuid),
        ('0199c1a2-3b12-7f42-93c1-8ae05d6b2210'::uuid),
        ('0199c1a2-3ad0-7c88-a4f2-11e3b9740c6d'::uuid)
     ) AS v7(id)
ORDER BY id;

The query takes the first 12 hexadecimal digits — those very 48 bits — and turns them into a timestamp. Sorting by the identifier gives creation order, though the query says nothing about time.

All three consequences of v4 disappear:

• The buffer cache holds the hot tail of the tree — new keys are always next to each other, not smeared across the whole index.
• Inserts close in time land on the same btree page, and it fills to 90 % and more.
• "The last N records" is a plain range scan on the primary key; no extra index needed.

Global uniqueness does not suffer: 74 bits are left for the random part, and guessing someone else's identifier by brute force is still impossible. But v7 does give one thing away: the creation time of the row sits in the leading bits in plain sight, and the query above reads it back. If the identifier in a page address must not reveal when the object appeared, use v4.

Moving from v4 to v7 is easy: the column type in PostgreSQL is the same — uuid, the format is the same — 16 bytes. Only the generator changes. The gen_random_uuid() function returns v4, so it is not the one to use for a primary key.

How to generate UUID v7 in the application

Generate the UUID in the application, not in the database: the identifier is then known before the row is written. That lets you:

• write an event to a queue with the same id that will go into the database;
• return the identifier to the client immediately, without waiting for the commit;
• use the id in logs from the very start of the transaction.

// build.gradle.kts
// implementation("com.github.f4b6a3:uuid-creator:5.3.7")

import com.github.f4b6a3.uuid.UuidCreator;
import java.util.UUID;

UUID id = UuidCreator.getTimeOrderedEpoch();   // UUID v7

// go get github.com/google/uuid@v1.6.0

import "github.com/google/uuid"

id, err := uuid.NewV7()   // UUID v7 (available since v1.6.0)
if err != nil {
    return err
}

// npm install uuid

import { v7 as uuidv7 } from "uuid";

const id: string = uuidv7();   // UUID v7

# Python 3.14+ — uuid.uuid7() in the standard library
# for 3.12-3.13 you need the uuid6 or uuid_utils package
import uuid

record_id = uuid.uuid7()   # UUID v7

In PostgreSQL 18 and newer there is a built-in uuidv7() function — for cases where generation on the database side is still needed. Before PostgreSQL 18, only the application or an extension.

bigint or UUID — how to choose

UUID is not the only option. bigint GENERATED ALWAYS AS IDENTITY is a counter the database keeps itself: 8 bytes instead of 16, shorter in logs, faster in joins. The price is that the counter is one per database, and the value is only known after the insert.

The row about APIs is about /order/12345 announcing that you have roughly twelve thousand orders. Counting someone else's volumes from increasing numbers is an old trick.

Hence a simple rule: take UUID when several services hand out identifiers independently, when the id goes outside, or when it is needed before the write. Take bigint IDENTITY when there is one database, the id never leaves it, and simplicity matters more.

You can use both

In practice, large products often combine them: bigint as the internal primary key for fast joins, and a separate uuid for everything that goes out.

CREATE TABLE order_doc (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    public_id   uuid   NOT NULL UNIQUE,
    created_at  timestamptz NOT NULL DEFAULT now()
);

public_id has no DEFAULT here on purpose: the application can generate v7, while gen_random_uuid() would give v4.

UUID and foreign keys: an index is mandatory

PostgreSQL does not create an index on a foreign key automatically — only on the primary key. On small tables this goes unnoticed; on large ones it backfires.

When you delete a record, PostgreSQL checks whether the child table has rows referencing it. Without an index that is a full scan of the child table. On 100 million rows — seconds per delete.

CREATE TABLE order_doc (
    id uuid PRIMARY KEY
);

CREATE TABLE order_item (
    id       uuid PRIMARY KEY,
    order_id uuid NOT NULL REFERENCES order_doc(id)
);

-- this index has to be created manually
CREATE INDEX ix_order_item_order_id ON order_item(order_id);

A foreign key means an index on it, as its own line in the migration.

How to migrate an existing varchar(36) to the uuid type

A single ALTER TABLE ... TYPE uuid will not do it: it rewrites the whole table under a heavy lock, and on a large table that means the service stops. So a new id_uuid uuid column is added next to the old one, filled in batches (UPDATE t SET id_uuid = id::uuid by key ranges), indexed with CREATE INDEX CONCURRENTLY, the application is switched to the new column, and only in the next release is the old one dropped.

This approach is called expand-contract: first you expand the schema, then you contract it.

In short

• The uuid type stores 16 bytes and compares them in one go; varchar(36) is 37 bytes, compared character by character, with no format check and no case normalisation.
• UUID v4 is fully random: inserts land on random btree pages, pages split and sit 30–60 % full.
• In UUID v7 the first 48 bits are a timestamp: keys close in time land side by side, pages pack to 90 %+, and "the last N" is a range scan on the primary key.
• The creation time can be read back out of a v7 value with a query. If that must not be visible, use v4.
• Generate UUIDs in the application: the id is known before the write. gen_random_uuid() is v4; the built-in uuidv7() only arrived in PostgreSQL 18.
• bigint IDENTITY takes 8 bytes and is easier to debug; UUID is justified for public addresses, global uniqueness, and when the id is needed before the commit.
• An index on a uuid foreign key is created by hand — PostgreSQL does not do it for you.

What to read next

• String types in PostgreSQL — why varchar is no place for an identifier.
• Numbers and precision in PostgreSQL — more on bigint and counters.
• Indexes in PostgreSQL — how a btree is built and when another index type is needed.
• Migrations without downtime — expand-contract in full, with locks and the order of steps.