Use a 64-bit identity column unless you have a concrete reason not to. If ids must be generated outside the database, be unguessable, or merge across systems, use a time-ordered UUID (version 7) or a ULID stored in a native 16-byte column, not a random UUID version 4 and not a 36-character string. This article explains the trade-offs behind that advice and how each engine spells it.
What a primary key has to do#
A primary key identifies a row, is referenced by foreign keys, and, in engines that cluster on it, decides the physical order of the table. InnoDB in MySQL and MariaDB always clusters on the primary key; SQL Server does by default; PostgreSQL and Oracle store rows in heaps but still keep a B-tree index on the key. Every choice below is a trade between size, locality in that index, where the value can be generated, and what the value reveals.
Integers: serial, identity and auto-increment#
An integer key is 4 or 8 bytes, sorts by insertion order so new rows land at the end of the index, and is the cheapest thing to join on. The way you ask for it differs per engine:
-- PostgreSQL (preferred since version 10)
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
-- PostgreSQL (older style, still common)
id BIGSERIAL PRIMARY KEY
-- MySQL / MariaDB
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY
-- SQL Server
id BIGINT IDENTITY(1,1) PRIMARY KEY
-- Oracle (12c and later)
id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY
Prefer identity columns over SERIAL in PostgreSQL. SERIAL is shorthand for a separately owned sequence plus a default, which means separate permissions, surprises when copying tables, and nothing stopping an explicit insert into the column. Identity columns are SQL standard, behave the same in Oracle and SQL Server, and GENERATED ALWAYS rejects accidental explicit values.
Use BIGINT from the start. A 32-bit key runs out at about 2.1 billion rows, and tables that reach that number always do so on a Friday. The extra four bytes per row and per foreign key are not measurable in most systems.
Integer keys reveal information. Sequential order ids expose how many orders you take per day, and a URL with /invoices/1042 invites someone to try /invoices/1041. Authorization has to be enforced regardless, but if the number itself is sensitive, an opaque public identifier next to the integer key is the usual fix.
Random UUIDs: the cost of version 4#
A UUID is 128 bits, twice a BIGINT, and a random one (version 4) has no order at all. Each insert lands at a random position in the primary key index, so pages are split and rewritten constantly, the working set of index pages is the whole index rather than its tail, and on clustered engines the table itself is fragmented. On write-heavy tables the difference against an integer key is visible in write amplification and cache hit rates, not just in theory.
What version 4 buys you is generation anywhere: the client, a mobile app, a service with no database connection yet, a batch that merges rows from two databases without renumbering. Those are real needs. The mistake is paying for them with random ordering when ordered alternatives exist.
Time-ordered ids: UUID version 7 and ULID#
UUID version 7, standardized in RFC 9562, puts a millisecond timestamp in the high bits and random bits below it. Consecutive ids sort by creation time, new rows go to the end of the index, and the value is still 128 bits of standard UUID that every driver, ORM and engine already understands. PostgreSQL 18 ships a built-in uuidv7() function; earlier versions and other engines generate it in the application or with an extension. ULID is the same idea from a few years earlier, encoded as a 26-character Crockford base32 string; it is popular in Node, Go and PHP codebases and can be stored in the same 16 bytes.
Two caveats. The timestamp leaks creation time, which is rarely a problem but is worth knowing. And ordering is only as good as the clocks that generate the ids; two servers a second apart produce ids that interleave, which is harmless for locality but means you should not use the id as an authoritative created-at.
Store UUIDs natively#
How the value is stored matters as much as which version it is. A UUID kept as CHAR(36) costs 36 bytes plus collation-aware comparison in every index and every foreign key; as a native 16-byte value it costs 16 bytes and compares as binary.
| Engine | Native column | Notes |
|---|---|---|
| PostgreSQL | uuid | gen_random_uuid() for version 4, uuidv7() from version 18 |
| MySQL 8 | BINARY(16) | UUID_TO_BIN(uuid, 1) reorders a version 1 value for locality; store version 7 as is |
| MariaDB | UUID type (10.7 and later) | stored in 16 bytes with a locality-friendly internal order |
| SQL Server | UNIQUEIDENTIFIER | NEWSEQUENTIALID() is ordered but only usable as a column default |
| Oracle | RAW(16) | SYS_GUID() is not time-ordered on every platform |
| SQLite | BLOB | generate in the application |
Natural keys#
A natural key is a value that already identifies the thing: an ISO country code, a currency code, a chemical symbol. Where the value is short, stable and defined by an external standard, it makes a fine primary key and saves a join. Where it merely looks stable, such as an email address, a national id number or a product SKU, it does not; all three change in practice and the change then ripples through every foreign key. The safe rule is that a natural key must be owned by a standards body, not by your users or your marketing department.
Composite keys#
Join tables are the one place where a composite key is the natural choice, because the pair is the identity and the uniqueness constraint comes free. Everywhere else a composite key means every foreign key carries every column, every join lists them all, and every ORM fights you. Designing join tables goes through the exceptions.
Recommendations#
| Situation | Use |
|---|---|
| Ordinary tables in one database | BIGINT identity column |
| Ids created before the row reaches the database, or in several systems | UUID version 7 or ULID, stored natively |
| Ids exposed in URLs that must not be enumerable | Integer key internally plus a random public id, or UUID version 7 if the timestamp is acceptable |
| Join tables | Composite key on the pair |
| Reference data defined by a standard | The standard's code as the key |
| Anything on a clustered engine with heavy inserts | Never random UUID version 4 |
Where SchemaDrawer fits#
The key type is one of the first things a diagram shows: every primary key is drawn in bold with its column type, so int4, int8, uuid and varchar keys are visible at a glance and a table that quietly kept a 32-bit key while its neighbors moved to BIGINT stands out. SchemaDrawer produces that diagram by running your migration files in Docker against the engine and version you choose and reading the catalog back over JDBC, so the types are the ones the engine really assigned, not the ones the migration asked for. The databases page lists the engines and versions it draws.
Key takeaways#
- Default:
BIGINT GENERATED ALWAYS AS IDENTITY, or the engine's equivalent. - Need client-side or cross-system generation: UUID version 7 or ULID, never random version 4 on a busy table.
- Store UUIDs in 16 bytes, not as text.
- Natural keys only when a standards body owns them.
- Composite keys for join tables, surrogate keys everywhere else.