Choosing the Right Unique ID Strategy: From DB Auto‑Increment to Snowflake

1. Database Auto‑Increment Sequence or Field

Most common method, uses the database to guarantee uniqueness across the whole database.

Advantages:

Simple, easy to code, acceptable performance.

Numeric IDs are naturally ordered, helpful for pagination or sorting.

Disadvantages:

Different DB syntaxes; migration can be painful.

Single master becomes a single point of failure in master‑slave setups.

Scalability issues under high load.

Complex when merging systems or sharding.

Optimization: In multi‑master environments assign different start values and same step size to each master, e.g., Master1 generates 1,4,7…, Master2 generates 2,5,8…, etc.

2. UUID

Common method, can be generated by DB or program, globally unique.

Advantages:

Simple, easy to code.

Very good generation performance.

Globally unique, eases data migration and system merging.

Disadvantages:

No natural ordering.

Usually stored as strings, query performance lower.

Large storage footprint, high transmission cost.

Not human‑readable.

3. UUID Variants

To improve readability, convert UUID to Int64:

public static long GuidToInt64()
{
    byte[] bytes = Guid.NewGuid().ToByteArray();
    return BitConverter.ToInt64(bytes, 0);
}

NHibernate’s COMB algorithm combines GUID with timestamp to retain ordering:

private Guid GenerateComb()
{
    byte[] guidArray = Guid.NewGuid().ToByteArray();
    DateTime baseDate = new DateTime(1900,1,1);
    DateTime now = DateTime.Now;
    TimeSpan days = new TimeSpan(now.Ticks - baseDate.Ticks);
    TimeSpan msecs = now.TimeOfDay;
    byte[] daysArray = BitConverter.GetBytes(days.Days);
    byte[] msecsArray = BitConverter.GetBytes((long)(msecs.TotalMilliseconds / 3.333333));
    Array.Reverse(daysArray);
    Array.Reverse(msecsArray);
    Array.Copy(daysArray, daysArray.Length-2, guidArray, guidArray.Length-6, 2);
    Array.Copy(msecsArray, msecsArray.Length-4, guidArray, guidArray.Length-4, 4);
    return new Guid(guidArray);
}

Result illustration:

4. Redis ID Generation

When database performance is insufficient, Redis can generate IDs using atomic INCR/INCRBY. In a cluster, each node can be initialized with a different start value and step equal to the number of nodes, ensuring unique IDs across the cluster.

Example sequence for a 5‑node cluster:

A: 1,6,11,16,21

B: 2,7,12,17,22

C: 3,8,13,18,23

D: 4,9,14,19,24

E: 5,10,15,20,25

Advantages:

Independent of the database, flexible, higher performance.

Numeric IDs are naturally ordered.

Disadvantages:

Requires adding Redis component, increasing system complexity.

Configuration and coding effort.

5. Twitter Snowflake Algorithm

Snowflake generates 64‑bit IDs composed of a timestamp (41 bits), data‑center ID (5 bits), machine ID (5 bits), and a sequence number (12 bits). The result is a long integer.

C# implementation:

public class IdWorker
{
    private long workerId;
    private long datacenterId;
    private long sequence = 0L;
    private static long twepoch = 1288834974657L;
    private static long workerIdBits = 5L;
    private static long datacenterIdBits = 5L;
    private static long maxWorkerId = -1L ^ (-1L << (int)workerIdBits);
    private static long maxDatacenterId = -1L ^ (-1L << (int)datacenterIdBits);
    private static long sequenceBits = 12L;
    private static long workerIdShift = sequenceBits;
    private static long datacenterIdShift = sequenceBits + workerIdBits;
    private static long timestampLeftShift = sequenceBits + workerIdBits + datacenterIdBits;
    private static long sequenceMask = -1L ^ (-1L << (int)sequenceBits);
    private long lastTimestamp = -1L;
    private static readonly object syncRoot = new object();

    public IdWorker(long workerId, long datacenterId) { /* validation omitted */ }

    public long nextId()
    {
        lock (syncRoot)
        {
            long timestamp = timeGen();
            if (timestamp < lastTimestamp)
                throw new ApplicationException($"Clock moved backwards. Refusing to generate id for {lastTimestamp - timestamp} milliseconds");
            if (lastTimestamp == timestamp)
            {
                sequence = (sequence + 1) & sequenceMask;
                if (sequence == 0)
                    timestamp = tilNextMillis(lastTimestamp);
            }
            else
                sequence = 0L;
            lastTimestamp = timestamp;
            return ((timestamp - twepoch) << (int)timestampLeftShift)
                   | (datacenterId << (int)datacenterIdShift)
                   | (workerId << (int)workerIdShift)
                   | sequence;
        }
    }

    protected long tilNextMillis(long lastTimestamp)
    {
        long timestamp = timeGen();
        while (timestamp <= lastTimestamp) timestamp = timeGen();
        return timestamp;
    }

    protected long timeGen() => (long)(DateTime.UtcNow - new DateTime(1970,1,1,0,0,0,DateTimeKind.Utc)).TotalMilliseconds;
}

Test code demonstrates concurrent generation and duplicate detection.

6. Zookeeper‑Based ID Generation

Zookeeper can use the version number of a znode to produce 32‑bit or 64‑bit sequential numbers. It is rarely used because it introduces a dependency on Zookeeper and requires multi‑step API calls; performance under high concurrency is limited.

7. MongoDB ObjectId

ObjectId is a 12‑byte value consisting of a 4‑byte timestamp, a 5‑byte machine identifier, a 2‑byte process identifier, and a 3‑byte incrementing counter. The timestamp at the beginning makes ObjectIds roughly sortable by creation time, which is useful for indexing.

Diagram: