Select Optimal Primary Key Strategy

Why This Matters

The primary key choice affects index performance, storage efficiency, and query speed. Sequential keys provide better index locality, while UUIDs offer distributed generation but can cause fragmentation.

Index locality: Sequential keys keep new rows together, improving cache efficiency
Fragmentation: Random UUIDs scatter inserts across the index, causing page splits
Generation overhead: serial uses sequences; IDENTITY is more efficient
Time-ordered UUIDs: UUIDv7 provides sequential-like behavior with global uniqueness

Common Mistakes

Incorrect - Using deprecated serial or random UUIDs

-- Deprecated: serial uses sequences (less efficient)
CREATE TABLE users (
  id SERIAL PRIMARY KEY  -- Old style, not recommended
);

-- Random UUIDv4 causes index fragmentation
CREATE TABLE events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid()  -- Random, causes fragmentation
);

Best Practices

Use IDENTITY Instead of SERIAL

Correct - IDENTITY is standard and more efficient

-- IDENTITY is the modern standard (Postgres 10+)
CREATE TABLE users (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT UNIQUE
);

-- Or use BY DEFAULT for manual inserts
CREATE TABLE users (
  id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  email TEXT UNIQUE
);

-- Benefits: Standard SQL, better performance, explicit control

Use UUIDv7 for Distributed Systems

-- UUIDv7 is time-ordered (better than UUIDv4)
-- Requires pg_uuidv7 extension (Postgres 17+)
CREATE EXTENSION IF NOT EXISTS pg_uuidv7;

CREATE TABLE events (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),  -- Time-ordered
  user_id INT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- UUIDv7 benefits:
-- - Time-ordered (better index locality than v4)
-- - Globally unique (no coordination needed)
-- - Can be generated client-side
-- - Better for distributed systems

UUIDv7 vs UUIDv4: UUIDv7 embeds a timestamp, making inserts more sequential and reducing index fragmentation compared to random UUIDv4. Use UUIDv7 when you need distributed ID generation.

When to Use Each Strategy

INT IDENTITY: Single-database applications, best performance, smallest storage (4 bytes)
BIGINT IDENTITY: High-volume tables that might exceed 2B rows
UUIDv7: Distributed systems, microservices, client-side ID generation, multi-database setups
Avoid UUIDv4: Random values cause severe index fragmentation; use UUIDv7 instead

-- Single database: Use INT IDENTITY
CREATE TABLE users (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT
);

-- Distributed system: Use UUIDv7
CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
  user_id UUID,
  total NUMERIC(10, 2)
);

-- High volume: Use BIGINT IDENTITY
CREATE TABLE analytics_events (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  event_type TEXT
);

Performance comparison: INT IDENTITY provides the best insert performance (sequential writes). UUIDv7 is 2-3x slower than INT but 10x faster than UUIDv4 due to better locality. UUIDv4 causes severe fragmentation and should be avoided.

Check Your Project Automatically

Connect your Supabase project to identify suboptimal primary key strategies and optimization opportunities.

Start Supabase Audit

Common Mistakes

Using SERIAL: SERIAL is deprecated. Use GENERATED ... AS IDENTITY instead (standard SQL, better performance).
UUIDv4 for everything: Random UUIDs cause index fragmentation. Use UUIDv7 for distributed systems, INT IDENTITY for single-database apps.
Not considering scale: Using INT when you might exceed 2B rows. Use BIGINT for high-volume tables.
Composite keys unnecessarily: Simple INT IDENTITY is usually better than composite keys unless you need natural keys.

Why This Matters

Common Mistakes

Best Practices

Use IDENTITY Instead of SERIAL

Use UUIDv7 for Distributed Systems

When to Use Each Strategy

Check Your Project Automatically

Common Mistakes

Related Practices

Learn More