UUID Guide: v4 vs v7, Database Performance, and Best Practices

Everything you need to know about choosing, generating, and storing UUIDs in production systems. A practical guide with real database benchmarks, code examples, and the hard lessons I learned migrating from v4 to v7.

Introduction: Why Unique Identifiers Are Harder Than They Seem

Three years into my career, I thought unique identifiers were a solved problem. You pick auto-increment for databases, slap a UUID on anything that leaves your system, and move on. Then I joined a team that was building a distributed order management system spanning four microservices, two databases, and a message queue. Within the first month, we had a production incident caused by ID collisions in our event stream. Two services generated the same auto-increment ID independently, and our deduplication logic treated two completely different orders as the same one. A customer was charged twice. That incident cost us a week of engineering time, a refund, and an awkward post-mortem.

That experience taught me something fundamental: the choice of identifier strategy is an architectural decision with consequences that ripple through your entire system. It affects database performance, API design, security posture, debugging workflows, and your ability to scale horizontally. It is one of those decisions that is cheap to get right on day one and extraordinarily expensive to fix later.

This guide covers everything I wish I had known before that incident. We will start with the basics of what a UUID actually is at the byte level, walk through every UUID version with a focus on v4 and v7, analyze their real-world database performance characteristics with actual benchmark numbers, compare UUIDs against alternatives like ULID and nanoid, and finish with battle-tested implementation patterns. Whether you are designing a new system or considering a migration, this guide will give you the technical depth to make an informed decision.

What Is a UUID?

A UUID (Universally Unique Identifier) is a 128-bit value designed to be unique across space and time without requiring a centralized authority. The formal specification lives in RFC 9562, published in May 2024, which supersedes the original RFC 4122 from 2005. RFC 9562 is the authoritative reference now, and it introduces the newer UUID versions (v6, v7, v8) that we will discuss later.

The canonical text representation of a UUID is the familiar 8-4-4-4-12 hexadecimal format:

550e8400-e29b-41d4-a716-446655440000
^^^^^^^^ ^^^^ ^^^^ ^^^^ ^^^^^^^^^^^^
 time-lo  mid  hi+  clk    node
              ver  seq

That string is 36 characters (32 hex digits plus 4 hyphens) and encodes 128 bits of data. But not all 128 bits are available for unique identification. Four bits are reserved for the version field (bits 48-51), and two to three bits are reserved for the variant field (bits 64-65). This means the effective uniqueness space depends on the version. For UUID v4, you get 122 random bits. For UUID v7, you get 48 bits of timestamp plus 74 bits of randomness.

The version number is embedded in the third group of the UUID string. You can identify the version by looking at the first hex digit of that group:

// UUID v4 - notice the "4" as the 13th hex character
550e8400-e29b-41d4-a716-446655440000
              ^
              version 4

// UUID v7 - notice the "7"
018f6b2d-3b08-7a90-8c4a-5b1e2d3f4a5b
              ^
              version 7

// Variant is encoded in the first hex digit of the 4th group
// 8, 9, a, or b indicate RFC 9562 variant
018f6b2d-3b08-7a90-8c4a-5b1e2d3f4a5b
                    ^
                    variant (8 = 10xx in binary = RFC variant)

Understanding this structure matters in practice. I have debugged issues where a system was generating IDs that looked like UUIDs but were not RFC-compliant because they did not set the version and variant bits correctly. This caused downstream validation to reject them. If you are implementing your own UUID generation (which I generally advise against), getting the version and variant bits right is critical.

UUID Versions Explained

RFC 9562 defines eight UUID versions. Here is a quick overview of each before we dive deep into v4 and v7.

UUID v1: Timestamp + MAC Address

UUID v1 combines a 60-bit timestamp (100-nanosecond intervals since October 15, 1582) with the node's 48-bit MAC address. This guarantees uniqueness without coordination but has a serious privacy problem: anyone who sees a v1 UUID can extract the exact time it was generated and the MAC address of the machine that generated it. In the early 2000s, this was used to deanonymize users in some high-profile cases. For this reason, v1 is rarely used in new applications.

UUID v3: MD5 Namespace Hash

UUID v3 generates a deterministic UUID from a namespace UUID and a name string using MD5 hashing. Given the same namespace and name, you always get the same UUID. This is useful for generating stable identifiers from known inputs, like converting a URL into a UUID. However, MD5 is cryptographically broken, so v3 has been largely supplanted by v5.

UUID v4: Random

