PostgreSQL UUID — the practical guide

Updated September 29, 2026 · 5 min read · by Samuel Yoo

To generate a UUID in PostgreSQL, use the native uuid type with gen_random_uuid() (random v4, built in since PostgreSQL 13) or uuidv7() (time-ordered v7, PostgreSQL 18 and later only). On PostgreSQL 13–17, generate v7 values in your application code and insert them. The older uuid-ossp extension is only needed for v1, v3 or v5.

TL;DR

-- PostgreSQL 18+ (September 2025): time-ordered, recommended for new tables
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 usual pattern. 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, and every commit dirties multiple pages. Time-ordered v7 keys avoid that scatter.

v7 UUIDs have a millisecond timestamp at the front. Sequentially-generated v7s land on the rightmost B-tree leaf, just like a serial integer, and you can still generate them without a central sequence. See UUID v4 vs v7 for primary keys for the index-locality breakdown, or UUID as primary key for UUID vs auto-increment.

-- 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;

-- uuid → 32-char hex, and hex → uuid
SELECT encode(uuid_send(id), 'hex') FROM orders;
SELECT encode('\x12fbc73a9e9d44b19e6d1d3e7c8a0f31'::bytea, 'hex')::uuid;
SELECT '12fbc73a9e9d44b19e6d1d3e7c8a0f31'::uuid;

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.
• gen_random_uuid() is built in from PostgreSQL 13. pgcrypto is still an extension. Install it only on older versions, where gen_random_uuid() is not in core.

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 v4 vs v7 for primary keys — B-tree locality vs timestamp leakage
• UUID as primary key — UUID vs auto-increment