UUIDs in PostgreSQL: The Complete Guide

PostgreSQL has one of the most mature UUID implementations among relational databases. Its native uuid data type stores UUIDs as 16 bytes of binary data, making it significantly more efficient than storing them as text. Here is a deep dive into everything you need to know.

Setting Up UUID Support

PostgreSQL requires an extension for server-side UUID generation. Choose between uuid-ossp and pgcrypto:

-- Option 1: uuid-ossp (v1, v4, v5)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL
);

-- Option 2: pgcrypto (v4 only, simpler)
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id),
  total DECIMAL(10, 2) NOT NULL
);

UUID vs CHAR vs TEXT Storage

Using the native uuid type is always the best choice. Here is why:

Column Type	Storage	Index Size	Validation
`UUID`	16 bytes	Smallest	Built-in
`CHAR(36)`	36 bytes	2.25x larger	None
`TEXT`	36+ bytes	Varies	None

Indexing Strategies

For uuid columns, PostgreSQL supports both B-tree and hash indexes. B-tree is the default for primary keys and works well, especially with UUID v7. For very large tables with random UUID v4 keys, consider these optimizations:

-- Standard B-tree primary key (good for v7, adequate for v4)
ALTER TABLE users ADD PRIMARY KEY (id);

-- Hash index (faster equality lookups, smaller size)
CREATE INDEX CONCURRENTLY idx_users_id_hash ON users USING hash (id);

-- Partial index for recent records (combine with UUID v7)
CREATE INDEX idx_recent_orders ON orders (created_at DESC)
WHERE created_at > NOW() - INTERVAL '30 days';

Integer to UUID Migration

Migrating from auto-increment integers to UUIDs requires careful planning. Here is a safe migration approach for PostgreSQL:

-- Step 1: Add new uuid column
ALTER TABLE users ADD COLUMN new_id UUID DEFAULT gen_random_uuid();

-- Step 2: Populate existing rows
UPDATE users SET new_id = gen_random_uuid() WHERE new_id IS NULL;

-- Step 3: Add foreign keys with new uuid column to related tables
ALTER TABLE orders ADD COLUMN new_user_id UUID;

-- Step 4: Update foreign key references
UPDATE orders o SET new_user_id = u.new_id
FROM users u WHERE o.user_id = u.id;

-- Step 5: Switch primary key (requires downtime)
BEGIN;
ALTER TABLE users DROP CONSTRAINT users_pkey CASCADE;
ALTER TABLE users RENAME COLUMN id TO old_id;
ALTER TABLE users RENAME COLUMN new_id TO id;
ALTER TABLE users ADD PRIMARY KEY (id);
COMMIT;

UUID v7 in PostgreSQL

PostgreSQL does not yet ship built-in UUID v7 generation. Until it does, use a custom function:

CREATE OR REPLACE FUNCTION uuid_generate_v7()
RETURNS UUID AS $$
DECLARE
  timestamp_ms BIGINT;
  rand_bytes BYTEA;
BEGIN
  timestamp_ms := (EXTRACT(EPOCH FROM clock_timestamp()) * 1000)::BIGINT;
  rand_bytes := gen_random_bytes(10);
  RETURN (
    lpad(to_hex(timestamp_ms), 12, '0') ||
    substring(encode(rand_bytes, 'hex') from 1 for 20)
  )::UUID;
END;
$$ LANGUAGE plpgsql;

Performance Tips

Always use the native uuid type — never store UUIDs as CHAR(36) or TEXT
Use UUID v7 for high-write tables — prevents B-tree page splits
Consider hash indexes for large v4 tables — 30-40% smaller than B-tree
Set fillfactor lower for random UUID indexes — e.g., FILLFACTOR = 80
Monitor bloat — random UUIDs can cause more index bloat over time

UUID Beautifier