Collision Probability: Why 'Auto-Number' Feels Safe but Isn't

Why '1, 2, 3' Is a Trap

Auto-Increment feels safe, but it breaks distributed systems.

The 'Two Waiters' Analogy

• The Setup — Two waiters are taking orders in different rooms. They both write 'Order #1' on their pads.
• The Kitchen — When they hand the tickets to the chef (the database), chaos ensues. Which 'Order #1' is the vegan one?
• The Crash — This is a Primary Key Collision. It happens whenever you scale beyond one server.

The Centralization Bottleneck

• Single Source of Truth — To keep IDs sequential (1, 2, 3...), every server must ask ONE database for the next number.
• The Latency — If your user is in Tokyo and the DB is in Virginia, they wait for the round trip just to get an ID.
• Offline Impossible — Mobile apps can't generate IDs while offline because they don't know the 'current number'.

The Distributed Solution: CUIDs

Collision Resistant Unique Identifiers.

• Decentralized — The client (browser/phone) generates the ID, not the server.
• Structure — Timestamp + Counter + Client Fingerprint + Randomness.
• Result — You can generate IDs on a plane with no WiFi, and they will sync perfectly later.

The Fear: 'What About Collisions?'

• The Anxiety — Humans are bad at probability. We fear that random strings will accidentally match.
• The Reality — With standard UUIDs/CUIDs, the universe might end before you get a collision.

The Math: Birthday Paradox

Why you don't need to worry.

• Vast Space — A UUID has 128 bits. That's 340 undecillion possibilities.
• The Odds — To have a 50% chance of collision, you'd need to generate billions of IDs per second for years.
• Perspective — You are more likely to be hit by a meteorite while reading this slide.

Scenario: Database Merges

• The Merger — Company A buys Company B. You need to merge their user databases.
• Auto-Increment Nightmare — Both have a User ID 105. You have to rewrite every single Foreign Key in the entire system.
• CUID/UUID Bliss — IDs are unique globally. You just dump the tables together. Zero conflicts.

Scenario: Security Through Obscurity

• The URL Attack — If I see site.com/user/500, I will try site.com/user/501.
• Information Leak — Sequential IDs tell competitors exactly how fast you are growing.
• Random Protection — CUIDs are unguessable. cjld2cjxh0000qzrmn831i7rn reveals nothing.

Visual Comparison

• Auto-Increment — Server-bound. Fast locally, fragile globally. Hard to merge.
• UUID (v4) — Random. Huge size. Bad for DB indexing (fragmentation).
• CUID / UUID (v7) — Time-ordered. Client-generated. DB friendly. The sweet spot.

When to stick with Auto-Inc?

• Internal Tools — Admin panels where URL guessing doesn't matter.
• Small Data — If you will never shard or merge the database.
• Human Readability — Order numbers (Invoice #1024) are easier for support teams to type than UUIDs.

Summary: Embrace the Chaos

Stop counting. Start generating.

Randomness is the only way to scale safely.

Have you ever had to fix a primary key collision?