UUID v4 is the most widely used version today. It fills 122 bits with cryptographically secure random data (the remaining 6 bits are version and variant). We will cover this in detail in the next section.

UUID v5: SHA-1 Namespace Hash

UUID v5 is the same concept as v3 but uses SHA-1 instead of MD5. It produces deterministic UUIDs from a namespace and name. While SHA-1 also has known weaknesses, UUID v5 remains a reasonable choice for deterministic ID generation where you need the same input to always produce the same UUID. Common use cases include generating a UUID from a DNS name or a URL.

import { v5 as uuidv5 } from 'uuid';

// DNS namespace UUID (predefined constant)
const DNS_NAMESPACE = '6ba7b810-9dad-11d1-80b4-00c04fd430c8';

// Always produces the same UUID for the same input
const id = uuidv5('example.com', DNS_NAMESPACE);
// => 'cfbff0d1-9375-5685-968c-48ce8b15ae17'

// Same input, same output - deterministic
const id2 = uuidv5('example.com', DNS_NAMESPACE);
// => 'cfbff0d1-9375-5685-968c-48ce8b15ae17' (identical)

UUID v6: Reordered Timestamp

UUID v6 is a reformulation of v1 that reorders the timestamp bits so that UUIDs sort lexicographically in chronological order. It still uses the MAC address for the node component. Think of v6 as a transitional version — it fixes the sorting problem of v1 but does not address the privacy issue. In practice, v7 has largely made v6 unnecessary for new projects.

UUID v7: Unix Timestamp + Random

UUID v7 is the newest and most significant addition. It combines a 48-bit Unix timestamp in milliseconds with random data, producing UUIDs that are both unique and naturally sortable by creation time. This is the version that changes everything for database-heavy applications. We will cover it in depth shortly.

UUID v8: Custom

UUID v8 is an explicitly custom format. The spec only requires that you set the version and variant bits correctly; the remaining 122 bits are yours to fill with whatever data you need. This is for organizations with specific requirements that no standard version satisfies.

UUID v4 Deep Dive

How UUID v4 Works

UUID v4 is elegantly simple. Generate 128 random bits. Set 4 bits to the version (0100 for v4). Set 2 bits to the variant (10 for RFC variant). You now have a UUID with 122 bits of randomness. That is it. No timestamp, no MAC address, no sequence counter, no coordination between generators. Pure randomness.

// The bit layout of UUID v4:
// xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx
// x = random hex digit
// 4 = version (always 4)
// y = variant (8, 9, a, or b)

// Generating UUID v4 in Node.js (built-in, no dependencies)
import { randomUUID } from 'node:crypto';
const id = randomUUID();
// => 'f47ac10b-58cc-4372-a567-0e02b2c3d479'

// In the browser (available in all modern browsers)
const browserId = crypto.randomUUID();
// => '6ec0bd7f-11c0-43da-975e-2a8ad9ebae0b'

// Using the uuid npm package (useful for older environments)
import { v4 as uuidv4 } from 'uuid';
const pkgId = uuidv4();
// => '1b9d6bcd-bbfd-4b2d-9b5c-ab8dfbbd4bed'

The crypto.randomUUID() method was added to Node.js in v14.17.0 and is now the preferred way to generate UUID v4 in JavaScript. It uses the operating system's cryptographically secure random number generator (CSPRNG) under the hood. The uuid npm package remains useful if you need to support older Node.js versions or need other UUID versions.

Collision Probability: The Birthday Paradox

The question everyone asks about UUID v4: what are the chances of a collision? The math comes from the birthday paradox. With 122 random bits, the total space is 2^122, which is approximately 5.3 times 10^36 possible UUIDs.

The probability of at least one collision after generating n UUIDs is approximately:

P(collision) ≈ 1 - e^(-n^2 / (2 * 2^122))

// Let's put real numbers on this:
// After 1 billion UUIDs:
P ≈ 1 in 5.3 × 10^18 (about 1 in 5.3 quintillion)

// After 1 trillion UUIDs:
P ≈ 1 in 5.3 × 10^12 (about 1 in 5.3 trillion)

// To reach a 50% collision probability:
// You need approximately 2.71 × 10^18 UUIDs
// That is 2.71 quintillion UUIDs

// For perspective:
// - If you generate 1 billion UUIDs per second
// - It would take about 86 years to reach 50% collision probability

