Generating unique IDs at scale: best practices explained

I've watched teams ship their first production system with auto-increment IDs and confidently scale horizontally—until the day they merge two databases and realize they have collisions everywhere. Generating unique IDs at scale is one of those problems that looks simple at first but demands careful thought as your system grows across multiple services, databases, and regions. In this article, I'll walk you through the trade-offs and show you what actually works in distributed systems.

Why auto-increment fails

Auto-increment is elegant for single-database applications. You insert a row, the database assigns the next sequential number, and collision is impossible. But the moment you introduce multiple write sources—whether it's database replication, service-to-service inserts, or geographical sharding—auto-increment becomes a nightmare.

In a ride-hailing platform I built early in my career, we had drivers and passengers inserting trips into a central trips table from different regions. We tried offsets: region A got even IDs, region B got odd IDs. It worked until we added region C. We switched to ranges: region A got 1–1M, region B got 1M–2M. Then the product team wanted dynamic regions. We ended up with ID collisions in staging and a very angry night shift.

The fundamental issue is that auto-increment assumes centralized coordination. In a distributed system, you need either a coordinator (which becomes a bottleneck) or a scheme that guarantees uniqueness without coordination.

UUIDv4: simple but problematic

UUIDv4 (random UUIDs) are the obvious answer: generate 128 random bits, collision probability is negligible, no coordination needed.

import { v4 as uuidv4 } from 'uuid';

const orderId = uuidv4();
// Example: 550e8400-e29b-41d4-a716-446655440000

For years, I used UUIDv4 everywhere. They solved the coordination problem. But I gradually noticed two real costs:

Index locality is terrible. When you insert UUIDv4 values into a B-tree index (which PostgreSQL and MySQL use), each new random value lands in a random page. You're constantly splitting pages and rebalancing the tree. With millions of inserts, you're doing massive amounts of unnecessary I/O. Index scans become slow. Compared to an auto-increment that always appends to the rightmost leaf, UUIDv4 indexes bloat and fragment.

Debugging becomes harder. UUIDs are opaque—they tell you nothing about when or where an entity was created. In a large trading platform, when someone reports an issue with "order ABC123," you can roughly guess the timeframe from the number. With a UUID, you have no signal. Logs become harder to correlate without explicit timestamps.

UUIDv4 is fine if you have a few million records and IDs are mostly read-only. But if you're building something that inserts millions of rows daily, the index performance cost compounds.

UUIDv7: time-ordered and index-friendly

UUIDv7 takes the best of both worlds: it's 128 bits like UUIDv4, but the first 48 bits are a timestamp (millisecond precision). The remaining bits are random. This makes it sortable and time-ordered.

import { v7 as uuidv7 } from 'uuid';

const userId = uuidv7();
// Example: 0198765f-d4ab-73b8-9c00-7de0e9c7f8b2
// First 48 bits encode milliseconds since Unix epoch

The benefit: when you insert UUIDv7 values, they're monotonically increasing (at millisecond granularity). B-tree indexes are happy because new values cluster near the rightmost leaf. Index fragmentation is minimal. You get both uniqueness without coordination and good index performance.

In my current work on a SaaS platform, we switched from UUIDv4 to UUIDv7 across the board about two years ago. Query performance on large tables with indexed UUIDs improved noticeably—I'd estimate 15–20% faster scans on our user table after the migration. The timestamp prefix also makes debugging easier: I can see at a glance that a user was created in Q2 2023.

The tradeoff is negligible. UUIDv7 is now standard (RFC 9562), and libraries exist for every language. Use it instead of UUIDv4 unless you have a specific reason not to.

Snowflake-style IDs: 64-bit and sortable

Snowflake IDs are a different approach: a 64-bit integer divided into fields. The canonical structure is:

The ID generation happens locally on each service: you read the current millisecond, add your worker ID, increment the sequence if you've already generated an ID in this millisecond. No centralized coordinator needed.

class SnowflakeIDGenerator {
  private workerId: number;
  private lastTimestamp: number = -1;
  private sequence: number = 0;

  constructor(workerId: number) {
    if (workerId < 0 || workerId > 1023) {
      throw new Error('Worker ID must be 0–1023');
    }
    this.workerId = workerId;
  }

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

    if (timestamp === BigInt(this.lastTimestamp)) {
      this.sequence = (this.sequence + 1) & 0xFFF; // 12-bit mask
      if (this.sequence === 0) {
        // Sequence overflow, wait for next millisecond
        while (timestamp === BigInt(this.lastTimestamp)) {
          timestamp = BigInt(Date.now());
        }
      }
    } else {
      this.sequence = 0;
    }

    this.lastTimestamp = Number(timestamp);

