When and Why to Use UUIDs in Your Database

Introduction

UUIDs (Universally Unique Identifiers) are 128-bit identifiers that are unique across systems without central coordination. Understanding when to use UUIDs versus auto-increment IDs is crucial for database design.

This guide helps you decide when UUIDs are the right choice for your application.

What is a UUID?

A UUID is a 128-bit number represented as 36 characters:

550e8400-e29b-41d4-a716-446655440000

  // Format: 8-4-4-4-12 hexadecimal digits
  // Total: 32 hex digits + 4 hyphens = 36 characters

Key Feature: Can be generated independently without coordination, yet virtually guaranteed to be unique globally.

UUID vs Auto-Increment ID

When to Use UUIDs

1. Distributed Systems

• Multiple databases generating IDs independently
• Microservices architecture
• Offline-first applications
• Multi-region deployments

2. Data Merging

• Combining data from multiple sources
• Database migration and consolidation
• Import/export operations
• Data synchronization

3. Security Requirements

• Hide database size (non-sequential)
• Prevent ID enumeration attacks
• Public-facing identifiers
• API resource identifiers

4. Client-Side Generation

• Offline mobile apps
• Optimistic UI updates
• Reduce server round-trips
• Frontend-generated records

When NOT to Use UUIDs

• High-volume single database: Auto-increment is faster
• Human-readable IDs needed: UUIDs are not user-friendly
• Tight storage constraints: UUIDs are 2-4x larger
• Simple CRUD apps: Auto-increment is simpler
• URL length concerns: UUIDs make longer URLs

Real-World Use Cases

Good UUID Use Cases:

// Public API Resources
  GET /api/users/550e8400-e29b-41d4-a716-446655440000

  // Session IDs
  sessionId: '9b1deb4d-3b7d-4bad-9bdd-2b0d7b3dcb6d'

  // File uploads
  filename: 'upload_f47ac10b-58cc-4372-a567-0e02b2c3d479.jpg'

  // Distributed events
  eventId: '3d6f4e2a-9b7c-4a1f-8e5d-2c3b4a5f6e7d'

Better Auto-Increment Use Cases:

// Internal database relations
  user_id: 12345
  order_id: 67890

  // Invoice numbers
  invoice_number: 2024-00123

  // Pagination
  GET /api/posts?page=5&limit=20

Best Practices

• Use UUID v6 or ULID for better database performance
• Store UUIDs as binary (not strings) to save space
• Index UUID columns properly
• Consider hybrid approach (internal ID + public UUID)
• Use UUIDs for cross-system references
• Generate UUIDs at application layer, not database

Hybrid Approach

Best of both worlds - internal auto-increment + public UUID:

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,        -- Internal, fast
    uuid UUID UNIQUE NOT NULL,        -- Public, secure
    email VARCHAR(255),
    created_at TIMESTAMP
  );

  -- Use id for internal joins (fast)
  SELECT * FROM users u
  JOIN orders o ON o.user_id = u.id;

  -- Use uuid for public API (secure)
  GET /api/users/550e8400-e29b-41d4-a716-446655440000

Try Our Tools

Explore our free online developer tools: