Surrogate keys decouple identity from business data — rows are stable even when business attributes change. UUID v7 combines surrogate key properties (generated, opaque, stable) with time-ordered monotonic behavior. See Composite Key for multi-tenant and partitioned variants.
UUID v7 as a surrogate primary key
-- PostgreSQL: UUID v7 surrogate key
CREATE TABLE customers (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email TEXT NOT NULL UNIQUE, -- natural key — can change
name TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- With UUID v7 client generation, omit DEFAULT and supply id from app
-- UUID v7 sort replaces ORDER BY created_at
import { Pool } from 'pg';
import { v7 as uuidv7 } from 'uuid';
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
async function createCustomer(email: string, name: string) {
const id = uuidv7(); // UUID v7 surrogate key — immutable, time-ordered
await pool.query(
'INSERT INTO customers (id, email, name) VALUES ($1, $2, $3)',
[id, email, name]
);
return id;
}
Surrogate vs natural key
// Natural key — changes when customer updates email
const byEmail = await pool.query(
'SELECT * FROM orders WHERE customer_email = $1',
['alice@example.com']
);
// Surrogate key — stable through all email changes
const byId = await pool.query(
'SELECT * FROM orders WHERE customer_id = $1',
[customerId]
);
Surrogate key comparison table
| Key type | Coordination needed | Time-ordered | Portable | Business data |
|---|---|---|---|---|
| Auto-increment | Yes (centralized) | Yes | No | No |
| UUID v4 | No | No | Yes | No |
| UUID v7 | No | Yes (ms) | Yes | No |
| ULID | No | Yes (ms) | Partial | No |
| Natural key (email) | No | No | No | Yes |
Cursor pagination on surrogate UUID v7
// UUID v7 surrogate key = natural sort order by creation time
async function listCustomers(cursor?: string, limit = 20) {
const { rows } = cursor
? await pool.query(
'SELECT id, email, name FROM customers WHERE id > $1 ORDER BY id LIMIT $2',
[cursor, limit]
)
: await pool.query(
'SELECT id, email, name FROM customers ORDER BY id LIMIT $1',
[limit]
);
return {
items: rows,
nextCursor: rows.length === limit ? rows.at(-1)?.id : null,
};
}
Extract creation time from UUID v7 surrogate key
function createdAt(id: string): Date {
const hex = id.replace(/-/g, '').slice(0, 12);
return new Date(parseInt(hex, 16));
}
// No created_at column needed — UUID v7 is self-describing
const customer = await getCustomer(id);
console.log(`Customer created: ${createdAt(customer.id).toISOString()}`);
Further reading
- Composite Key — Multi-column Identity
- UUID v4 vs UUID v7
- ULID — Universally Unique Lexicographic Identifier
- UUID in PostgreSQL
- What is a UUID?
Frequently asked questions
What is a surrogate key?
A surrogate key is a unique row identifier generated by the database or application — not derived from business data. Examples are auto-increment integers, UUIDs, and ULIDs. Surrogate keys never change even if business data changes (e.g., a customer email update does not affect the surrogate key). See Composite Key for multi-column alternatives.
Why use UUID v7 as a surrogate key instead of an integer?
Integer surrogate keys require a centralized sequence — problematic in distributed systems, multi-region writes, and microservices. UUID v7 is generated client-side with no coordination, making it safe across any number of writers. UUID v7 also encodes creation time, providing chronological ordering and cursor pagination without extra columns. See UUID v4 vs UUID v7.
What is the difference between a surrogate key and a natural key?
A natural key is derived from business data — email address, product SKU, or ISBN. A surrogate key is system-generated with no business meaning. Natural keys can change (email updates) and may not be globally unique across systems. Surrogate UUID v7 keys are immutable, globally unique, and decoupled from business logic changes.