UUID Primary Keys

Use UUIDs as globally unique identifiers for distributed and security-sensitive applications.

Overview

UUID (Universally Unique Identifier) is a 128-bit value represented as 32 hex digits with hyphens (e.g., 550e8400-e29b-41d4-a716-446655440000). PostgreSQL provides the uuid data type and, since v13, the built-in gen_random_uuid() function. UUIDs are ideal as primary keys when: rows are created across multiple servers, IDs must not be guessable, or merging data from different sources. The trade-off vs SERIAL: UUIDs are larger (16 bytes vs 4/8), cause more index bloat (random insertion order), and are harder to read. For most single-server applications, BIGSERIAL is simpler and more efficient.

Key Points

•UUID 128-bit, globally unique, stored as 16 bytes
•gen_random_uuid() generates a random UUID v4 (PG 13+)
•uuid-ossp extension: uuid_generate_v4() and other UUID versions
•Good for: distributed systems, external IDs, security (not guessable)
•Trade-off: larger than integer PKs, random order causes index fragmentation
•UUID v7 (time-ordered) reduces index fragmentation vs v4

Examples

UUID primary keys

-- Gen random UUID (PG 13+ built-in)
SELECT gen_random_uuid();

-- Table with UUID PK
CREATE TABLE sessions (
  id         UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id    INTEGER     NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO sessions (user_id) VALUES (42) RETURNING id;

-- uuid-ossp extension for more UUID versions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v4();
SELECT uuid_generate_v1mc();  -- time-based + random

UUID Primary Keys

Overview

Key Points

Examples

Exercises

Knowledge Check

What function generates a random UUID in PostgreSQL 13+?