Lorixen.
EN

UUIDs or auto-increment IDs for your app's records?

Short answer: Auto-increment integers are small, fast and easy to read, but they reveal how many records you have and are guessable in URLs. Random UUIDs (version 4) are unguessable and can be created anywhere, but their randomness spreads inserts across an index, which can hurt write performance on big tables. UUIDv7, standardised in RFC 9562, puts a timestamp first so new IDs sort roughly in creation order. For most new business apps, UUIDv7 or integers with a separate public ID are the sensible choices.

What the choice affects

A primary key looks like a technical detail, but it shows up everywhere: in URLs, API responses, emails, exports, logs and every table that references another. Changing it later means touching every one of those, so it's worth a few minutes' thought at the start.

The main trade-offs:

  • Exposure. Can someone learn something from the ID, or guess other valid ones?
  • Where IDs are created. Only in the database, or also in apps, offline devices and other services?
  • Performance. Size on disk and in indexes, and how inserts behave as tables grow.
  • Usability. Can support staff read an ID over the phone?

Auto-increment integers

The classic choice: the database hands out 1, 2, 3 and so on, using a sequence or identity column.

Strengths:

  • Compact: a bigint is 8 bytes, half the size of a UUID.
  • New rows always go at the end of the index, which keeps inserts efficient.
  • Easy for people to read and say.

Weaknesses:

  • They leak information. If a customer's order is /orders/1043 today and /orders/1187 next week, they can estimate your order volume.
  • They're guessable. If an endpoint forgets an authorisation check, an attacker can step through IDs and read other customers' records. Authorisation checks are the real fix, but guessable IDs make mistakes easier to exploit.
  • Only the database can create them. Merging data from two systems, or creating records offline in a mobile app, needs extra work.

Use bigint rather than a 4-byte integer for anything that might grow. Running out of IDs is a painful migration.

UUIDs: what they are

A UUID is a 128-bit identifier, usually shown as 36 characters such as 0192f3a1-7c4e-7d2a-9b1f-3e5c8a6d4f21. RFC 9562, published in 2024, is the current specification and defines several versions. Two matter for app databases:

  • Version 4 is random apart from a few fixed bits.
  • Version 7 starts with a Unix timestamp in milliseconds followed by random bits. The RFC describes it as derived from the widely used Unix epoch time source and notes its improved characteristics compared with older time-based versions.

Both can be created by your app, a mobile device or another service, without asking the database, and collisions are not a practical concern.

Why random UUIDs can slow inserts

Database indexes, typically B-trees, are ordered. With auto-increment IDs, each new row lands at the end. With random UUIDv4 values, each new row lands somewhere random in the index. On large, write-heavy tables this means more pages touched, more cache pressure and larger indexes over time.

The RFC's database considerations discuss this kind of locality issue, which is one reason version 7 exists: because its leading bits are a timestamp, new IDs sort roughly in creation order, so inserts behave much more like auto-increment. For small and medium tables, any difference may be negligible. Measure on your own data if performance matters.

UUIDv7 has a small catch

A UUIDv7 contains its creation time. Anyone holding the ID can work out roughly when the record was made. For most records, such as orders, invoices and bookings, that's harmless, since the date is usually visible anyway. If creation time is sensitive, use UUIDv4 for that identifier.

A pattern that suits many apps

You don't have to choose one ID for every purpose:

Approach How it works Good for
UUIDv7 everywhere One ID, used internally and publicly New apps, APIs, offline creation
Integer key plus public ID Internal bigint key; a separate random or UUID column used in URLs and APIs Existing integer-based apps
Integer key plus human reference Internal key, plus a short readable reference like ORD-7K3Q9 Order numbers customers quote

The second pattern is a practical way to stop exposing sequential IDs without migrating every foreign key. Add a unique public ID column, use it in URLs and API responses, and keep integers for joins.

Whatever you pick, store UUIDs in a native uuid type where your database has one, not as text, which takes more than twice the space.

Human-friendly references

Neither integers nor UUIDs make good references for a customer to read out on the phone. If staff and customers need to quote an order or booking number, generate a separate short reference that avoids confusable characters like O and 0, and keep it unique with a constraint. That reference is for people; the primary key is for the database.

A worked example

Imagine a hypothetical Phoenix home-services app whose booking links looked like /bookings/2291. A customer noticed they could change the number and see another customer's booking page because one endpoint lacked an authorisation check. The team fixed the check first, then added a UUIDv7 public_id column used in all links and API responses, keeping integer keys internally. They also introduced short booking references for phone calls.

FAQ

Can I generate UUIDv7 in PostgreSQL?

Recent versions add a built-in function, and libraries exist for most languages. Check your database version's documentation.

Are UUIDs secret tokens?

No. They're hard to guess, but treat them as identifiers, not passwords. Use proper authorisation and signed tokens for access.

Should I convert an existing app to UUIDs?

Usually not wholesale. Adding a public ID column is far less risky than changing every primary and foreign key.

Do UUIDs make URLs ugly?

They're longer. For customer-facing pages, a slug or short reference can sit alongside the ID.

If you're starting a new app or worried about guessable IDs, tell us how your records are identified today.

Sources

WhatsAppBook a call