Why Global Unique, Trend‑Ordered IDs Matter and How to Generate Them Efficiently

Introduction

Business systems need a reliable way to generate identifiers (order ID, product ID, article ID, etc.) that serve as the primary key and clustered index in a relational database. The two essential properties of a good identifier are:

Global uniqueness – no two rows anywhere in the system may share the same value.

Trend‑ordered (monotonically increasing) – the values grow in the same order as they are inserted, which aligns with B‑tree clustered indexes.

Why Global Uniqueness?

Natural identifiers (e.g., national ID numbers) can be duplicated when a person obtains a replacement document. If such a value is used as the primary key, old records cannot be deactivated, leading to security and fraud risks. A surrogate key – an artificial primary key unrelated to business data – avoids these problems and satisfies the first normal form of relational design.

Why Trend‑Ordered IDs?

In MySQL InnoDB (and most RDBMS) the table data is stored in a B+‑tree clustered index. When the primary‑key values are inserted in the same order as the leaf‑node order of the tree, inserts cause only tail‑page splits, minimizing page‑splits and maximizing write throughput. Therefore a primary key that is naturally increasing (auto‑increment, ROWID, or a custom monotonic key) yields the best performance.

Database Auto‑Increment

Most relational databases provide an auto‑increment column (INT or BIGINT). It is simple to use and yields ordered numeric IDs that are useful for pagination.

Advantages : trivial implementation, low latency, natural ordering.

Limitations : syntax varies across databases, a single master becomes a bottleneck, scaling under high write load is difficult, and sharding or multi‑database migrations require careful coordination.

UUID

Universally Unique Identifiers (UUID v4) can be generated by the database or in application code. They guarantee global uniqueness and are handy for data migration or system merging.

Advantages : no coordination needed, works across heterogeneous systems.

Drawbacks : not ordered, 128‑bit size increases storage, slower index look‑ups, and the string representation is not human‑readable.

Redis‑Based ID Generation

When database‑generated IDs become a performance bottleneck, Redis can be used because its single‑threaded nature makes INCR and INCRBY atomic.

Typical pattern for a Redis cluster with N nodes:

# Example for a 5‑node cluster
# Node 1 starts at 1, step 5 → 1,6,11,…
# Node 2 starts at 2, step 5 → 2,7,12,…
# Node 3 starts at 3, step 5 → 3,8,13,…
# Node 4 starts at 4, step 5 → 4,9,14,…
# Node 5 starts at 5, step 5 → 5,10,15,…

Each node produces a disjoint, monotonic sequence, giving globally unique, trend‑ordered IDs without a single point of failure. Redis can also generate daily serial numbers by resetting a key each day and using INCR for the per‑day counter.

Pros : high throughput, no database dependency, natural numeric ordering.

Cons : introduces a new component, requires operational overhead for configuration and monitoring.

Twitter Snowflake

Snowflake is a 64‑bit, time‑ordered, globally unique ID format originally built by Twitter. Its bit layout is:

+-------------------------------------------------------------------+
| 41 bits timestamp | 10 bits machine ID | 12 bits sequence per ms |
+-------------------------------------------------------------------+

Details:

Timestamp: milliseconds since a custom epoch (covers ~69 years).

Machine ID: up to 1024 distinct nodes.

Sequence: up to 4096 IDs per node per millisecond.

Benefits: high throughput, low latency, IDs are roughly time‑ordered. Drawbacks: requires a dedicated service, is sensitive to clock drift (time‑rollback can cause duplicates), and IDs are not contiguous.

MongoDB ObjectId

MongoDB’s 12‑byte ObjectId follows a similar principle:

Byte layout (big‑endian)
+----------------+----------------+----------------+----------------+
| 4‑byte seconds | 5‑byte machine | 2‑byte pid     | 3‑byte counter |
+----------------+----------------+----------------+----------------+

Characteristics:

Timestamp (seconds) provides approximate time ordering and embeds creation time.

Machine identifier is typically a hash of the host name.

Process ID distinguishes multiple mongod instances on the same host.

Counter ensures uniqueness within the same second (max 16,777,216 per process).

ObjectId can be generated on the client side, reducing server load.

Snowflake Variants in China

Open‑source projects extend Snowflake with additional safety features such as clock‑rollback protection:

Baidu uid‑generator – https://github.com/baidu/uid-generator

Meituan Leaf – https://github.com/zhuzhong/idleaf

Both retain the 64‑bit structure while adding configurable epoch, node‑id allocation, and fallback strategies.

Design Requirements Summary

High availability – no single point of failure.

Global uniqueness – IDs must never collide across all nodes and data centers.

Trend‑ordered (monotonic) – aligns with B‑tree clustered indexes for optimal write performance.

Time‑ordered – embeds a timestamp to simplify sharding, hot‑cold data separation, and debugging.

Sharding support – ability to embed a shard identifier (e.g., user‑id) if needed.

Compact size – preferably 64‑bit (fits in a signed BIGINT), avoiding the overhead of 128‑bit UUIDs.

Security considerations – avoid predictable sequential IDs when exposure could aid enumeration or fraud; optional randomization or hashing may be applied.