Designing a Unique ID Generator: UUIDs, ULIDs, Snowflake IDs, and Database Sequences for Distributed Systems

Every distributed system eventually hits the same question: where do IDs come from? The answer shapes your database index performance, your ability to sort by creation time, your operational complexity, and how well your system handles partial failures. Get it wrong early and migration is painful. Get it right and you stop thinking about it for years.

This guide covers the four main approaches, what each one actually costs you in practice, and how to choose between them.

The Problem With the Obvious Answers

The two most common starting points are AUTO_INCREMENT in MySQL or SERIAL in Postgres. They work fine until they do not. You add a read replica and suddenly you cannot insert from multiple writers without coordination. You shard your database and now you need a sequence server just to keep IDs unique. You expose IDs in your API and a competitor can enumerate your customers by incrementing an integer.

The other obvious answer is uuid_generate_v4() in Postgres or crypto.randomUUID() in Node. True randomness, globally unique, no coordination required. But random UUIDs sort randomly, which means every insert into a UUID primary key index lands at an arbitrary position, causing page splits and index fragmentation at scale.

Neither approach is wrong in isolation. Both become wrong when your system grows past the assumptions they were built on.

Approach 1: Auto-Incrementing Database Sequences

The simplest possible ID: a counter the database owns and increments atomically.

// Postgres: CREATE TABLE orders (id BIGSERIAL PRIMARY KEY, ...);
// MySQL: CREATE TABLE orders (id BIGINT AUTO_INCREMENT PRIMARY KEY, ...);

// Reading the sequence directly in Postgres
async function nextOrderId(db: Pool): Promise<bigint> {
  const result = await db.query("SELECT nextval('orders_id_seq') AS id");
  return BigInt(result.rows[0].id);
}

// Pre-allocating a batch (useful when inserting many rows at once)
async function nextOrderIdBatch(db: Pool, count: number): Promise<bigint[]> {
  const result = await db.query(
    `SELECT nextval('orders_id_seq') AS id
     FROM generate_series(1, $1)`,
    [count]
  );
  return result.rows.map((r) => BigInt(r.id));
}

What you get: sequential IDs, no index fragmentation, trivially sortable by creation order, zero configuration.

What you give up: every writer must talk to the sequence owner. In a single-database setup this is fine. In a multi-primary or sharded setup you need a distributed sequence service or you abandon this approach entirely. IDs are also enumerable, which leaks information: a competitor can call GET /orders/10001 after seeing GET /orders/10000 in a network tab.

Sequences scale well in practice up to surprisingly high write rates because Postgres batches sequence allocation internally. At 50K inserts/second on a single primary you will not hit a sequence bottleneck before you hit other limits.

Approach 2: UUIDs (v4 and v7)

UUID v4 is 128 bits of randomness, formatted as xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx. Version 7 keeps the same format but puts a 48-bit millisecond timestamp in the high bits, making it lexicographically sortable.

import { randomUUID } from "crypto";

// UUID v4: fully random, no sortability
function generateV4(): string {
  return randomUUID(); // built into Node 14.17+
}

// UUID v7: timestamp-prefixed, sortable
// No native Node support yet, implement directly
function generateV7(): string {
  const timestamp = BigInt(Date.now());
  const timestampHex = timestamp.toString(16).padStart(12, "0");

  // 12 bits of random sequence to handle same-millisecond collisions
  const seqRandom = Math.floor(Math.random() * 0xfff);
  const seqHex = seqRandom.toString(16).padStart(3, "0");

  // 62 bits of random data for the remainder
  const randomBytes = new Uint8Array(8);
  crypto.getRandomValues(randomBytes);
  const randomHex = Array.from(randomBytes)
    .map((b) => b.toString(16).padStart(2, "0"))
    .join("");

  const raw =
    `${timestampHex}` +      // 48-bit timestamp (ms)
    `7${seqHex}` +            // version 7 + 12-bit seq
    `8${randomHex.slice(0, 3)}` + // variant bits + 12 random bits
    randomHex.slice(3, 15);  // 48 bits random

  return [
    raw.slice(0, 8),
    raw.slice(8, 12),
    raw.slice(12, 16),
    raw.slice(16, 20),
    raw.slice(20, 32),
  ].join("-");
}

