UUIDv7 or bigint in PostgreSQL: arrival order matters too

One application can ask the database for a number when it saves a row. Another needs the identifier earlier, perhaps across several services or on a device that will work offline. Those requirements lead to different choices before performance enters the discussion.

I would start with where the ID is assigned. A bigint identity column is straightforward when one database handles allocation. A UUID also allows identifiers to be generated elsewhere, with a very low collision probability. Then comes the physical question: what does it cost to maintain them in an index?

In this article’s lab, the UUIDv7 index for 500,000 rows occupied about 15 MiB, compared with almost 19 for UUIDv4 and less than 11 for bigint. The more useful result came from changing only the arrival order of the same UUIDv7 values: the index grew back to nearly 19 MiB. That makes the data flow worth examining before changing a column’s type.

The data type and the generator are separate choices

A primary key identifies a row and rejects duplicate or null keys. PostgreSQL also creates the index used to find that key. This comparison uses a B-tree.

bigint and uuid are data types; identity, UUIDv4 and UUIDv7 describe how values are assigned.

A UUID’s usual text representation has 36 characters, but the native PostgreSQL type stores 128 bits. A text column would introduce a different comparison that we do not need here.

PostgreSQL 18 provides uuidv4() and uuidv7() without installing an extension. The latter uses a timestamp, a submillisecond component and randomness. The same uuid column can store either version. UUID functions

The lab’s bigint column uses GENERATED BY DEFAULT AS IDENTITY. The database can assign a number, while the test can also supply an ID prepared earlier. The primary key remains in place in both cases. Identity columns

What changes inside the B-tree

A B-tree keeps keys ordered in pages. When new values increase, inserts concentrate towards the right side of the index. Random keys spread that work across different pages; when a page runs out of room, PostgreSQL splits it.

How full those pages remain also matters. The default B-tree fillfactor is 90: it targets 90% occupancy of leaf pages, which hold the index entries. PostgreSQL uses it during an initial index build and when extending the right side. The density left after many inserts depends on the workload. CREATE INDEX

UUIDv7 helps when its time ordering resembles the order in which values reach the database. Consider an import that mixes events collected over several days, or devices syncing late: the time prefix remains, but the insertion sequence changes.

Locality is already described in RFC 9562 and examined at page level by Josef Machytka. My aim here is to connect it to a practical choice and make the comparison easy to repeat.

The experiment: the same rows, different keys

The workload is a bulk insert from a single PostgreSQL 18.6 session in Docker. Each table contains its key, a sequential seq number and the same 128-character ASCII payload. The primary-key index exists before loading begins.

The two scales are 100,000 and 500,000 rows. At each scale the test follows two paths: IDs prepared before measurement, and IDs generated by the column default during insertion. Each combination runs three times on a new table; a recorded seed shuffles the order of the 36 cases.

The test environment is an Apple M2 Mac with 16 GiB of RAM, Docker Engine 29.9.2 and the official PostgreSQL 18.6 ARM64 image. The container has two CPUs, 2 GiB of memory and shared_buffers=128MB. Data lives on an ext4 volume inside the Docker Desktop VM. fsync, full_page_writes and synchronous_commit are enabled.

The reported time covers the INSERT SELECT statement inside the server, including its input scan. It excludes the subsequent commit flush and the client-server round trip. Each case starts with a checkpoint and ends with VACUUM ANALYZE. The README and raw results retain the full method.

These tables are small relative to the available memory, and every index fits within the 128 MiB shared buffer pool. The experiment describes sequential loads and warm lookups. Concurrent writers, joins, foreign keys and cold disk reads need a different workload.

Results with IDs prepared in advance

The median is the middle of three runs; the range shows the shortest and longest. One MiB is 1,048,576 bytes.

At 100,000 rows, the ranges overlap. Even at 500,000, bigint and UUIDv7 do not show a convincing separation in the timings of this small experiment. UUIDv4 takes longer in all three runs at that scale.

Space shows a more stable difference. At 500,000 rows, the v7 index is about 20% smaller than v4 despite using the same 16-byte data type. Its leaf pages are 89.98% full, compared with 72.05%. Bigint retains a further space advantage because its key is narrower.

WAL changes too. This is the log PostgreSQL uses to reconstruct changes. During the loading statement, the median amount is about 131.1 MiB for bigint, 146.1 for v4 and 139.3 for v7. The lab measures the difference between logical WAL insertion positions on a dedicated instance, rather than all disk traffic for the transaction. Measurement functions

