UUID vs Sequential IDs: When to Use Each

The Great ID Debate

Choosing between UUIDs and sequential IDs is one of the most important architectural decisions in application design. Both have compelling advantages and significant trade-offs that can impact performance, security, and maintainability.

Performance Comparison

Database Index Performance

This is where the biggest difference lies:

Storage Impact

Performance Benchmarks

-- PostgreSQL INSERT performance (1M records)
-- Table with sequential ID primary key
Time: 15.3 seconds
Index size: 21 MB
Index depth: 3 levels

-- Table with UUID primary key  
Time: 34.7 seconds
Index size: 67 MB
Index depth: 4 levels

-- MySQL InnoDB (similar pattern)
Sequential: 12.1 seconds, 18 MB index
UUID: 41.3 seconds, 89 MB index

Security & Privacy Considerations

Information Disclosure

Real-World Security Examples

// Sequential ID vulnerability
GET /api/users/12345
GET /api/users/12346  // Easy to guess next user
GET /api/users/12347

// Reveals:
// - Total user count (at least 12,347)
// - Registration patterns
// - Business growth metrics

// UUID protection
GET /api/users/550e8400-e29b-41d4-a716-446655440000
GET /api/users/????-????-????-????-????????????  // Cannot guess

// Reveals: Nothing useful to attackers

Enumeration Attack Scenarios

• E-commerce: Competitors scraping product catalogs via sequential IDs
• Social platforms: Harvesting user profiles by ID iteration
• SaaS applications: Discovering customer count and growth rates
• Financial systems: Predicting transaction volumes and timing

Scalability & Distribution

Multi-Database Challenges

UUID Advantages in Distributed Systems

• No coordination required: Each instance generates unique IDs independently
• Offline-friendly: Generate IDs without database connection
• Merger-friendly: No conflicts when combining databases
• Microservices-ready: Services can own their ID generation

Distributed ID Generation Patterns

// Sequential ID - requires coordination
class SequentialIDGenerator {
  constructor(nodeId, nodes) {
    this.nodeId = nodeId;
    this.nodes = nodes;
    this.offset = nodeId;
  }
  
  nextId() {
    // Each node uses different starting offset
    this.offset += this.nodes;
    return this.offset;
  }
}

// Node 1: 1, 4, 7, 10, 13...
// Node 2: 2, 5, 8, 11, 14...  
// Node 3: 3, 6, 9, 12, 15...

// UUID - no coordination needed
class UUIDGenerator {
  nextId() {
    return crypto.randomUUID(); // Always unique
  }
}

// Node 1: f47ac10b-58cc-4372-a567-0e02b2c3d479
// Node 2: 6ba7b810-9dad-11d1-80b4-00c04fd430c8
// Node 3: 5f8a7cb2-4923-4567-8901-234567890abc

Database-Specific Considerations

PostgreSQL

-- PostgreSQL UUID setup
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  email VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Performance optimization: separate UUID for external use
CREATE TABLE users_optimized (
  id BIGSERIAL PRIMARY KEY,           -- Fast sequential internal ID
  external_id UUID DEFAULT uuid_generate_v4(),  -- Public UUID
  email VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW(),
  
  UNIQUE(external_id)
);

MySQL

