10. Unique ID Generation
1
The picture to keep in your head2
10.1 What makes a good ID?3
10.2 What's wrong with auto-increment?4
10.3 Why does auto-increment break across many servers?5
10.4 What are UUIDs, and which version should you use?6
10.5 How do Snowflake IDs work?7
10.6 What are ticket servers and ID ranges?8
10.7 Why does clock skew matter for time-based IDs?9
10.8 How do you make short IDs with base62?10
10.9 Should IDs be guessable or unguessable?11
Memory card and where the analogy breaks12
Practice: five levels13
Interview questions14
Solutions15
Sources16
Code lab10. Unique ID Generation
10.2 What's wrong with auto-increment?
3 min read+10 XP
At the bank: With one branch, the token machine is perfect: simple, short numbers, always in order, never repeated.
In the system: Auto-increment (SERIAL, IDENTITY, AUTO_INCREMENT) makes the database hand out the next number on every insert. With one database, it's excellent:
- Unique, guaranteed by the database.
- Short (fits in a 64-bit integer, or even 32-bit at first).
- Sorted: new rows go at the end of the primary-key index, which databases handle very efficiently.
- No extra system to run.
For a single database, use it. Many very large products ran on auto-increment IDs for years. The problems only start when you need IDs from more than one place (10.3), or when IDs are shown to the public (10.9).
One gotcha even on one database: auto-increment numbers can have gaps. A rolled-back transaction or a crash may skip numbers. Never rely on IDs being continuous; never use them to count rows.
Memory code: "One machine, one counter: perfect, until there are two."