// Storing as bytes in Postgres for 2x space efficiency
// CREATE TABLE events (id uuid PRIMARY KEY DEFAULT gen_random_uuid());
// uuid type stores 16 bytes vs 36 bytes for text

UUID v4 tradeoffs: collision probability is negligible for any realistic workload. Generating a billion UUIDs per second for 85 years gives you a 50% chance of one collision. In practice, you will never hit one. But random distribution across a B-tree index causes write amplification. Every insert touches a different leaf page. At a few million rows this is barely measurable. At hundreds of millions with a hot write path, you will see it in your storage engine’s page split rate.

UUID v7 tradeoffs: the timestamp prefix means new IDs cluster at the end of the index, just like sequential integers. Page splits drop to near zero for ordered inserts. You also get free chronological ordering without a separate created_at column in many query patterns. The tradeoff is a 1ms time resolution for the sort prefix, meaning same-millisecond IDs sort by the random suffix, not insertion order. Postgres 17 added gen_random_uuid() v7 support natively.

Approach 3: ULIDs

ULID (Universally Unique Lexicographically Sortable Identifier) predates UUID v7 and solves the same problem: sortable, globally unique, no coordination. It uses a 48-bit millisecond timestamp followed by 80 bits of randomness, encoded in Crockford Base32 (26 characters, case-insensitive, no ambiguous characters like O and 0).

// Using the ulid package: npm install ulid
import { ulid, monotonicFactory } from "ulid";

// Basic usage: 01ARZ3NDEKTSV4RRFFQ69G5FAV
const id = ulid(); // timestamp-prefixed, URL-safe, 26 chars

// Monotonic factory: within the same millisecond, increments the random component
// instead of generating a new random suffix. Guarantees strict ordering for
// same-millisecond inserts from a single generator.
const generateULID = monotonicFactory();

for (let i = 0; i < 5; i++) {
  console.log(generateULID());
  // 01JPKQ7KBPA0000000000000000
  // 01JPKQ7KBPA0000000000000001
  // 01JPKQ7KBPA0000000000000002
  // ... strict ordering even in the same ms
}

// Extracting the timestamp from a ULID
function ulidToTimestamp(id: string): Date {
  // First 10 characters are the timestamp in Crockford Base32
  const ENCODING = "0123456789ABCDEFGHJKMNPQRSTVWXYZ";
  const timeStr = id.slice(0, 10).toUpperCase();
  let ms = 0;
  for (const char of timeStr) {
    ms = ms * 32 + ENCODING.indexOf(char);
  }
  return new Date(ms);
}

// Schema: store as CHAR(26) or convert to UUID for Postgres
// Many teams store ULIDs as UUID by encoding the 128 bits into UUID format
function ulidToUUID(id: string): string {
  // Use the ulid-to-uuid conversion for Postgres uuid columns
  const { decodeTime } = require("ulid");
  // ... binary conversion omitted for brevity
  // In practice: use the 'ulidx' package which provides toUUID()
  return id;
}

What makes ULIDs different from UUID v7: the Base32 encoding is human-readable and copy-paste safe. You can visually inspect a ULID and immediately see the time component. They sort correctly as plain strings, so even databases without a dedicated UUID type handle ordering correctly.

The monotonic factory solves a subtle problem: if you generate 10 IDs in the same millisecond, a naive implementation generates 10 random suffixes that could sort in any order. The monotonic variant increments the random component by 1, guaranteeing strict order within a millisecond from a single generator process. This breaks down across multiple generators in the same millisecond, but that is acceptable in practice.

The catch with ULIDs: the ulid package is widely used but the spec has no formal standardization body. UUID v7 is an IETF RFC. If you are in an environment with strict library approval processes, UUID v7 is the lower-friction choice.

Approach 4: Snowflake-Style IDs

Twitter developed Snowflake in 2010 to generate 64-bit integer IDs at high throughput across multiple nodes without coordination. The format: a timestamp (milliseconds since a custom epoch), a machine/datacenter identifier, and a per-machine sequence counter.