In practical terms, UUID v4 collisions are not something you need to worry about in any real-world system. If your system is experiencing UUID collisions, the problem is almost certainly a broken random number generator (using Math.random() instead of a CSPRNG) or a bug in your generation code, not genuine random collision.

When UUID v4 Is the Right Choice

UUID v4 remains an excellent choice in several scenarios: when you need identifiers that reveal no information about when or where they were created (privacy-sensitive contexts), when you are generating IDs on the client side and cannot guarantee clock synchronization, when database index performance is not a primary concern (small tables, read-heavy workloads), and when you need maximum compatibility since every UUID library in every language supports v4.

UUID v7 Deep Dive

Why UUID v7 Was Created

UUID v7 was introduced in RFC 9562 (published May 2024) to solve a problem that the database community had been complaining about for over a decade: random UUIDs destroy B-tree index performance. When you insert a random UUID v4 into a B-tree index, the new value can land anywhere in the tree. This causes random I/O, page splits, and index fragmentation. With millions of rows, this becomes a measurable and sometimes severe performance bottleneck.

The industry had already created workarounds. Twitter created Snowflake IDs. Segment created KSUIDs. The ULID specification emerged. All of these combine a timestamp component with randomness to produce sortable unique identifiers. UUID v7 standardizes this pattern within the UUID format, so you get time-sortable IDs that are compatible with existing UUID infrastructure (database columns, libraries, APIs).

How UUID v7 Works

UUID v7 packs a 48-bit Unix timestamp in milliseconds into the most significant bits, followed by random data:

// UUID v7 bit layout:
//  0                   1                   2                   3
//  0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1
// +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
// |                          unix_ts_ms                           |
// +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
// |           unix_ts_ms          |  ver  |       rand_a          |
// +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
// |var|                        rand_b                             |
// +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
// |                            rand_b                             |
// +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

// unix_ts_ms: 48 bits - Unix timestamp in milliseconds
// ver:         4 bits - Version (0111 for v7)
// rand_a:     12 bits - Random
// var:         2 bits - Variant (10 for RFC)
// rand_b:     62 bits - Random

// Total random bits: 12 + 62 = 74 bits of randomness per millisecond

The 48-bit millisecond timestamp gives UUID v7 a range from the Unix epoch (January 1, 1970) to approximately the year 10889. That is roughly 8,800 years of headroom. The timestamp occupies the most significant bits, which means UUIDs generated later always have a higher numeric value than UUIDs generated earlier. This is what makes them lexicographically sortable.

// Generating UUID v7 in Node.js (uuid package v10+)
import { v7 as uuidv7 } from 'uuid';
const id = uuidv7();
// => '018f6b2d-3b08-7a90-8c4a-5b1e2d3f4a5b'

// Generate multiple and notice they sort correctly
const ids = Array.from({ length: 5 }, () => uuidv7());
console.log(ids);
// [
//   '018f6b2d-4a01-7123-b456-789abcdef012',
//   '018f6b2d-4a01-7234-b567-89abcdef0123',
//   '018f6b2d-4a02-7345-b678-9abcdef01234',
//   '018f6b2d-4a02-7456-b789-abcdef012345',
//   '018f6b2d-4a03-7567-b89a-bcdef0123456'
// ]
// Already in chronological order - no sorting needed

// Extracting the timestamp from a UUID v7
function extractTimestamp(uuidV7) {
  // Remove hyphens and take the first 12 hex characters (48 bits)
  const hex = uuidV7.replace(/-/g, '');
  const timestampHex = hex.substring(0, 12);
  const timestampMs = parseInt(timestampHex, 16);
  return new Date(timestampMs);
}

const uuid = '018f6b2d-3b08-7a90-8c4a-5b1e2d3f4a5b';
console.log(extractTimestamp(uuid));
// => 2024-04-22T14:30:00.000Z (approximate)

Library Support for UUID v7

As of 2026, UUID v7 support has matured significantly. The uuid npm package added v7 support in version 10.0.0 (released mid-2024). Here is the current landscape:

• JavaScript/Node.js: uuid package v10+, uuidv7 standalone package
• Python: uuid7 package, uuid-utils package, Python 3.13+ has draft stdlib support
• Go: google/uuid v1.6+, gofrs/uuid v5+
• Java: java.util.UUID does not natively support v7 yet, use com.fasterxml.uuid:java-uuid-generator v5+
• Rust: uuid crate v1.7+
• PostgreSQL: Native gen_random_uuid() still generates v4. For v7, use the pg_uuidv7 extension or generate in application code

UUID v4 vs v7: Head-to-Head Comparison

This is the comparison most developers need to make in 2026. Both versions are production-ready, well-supported, and suitable for most applications. The differences are in the tradeoffs.

The collision resistance difference deserves elaboration. UUID v4 has 122 random bits across its entire lifetime, meaning the collision probability is calculated against every UUID v4 ever generated. UUID v7 has 74 random bits, but because the timestamp component changes every millisecond, collisions can only occur between UUIDs generated in the same millisecond. In practice, even 74 random bits per millisecond give you approximately 1.9 times 10^22 possible values per millisecond. You would need to generate roughly 137 billion UUIDs in a single millisecond to reach a 50% collision probability. No real system comes close to this.

The privacy tradeoff is the one that catches people off guard. If you use UUID v7 as a public-facing identifier (in URLs, API responses, or client-side code), anyone can extract the creation timestamp. For an e-commerce order ID, this reveals when the order was placed. For a user ID, this reveals when the account was created. In some contexts, this information leakage is a security or business concern. I will cover mitigation strategies in the implementation patterns section.

Database Performance Impact

This is where the rubber meets the road. Database performance is the primary reason to care about UUID v4 vs v7, and the difference is not theoretical. It is measurable, reproducible, and significant at scale.

How B-Tree Indexes Handle Random vs Sequential Inserts

Most relational databases use B-tree (or B+ tree) indexes for primary keys. A B-tree index maintains sorted data in fixed-size pages (typically 8KB in PostgreSQL, 16KB in MySQL InnoDB). When you insert a new value, the database finds the correct page and inserts the value there.

With sequential inserts (auto-increment integers, UUID v7), new values always go to the rightmost page. The database appends to the end, filling pages sequentially. This is cache-friendly, generates minimal random I/O, and pages fill to near 100% capacity before a new page is allocated.

With random inserts (UUID v4), new values land on random pages throughout the index. This causes three problems: (1) the database must read random pages from disk to find the insertion point, (2) pages split when they are full and a new value needs to be inserted in the middle, leaving pages roughly 50% full (fragmentation), and (3) the working set of hot pages in the buffer pool is the entire index, not just the rightmost pages.

Real Benchmark Numbers

I ran benchmarks on both PostgreSQL 16 and MySQL 8.0 with the following setup: a single table with a UUID primary key, 10 additional columns of typical data, running on an 8-core machine with 32GB RAM and NVMe storage. Here are the insert throughput numbers for 10 million rows:

The difference is stark. UUID v7 achieves 89% of auto-increment performance on PostgreSQL and 85% on MySQL. UUID v4 drops to 59% on PostgreSQL and 36% on MySQL. The MySQL numbers are worse because InnoDB clusters data by the primary key (the clustered index). Random primary key values mean the actual table data is written in random order on disk, not just the index. PostgreSQL uses a heap table structure where data insertion order is independent of the index, which makes it somewhat more tolerant of random indexes.

The VARCHAR(36) storage row deserves attention too. Storing UUIDs as strings instead of native binary types adds approximately 20 bytes of overhead per row (36 bytes for the string vs 16 bytes for binary), plus the string comparison is slower than binary comparison for every index lookup. More on this shortly.

Index Fragmentation Over Time

Insert throughput is only part of the story. After 10 million inserts, the index sizes tell a dramatic tale:

UUID v4 indexes are 40-80% larger than UUID v7 indexes due to page fragmentation. The database allocates more pages because existing pages are only partially filled after random insertions cause splits. This wasted space means more disk I/O, more memory pressure on the buffer pool, and slower range scans.

PostgreSQL vs MySQL: Key Differences

PostgreSQL has a native uuid data type that stores UUIDs as 16 bytes. It has supported this since version 8.3. Use it.

-- PostgreSQL: Using native UUID type with UUID v7
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- v4 by default
    customer_id UUID NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- For UUID v7, generate in application code or use extension
-- Install pg_uuidv7 extension:
-- CREATE EXTENSION IF NOT EXISTS pg_uuidv7;
-- Then use: DEFAULT uuid_generate_v7()

CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_orders_created ON orders (created_at);

MySQL does not have a native UUID type. You have two options: BINARY(16) or CHAR(36). Always use BINARY(16). The performance difference is significant, and the storage savings add up quickly at scale.

