The Night "Order 101" Existed Twice: Auto Increment vs UUID vs UUIDv7

A fictional story, based on real architectural trade-offs.

Choosing a primary key strategy feels like a boring decision. It's usually made in the first hour of a project, in a migration file, by whoever is setting up the database. But it quietly shapes everything that comes after: how your indexes behave, how your system scales, and whether you end up in a painful migration a few years down the line.

To see why, let's follow a small team building a travel booking app, from their first day to their first multi-region launch.

---

Chapter 1: The Golden Age of 1, 2, 3, 4...

The team starts simple: one application, one database. Every table gets an auto increment primary key.

Each time a new order is inserted, the database assigns the next number: 1, 2, 3, 4. The application code doesn't generate anything or coordinate anything. It hands the whole job to the database.

And it works beautifully:

• It's compact. A standard INT takes just 4 bytes, and a BIGINT takes 8 bytes.
• It keeps indexes small. Smaller keys mean smaller index structures, which means less memory pressure and less disk I/O.
• It's human-friendly. When a customer calls support, the agent can say, "Let me pull up Order 18427." Numbers like that are easy to read, search, and say out loud on a phone call.

For a single, centralized database, auto increment is still the standard choice, and for good reason. The team ships fast and sleeps well.

---

Chapter 2: The Night Order 101 Existed Twice

Then the app takes off in Europe. Latency to the US database is painful, so the team does the sensible thing and spins up a second database in a European region.

A week later, someone from the data team pings the channel:

"Why are there two different orders with ID 101?"

Of course there are. The US database and the European database are independent. Each one happily hands out its own next number, so both issued Order 101. Nobody did anything wrong. The design just assumed there was only ever one source of truth.

Now the team is in workaround territory:

• Give each database its own offset range (US gets 1 to 1,000,000, Europe gets 1,000,001 and up)
• Build a central ID generator service that every database has to call
• Set up cross-database sequence synchronization

Every option adds coordination overhead, moving parts, and new ways to fail. The simplicity that made auto increment attractive has turned into a tax.

---

Chapter 3: The Curious Visitor at /orders/123

While the team is untangling that mess, a security-minded engineer notices something in the API logs. Someone is calling /orders/123, then /orders/124, then /orders/125.

Sequential IDs in public API routes are an invitation. A curious (or malicious) person can guess adjacent IDs, and by watching how high the numbers go, they can even estimate your data volume. Nothing about the database is broken, but the design leaks information.

---

Chapter 4: Enter the UUID

The team needs identifiers that no single database has to hand out. Enter the UUID: a 128-bit (16-byte) identifier generated by algorithms with an extraordinarily low chance of collision across independent nodes.

Suddenly, a lot of problems disappear:

• Decentralized generation. Microservices, regional databases, even offline client devices can create IDs without asking a central authority.
• Painless merging and sharding. Combining records from separate databases, or moving data between shards, is straightforward because every ID is globally unique.
• No more trivial enumeration. Non-sequential values make URL guessing much harder.

The team celebrates. Order 101 can never exist twice again.

But UUIDs come with a bill.

---

Chapter 5: The Bill Arrives

Six months later, the dashboards tell a different story.

• Storage doubled. A UUID needs at least 16 bytes, twice the size of a BIGINT, and even more if someone stores it as a text string.
• Indexes bloated. Larger IDs inflate the primary key and every secondary index that references it, driving up RAM usage and disk I/O.
• Writes got slower. This is the big one. The team used standard random UUIDs (UUIDv4), and random values scatter new inserts across the index B-tree. As the table grows, this causes severe index fragmentation and high write latency.

The very thing that gave them distributed independence (randomness) is now hurting their write performance.

---

Chapter 6: The Plot Twist, UUIDv7

The fix isn't to abandon UUIDs. It's to change what's inside them.

UUIDv7 places a Unix timestamp in the most significant bits, followed by random bits. That one design choice changes everything: IDs are still globally unique and can still be generated anywhere, but they are now roughly time-ordered, so new inserts land at the end of the B-tree instead of all over it.

The team migrates new tables to UUIDv7. They keep the distributed independence and get the sequential index behavior back.

---

Chapter 7: Two Lessons the Team Learned the Hard Way

Lesson 1: A UUID is not a security mechanism

UUIDs obscure sequential numbers and stop naive endpoint crawling, but they are not access control. Every API endpoint still needs proper authentication and authorization. If /orders/{uuid} returns someone else's data to any logged-in user, the format of the ID doesn't save you.

Lesson 2: You don't have to pick just one

The support team still misses saying "Order 18427" out loud. Reading 36 characters of UUID to a customer over the phone is nobody's idea of a good time.

So the team adopts the Hybrid Pattern:

• Internal primary key: a UUID (or UUIDv7) inside the database, for distributed safety, cross-system sync, and sharding.
• External customer reference: a short, human-friendly code like ORD-104582 shown in the UI and in support emails.

Engineers get global uniqueness, customers and support get something readable, and everybody gets what they need.

---

The Moral of the Story

Get this decision right early, and your system scales across services and regions without drama. Get it wrong, and you'll meet Order 101 (twice) at the worst possible time.

---

What's your team's default primary key strategy, and has it ever bitten you in production? I'd love to hear your stories.

---

Credits

This post was inspired by the concepts explained in this video: Auto Increment vs UUID (and UUIDv7). All credit for the original technical explanation goes to its creator. The story, characters, and narrative framing here are my own retelling.