Why a random ID makes a poor database key

A version 4 UUID is 122 bits of randomness with six bits reserved to say what it is. Two of them colliding is not a practical concern, which is why it became the default identifier for almost everything.

It has one real weakness, and it is not collisions. Random identifiers arrive in no order, so each insert lands at an arbitrary point in an index rather than at the end — and an index written in random order fragments in a way a sequential one does not.

What are the alternatives?

Several, and they mostly differ in whether the first part of the value is a timestamp.

The sortable ones put a millisecond timestamp in the leading bits and fill the rest with randomness. Values generated later sort after values generated earlier, so inserts land at the end of the index and the fragmentation problem disappears.

They also give you a rough creation time for free, readable from the identifier itself, which is occasionally useful and occasionally a disclosure you did not intend.

What was wrong with the older versions?

The first version encoded a timestamp and the machine’s hardware address, which meant an identifier told you which machine produced it and when. That is a privacy property nobody asked for.

The random version was the reaction, and it went to the opposite extreme by discarding the ordering that made the first one useful for storage. The timestamp-prefixed version is the reconciliation — ordering without the hardware address.

All of them share the same textual shape, so a system accepting one accepts the others without changes.

Why is a shorter identifier attractive?

Because identifiers end up in URLs, in logs and on screens. A 36-character value in a path is unwieldy, and a 21-character one carrying comparable randomness is easier to handle everywhere it appears.

The alphabet matters as much as the length. An identifier built from URL-safe characters needs no encoding when it goes into a link, and one built from an alphabet that omits easily confused characters is safer to read aloud or type from a screen.

The trade is recognisability. A UUID is instantly identifiable as one; a short random string is not, and telling two kinds of identifier apart in a log becomes harder.

How does a card number check itself?

With the same kind of checksum a barcode uses, over a different weighting. Working from the right, every second digit is doubled, any result above nine has nine subtracted, and the whole lot is summed — a valid number gives a total divisible by ten.

It catches every single-digit error and most transpositions of adjacent digits, which are the two mistakes people make when copying a number by hand. It is not security: it says a number is well formed, not that an account exists.

That is exactly why test numbers work. A number constructed to pass the checksum will get through a form’s validation and be declined by any real payment system, which is what makes it useful for checking that a checkout form behaves correctly.

Which should you choose?

A time-ordered identifier for anything stored in a database, and a short random one for anything that appears in a URL a person might type or share.

A sequential integer remains the best choice when the identifier never leaves your own system. It is small, it is fast, and it orders naturally — its only failure is that it leaks how many records exist and lets someone walk the range.

The pattern that avoids choosing is to have both: an internal sequential key and an external opaque identifier. It costs a column and settles the argument permanently.

Where should an identifier be generated?

Wherever it is most convenient, which is the point of these formats. All of them can be produced without coordination, so a client, a server and a background job can each mint identifiers without asking anything else whether a value is taken.

That property is what makes them worth the extra bytes over a sequential number. A database sequence needs a round trip and a single authority; a random or time-ordered identifier needs neither, which is what allows a record to be created offline and reconciled later.

It also means an identifier can be assigned before a record is saved, so the thing being created has a name from the moment it exists rather than only after it lands.

Questions people ask

Could two UUIDs collide? In theory. The probability is small enough to be ignored in any real system.

Are these generated on my machine? Yes. Identifier generation is arithmetic on a random source and needs no server.

Is a UUID a secret? A random one is unguessable, which is not the same as being a credential. Do not use one as a password.

Does a card number identify a bank? The leading digits identify the issuer. That is public information and not sensitive on its own.

Pick the shape that matches where it will live. The UUID generator and validator cover the standard form, the ULID generator produces the sortable short one, the NanoID generator the compact URL-safe one, and the credit card generator and validator handle checksum-valid test numbers for payment forms.