Four Ways to Ensure Unique IDs After Database Sharding

Step 1: Understand the Source of Collisions

In a single‑database setup, an auto‑increment primary key is unique because there is only one "number‑issuing window". After sharding, each node maintains its own auto‑increment sequence, so A‑DB and B‑DB may both start from 1, causing ID clashes.

Setting different initial values and step sizes per database (e.g., A starts at 1 with step 3, B starts at 2 with step 3) can avoid collisions, but it dramatically increases operational complexity and makes scaling difficult.

Therefore a dedicated "ID generator" is needed to centralize issuance.

Step 2: Four Main ID Generation Schemes

UUID : Generated locally without external services and extremely fast. However, it is a 32‑character unordered string; using it as a primary key leads to frequent B‑tree page splits, severe write‑performance degradation, and wasted storage. It can be used for temporary identifiers but is not recommended as a primary key.

Database Auto‑Increment : The most intuitive approach—create a separate table to issue IDs. Simplicity is a plus, yet under high concurrency the issuing table becomes a single‑point bottleneck and scaling is hard.

Snowflake (Twitter open‑source): Packs a 64‑bit integer into timestamp, machine ID, and sequence number, allowing local generation without network calls and delivering extremely high performance. The only major concern is clock‑backward events.

Segment Allocation (号段模式) : Pre‑fetch a batch of IDs (e.g., 10,000) from the database into memory and consume them locally; when exhausted, fetch another batch. This puts minimal load on the database and guarantees strictly increasing IDs. Implementations include Meituan Leaf and Didi TinyId.

One‑sentence summary: Use UUID or auto‑increment for low concurrency, segment allocation for medium concurrency, and Snowflake for high concurrency.

Step 3: Why Snowflake Is the Champion

Snowflake splits a 64‑bit Long into:

1 sign bit : fixed to 0 to keep IDs positive.

41 bits timestamp : millisecond precision, covering about 69 years; high‑order placement ensures natural ordering, which is index‑friendly.

10 bits machine ID : supports up to 1024 nodes; can be divided into 5‑bit data‑center and 5‑bit machine numbers for multi‑region deployments.

12 bits sequence : increments within the same millisecond, allowing 4096 unique IDs per millisecond per node.

The theoretical per‑node QPS reaches 409.6 万, more than enough for most applications. The bit allocation is flexible—if you have few machines but high concurrency, shrink the machine bits and enlarge the sequence bits, or embed business identifiers as needed.

Step 4: Six Practical Pitfalls in Production

Clock Backward : NTP adjustments or VM pauses can cause time to move backwards, leading to duplicate IDs. Small backward shifts (a few ms) can be tolerated; large shifts require switching to a standby machine ID.

Machine‑ID Conflict : Container restarts may change IPs, or manual config errors can cause duplicate machine IDs. Use Zookeeper or Nacos for dynamic allocation instead of manual settings.

Epoch Setting : Using the 1970 epoch means the 41‑bit timestamp will run out around 2026. Set the epoch to the actual service launch date to utilize the full 69‑year range.

Rigid Bit Allocation : The default 10‑bit machine + 12‑bit sequence may not suit all scenarios. Expand machine bits for large clusters or sequence bits for ultra‑high concurrency.

Non‑Monotonic IDs Across Nodes : Millisecond‑level clock drift can cause IDs to appear out of order globally. This usually does not affect sharding scenarios, but if absolute ordering is required, prefer segment allocation.

Security Leakage : Sequential IDs can be enumerated and may expose timestamp and machine information. Apply XOR obfuscation or start the sequence at a random offset to mitigate.

Decision‑Making Template

When faced with a concrete scenario, follow this decision tree:

Concurrent requests > 100 k TPS → Choose Snowflake and protect against clock rollback.

Need strictly increasing IDs with zero tolerance for clock issues → Use segment allocation; deploy an independent ID service (e.g., Meituan Leaf).

Medium concurrency with Redis available → Redis auto‑increment is simple and effective.

Low concurrency, temporary identifiers only → UUID suffices, but avoid using it as a primary key.

Complex business may combine approaches: core transactions use Snowflake, social relationships use segment allocation, and logging uses UUID v7.

Final Thought

Choosing a distributed ID solution is fundamentally about understanding system scale. Do not over‑engineer with Snowflake clusters for a system handling a few thousand daily active users, nor rely on database auto‑increment for million‑level concurrent workloads. The right solution is the one that best matches the business requirements.