What Is the Best Primary Key?

One of the first things I think about when designing a table is how to choose its primary key.

An incrementing number seems like a good starting point. The database handles the numbering, and a small, increasing key can work well with a B-tree index. So why would we choose something else?

For users or groups, we often use that ID in the UI too. Navigating to a page may reveal it in the URL:

something.com/users/1234
something.com/users?userId=1234

From there, someone can try nearby IDs. A number like 4567 may also give them a rough clue about the service’s size or growth. It is not a user count, though: deleted rows, failed inserts, and gaps in numbering can make that guess wrong.

The Primary Key and the Public ID

We don’t have to use the same ID inside the database and in the URL. We can keep a numeric primary key for relationships between tables and give each record a separate public ID. Or we can use one ID for both. That is a choice worth making before comparing formats.

A hard-to-guess ID makes enumeration harder, but the server still needs to check whether the current user can access the requested record. Switching from numbers to UUIDs does not replace that permission check. OWASP explains this distinction in its IDOR prevention guide.

Then, What Should We Use…?

UUID stands for Universally Unique Identifier. It is a 128-bit identifier, often displayed as a 36-character string with hyphens.

With UUID v4, we can generate IDs on different computers without asking one central server for the next number. A suitable random generator makes collisions very unlikely, but it does not make them impossible. The same general caution applies to other randomly generated IDs.

In a database, we still use a primary key or unique constraint to reject duplicates. If a generated ID collides, the application needs to handle that failed insert, for example by generating another ID and retrying. The UUID specification discusses collision resistance and uniqueness.

BUT Isn’t It Slow? Like, It’s Random?

UUID v4 has a random order. With a B-tree index, new entries can land in different pages across the index. An increasing key tends to put new entries near one end, which can help with cache locality and reduce page splits. The difference can matter as the data grows.

There is no fixed row count where UUID v4 becomes slow. The database engine, memory, storage, and index design all affect the result. A benchmark showing a slowdown at five million rows does not give us a limit for another application. The UUID specification also discusses database index locality.

For many applications, UUID v4 is fast enough. For a system with heavy writes, I would measure the expected workload before deciding. B2B and internal systems can have large tables too, so the type of business alone does not tell us whether performance will be a problem.

But I’m Developing My Own Facebook and Don’t Know the Scale Yet!

We still need to make a choice before we know everything. These are some options, with their text representation and storage format kept separate:

The storage column describes the value’s representation, not the full cost of a database row or index entry. A UUID stored as text takes more space than its 16-byte binary form. Nano ID is shorter than UUID’s usual text format, but that does not make it smaller than a binary UUID.

For auto-increment IDs, a database sequence can let us obtain a number before inserting a row. That still involves the database. It is different from generating an ID on a client without contacting it.

UUID v7 and ULID include timestamps. They can sort by those timestamps, but don’t guarantee the exact order of events across machines. IDs generated within the same millisecond and differences between clocks need care. An auto-increment value does not guarantee transaction commit order either. Keep an explicit date column when the application needs to record when something happened.

There are a few format details worth keeping in mind:

• UUID v4 and v7 have an RFC specification. PostgreSQL 18 added built-in UUID v7 generation; earlier versions can still store v7 values in the UUID type. Generating a format and storing it are separate capabilities. PostgreSQL UUID documentation
• ULID uses Crockford’s Base32 alphabet, excluding I, L, O, and U. Its specification includes a monotonic option for IDs generated within the same millisecond. ULID specification
• cuid2 and Nano ID let us change the length. Shortening them increases collision risk. Nano ID’s default alphabet uses letters, digits, _, and -; we can configure that too. cuid2, Nano ID

I would compare ID generation speed and database write speed separately. Nano ID can be quick to generate, but it still produces random keys. Choosing it does not remove the random-insertion behavior discussed above.

So What Should I Use?

If database-generated numbers fit the application, auto-increment is a reasonable starting point. If we need to generate IDs across clients or services and want timestamp-based ordering, UUID v7 is worth considering. UUID v4 is another option when we don’t need that ordering. Nano ID is useful when a shorter, URL-friendly text ID matters.

There is a privacy tradeoff with timestamp-based IDs: UUID v7 and ULID expose a generation timestamp. If we want to keep that out of a public URL, a random ID such as UUID v4 or a securely generated Nano ID may fit better.

We can also use an auto-increment primary key and a separate UUID v4 or Nano ID column with a unique constraint. The numeric key serves internal relationships, and the public ID goes in URLs. This adds a column and an index to maintain, but it lets us choose each ID for its job. It is one primary key and one public unique key.

I don’t think there is one best choice for all tables. I would start with where we generate the ID, whether we expose it to users, and what the database needs to handle. Those questions narrow the options more than a general ranking of which ID is fastest.

I used AI to help check the accuracy of the explanations in this article.

These two Japanese tech videos helped me think about the topic. Check them out if you understand the language or can translate!