bigint, UUID v4, or UUID v7? How to Choose a Primary Key (with PostgreSQL 18's uuidv7())

One primary key spreads across your whole schema

A primary key is never just one table's concern. Every foreign key column that references it takes the same type, and the value travels through indexes, joins, API URLs, and logs. That makes the primary key type one of the hardest design decisions to change later.

In practice, most teams choose among three options:

• bigint — an integer the database assigns in order: 1, 2, 3…
• UUID v4 — an identifier with 122 of its 128 bits random
• UUID v7 — a UUID that starts with its creation time (in milliseconds), so it sorts by time

PostgreSQL 18, released on September 25, 2025, added a built-in uuidv7() function, so the third option now works without any extension. Here are the trade-offs and how to choose.

bigint: small and fast, but the database hands out the numbers

In PostgreSQL, declare it with GENERATED ALWAYS AS IDENTITY. This follows the SQL standard more closely than the older serial.

CREATE TABLE customers (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE
);

Pros

• At 8 bytes it is half the size of a UUID (16 bytes). The primary key index and every foreign key index that references it get smaller.
• New values always land at the right end of the index, so writes are efficient.
• Easy for people to read and say ("order #1042").

Cons

• The database assigns the number, so you don't know the ID until the row is saved. That gets awkward for offline apps or for several services that create records independently and merge them later.
• Exposed in URLs, the next number is easy to guess, and your total count and growth rate become visible.
• Merging data from several databases causes number collisions.

UUID v4: generate anywhere, but scattered across the index

CREATE TABLE customers (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),  -- same function as uuidv4() in PostgreSQL 18
  email text NOT NULL UNIQUE
);

Pros

• Can be created up front anywhere — application, client, or another service — and collisions are practically impossible.
• Unguessable, so exposing it in a URL doesn't hint at other records.

Cons

• Because the values are random, new rows land at arbitrary positions in the B-tree index. On write-heavy tables, index pages split often and recently written data doesn't stay together in the memory cache, which increases disk I/O.
• At 16 bytes, its indexes are larger than bigint's. The more foreign keys you have, the bigger the gap.

In engines that store the table itself in primary key order (a clustered index), such as MySQL's InnoDB, the cost of random inserts affects the whole table.

UUID v7: a UUID that sorts by time

UUID v7 is defined in RFC 9562, published in May 2024. The first 48 of its 128 bits hold the Unix time in milliseconds, and the rest — apart from the version and variant bits — is random. RFC 9562 says implementations SHOULD use UUIDv7 instead of UUIDv1 and UUIDv6 where possible.

For example, in 019535d9-3df7-79fb-b466-fa907fa17f9e, the first 12 hex digits (019535d9-3df7) are the creation time, and the following 7 is the version.

Because the time comes first, later values are generally larger. New rows therefore gather at the right end of the index, like bigint, and the random-insert problem of UUID v4 largely goes away. You keep the UUID benefits: it can be generated anywhere and its random part makes it hard to guess.

Watch out for

• The ID reveals when it was created. RFC 9562's security considerations note that embedded timestamps pose a very small attack surface. If the creation time is sensitive — for example, when a user signed up — use a separate ID for anything you show externally.
• It is 16 bytes, the same as v4.

Using it in PostgreSQL 18

From PostgreSQL 18 on, it works out of the box.

CREATE TABLE orders (
  id uuid PRIMARY KEY DEFAULT uuidv7(),
  customer_id uuid NOT NULL REFERENCES customers(id),
  total_amount numeric(12, 2) NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- Check the creation time and version stored in the ID
SELECT id,
       uuid_extract_timestamp(id) AS id_created_at,
       uuid_extract_version(id)   AS version
FROM orders
ORDER BY id DESC
LIMIT 10;

The UUID functions in PostgreSQL 18:

Don't drop your created_at column just because uuid_extract_timestamp() exists. IDs can be generated ahead of time in the application or with a shifted time, so they don't always match the creation time your business logic relies on.

In MySQL

According to the MySQL 8.4 documentation, UUID() generates a version 1 UUID, and there is no built-in function for UUID v7 (a request for native support is filed in the MySQL bug tracker). There are two practical approaches:

• Generate UUID v7 in the application and store it in a BINARY(16) column
• If you use UUID(), store it with UUID_TO_BIN(UUID(), 1) — the second argument 1 swaps the time parts so values are inserted closer to sequential order

Either way, storing it as a string (CHAR(36)) more than doubles the size, so binary is the better choice.

Choosing by situation

If you already run on UUID v4, using v7 for new tables is often enough. Changing an existing key means changing every foreign key that references it, so weigh the benefit against the cost first.

What to check when drawing the ERD

• A foreign key's type must match the primary key it references. Mixing uuid with text, or bigint with integer, forces a type conversion on every join or prevents you from creating the constraint at all.
• Rather than deciding per table, keep one primary key rule for the whole schema to avoid mistakes. Put exceptions (such as public tokens) in separate columns.
• Besides the primary key index, index the foreign keys you query often. With 16-byte keys, factor in index size as well.

Decide the key type first and then draw the relationships, and you will catch foreign key type mismatches early. For more, see Mastering Foreign Keys and Table Relationships and the Database Indexing Guide.