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

← Back to the section

UUID is the standard way to make identifiers in distributed systems. But even if you work with a single PostgreSQL, the choice of type, UUID version, and who generates it has a big impact on performance. Let's break it down from scratch.

The uuid type, not varchar(36)

When storing a UUID in the database, the first instinct is to put it in a string. A UUID looks like 550e8400-e29b-41d4-a716-446655440000 — that's 36 characters, so varchar(36) seems logical. But it's wrong.

PostgreSQL knows about UUID and stores it as 16 bytes — a binary representation. varchar(36) will take at least 37 bytes plus overhead, and it compares character by character, like text.

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

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

The difference in practice:

On a schema with dozens of tables and foreign keys, the size difference adds up to tens of gigabytes. Indexes also become larger and slower.

Bottom line: always use the uuid type. No varchar(36), char(36), or text.

UUID v4 and why it works poorly as a primary key

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

A primary key in PostgreSQL is a btree index. A btree keeps data in sorted order. When you insert rows with UUID v4, each new insert lands in a random place in that tree:

INSERT 550e8400...  → index page 47
INSERT a1b2c3d4...  → index page 9123
INSERT 3f2504e0...  → index page 218

This means:

• Every insert may require loading a different page from disk.
• Postgres is forced to split btree pages on inserts into the middle — they fill up to 30–60%, and the rest of the space is wasted.
• The buffer cache is constantly evicted because hot pages are not concentrated.
• A "last 100 orders" query can't use the index efficiently — the identifiers are scattered across the whole tree.

On a 100-million-row table this shows up as a 2–5x slowdown in inserts and dips during cache warm-up.

UUID v7 — the same UUID, but with time inside

UUID v7 is built differently: the first 48 bits are a timestamp (milliseconds), the rest are random bits.

2026-05-07T12:00:00.001 → 0190abcd-...
2026-05-07T12:00:00.002 → 0190abce-...
2026-05-07T12:00:00.003 → 0190abcf-...

Inserts that are close in time get adjacent identifiers — and land on the same btree page. Everything changes:

• Index pages fill up to 90%+.
• The buffer cache holds hot pages — they're next to each other.
• A "last N records" query turns into a simple range scan over the index.
• Global uniqueness is preserved — 74 random bits are enough to avoid collisions.
• UUID v7 is just as unpredictable from the outside — you can't guess someone else's identifier.

Switching 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.

Rule: for primary keys and foreign keys use UUID v7, not v4. The gen_random_uuid() function in PostgreSQL generates v4 — don't use it for a PK.

How to generate UUID v7 in the application

You should generate the UUID on the application side, not in the database. The reason is simple: then the identifier is known before the row is written to the database. This 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
// npm install --save-dev @types/uuid

import { v7 as uuidv7 } from "uuid";

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

# Python 3.12+ — uuid.uuid7() in stdlib
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 IDENTITY (an auto-increment integer identifier) takes 8 bytes versus 16, is faster and easier to debug. The choice depends on the requirements.

UUID is justified when:

• The identifier must be globally unique — several services or databases generate ids independently.
• The id is exposed in an API or URL, and you don't want it to reveal your data volume. /order/12345 clearly says "we have about 12 thousand orders."
• The id is needed before the write to the database — for events, for returning to the client before the commit.

bigint IDENTITY is justified when:

• One service, one database, no need for global uniqueness.
• The id is internal — it never goes out.
• Simplicity matters: 1234 is easier to find in logs than a UUID.

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 DEFAULT gen_random_uuid(),
    -- ...
);

bigint — for internal table joins. uuid — for the API and URLs. If you need the uuid in public_id to specifically be v7 — generate it on the application side and pass it explicitly, don't rely on DEFAULT gen_random_uuid() (that's v4).

UUID and foreign keys: an index is mandatory

PostgreSQL does not create an index on a foreign key automatically. On small tables this goes unnoticed. On large ones — a serious problem.

When you delete a record, PostgreSQL checks whether the child table has any rows that reference it. Without an index this is a full scan of the child table (sequential scan). 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);

Rule: if a table has a foreign key of type uuid — create an index on it explicitly.

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

If the database already has a varchar(36) column with UUIDs and you need to move to the uuid type without downtime, it's done in stages:

1. Add a new id_uuid uuid column.
2. Fill it: UPDATE t SET id_uuid = id::uuid; — in small batches, so the table isn't locked.
3. Build indexes and move the foreign keys to the new column (CREATE INDEX CONCURRENTLY).
4. Switch the application to read from the new column.
5. In a separate release, drop the old varchar(36).

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

In short

• The uuid type in PostgreSQL stores 16 bytes and compares as a number. varchar(36) — 37+ bytes, character-by-character comparison, no format validation.
• UUID v4 is fully random — inserts land in random places in the btree, pages fill up to 30–60%, the cache gets evicted.
• UUID v7 contains a timestamp in the first 48 bits — adjacent inserts land next to each other, pages fill up to 90%+, range scans work.
• Generate UUIDs on the application side — the id is known before the write to the database.
• bigint IDENTITY takes 8 bytes and is simpler. UUID is justified for public APIs, global uniqueness, and when the id is needed before the commit.
• A foreign key of type uuid needs an explicit index — PostgreSQL does not create it automatically.

What to read next

• String types in PostgreSQL — why not varchar for UUID.
• Numbers and precision in PostgreSQL — bigint IDENTITY in more detail.
• Indexes in PostgreSQL — when and which index you need.
• Migrations without downtime — the expand-contract pattern.