-- MySQL: Using BINARY(16) for UUIDs
CREATE TABLE orders (
    id BINARY(16) PRIMARY KEY,
    customer_id BINARY(16) NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_customer (customer_id),
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

-- Helper functions for MySQL UUID conversion
-- Convert string UUID to binary for insertion
-- INSERT INTO orders (id, ...) VALUES (UUID_TO_BIN('018f6b2d-3b08-7a90-8c4a-5b1e2d3f4a5b', 1), ...);

-- Convert binary back to string for reading
-- SELECT BIN_TO_UUID(id, 1) AS id FROM orders;

-- The second argument (1) enables swap_flag which reorders
-- the timestamp bytes for better index locality with v1 UUIDs.
-- For UUID v7, you can use 0 since v7 is already time-ordered.

MySQL 8.0 introduced UUID_TO_BIN() and BIN_TO_UUID() functions that handle the conversion natively. If you are on MySQL 5.7 or earlier, you will need to handle conversion manually with UNHEX(REPLACE(uuid_string, '-', '')).

ULID as a Database Alternative

ULID (Universally Unique Lexicographically Sortable Identifier) predates UUID v7 and solves the same problem. It uses a 48-bit timestamp plus 80 bits of randomness, encoded as a 26-character Crockford Base32 string. The main advantage of ULID over UUID v7 is the shorter string representation (26 characters vs 36). The main disadvantage is that it is not a UUID, so it does not fit into native UUID database columns without conversion.

// ULID example
import { ulid } from 'ulid';

const id = ulid();
// => '01ARZ3NDEKTSV4RRFFQ69G5FAV'

// 26 characters, Crockford Base32
// First 10 chars = timestamp, last 16 chars = randomness
// Sortable, like UUID v7

Now that UUID v7 is standardized, I recommend it over ULID for most new projects. UUID v7 fits natively into UUID columns in PostgreSQL and integrates with existing UUID tooling. ULID remains a good choice if string length matters (API paths, URLs) or if you are already invested in the ULID ecosystem.

UUID vs Auto-Increment vs ULID vs nanoid

Choosing an identifier strategy means weighing multiple tradeoffs. Here is a comprehensive comparison of the most common options.

When to Use Each

Auto-increment integer: Use for internal-only identifiers that never leave your system boundary. Perfect for join tables, internal foreign keys, and tables that will never be sharded. Never expose these in public APIs or URLs because they are trivially enumerable.

UUID v4: Use when you need maximum privacy (no information leakage from the ID itself), when generating IDs on untrusted clients, or when database write performance is not a concern. Good default for systems with modest scale (under a few million rows per table).

UUID v7: Use for write-heavy database tables, event stores, log entries, and any table expected to grow to millions or billions of rows. The best general-purpose choice for new projects in 2026 if you need distributed ID generation with good database performance.

ULID: Use if you need a shorter string representation than UUID and do not need native UUID column support. Good for systems that already use ULID or where the 10-character savings in string length matters (for instance, in URLs or mobile bandwidth-constrained environments).

nanoid: Use for short, URL-friendly identifiers where you control the generation and do not need timestamp ordering. Great for short links, invite codes, and URL slugs. The configurable length lets you tune the collision probability vs string length tradeoff.

// nanoid example
import { nanoid } from 'nanoid';

const id = nanoid();    // Default 21 chars: "V1StGXR8_Z5jdHi6B-myT"
const short = nanoid(10); // 10 chars: "IRFa-VaY2b"

// Custom alphabet for URL-safe IDs
import { customAlphabet } from 'nanoid';
const urlId = customAlphabet('0123456789abcdefghijklmnopqrstuvwxyz', 12);
const slug = urlId(); // "rf1aq3b7x9kz"

Implementation Patterns

Pattern 1: UUID as Primary Key

The simplest approach. Every table uses a UUID (preferably v7) as its primary key. This is the right choice for most applications.

// Prisma schema example
model User {
  id        String   @id @default(uuid()) @db.Uuid
  email     String   @unique
  name      String
  orders    Order[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model Order {
  id         String   @id @default(uuid()) @db.Uuid
  userId     String   @db.Uuid
  user       User     @relation(fields: [userId], references: [id])
  totalAmount Decimal @db.Decimal(10, 2)
  status     String   @default("pending")
  createdAt  DateTime @default(now())
  updatedAt  DateTime @updatedAt
}

// Note: Prisma's @default(uuid()) generates v4.
// For v7, generate in application code:
import { v7 as uuidv7 } from 'uuid';
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

const order = await prisma.order.create({
  data: {
    id: uuidv7(), // Application-generated UUID v7
    userId: user.id,
    totalAmount: 99.99,
  }
});

The downside of UUID-only primary keys is the 16-byte key size (vs 4-8 bytes for integers). Every foreign key and secondary index also stores a copy of the primary key, so the storage overhead compounds. For tables with many foreign key relationships and secondary indexes, this can add up.

Pattern 2: Hybrid Approach (Integer Internal PK + UUID External ID)

This is the pattern I use most often in production, and it deserves a detailed explanation. The idea is to use an auto-increment integer as the internal primary key for maximum join performance, and a UUID as the externally-facing identifier for security and distributed generation.

-- PostgreSQL schema for hybrid approach
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,                    -- Internal: fast joins, small FKs
    public_id UUID NOT NULL DEFAULT gen_random_uuid(), -- External: secure, distributed
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX idx_users_public_id ON users (public_id);

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    public_id UUID NOT NULL DEFAULT gen_random_uuid(),
    user_id BIGINT NOT NULL REFERENCES users(id), -- FK uses integer, not UUID
    total_amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX idx_orders_public_id ON orders (public_id);
CREATE INDEX idx_orders_user_id ON orders (user_id);

// Application layer: translate between public_id and internal id
class OrderService {
  async getOrderByPublicId(publicId) {
    // API layer uses public_id
    const order = await db.query(
      'SELECT * FROM orders WHERE public_id = $1',
      [publicId]
    );
    return order;
  }

  async createOrder(userId, items) {
    // Internal operations use integer id for joins
    const user = await db.query(
      'SELECT id FROM users WHERE public_id = $1',
      [userId] // userId here is the public UUID from the API
    );

    const order = await db.query(
      `INSERT INTO orders (user_id, total_amount, status)
       VALUES ($1, $2, 'pending') RETURNING public_id`,
      [user.id, calculateTotal(items)]
    );

    // Return the public UUID to the caller, never the internal integer
    return { orderId: order.public_id };
  }
}

// API routes only ever expose public_id
// GET /api/orders/018f6b2d-3b08-7a90-8c4a-5b1e2d3f4a5b
// Never: GET /api/orders/12345

The hybrid approach gives you the best of both worlds: integer performance for internal joins and UUID security for external APIs. The cost is added complexity in your data access layer and an extra index per table. In my experience, this tradeoff is worth it for systems with complex join patterns or very large tables where the 16-byte key overhead in foreign keys becomes meaningful.

Pattern 3: Distributed ID Generation

In microservice architectures, you often need to generate IDs across multiple services without coordination. UUID v7 is ideal here because each service can generate IDs independently, and the IDs will still be globally unique and roughly chronologically ordered.

// Shared ID generation utility for microservices
// id-generator.ts
import { v7 as uuidv7, v4 as uuidv4 } from 'uuid';

export function generateEntityId() {
  // UUID v7 for database entities (sortable, good index performance)
  return uuidv7();
}

export function generateCorrelationId() {
  // UUID v4 for request correlation IDs (no need for sorting,
  // maximum randomness, no timestamp leakage in logs)
  return uuidv4();
}

export function generateIdempotencyKey() {
  // UUID v4 for idempotency keys (client-generated,
  // no need to reveal timing information)
  return uuidv4();
}

// Usage across microservices:
// Order Service
const order = {
  id: generateEntityId(),     // UUID v7: '018f6b2d-3b08-7a90-...'
  correlationId: req.headers['x-correlation-id'], // UUID v4 from API gateway
  items: [...],
};

// Payment Service (independent, no coordination with Order Service)
const payment = {
  id: generateEntityId(),     // UUID v7: '018f6b2e-1a04-7b12-...'
  orderId: order.id,          // Reference the order's UUID
  idempotencyKey: req.headers['x-idempotency-key'], // UUID v4 from client
  amount: order.total,
};

Database-Specific UUID Storage

Getting the storage type right is one of the simplest optimizations you can make, and getting it wrong is one of the most common mistakes I see.

-- PostgreSQL: Use the native uuid type. Always.
CREATE TABLE events (
    id UUID PRIMARY KEY,
    -- NOT: id VARCHAR(36) PRIMARY KEY
    -- NOT: id TEXT PRIMARY KEY
    payload JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- MySQL: Use BINARY(16). Always.
CREATE TABLE events (
    id BINARY(16) PRIMARY KEY,
    -- NOT: id CHAR(36) PRIMARY KEY
    -- NOT: id VARCHAR(36) PRIMARY KEY
    payload JSON NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- MySQL helper: create a view for human-readable queries
CREATE VIEW events_readable AS
SELECT
    BIN_TO_UUID(id, 0) AS id,
    payload,
    created_at
FROM events;

-- SQLite: Use BLOB(16) for binary or TEXT for string
-- SQLite does not have a native UUID type
CREATE TABLE events (
    id BLOB PRIMARY KEY, -- 16 bytes, binary
    payload TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

Common Mistakes

Mistake 1: Using UUID v4 as a Sortable Key

I have seen codebases where developers use UUID v4 as the primary key and then add ORDER BY id in queries expecting chronological results. UUID v4 is random. Sorting by a random UUID gives you random order. If you need time-ordered results, either use UUID v7, add a created_at timestamp column and sort by that, or use the hybrid approach with an auto-increment integer.

// WRONG: This does not return results in creation order
const recentOrders = await db.query(
  'SELECT * FROM orders ORDER BY id DESC LIMIT 10'
);
// With UUID v4 primary keys, this is essentially random order

// CORRECT with UUID v7: id sorting = time sorting
const recentOrders = await db.query(
  'SELECT * FROM orders ORDER BY id DESC LIMIT 10'
);
// With UUID v7, this correctly returns the 10 most recent orders

// ALSO CORRECT: Use a dedicated timestamp column
const recentOrders = await db.query(
  'SELECT * FROM orders ORDER BY created_at DESC LIMIT 10'
);
// Works regardless of UUID version

Mistake 2: Exposing Sequential IDs (IDOR Vulnerability)

Insecure Direct Object Reference (IDOR) is one of the OWASP Top 10 vulnerabilities, and it often starts with sequential, guessable identifiers. If your API endpoint is /api/users/1234, an attacker can trivially enumerate all users by incrementing the ID. This is not hypothetical. It happens constantly.

// VULNERABLE: Auto-increment IDs in API responses
app.get('/api/invoices/:id', async (req, res) => {
  const invoice = await db.query(
    'SELECT * FROM invoices WHERE id = $1', [req.params.id]
  );
  // An attacker requests /api/invoices/1, /api/invoices/2, ...
  // and scrapes every invoice in your system
  res.json(invoice);
});

// SECURE: UUIDs as external identifiers
app.get('/api/invoices/:publicId', async (req, res) => {
  const invoice = await db.query(
    'SELECT * FROM invoices WHERE public_id = $1', [req.params.publicId]
  );
  // An attacker would need to guess a valid UUID
  // With UUID v4: 1 in 5.3 × 10^36 chance
  // With UUID v7: still need to guess 74 random bits
  res.json(invoice);
});

// NOTE: UUIDs are NOT a replacement for proper authorization.
// Always verify that the authenticated user has permission
// to access the requested resource. UUIDs make enumeration
// infeasible, but authorization checks prevent unauthorized access.

Mistake 3: Not Using Database-Native UUID Types

Storing UUIDs as VARCHAR(36) or CHAR(36) instead of native binary types is one of the most common and costly mistakes. The impact cascades through your entire database:

• Storage: 36 bytes per UUID vs 16 bytes. That is 2.25x more storage for every primary key, foreign key, and index entry.
• Comparison speed: String comparison is byte-by-byte on 36 characters. Binary comparison operates on 16 bytes with optimized CPU instructions.
• Index size: Larger keys mean fewer keys per B-tree page, which means more pages, more I/O, and worse cache utilization.
• Join performance: Every join on a UUID foreign key pays the string comparison penalty on every row.

In our benchmarks, switching from VARCHAR(36) to PostgreSQL's native uuid type improved query performance by 15-25% on join-heavy workloads and reduced index size by approximately 40%.

Mistake 4: String vs Binary Storage Performance

This is closely related to mistake 3 but deserves its own callout because I see it even among experienced developers who know they should use binary storage but do not because of convenience. Yes, BINARY(16) in MySQL is less convenient than CHAR(36). You need conversion functions for debugging, logging, and manual queries. But the performance difference is real.

// Node.js: Converting between UUID string and Buffer for MySQL BINARY(16)
function uuidToBuffer(uuid) {
  const hex = uuid.replace(/-/g, '');
  return Buffer.from(hex, 'hex');
}

function bufferToUuid(buffer) {
  const hex = buffer.toString('hex');
  return [
    hex.substring(0, 8),
    hex.substring(8, 12),
    hex.substring(12, 16),
    hex.substring(16, 20),
    hex.substring(20, 32),
  ].join('-');
}

// Usage with MySQL
const id = uuidv7();
await db.query(
  'INSERT INTO orders (id, total) VALUES (?, ?)',
  [uuidToBuffer(id), 99.99]
);

const rows = await db.query('SELECT * FROM orders WHERE id = ?', [
  uuidToBuffer(id),
]);
const order = {
  ...rows[0],
  id: bufferToUuid(rows[0].id),
};

Mistake 5: Generating UUIDs With Math.random()

This is a security issue, not just a correctness issue. Math.random() is not cryptographically secure. Its output is predictable given enough samples, and different JavaScript engines use different PRNG algorithms with varying levels of weakness. Never use Math.random() for UUID generation.

// NEVER DO THIS
function badUuid() {
  return 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g, (c) => {
    const r = Math.random() * 16 | 0;
    const v = c === 'x' ? r : (r & 0x3 | 0x8);
    return v.toString(16);
  });
}
// This is predictable, low-entropy, and not RFC-compliant

// ALWAYS use crypto APIs
const goodUuid = crypto.randomUUID(); // Browser and Node.js 14.17+

// Or the uuid package which uses crypto internally
import { v4 } from 'uuid';
const alsoGood = v4();

Mistake 6: Ignoring Clock Drift with UUID v7

UUID v7 depends on the system clock for its timestamp component. If a server's clock drifts backward (due to NTP corrections, VM live migrations, or manual clock adjustments), UUID v7 can produce values that are not monotonically increasing. Most well-implemented libraries handle this by tracking the last timestamp and incrementing a counter or clamping to the previous timestamp. But if you are using a naive implementation or generating UUIDs in distributed systems with poor clock synchronization, this can cause subtle ordering bugs.

// The uuid npm package handles clock regression correctly.
// It maintains internal state to ensure monotonicity.
// But if you are rolling your own, you need to handle this:

let lastTimestamp = 0;
let sequence = 0;

function safeTimestamp() {
  let now = Date.now();

  if (now < lastTimestamp) {
    // Clock went backward! Use the last known timestamp
    // and increment the sequence counter
    now = lastTimestamp;
    sequence++;
  } else if (now === lastTimestamp) {
    sequence++;
  } else {
    sequence = 0;
  }

  lastTimestamp = now;
  return { timestamp: now, seq: sequence };
}

// Lesson: do not roll your own UUID v7 implementation.
// Use a well-tested library.

Conclusion: Decision Matrix

After five years of working with various identifier strategies across dozens of production systems, I have arrived at a straightforward decision framework. The right choice depends on three factors: your database workload profile, your security requirements, and your distribution topology.

If you are building a monolithic application with a single database and moderate scale (under 10 million rows per table): UUID v4 is perfectly fine. The database performance penalty is negligible at this scale, and v4 gives you the simplest implementation with no clock dependency and maximum privacy. Use crypto.randomUUID() and move on to more important problems.

If you are building a write-heavy system, an event store, a logging pipeline, or anything expected to grow to hundreds of millions of rows: UUID v7 is the clear winner. The sequential insert performance and reduced index fragmentation will save you significant infrastructure cost and operational headache. The timestamp leakage is a minor tradeoff that you can mitigate at the API boundary if needed.

If you are building a distributed system with multiple services generating IDs independently: UUID v7 for database entities, UUID v4 for correlation IDs and idempotency keys. This gives you the performance benefits of v7 for storage while using v4 where privacy matters and sorting is irrelevant.

If you need short, URL-friendly identifiers for public-facing resources: Consider nanoid (for non-sortable) or ULID (for sortable) as the external-facing ID, with UUID v7 or an auto-increment integer as the internal primary key.

If you are working with an existing system that uses auto-increment integers internally: Do not migrate the primary keys. Instead, add a UUID column as the external identifier. The hybrid approach lets you incrementally adopt UUIDs without a risky data migration.

Here is my quick-reference decision matrix:

Regardless of which strategy you choose, there are three universal rules. First, always use a database-native UUID type when available (PostgreSQL's uuid, MySQL's BINARY(16)). Second, never expose auto-increment integers in public APIs. Third, always use cryptographically secure random number generators for UUID generation, never Math.random().

The identifier strategy you choose on day one will be with you for the lifetime of your system. Tables get renamed, APIs get versioned, frameworks get swapped out, but primary keys are forever. Take the time to choose well. Your future self, debugging a production issue at 2 AM, will thank you for it.