UUIDs in PostgreSQL

PostgreSQL has first-class support for UUIDs with a dedicated uuid data type that stores values as 16 bytes internally — the most space-efficient representation possible.

Creating UUID columns:

-- UUID v4 as primary key (PostgreSQL 13+, no extension needed)
CREATE TABLE users (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    email text NOT NULL UNIQUE,
    created_at timestamptz DEFAULT now()
);

-- Insert with auto-generated UUID
INSERT INTO users (email) VALUES ('alice@example.com');

-- Insert with explicit UUID
INSERT INTO users (id, email)
VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'bob@example.com');

UUID generation functions:

Function	Source	Version
gen_random_uuid()	Built-in (PG 13+)	v4
uuid_generate_v4()	uuid-ossp extension	v4
uuid_generate_v1()	uuid-ossp extension	v1
uuid_generate_v1mc()	uuid-ossp extension	v1 (random MAC)
Custom function	PL/pgSQL	v7

UUID v7 in PostgreSQL: PostgreSQL does not yet have a built-in v7 generator, but you can create one with PL/pgSQL:

CREATE OR REPLACE FUNCTION uuid_generate_v7() RETURNS uuid AS $$
DECLARE
    unix_ts_ms bigint;
    uuid_bytes bytea;
BEGIN
    unix_ts_ms = (extract(epoch from clock_timestamp()) * 1000)::bigint;
    uuid_bytes = set_byte(
        set_byte(
            overlay(
                uuid_send(gen_random_uuid())
                placing substring(int8send(unix_ts_ms) from 3)
                from 1 for 6
            ),
            6, (get_byte(uuid_send(gen_random_uuid()), 6) & 15) | 112
        ),
        8, (get_byte(uuid_send(gen_random_uuid()), 8) & 63) | 128
    );
    RETURN encode(uuid_bytes, 'hex')::uuid;
END
$$ LANGUAGE plpgsql VOLATILE;

Alternatively, the pg_uuidv7 extension provides a native C implementation with better performance.

Indexing performance:

-- B-tree index (default for PRIMARY KEY)
-- Works well with UUID v7 (sequential), poorly with UUID v4 (random)
CREATE INDEX idx_users_id ON users (id);

-- BRIN index (Block Range Index) — excellent for UUID v7
-- Much smaller than B-tree, ideal for append-mostly tables
CREATE INDEX idx_events_id ON events USING BRIN (id);

-- Hash index — O(1) lookups, no range scans
-- Smaller than B-tree, good for equality-only queries
CREATE INDEX idx_sessions_id ON sessions USING HASH (id);

Storage comparison:

Type	Column Size	10M rows index
uuid	16 bytes	~350 MB
bigint	8 bytes	~175 MB
text(36)	40 bytes	~750 MB

Useful UUID operations in PostgreSQL:

-- Cast string to UUID
SELECT '550e8400-e29b-41d4-a716-446655440000'::uuid;

-- Extract timestamp from UUID v7 (approximate)
SELECT to_timestamp(
    ('x' || lpad(left(id::text, 8) || substr(id::text, 10, 4), 12, '0'))::bit(48)::bigint / 1000.0
) FROM events;