Generating Unique IDs: Auto-Increment, UUIDv4, UUIDv7 and Snowflake

Version note: The code in this post was built and run with C# 14 and .NET 10; the Snowflake measurement was taken with a Release build.

Guess First

Bookstore's Orders table in SQL (Structured Query Language) Server has lived on int IDENTITY for years. Order volume grew, and the team decided to split the table into four shards by customer_id, as in this series' "Scaling the Data Layer: Replication, Sharding and SQL vs NoSQL" post. Now each of the four databases counts its own identity from 1. Someone on the team says "let's use Guid.CreateVersion7(), it's time ordered"; someone else says "let's generate a 64-bit number like Snowflake, it fits in a bigint".

Two questions. Of 100,000 UUIDv7 values generated back to back in a single process, how many sort "lower" than the one generated just before them? And when you send a Snowflake ID to the Angular app as a number inside JSON (JavaScript Object Notation), does the browser see the same number?

What Do We Want from an ID?

"A unique ID" looks like a single requirement, but there are at least six separate questions underneath. Uniqueness: no collisions across the whole system, ever. Sortability: sorting by ID should give an order close to creation order; the cost of inserting into the index and the "latest orders" query depend on it. Size: the difference between an 8-byte bigint and a 16-byte UUID (Universally Unique Identifier) repeats in every foreign key and every index. Coordination: do you need to go to a central place to get an ID, and do writes stop if that place goes down? Unguessability: can someone who sees an ID guess the next one? Information leakage: does the ID reveal when it was created, which machine produced it, or how many orders you take per day?

Database Sequences and Sharding

As long as there's a single database, an identity or sequence is unbeatable: small, ordered, guaranteed by the database itself. With sharding, four databases produce the same 1, 2, 3. There are three classic remedies. The first is giving each shard a different start and the same increment: 1, 5, 9... / 2, 6, 10... for four shards. It works, but changing the shard count means rethinking the whole scheme. The second is a central ticket server: in 2010 Flickr described, in "Ticket Servers: Distributed Unique Primary Keys on the Cheap", two MySQL servers that existed only to hand out IDs; one gave odd numbers and the other even, so that one could carry on when the other went down. The third is block allocation (hi-lo): the application takes not a single ID from the central sequence but a block of, say, 1,000, and hands out IDs from memory until the block is used up. UseHiLo() in Entity Framework Core's (EF Core) SQL Server provider is the .NET version of this pattern. The cost is that order is only roughly preserved: two instances' blocks interleave.

What they all have in common is that the ID is "fetched" from somewhere. In 2011 Instagram took another path in "Sharding & IDs at Instagram": it generated the ID inside each logical shard, in PostgreSQL, with a function written in PostgreSQL's procedural language (PL/pgSQL); 41 bits of time, 13 bits of shard ID, 10 bits of sequence. That's the Snowflake idea we'll see below, embedded in the database.

UUIDv4: Unique but Scattered

UUIDv4 has 122 random bits; the chance of a collision is practically zero and it needs no coordination. Guid.NewGuid() is exactly that. The trouble starts in the database. A clustered index (or a B-tree primary key) keeps rows in key order; a random key lands at a random spot in the index every time, pages split and stay half full, and the set of "hot" pages that need to stay in memory spreads over the entire index. RFC (Request for Comments) 9562, which defines UUIDs, names v4's poor index locality as one of the main reasons for defining the new versions. The Solution Patterns series post "Exposing Ids: int Identity, GUID v7 and the Public Id Split" measured this in SQL Server: with 200,000 rows, a random GUID (Globally Unique Identifier) primary key produced 99.4 percent fragmentation and about 60 percent more pages than identity.

UUIDv7 and Guid.CreateVersion7()

RFC 9562 (May 2024, replacing RFC 4122) defined a new time-ordered version: the first 48 bits are Unix time in milliseconds, followed by 4 bits of version, 12 bits of rand_a, 2 bits of variant and 62 bits of rand_b. The RFC recommends v7 over v1 and v6 where possible. Guid.CreateVersion7(), which arrived with .NET 9, produces it; PostgreSQL 18 also has a uuidv7() function.

There are two traps. The first is specific to SQL Server: uniqueidentifier isn't sorted in string order but treats the last 6 bytes as the most significant; in v7 those bytes are the random part. In the measurement in the Solution Patterns post mentioned above, a v7 primary key came out as scattered as v4 in SQL Server. PostgreSQL's uuid type sorts by byte order, so there v7 really is ordered.

The second is the subject of the first Guess First question: order within the same millisecond. I generated 100,000 in a tight loop and compared each with the previous one:

// Guid.CreateVersion7 in a tight loop: does creation order match sort order?
Guid[] v7 = new Guid[100_000];
for (int i = 0; i < v7.Length; i++) v7[i] = Guid.CreateVersion7();
int outOfOrder = 0, sameMs = 0;
for (int i = 1; i < v7.Length; i++)
{
    if (string.CompareOrdinal(v7[i].ToString(), v7[i - 1].ToString()) < 0) outOfOrder++;
    if (v7[i].ToString()[..13] == v7[i - 1].ToString()[..13]) sameMs++;
}
Console.WriteLine($"v7: {v7.Length:N0} ids, {sameMs:N0} share the previous id's ms, {outOfOrder:N0} sort before the previous id");

v7: 100,000 ids, 99,970 share the previous id's ms, 49,969 sort before the previous id

About half. The reason is plain in the implementation in dotnet/runtime: CreateVersion7 produces a NewGuid() and overwrites only the first 48 bits with the time and the version and variant bits with constants; rand_a and rand_b stay fully random, and there's no counter within the millisecond. The RFC leaves this open: for implementations that generate batches within the same millisecond and care about order it suggests a counter (Method 1), a monotonically increasing random value (Method 2) or sub-millisecond time (Method 3), but it doesn't make them mandatory. The practical upshot: v7 is more than enough for index locality, because IDs from the same millisecond land on the same index page anyway. But don't rely on "sort by ID, get creation order" below the millisecond. If order matters to the business (like messages in a conversation), keep a separate sequence number; the series' chat case study does exactly that.

Snowflake: An Ordered ID That Fits in 64 Bits

In 2010 Twitter announced Snowflake for tweet IDs. The design in its open-source README (where the machine ID is "configured", that is, supplied from outside): 41 bits of milliseconds (about 69 years with a custom epoch), 10 bits of machine ID (1,024 machines), 12 bits of sequence (4,096 IDs per machine per millisecond). The requirements are in the README too: machines should generate IDs without coordinating with each other, and IDs should be roughly time ordered. The result is a coordination-free ID that fits in a bigint and is appended at the end of the index. In C#:

// 1 unused bit | 41 bits milliseconds since epoch | 10 bits worker | 12 bits sequence
public sealed class SnowflakeGenerator
{
    public static readonly DateTimeOffset Epoch = new(2026, 1, 1, 0, 0, 0, TimeSpan.Zero);
    private const int WorkerBits = 10, SequenceBits = 12;
    private const long MaxSequence = (1L << SequenceBits) - 1;     // 4095

    private readonly TimeProvider _time;
    private readonly long _workerId;
    private readonly Lock _lock = new();
    private long _lastMs = -1, _sequence;

    public SnowflakeGenerator(int workerId, TimeProvider? time = null)
    {
        ArgumentOutOfRangeException.ThrowIfGreaterThan(workerId, (1 << WorkerBits) - 1);
        _workerId = workerId;
        _time = time ?? TimeProvider.System;
    }

    public long WaitedForNextMs { get; private set; }

    public long NextId()
    {
        lock (_lock)
        {
            long ms = NowMs();
            if (ms < _lastMs)
                throw new InvalidOperationException($"Clock moved backwards by {_lastMs - ms} ms; refusing to generate ids.");
            if (ms == _lastMs)
            {
                _sequence = (_sequence + 1) & MaxSequence;
                if (_sequence == 0)                        // 4096 ids in this ms: wait for the next one
                {
                    WaitedForNextMs++;
                    while ((ms = NowMs()) <= _lastMs) Thread.SpinWait(50);
                }
            }
            else
            {
                _sequence = 0;
            }
            _lastMs = ms;
            return (ms << (WorkerBits + SequenceBits)) | (_workerId << SequenceBits) | _sequence;
        }
    }

    private long NowMs() => (long)(_time.GetUtcNow() - Epoch).TotalMilliseconds;

    public static (DateTimeOffset CreatedAt, int Worker, int Sequence) Decode(long id) =>
        (Epoch.AddMilliseconds(id >> (WorkerBits + SequenceBits)),
         (int)((id >> SequenceBits) & ((1 << WorkerBits) - 1)),
         (int)(id & MaxSequence));
}

Time comes through TimeProvider; below we'll use that to turn the clock back by hand. I ran four workers in parallel and had each generate a million IDs:

snowflake: 4,000,000 distinct of 4,000,000 in 296 ms, increasing per worker: True, waited for next ms 932 times

All four million are distinct, and each worker's IDs are strictly increasing. The "waited 932 times" line is the limit itself: when a worker asks for more than 4,096 IDs in a millisecond, the sequence wraps and the generator waits for the next millisecond. A ceiling of about 4 million IDs per second per worker is far above Bookstore's needs; but knowing it's a ceiling lets you change the bit split for your own needs (fewer worker bits, more sequence bits).

The really hard part of Snowflake isn't the code, it's the worker ID. If two instances run with the same worker ID, they produce the same IDs in the same millisecond and the uniqueness guarantee silently disappears. Options: a fixed value from configuration (fragile under autoscaling), the pod ordinal in a Kubernetes StatefulSet, or taking a time-limited lease from a store like Redis or ZooKeeper at startup.