-- MySQL UUID implementation
CREATE TABLE users (
  id BINARY(16) PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert with UUID
INSERT INTO users (id, email) 
VALUES (UNHEX(REPLACE(UUID(), '-', '')), '[email protected]');

-- Query with UUID  
SELECT HEX(id) as uuid, email 
FROM users 
WHERE id = UNHEX(REPLACE('f47ac10b-58cc-4372-a567-0e02b2c3d479', '-', ''));

MongoDB

Application Development Impact

URL Design

/users/12345
/orders/98765  
/products/54321

/users/f47ac10b-58cc-4372-a567-0e02b2c3d479
/orders/6ba7b810-9dad-11d1-80b4-00c04fd430c8
/products/550e8400-e29b-41d4-a716-446655440000

API Response Size

// Sequential ID response
{
  "users": [
    {"id": 12345, "name": "John"},
    {"id": 12346, "name": "Jane"},
    {"id": 12347, "name": "Bob"}
  ]
}
// Size: ~85 bytes

// UUID response  
{
  "users": [
    {"id": "f47ac10b-58cc-4372-a567-0e02b2c3d479", "name": "John"},
    {"id": "6ba7b810-9dad-11d1-80b4-00c04fd430c8", "name": "Jane"}, 
    {"id": "550e8400-e29b-41d4-a716-446655440000", "name": "Bob"}
  ]
}
// Size: ~205 bytes

Frontend Development

• Type safety: Sequential IDs use simple number types
• Form validation: UUIDs require regex validation
• URL routing: UUID patterns are more complex
• Debugging: Sequential IDs easier to remember and test

Hybrid Approaches

Dual ID Strategy

Use both sequential and UUID IDs for different purposes:

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,                    -- Internal fast lookups  
  uuid UUID DEFAULT uuid_generate_v4() UNIQUE, -- External API access
  email VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Internal queries use sequential ID
SELECT * FROM users WHERE id = 12345;

-- External API uses UUID
SELECT * FROM users WHERE uuid = 'f47ac10b-58cc-4372-a567-0e02b2c3d479';

Sequential UUIDs (ULID)

Combine the benefits of both approaches using ULID (Universally Unique Lexicographically Sortable Identifier):

// ULID format: 26 characters, sortable
// 01ARZ3NDEKTSV4RRFFQ69G5FAV
//  ^^^^^^^^^^  ^^^^^^^^^^^^^^^^
//  Timestamp    Random entropy

const { ulid } = require('ulid');

// Generate sortable UUIDs
const id1 = ulid(); // 01ARZ3NDEKTSV4RRFFQ69G5FAV
const id2 = ulid(); // 01ARZ3NDEKTSV4RRFFQ69G5FB0

// Naturally sorted by creation time
console.log(id1 < id2); // true

Short UUIDs

Use shortened versions of UUIDs for better URLs while maintaining uniqueness:

const shortUUID = require('short-uuid');

// Generate short UUID (22 characters vs 36)
const short = shortUUID.generate();
console.log(short); // "mhvXdrZT4jP5T8vBxuvm75"

// Still unique, but more URL-friendly
const longUUID = "f47ac10b-58cc-4372-a567-0e02b2c3d479";
const shortForm = shortUUID.fromUUID(longUUID);
console.log(shortForm); // "9jqo4qWg6dSA7R1JCkz5z6"

Migration Strategies

Sequential to UUID Migration

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

-- Step 2: Populate UUIDs for existing records
UPDATE users SET uuid = uuid_generate_v4() WHERE uuid IS NULL;

-- Step 3: Make UUID NOT NULL and add constraint  
ALTER TABLE users ALTER COLUMN uuid SET NOT NULL;
ALTER TABLE users ADD CONSTRAINT users_uuid_unique UNIQUE (uuid);

-- Step 4: Update application to use UUIDs for new records
-- Step 5: Gradually migrate foreign keys
-- Step 6: Eventually drop sequential ID (optional)

UUID to Sequential Migration

-- Step 1: Add sequential ID column
ALTER TABLE users ADD COLUMN new_id BIGSERIAL;

-- Step 2: Create mapping table for references  
CREATE TABLE id_mapping (
  old_uuid UUID PRIMARY KEY,
  new_id BIGINT NOT NULL
);

INSERT INTO id_mapping (old_uuid, new_id)
SELECT uuid, new_id FROM users;

-- Step 3: Update foreign key tables
UPDATE orders SET user_id = (
  SELECT new_id FROM id_mapping WHERE old_uuid = orders.user_uuid
);

-- Step 4: Switch primary key and clean up

Decision Framework

Use Sequential IDs When

Use UUIDs When

Consider Hybrid Approaches When

Quick Decision Tree

Best Practices Summary

For Sequential IDs

• Use 64-bit integers for future-proofing
• Never expose sequential IDs in public APIs
• Consider adding UUIDs later for external access
• Implement proper authorization to prevent enumeration
• Monitor for ID exhaustion in high-volume systems

For UUIDs

• Use UUID v4 for most applications
• Store as binary (16 bytes) not text (36 bytes)
• Consider UUID v1 or ULID for better database performance
• Index UUID columns properly
• Validate UUID format in application code
• Use appropriate database UUID types when available

General Guidelines

• Profile your specific use case - theoretical performance isn't always reality
• Consider the total cost: development time, maintenance, security
• Plan for growth - what works for 1K records may not work for 1B
• Document your choice and rationale for future developers
• Be consistent within your application architecture