    // Compose: timestamp (41) | workerId (10) | sequence (12)
    const id =
      (timestamp << 22n) |
      (BigInt(this.workerId) << 12n) |
      BigInt(this.sequence);

    return id;
  }
}

const generator = new SnowflakeIDGenerator(1);
console.log(generator.generate()); // Example: 7834625903681n

I've used Snowflake IDs on a ride-hailing platform where we needed to generate millions of IDs per minute. The advantages were clear:

• Small and efficient: 64 bits fits in a single integer. Storage is half that of UUIDs. Network payload is smaller.
• Naturally sortable: Because the timestamp is the most significant bits, IDs are time-ordered. Queries like "all trips since 9 AM" can use index range scans efficiently.
• Inspectable: You can decode the timestamp and worker ID from the ID itself (no database lookup needed).
• Fast generation: Pure computation, no external calls.

The downside: you must ensure worker IDs are unique across your deployment. If two instances both think they're worker ID 1, you'll have collisions. This is solvable (assign IDs at startup via configuration or service discovery) but requires discipline.

Snowflake IDs are ideal when you control the entire stack and want maximum performance. UUIDs are better when you're integrating with external systems or can't guarantee worker ID uniqueness.

Never expose sequential IDs when enumeration is a risk

This deserves its own section because it's a security issue, not just a performance one.

If your REST API exposes sequential IDs in URLs—like /api/orders/12345—an attacker can trivially enumerate all orders by incrementing the number. They don't need to authenticate or exploit anything; they just iterate.

In a travel SaaS I worked on, we exposed booking IDs sequentially in the URL. A competitor wrote a bot that scraped the first 100K booking IDs over a weekend and got detailed pricing and customer information for all our bookings. We didn't notice for weeks.

The fix:

• Use non-sequential IDs in APIs: UUIDs or UUIDv7 are perfect here. If someone requests /api/orders/550e8400-e29b-41d4-a716-446655440000, incrementing the UUID doesn't help them.
• Add authorization checks: Even with sequential IDs, verify that the user owns the resource before returning it. But don't rely on this alone—attackers will find you anyway.
• Keep internal IDs private: Your database can use auto-increment or Snowflake IDs for performance. Your API surfaces a different ID (UUID or a hash). It's a small layer of translation but saves you.

Here's a pattern I use: store both an internal ID and an external ID (API ID):

// Database schema
CREATE TABLE orders (
  id BIGINT PRIMARY KEY,  // Snowflake ID, internal use only
  external_id UUID NOT NULL UNIQUE,  // Exposed in API
  user_id BIGINT,
  total DECIMAL,
  created_at TIMESTAMP
);

// API endpoint
app.get('/orders/:externalId', async (req, res) => {
  const order = await db.orders.findOne({
    external_id: req.params.externalId,
  });
  if (!order || order.user_id !== req.user.id) {
    return res.status(404).json({ error: 'Not found' });
  }
  res.json(order);
});

This way, your database gets the performance benefits of sequential or Snowflake IDs, and your API remains secure against enumeration.

Common mistakes

• Treating UUIDv4 as a solution to every problem: It works, but at a performance cost. Evaluate the trade-offs.
• Assuming a coordinator is free: If you set up a central service to hand out IDs, make sure it's highly available and fast. A slow coordinator becomes a bottleneck fast. Redis INCR can work for modest scale, but Twitter's Snowflake architecture exists because they needed to generate billions of IDs per day with multiple datacenters.
• Mixing ID schemes: If your database uses auto-increment, your cache uses Redis IDs, and your API exposes UUIDs, you'll have a maintenance nightmare. Pick one scheme per domain and stick with it.
• Forgetting about monotonicity: Snowflake IDs are only ordered if your clock is synchronized. Leap seconds, NTP corrections, or system clock resets can cause collisions. Mitigate by handling sequence overflow correctly (as shown in the code above).
• Exposing internal IDs without authorization checks: Even if you use UUIDs, implement authorization. Defense in depth.

Key takeaways

• Auto-increment doesn't survive multiple write sources. You need a scheme that guarantees uniqueness without centralized coordination.
• UUIDv4 works but has poor index locality and bloats B-trees. Consider it only for small datasets or when you have no better option.
• UUIDv7 is time-ordered and index-friendly. It's the default choice for most new systems. Use it unless you have a specific reason not to.
• Snowflake IDs are 64-bit, compact, and fast. They're ideal when you need to generate millions of IDs per minute and can ensure worker ID uniqueness.
• Never expose sequential IDs in APIs when enumeration is a risk. Use UUIDs externally and keep internal IDs (auto-increment or Snowflake) hidden.
• The best ID scheme depends on your scale, storage constraints, and security model. Measure before and after migration to confirm the trade-off is worth it.