The primary key type is one of the earliest schema decisions and one of the hardest to change later. It affects storage, index size, insert performance, and what your identifiers reveal to anyone who sees them.
The argument is usually framed as opacity versus performance. That is roughly right and misses which performance, and by how much.
What changed in 2026
- Time-ordered UUIDs became the recommended default where UUIDs are needed, largely removing the insert-fragmentation objection — see UUID v7.
- Distributed generation became more common. More systems needed identifiers created outside a single database.
- Enumeration attacks stayed relevant. Sequential identifiers in public URLs continued to be a routine finding in security reviews.
- The hybrid pattern spread. Internal integer keys with external opaque identifiers became a widely-used compromise.
The costs, concretely
|
Bigint |
Random UUID |
Time-ordered UUID |
| Storage per key |
8 bytes |
16 bytes |
16 bytes |
| Insert position |
Sequential |
Random |
Sequential |
| Page splits |
None |
Frequent |
None |
| Index size |
Smallest |
Larger |
Larger |
| Generatable offline |
No |
Yes |
Yes |
| Reveals row count |
Yes |
No |
No |
| Reveals creation time |
No |
No |
Yes, roughly |
The storage difference is twice per key, and it appears everywhere: the primary key, every foreign key referencing it, and every index containing it. In a clustered table, secondary indexes carry the primary key too, so the multiplier is larger than it first appears — see clustered indexes.
The insert behaviour is the larger effect. Random UUIDs insert at arbitrary positions, splitting pages and fragmenting storage. On a write-heavy table that is a substantial and ongoing cost, not a one-off.
What sequential keys leak
The case against integers is not aesthetic.
Row counts and growth. An identifier of 4,817 in a URL tells anyone your system has around 4,817 of that thing. Comparing two over time gives a growth rate. For a business that is competitive information given away in a URL.
Enumerability. Sequential identifiers let anyone iterate. If authorisation is anywhere less than perfect, that turns a single leaked record into a systematic extraction. Sequential identifiers do not cause the vulnerability; they make exploiting it trivial.
Cross-referencing. Identifiers that increase together suggest records created together, which can reveal relationships you did not intend to expose.
None of this matters for a table nobody outside your system ever sees. All of it matters for anything in a URL, an API response, or a document sent to a customer.
The hybrid that resolves it
The pattern most teams land on: a sequential integer as the internal primary key, and a separate opaque identifier for external use.
Joins, foreign keys, and indexes all use the compact integer, so you keep the storage and insert-performance benefits internally. Anything leaving your system uses the opaque identifier, indexed separately.
The cost is one extra column and one extra unique index. In exchange you get compact internal keys and no information leakage externally, which is strictly better than either pure approach for anything with an external surface.
Where you genuinely need identifiers generated outside the database — multiple services creating records independently, offline clients, or merging data from several sources — a time-ordered UUID as the primary key is the right choice, and it removes the fragmentation problem that made random UUIDs painful.
Common mistakes
- Random UUIDs as a clustered primary key. Fragmentation with no benefit over time-ordered.
- Sequential integers in public URLs. Leaks counts and enables enumeration.
- UUIDs on internal-only tables. Overhead for nothing.
- Storing UUIDs as text. Roughly double the size again; use the native type.
- Assuming UUID uniqueness removes the need for a unique constraint. Bugs generate duplicates.
- Changing key type later. Extremely expensive on a large table with foreign keys.
FAQ
Which should I default to?
Sequential integers for internal keys, with a separate opaque identifier where records are externally visible. Time-ordered UUIDs where identifiers must be generated outside the database.
How much does the storage difference really matter?
On small tables, negligible. On large tables with several indexes and many foreign key references, it compounds into a real fraction of total storage and cache — measure rather than assume.
Do time-ordered UUIDs leak anything?
The approximate creation time, by design. Usually acceptable and worth knowing if creation timing is sensitive.
Can I migrate later?
Technically yes, and it means rewriting the table and every referencing foreign key. Treat it as a design-time decision.
Where to go next
For the time-ordered variant, read UUID v7. For why insert order matters so much, clustered indexes, and for what happens when integer keys run out, sequence exhaustion.