Including ID generation changes the 500,000-row medians to 550.9 ms, 1,061.1 ms and 833.8 ms. The v7 runs range from 805.7 to 1,157.2 ms. Keeping the two paths separate makes the changed workload visible; subtracting their times would be a poor way to claim an isolated generator cost.

The read results are less interesting. After a warmup pass, 2,000 key lookups take median times of 4.33 ms, 4.43 ms and 4.45 ms. They run in a server-side loop, without network calls. The ranges are 4.227–4.371 ms for bigint, 4.407–4.485 for v4 and 4.425–5.757 for v7: only the two UUID ranges overlap. With three runs, such small absolute differences and this entirely warm workload, I would not choose a key for faster reads from these results.

The same UUIDv7 values, delivered out of order

The second test takes 500,000 pregenerated UUIDv7 values and changes only their arrival order. One path follows the original sequence; the other uses a deterministic permutation. The values and payloads are identical.

Each order produces the same result across its three runs. With shuffled inserts, the index occupies almost as much space as UUIDv4’s. Shuffling the same UUIDv7 values removed the locality between consecutive inserts.

This is the check I would bring to a migration discussion. First I would examine whether the application generates and immediately saves IDs, or accumulates, retries and imports events out of order. The column format alone does not describe that behaviour.

Repeat the lab in Docker

You need a running Docker installation, Python 3 and a POSIX shell. Use an ARM64 host to reproduce the measured environment; runs on another architecture belong to a different environment. PostgreSQL does not need to be installed on the host.

Download and extract the lab. It contains README.md, run_benchmark.py, run_order_control.py and a hash manifest. Run all commands below from the extracted folder.

postgres-uuid-keys-lab.zip ↓

Start the isolated instance. The name is dedicated to this experiment: if it already exists, check its owner before taking action.

docker run -d --name blog-pg-uuid-20261025 \
  --label blog.editorial.task=pg-uuid-20261025 \
  --network none --cpus=2 --memory=2g --memory-swap=2g --shm-size=256m \
  -e POSTGRES_PASSWORD=local-benchmark-only -e POSTGRES_DB=uuid_lab \
  postgres@sha256:74935e72241653ca55e0414067e6d8763aceb8a810eb51b452253ec3dcfc4336 \
  -c shared_buffers=128MB -c max_wal_size=1GB -c checkpoint_timeout=15min \
  -c jit=off -c max_parallel_workers_per_gather=0 -c track_io_timing=on

docker exec blog-pg-uuid-20261025 pg_isready -U postgres -d uuid_lab

Wait for accepting connections, repeating the second command if needed. The container publishes no ports and uses an anonymous volume. The displayed password is only for this local, network-isolated instance.

python3 run_benchmark.py
python3 run_order_control.py

The first script should finish with Verified 36 cases. It checks row counts and the length of every retrieved payload, and records an actual lookup plan for each case. The primary key enforces uniqueness during loading. The second script produces six results and verifies that both orders contain the same IDs.

The scripts generate the SQL files and outputs; you do not need to fill them in. raw.ndjson contains the environment, settings and 36 results; order-control.ndjson contains the arrival-order control. The README also gives the command for calculating medians and ranges from your data. These are the 36 observations behind the results above:

postgres-uuid-keys-results.csv ↓

After keeping the results you need, remove the lab container and its volume:

docker rm -f -v blog-pg-uuid-20261025

The image remains available. Keeping an image already used by other projects avoids unnecessary removal and downloads.

The choice I would make before a migration

With one database assigning identifiers and no need to generate them before saving, I would start with bigint identity. The lab shows why it remains a useful baseline: a narrow key and a compact index.

When IDs must be assigned in several places, I would consider UUIDv7 and examine their actual arrival order. I would keep UUIDv4 when its randomness and lack of a time prefix are useful requirements, and the current workload shows no problem worth changing it for.

Another option is bigint for internal relationships and a separate UUID for the public identifier. That adds a column, a unique index and the mapping between the two identifiers. The cost deserves its own measurement, particularly if saving space motivated the choice.

The timestamp inside UUIDv7 exposes time information, but it does not certify when a transaction committed or when a business event occurred. I would retain an explicit date when that distinction matters. Access checks remain necessary whichever identifier is exposed.

In the EAV and PostgreSQL article, the first problem was returning the right rows. The next question here is physical: what does it cost to maintain and find them? If your system struggles with joins or concurrent writes, put those into the next test. For a key choice, the missing workload matters more than another decimal place in this table.