When the Clock Goes Backwards

Snowflake's ordering relies on the clock, and clocks can go backwards: NTP (Network Time Protocol) synchronization can pull a clock that ran ahead back by a few milliseconds, and a clock can jump when a virtual machine is migrated. If the clock goes back, the generator risks producing IDs for a millisecond in the past again. Twitter's README gives a clear answer: Snowflake refuses to generate IDs until it reaches a time after the last one it generated, and it recommends running NTP in a mode that doesn't move the clock backwards. I tried the same behavior with a clock I set by hand:

ManualClock clock = new(new DateTimeOffset(2026, 11, 8, 9, 30, 0, TimeSpan.Zero));
SnowflakeGenerator gen = new(7, clock);
gen.NextId();
clock.Now = clock.Now.AddMilliseconds(-5);
try { gen.NextId(); }
catch (InvalidOperationException ex) { Console.WriteLine($"clock went back: {ex.Message}"); }

clock went back: Clock moved backwards by 5 ms; refusing to generate ids.

Refusing is safe but harsh: waiting briefly on a pullback of a few milliseconds and failing with an alert on a large backward jump is a more practical middle ground. Whatever you choose, it should be a metric; a clock going backwards isn't something to paper over silently. Why clocks are unreliable in distributed systems is the subject of the upcoming Distributed Systems Fundamentals series.

What Does a Public ID Leak?

The moment an ID leaves through an API (Application Programming Interface), an address bar (URL, Uniform Resource Locator) or an email, it becomes readable data. I decoded two IDs in the same program:

snowflake 112719806108546862 -> created 2026-11-08 01:08:16.009, worker 3, sequence 1838
v7 01a1ba7e-463b-70a2-a544-55020b79ccef -> created 2026-11-08 09:30:00.123

The Snowflake ID tells you when the order was created, which worker produced it and its position within that millisecond; v7 gives the creation time down to the millisecond. A sequential int tells you the total order count, and from the difference between two dates, the daily volume. That can be enough for a competitor to estimate your business volume. Worse, guessable IDs invite IDOR (Insecure Direct Object Reference): as in the OWASP (Open Worldwide Application Security Project) Top 10 series post "OWASP A01: Broken Access Control (1) — IDOR, Admin Endpoints and Authorization in ASP.NET Core", someone who can see /orders/1042 tries /orders/1043. An unguessable ID doesn't replace authorization, but it's a second line of defense. The common answer is the split from the Solution Patterns post: a small, ordered ID inside and a separate public ID outside.

And the second Guess First question. In JavaScript, Number is a double and can't represent integers above 2^53 − 1 exactly. Snowflake IDs are far above that limit:

snowflake 112719806108546862 as a JS Number -> 112719806108546864, equal: False, max safe integer 9007199254740991

An ID serialized as a number turns into a different number in the browser, and the Angular app requests an order that doesn't exist. That's why Twitter's API also returned every ID as a string in id_str. In ASP.NET Core, write 64-bit IDs as strings in JSON with JsonNumberHandling.WriteAsString or a converter specific to the ID; in the API contract, the ID's type should be string.

Common Pitfalls

Assuming UUIDv7 is ordered in every database

SQL Server sorts uniqueidentifier by its last bytes; there v7 is as scattered as v4. In PostgreSQL it's ordered. Check your database's comparison rules.

Relying on order within a millisecond

Guid.CreateVersion7() uses no counter within a millisecond; half of consecutive IDs sort in reverse. If you need strict order, keep a separate sequence number.

Leaving the worker ID to chance

Two instances with the same worker ID silently break uniqueness. Assign worker IDs with a lease and check for conflicts at startup.

Sending a 64-bit ID as a JSON number

JavaScript rounds above 2^53. IDs should be strings in the API.

Treating the ID as authorization

An unguessable ID doesn't make the ownership check unnecessary; every query still needs the "does this record belong to this user?" check.

A Small Experiment

★☆☆ In SnowflakeGenerator, reduce worker bits from 10 to 5 and increase sequence bits to 17. What's the ceiling per worker per millisecond, and how many workers can you run?

★★☆ Implement Method 1 from RFC 9562: write a CreateMonotonicV7() that uses the 12 bits of rand_a as a counter within the same millisecond, and measure the number of reverse-ordered IDs in the loop above again.

★★★ Write a policy that waits when the clock goes back 3 ms and fails when it goes back more than 1 second. Test both cases with ManualClock and expose the wait time as a metric.

The building blocks are complete: load balancing, caching, the data layer, consistent hashing, asynchronous processing, rate limiting, reliability and ID generation. In the next post we bring them all together in the first case study, designing a URL shortener from requirements to deep dive.