PostgreSQL UUID — the practical guide

Updated April 26, 2026 · 5 min read · by Samuel Yoo

PostgreSQL has a native uuid data type and built-in generators. For new tables, use the uuid type with uuidv7() (PG 18+) or gen_random_uuid() (PG 13+). Avoid the older uuid-ossp extension unless you specifically need v3 or v5.

TL;DR

-- PostgreSQL 18+ (October 2025): time-ordered, the new default
CREATE TABLE orders (
    id uuid PRIMARY KEY DEFAULT uuidv7(),
    created_at timestamptz NOT NULL DEFAULT now(),
    ...
);

-- PostgreSQL 13–17: random UUIDs (built-in, no extension needed)
CREATE TABLE orders (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    ...
);

That’s the whole pattern for 95% of cases. Read on for when to deviate.

Built-in generators by version

Note: uuidv7() was added in PostgreSQL 18. For 13–17, generate v7 in your application code (see Python, JavaScript, C#) and insert the value.

Why v7 over v4 for primary keys

v4 UUIDs are random. As primary keys, they cause B-tree page splits all over the index — every insert lands on a different page, every commit dirties multiple pages. On a 100M-row table, this can be 2–5× slower than a serial integer.

v7 UUIDs have a millisecond timestamp at the front. Sequentially-generated v7s land on the rightmost B-tree leaf, just like a serial integer. Same insert performance, none of the coordination issues. See UUID as primary key for the full breakdown.

-- Migrate a v4 table to v7 for new rows (existing rows keep their v4 IDs)
ALTER TABLE orders ALTER COLUMN id SET DEFAULT uuidv7();

Indexing

The default B-tree index works fine for both v4 and v7. For very large tables (>100M rows), consider:

-- Hash index — smaller than B-tree, equality lookups only
CREATE INDEX orders_id_hash ON orders USING hash (id);

-- BRIN — tiny, only useful if data is physically clustered by ID
-- (works for v7 because of the time-ordered prefix, not for v4)
CREATE INDEX orders_id_brin ON orders USING brin (id);

For most workloads, the B-tree primary key index is enough.

Storage size

Don’t store UUIDs as text. It’s twice the storage, slower to compare, and doesn’t validate format.

Casting

-- text → uuid (validates format, throws on invalid)
SELECT '12fbc73a-9e9d-44b1-9e6d-1d3e7c8a0f31'::uuid;

-- uuid → text (canonical lowercase form)
SELECT id::text FROM orders;

-- bytes ↔ uuid
SELECT encode(uuid_send(id), 'hex') FROM orders;  -- 32-char hex
SELECT uuid_recv('\x12fbc73a9e9d44b19e6d1d3e7c8a0f31'::bytea);

Generated columns: extracting v7 timestamps

If your primary key is v7, you can compute the creation time without storing a separate created_at:

ALTER TABLE orders
ADD COLUMN created_from_id timestamptz GENERATED ALWAYS AS (
    to_timestamp(
        ('x' || lpad(replace(id::text, '-', '')::text, 12))::bit(48)::bigint::numeric / 1000.0
    )
) STORED;

For most cases it’s cleaner to keep an explicit created_at column — the storage cost is trivial and queries against it are clearer. But for audit / forensics, knowing every v7 ID has its time embedded is useful.

Foreign keys

UUIDs as foreign keys work identically to any other type:

CREATE TABLE order_items (
    id uuid PRIMARY KEY DEFAULT uuidv7(),
    order_id uuid NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    ...
);

For very wide foreign key chains (e.g. a 4-level deep tree of UUID FKs), the 16-byte-per-FK overhead can add up. At that point consider whether the join pattern is right, not whether to abandon UUIDs.

Sharding / multi-tenant

UUIDs solve the “two databases, no coordination” problem cleanly. Two PostgreSQL instances on different hosts can both generate uuidv7() and insert into the same logical table without colliding.

For sharded systems (Citus, partitioned tables), UUIDs let any shard generate IDs without consulting the others — no central sequence service needed.

Performance tips

• Use uuid, not text. Repeating because it’s the most common mistake.
• Default to uuidv7() for primary keys. Random v4 is fine for non-PK identifiers (e.g. share tokens).
• Don’t index id::text. Indexes on the native uuid type are smaller and faster.
• pgcrypto is built in to PG 13+. No need to install if you’re using gen_random_uuid() on a recent version.

Common pitfalls

• SELECT id FROM ... WHERE id = '...' — works because PG auto-casts the literal. But in app code, pass the parameter as a typed UUID, not a string, to avoid round-trip casting overhead.
• uuid_generate_v4() vs gen_random_uuid() — both produce v4. Prefer gen_random_uuid(); it’s built-in (no extension).
• Migration with v4 → v7 — only new rows benefit. Existing v4 IDs stay v4 forever (and that’s fine).
• SELECT * FROM ... ORDER BY id with v4 sorts by random hex, which is essentially random. For chronological order with v4, you need a created_at column. With v7, ORDER BY id and ORDER BY created_at produce ~the same order.

Cheat sheet

Try the tools

• UUID v7 generator — generate keys client-side for older PG versions
• Bulk UUID generator — seed thousands of test rows
• UUID validator — paste a UUID, decode v7 timestamp
• UUID as primary key — the full v4-vs-v7 design discussion