const EPOCH = 1700000000000n; // Custom epoch: Nov 14 2023

const MACHINE_ID_BITS = 10n;
const SEQUENCE_BITS = 12n;

const MAX_MACHINE_ID = (1n << MACHINE_ID_BITS) - 1n; // 1023
const MAX_SEQUENCE = (1n << SEQUENCE_BITS) - 1n;      // 4095

const MACHINE_SHIFT = SEQUENCE_BITS;
const TIMESTAMP_SHIFT = SEQUENCE_BITS + MACHINE_ID_BITS;

class SnowflakeGenerator {
  private machineId: bigint;
  private sequence: bigint = 0n;
  private lastTimestamp: bigint = -1n;

  constructor(machineId: number) {
    if (machineId < 0 || machineId > Number(MAX_MACHINE_ID)) {
      throw new Error(`Machine ID must be between 0 and ${MAX_MACHINE_ID}`);
    }
    this.machineId = BigInt(machineId);
  }

  generate(): bigint {
    let timestamp = BigInt(Date.now()) - EPOCH;

    if (timestamp < this.lastTimestamp) {
      // Clock moved backwards: either wait or throw
      throw new Error(
        `Clock moved backwards by ${this.lastTimestamp - timestamp}ms`
      );
    }

    if (timestamp === this.lastTimestamp) {
      this.sequence = (this.sequence + 1n) & MAX_SEQUENCE;
      if (this.sequence === 0n) {
        // Sequence exhausted in this millisecond: spin until next ms
        while (timestamp <= this.lastTimestamp) {
          timestamp = BigInt(Date.now()) - EPOCH;
        }
      }
    } else {
      this.sequence = 0n;
    }

    this.lastTimestamp = timestamp;

    return (
      (timestamp << TIMESTAMP_SHIFT) |
      (this.machineId << MACHINE_SHIFT) |
      this.sequence
    );
  }

  generateString(): string {
    return this.generate().toString();
  }
}

// Usage: one instance per service process, machine ID from config/environment
const generator = new SnowflakeGenerator(
  parseInt(process.env.MACHINE_ID ?? "0", 10)
);

// Extracting creation time from a Snowflake ID
function snowflakeToDate(id: bigint): Date {
  const timestamp = (id >> TIMESTAMP_SHIFT) + EPOCH;
  return new Date(Number(timestamp));
}

// IDs fit in a Postgres BIGINT column (64-bit signed integer)
// Max value: 2^63 - 1 = 9,223,372,036,854,775,807
// At 4096 IDs/ms/node with 1023 nodes: years of headroom

What Snowflake gets right: 64-bit integers fit in a BIGINT column without any extension or special type, join performance is optimal, and integer comparisons are faster than string comparisons. IDs are monotonically increasing per machine, so index fragmentation is near zero. You can extract creation time from the ID without a database lookup.

What Snowflake gets wrong: machine ID assignment is a coordination problem. You need some mechanism to assign unique machine IDs to each process. The common approaches are: hardcode them per deployment (fragile), use a distributed coordinator like ZooKeeper or etcd (operational overhead), or use a partial ID from the machine’s IP or MAC address (collision-prone in containerized environments).

Clock skew is the other failure mode. If the system clock jumps backward, the generator must either wait until the clock catches up or reject requests. Waiting is usually the right choice for small corrections. For larger corrections (NTP adjustments, VM live migration, leap seconds), you need operational procedures.

Tradeoffs at a Glance

Property	Auto-Increment	UUID v4	UUID v7	ULID	Snowflake
Sortable by creation time	Yes (within single sequence)	No	Yes (1ms resolution)	Yes (1ms resolution)	Yes (1ms resolution)
Index fragmentation	None	High	Low	Low	None
Coordination required	Yes (sequence owner)	No	No	No	Partial (machine ID)
Storage (bytes)	4-8	16 (binary) / 36 (text)	16 (binary) / 36 (text)	16 binary / 26 chars	8
Enumerable	Yes	No	No	No	No
Clock skew sensitivity	None	None	Low (random fallback)	Low (random fallback)	High
Human readable	Yes	Partially	Partially	Yes (Base32)	No
Max throughput/node	Sequence limited	Unlimited	Unlimited	Unlimited	4.096M/s
Standardization	SQL standard	RFC 4122	RFC 9562	Community spec	No RFC

