UUID for Distributed Photo IDs

advanced

Photo-sharing app with multiple database shards. Traditional INT auto-increment requires coordination across shards (slow, complex). Solution: Use UUID for globally unique IDs without coordination.

-- Problem: INT with auto-increment

CREATE TABLE photos_int (

photo_id SERIAL PRIMARY KEY, -- Conflicts across shards

user_id INTEGER,

image_url TEXT

);

-- Problem: Shard 1 generates photo_id=1, Shard 2 also generates photo_id=1 (collision)

-- Solution: UUID

CREATE TABLE photos (

photo_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

user_id UUID,

image_url TEXT,

created_at TIMESTAMP DEFAULT NOW()

);

-- Insert with auto-generated UUID

INSERT INTO photos (user_id, image_url)

VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'https://cdn.example.com/photo1.jpg')

RETURNING photo_id;

-- Returns: 550e8400-e29b-41d4-a716-446655440000 (globally unique)

-- No collisions across shards

-- Shard 1: 550e8400-e29b-41d4-a716-446655440000

-- Shard 2: 7c9e6679-7425-40de-944b-e07fc1f90ae7

-- Different UUIDs, no coordination needed

UUID enables distributed ID generation without coordination. Benefits: (1) Globally unique (no collisions), (2) No coordination needed (fast), (3) Can generate client-side. Trade-off: 16 bytes vs 4 bytes INT (4x larger), slower joins. Real-world: Instagram uses UUID for 2B users across 1000+ shards.

UUID solves distributed ID generation. No coordination needed across shards. Trade-off: 4x larger than INT, slower joins. Use UUID for distributed systems. Use INT for single database.

PostgreSQL

UUID: 16 bytes, globally unique, no coordination. INT: 4 bytes, requires coordination. UUID joins are slower (random order) vs INT (sequential). Use UUID for distributed systems, INT for single database.

graph TB

subgraph "INT Problem"

I1["Shard 1: photo_id=1<br/>Shard 2: photo_id=1<br/>COLLISION"]

I2["Need coordination<br/>Slow, complex"]

end

subgraph "UUID Solution"

U1["Shard 1: 550e8400...<br/>Shard 2: 7c9e6679...<br/>NO COLLISION"]

U2["No coordination<br/>Fast, simple"]

end

subgraph "Trade-offs"