Where Auto-Increment Falls Apart
Auto-increment IDs are perfect until you have more than one server. Offsets, ticket servers, and the answer that actually works.
A topic that sounds boring until it bites: how to generate unique IDs when more than one machine is writing.
The two usual suspects
UUID. For the most part 128 random bits. Any machine can generate one without asking anyone, and collisions are a theoretical concern. The downsides: they are big, they are not sortable by creation time, and they scatter writes all over the index, which databases do not enjoy.
Auto-increment. Start at 1, add 1 for every new row. Small, sortable, excellent index performance, and the database does all the work. One catch: it only works if exactly one thing is handing out numbers.
Where it falls apart
Three servers, A, B and C, each needs to create IDs on its own. With plain auto-increment they all start at 1. Three rows, one ID. Dead on arrival.
Offsets. Start A at 1, B at 2, C at 3, and count in steps of three. A gets 1, 4, 7. B gets 2, 5, 8. C gets 3, 6, 9. This works decently well for a fixed number of servers. Then you add server D. Now everyone has to count in steps of four, and nobody knows where D should start without colliding with numbers already handed out. The scheme falls apart the moment the cluster changes size, which is exactly when you need it most.
A ticket server. Put one isolated server in the middle whose only job is to hand out the next number. A asks, gets 1. B asks, gets 2. Clean, unique, sortable. And it ruins the whole point, because now it is centralised again. One machine everyone waits on, one machine that takes everything down with it.
The answer that works
Build the ID from parts that are unique on their own: a timestamp, a node ID, and a per-node sequence counter. Twitter's Snowflake did this with 64 bits (41 bits of milliseconds, 10 bits of machine ID, 12 bits of sequence). Every node generates IDs alone, no ticket server, no offsets, and the result still sorts roughly by time. The same idea lives on in ULID and in UUID version 7, which puts a timestamp in the top bits of an otherwise random UUID so it stays index-friendly.
The trade: you need every node to know its own ID and to have a roughly correct clock. Both are solvable. Neither is free.
When it matters
With a single writer, none of this matters. One database, one counter, no problem. Auto-increment is the right answer and stays the right answer.
The value is in knowing where the cliff is before you walk towards it. The day a second writer shows up, the cheapest fix is not offsets and not a ticket server. It is switching the ID type before the first row is written, because changing it afterwards is the part that actually hurts.