Production Considerations

Multi-Tenant SaaS

Expose a different ID to customers than the one you use internally. Use a UUID or ULID as the external API identifier, keyed to an internal integer primary key. This lets you reference records efficiently in the database while preventing enumeration of other tenants’ records.

// External: ULID (opaque, non-enumerable, sortable)
// Internal: BIGINT (fast joins, index efficient)
CREATE TABLE tenants (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  public_id  CHAR(26) UNIQUE NOT NULL DEFAULT generate_ulid(),
  name       TEXT NOT NULL
);

// API routes use public_id, queries resolve to id internally
async function getTenant(publicId: string, db: Pool) {
  return db.query(
    "SELECT * FROM tenants WHERE public_id = $1",
    [publicId]
  );
}

Database Index Fragmentation

If you inherit a UUID v4 primary key on a large table, the fragmentation is already done. Before switching ID schemes, benchmark whether the fragmentation is actually causing problems. Run pg_stat_user_tables and look at n_dead_tup and the bloat estimates from pgstattuple. If fragmentation is measurable, a CLUSTER operation on the primary key index will physically reorder the table, but it locks the table exclusively.

For new tables, the rule is simple: if you need random IDs, use UUID v7 or ULID over UUID v4. The timestamp prefix adds no meaningful overhead and eliminates the fragmentation problem.

Clock Skew in Snowflake Deployments

The practical approach for containerized environments is to assign machine IDs via the orchestrator, not the machine itself. In Kubernetes, this means using a StatefulSet and deriving the machine ID from the pod ordinal:

// In a Kubernetes StatefulSet, pod name is: my-service-0, my-service-1, etc.
const podName = process.env.HOSTNAME ?? "my-service-0";
const ordinal = parseInt(podName.split("-").pop() ?? "0", 10);
const generator = new SnowflakeGenerator(ordinal % 1024);

For clock skew tolerance, add a startup check that validates the local clock against a trusted source before accepting traffic. NTP-disciplined hosts with a max drift of ±10ms are sufficient for Snowflake’s 1ms timestamp resolution.

Migrating Between ID Schemes

Migration is the situation you want to avoid, but if you must do it: add the new ID column first, populate it asynchronously, update foreign key references in a separate migration, then drop the old primary key. Never do this as a single ALTER TABLE on a large table.

// Step 1: add new column, keep old primary key
ALTER TABLE orders ADD COLUMN ulid_id CHAR(26);

// Step 2: backfill in batches (run outside peak traffic)
UPDATE orders
SET ulid_id = generate_ulid()
WHERE ulid_id IS NULL AND id BETWEEN $1 AND $2;

// Step 3: add unique constraint (build index concurrently)
CREATE UNIQUE INDEX CONCURRENTLY idx_orders_ulid ON orders(ulid_id);

// Step 4: update application to write both IDs for a transition period
// Step 5: update API routes to accept both old and new IDs
// Step 6: once old ID references are gone, drop the column

The transition period where both IDs coexist is unavoidable if you have external consumers. Build your API layer to accept both formats and return the new format in responses. Give consumers a deprecation window before removing the old ID.

The Practical Decision Tree

Start with the simplest thing that does not create a future constraint:

If you have a single Postgres database and no sharding plans: use BIGINT GENERATED ALWAYS AS IDENTITY or uuid with UUID v7. Integer primary keys for internal joins, UUID v7 as the external-facing API identifier.

If you have multiple writers or expect to shard: use UUID v7 or ULID. Both require no coordination, sort correctly, and fit in standard column types.

If you need IDs to be 64-bit integers (binary protocol, specific client libraries, extreme join performance requirements): implement Snowflake. Accept the operational cost of machine ID management.

If you have legacy UUID v4 and the system is running fine: do not migrate. The cost of migration on a running system almost always exceeds the benefit of better index performance unless you have measured a real problem.

The ID scheme you pick is not the most important decision in your system. But it is one of the decisions that becomes load-bearing in ways you do not notice until it is expensive to change.