Use a bigint when one database issues every id, and UUIDv7 when ids are minted somewhere the database cannot see - offline clients, several writers, rows merged in from elsewhere. Random UUIDv4 as a primary key is the option to reject in both cases, and PostgreSQL 18 is the release that made rejecting it easy.

What makes this decision worth getting right early is that it does not stay in one column. A primary key type is copied into every foreign key that references the table, and into every index over those columns, so by the time anyone measures the cost the choice has been made a hundred times by a generator nobody was watching. It is the same shape as an ORM quietly deciding how a status column is constrained: the physical representation gets picked at rails new or prisma init, no review ever sees a decision, and the schema ends up expressing an opinion nobody formed.

Should I use a UUID or a bigint as the primary key in PostgreSQL?

The clearest framing of this comes from Christophe Pettus on the pgsql-general list, in a thread called Sequence vs UUID on 26 January 2023, and it is worth quoting because most arguments about primary keys are two arguments wearing one coat:

First, the distinction isn’t exactly UUIDs vs sequences. There are two distinctions: 1. UUIDs vs bigints. 2. Sequential values vs random values.

The first distinction is about width. A bigint is a 64-bit value and a uuid is a 128-bit one, so as the same message puts it, bigints are “half the size of a UUID (so, smaller tables, smaller indexes, etc.)”. The second is about where new values land in a B-tree. Sequential values append at the right edge of the index and keep a small working set hot; random ones are spread evenly across the whole key space, so every insert dirties a different page, and “random values will have a harder time maintaining a reasonable in-memory working set”.

Confusing the two is what produces the bad arguments in both directions. “UUIDs are slow” is a claim about randomness, not about the type, and it stopped being automatically true. “Bigints leak your row count” is a claim about sequential values, not about the type, and it applies to any key that counts.

What did PostgreSQL 18’s uuidv7() change?

It removed the write-locality objection from the core of the database. PostgreSQL 18 was released on 25 September 2025 and “adds UUIDv7 generation through the uuidv7() function, letting you generate random UUIDs that are timestamp-ordered to support better caching strategies.”

The documentation for the function is more precise about how the ordering is achieved: the timestamp is “UNIX timestamp with millisecond precision + sub-millisecond timestamp + random”, so two rows written in the same millisecond still sort in the order they were written rather than falling back to chance. The 48-bit timestamp field covers 1970 to approximately the year 10889.

The measured difference is real and modest. Aiven’s walkthrough of the new support inserted 10,000 rows both ways: 89.084 ms with UUIDv4 against 64.834 ms with UUIDv7, with shared buffer hits falling from 40,220 to 20,415 and dirtied buffers from 397 to 112. Sorting 60,000 rows by creation order took 52.249 ms with a UUIDv4 key plus a timestamp column, against 16.597 ms reading the UUIDv7 primary key index directly, because the key is already in that order.

Two things it did not change. The value is still 128 bits, so every size argument survives intact. And the timestamp is not hidden: uuid_extract_timestamp() reads it back out of any v7 value, which the same Aiven post frames as a reason to keep these keys internal, since “the identifier itself leaks the record’s creation time.” The docs add the honest caveat that the extracted timestamp “is not necessarily exactly equal to the time the UUID was generated”, because it depends on whatever produced the value.

Why the width matters more than one column suggests

The reason a 16-byte key is not simply “8 bytes more per row” is that a primary key is the one column in a schema guaranteed to be repeated somewhere else. It appears in its own index, in every foreign key column pointing at the table, and in every index over those foreign keys. So the multiplier is the number of references, not the number of tables, and the tables with the most references are usually the ones you cannot avoid joining.

Junction tables are where this compounds fastest, because their primary key is usually the pair of foreign keys itself. Two bigint parents give a 16-byte composite key; two UUID parents give a 32-byte one, in a table whose entire purpose is to hold many rows and be joined from both sides.

None of that makes UUID keys wrong. It makes them a purchase. What you buy is the ability to generate an id without asking the database - which is what actually decides the question:

• A single PostgreSQL instance issues every id, and the application waits for it. bigint with an identity column. There is no problem to solve.
• Ids are created before the row reaches the database. Offline-capable clients, mobile apps writing into a local store, an ingest pipeline that must be idempotent on retry, rows merged from separate systems. UUIDv7. Round-tripping to a sequence is either impossible or the thing you were trying to avoid.
• Rows move between environments or tenants and must not collide. UUIDv7, for the same reason.

Should the primary key be the id that appears in URLs?

Often not, and treating these as one column is what pushes teams into UUID keys they did not otherwise need. The objection to bigint is rarely about storage; it is that /invoices/1042 tells a stranger there are about a thousand invoices, and that /invoices/1043 exists.

That is a claim about the identifier you publish, which does not have to be the identifier you join on. A bigint primary key with a separate uuid public column - unique, not null, generated by uuidv4() because this one should reveal nothing - keeps joins narrow and keeps the enumerable number private. The cost is one extra column and one extra unique index on each table that has a public surface, which is usually a handful rather than all of them.

The part worth drawing carefully is that unique index. A public id column is exactly the case where a unique constraint over a nullable column silently stops enforcing anything: rows with no public id are all exempt from the constraint, so a bug that fails to populate it produces duplicates the schema promised were impossible. If every row must have one, the column is NOT NULL and the constraint means what it appears to mean.

Finding the key types that already disagree

The decision above is easy to accept and does not help with the schema you have. The defect that actually shows up in a production database is not the wrong choice - it is two choices, made in different quarters, that no longer line up: a users.id of type uuid referenced by an orders.user_id of type varchar(36), or an int foreign key pointing at a bigint primary key on a table that outgrew its original key type.

A migration linter cannot report either one, and this is structural rather than an oversight. Squawk and its peers read the statements in the file in front of them, and both defects are facts about two objects at once, which is what makes schema linting and migration linting different jobs. The ALTER TABLE that added user_id varchar(36) was correct in isolation and merged two years ago.

Schemity checks the model instead. fk-type-mismatch is one of the seventeen rules in schema lint, and it reports exactly this pair - int to bigint, varchar(36) to uuid - because the join then cannot use the index on the other side, and MySQL rejects the constraint outright rather than performing badly. It runs offline against the open diagram, so a schema reverse engineered from the live database is checked in full, every reference at once, and the finding lands as a colored strip in the margin beside the exact entity and the exact field row rather than in a list you have to translate back into the picture.

For the wider audit - how far did this key type actually spread - search answers faster than the lint does, because it is not a defect you are looking for but a population. Search on the canvas matches a field on its type as well as its name, using the text the canvas shows, and the [f prefix scopes a query to fields before any matching happens. So [f uuid lists every UUID column in the diagram, [f varchar(36) lists the ones pretending to be UUIDs, and [f bigint lists what is left. Every field also carries a type icon, with a dedicated glyph for UUID, so once you know the population you can see it on the canvas without reading a single type name.

When a column does need converting, the change goes through the ordinary path: edit the type in the ERD, read the generated migration SQL diff, then apply it. Reading it matters more here than for most changes, because converting a key type touches the referencing columns too, and the order in which constraints are dropped and recreated is the difference between a short lock and a long one.

Choosing once, for the tables that do not exist yet

The reason this decision gets made by a generator is that nobody is asked. The first table sets a precedent, the next forty copy it, and the only artifact recording the choice is the shape of the columns themselves.

The cheap fix is to make the precedent explicit. An entity template defines the fields every new entity starts with, so the key type, the public id column if you use one, and the timestamp columns arrive already correct instead of being retyped and occasionally mistyped - the same reason timestamptz belongs in the template rather than in a convention document. New tables then match by construction, and the lint rule is left to deal with the history rather than the present.

The wider point is that a key type is not a property of a table. It is a property of every path into that table, which is why it reads as a small decision in a CREATE TABLE statement and a large one in a diagram, where every line inheriting it is drawn. That is also true of the other choices that look local and are not: an array column that hides a relationship the schema cannot enforce, or a cycle of foreign keys whose first row cannot be inserted. The model is where they become visible, and that is the argument for keeping one.