UUID vs. Auto-Increment ID: Choosing the Right Primary Key

It's one of the first decisions you make designing a database table, and it's easy to default to whatever your framework does automatically without thinking about it. Both approaches work — but they fail differently at scale, which is worth understanding before you're locked into one.

Auto-increment: simple, sequential, predictable

An auto-increment ID is just a counter: 1, 2, 3, 4... Every new row gets the next number. It's compact (a small integer), naturally sorts in creation order, and is easy to reason about.

The tradeoffs: it reveals information (a competitor can estimate your total signups from the ID in a public URL), it doesn't work well if you need to generate IDs before inserting into the database (e.g. offline-first apps), and merging data from multiple databases means ID collisions — two different databases will both have a row with ID 1.

UUID: random, unique, decentralized

A UUID is a 128-bit value, effectively unique across any system, generated without needing to ask a central database "what's the next number?" That decentralization is the whole point: any client, any server, any offline app can generate a valid ID independently, with no coordination and no realistic chance of collision.

The tradeoffs: UUIDs are larger (16 bytes vs. 4-8 for an integer), which means bigger indexes and slightly slower joins at large scale. A fully random UUID (v4) also doesn't sort by creation order, which can hurt database index performance on inserts.

UUID v7 splits the difference

This is why UUID v7 exists: it embeds a timestamp in the first part of the value, so v7 UUIDs sort chronologically just like auto-increment IDs do, while keeping the decentralized-generation and non-guessable properties of a UUID. For new projects that want both benefits, v7 is increasingly the default recommendation over v4.

The practical answer

• Small internal tool, single database, no offline generation needed → auto-increment is simpler and fine
• Public-facing IDs (URLs, API responses) → UUID, so you're not leaking sequential business metrics
• Distributed systems, offline-first apps, or merging data from multiple sources → UUID, ideally v7 for the sorting benefit