How to Choose a PostgreSQL 18 Primary Key: Measure UUIDv4, UUIDv7, and bigint, and Switch an Existing Table to uuidv7() Without a Rewrite

Every table you create needs a way to name exactly one row. That name is the primary key, and on PostgreSQL 18 there are three popular ways to make it: a counter (bigint), a random UUID (version 4), or a time-ordered UUID (version 7), which PostgreSQL 18 can now generate for you with the built-in uuidv7() function. All three work. They do not cost the same. On a small table you will never notice the difference, and on a big table it can dominate the write cost of your busiest inserts.

In this tutorial you will measure that cost yourself instead of taking anybody’s word for it. You will start a scratch PostgreSQL 18 server with a deliberately small cache, load two million rows into three tables that differ only in their key, and then ask PostgreSQL how much it wrote to its write-ahead log, how big and how full each index is, and how long a read workload takes. In my run, adding 100,000 rows to the 2-million-row tables wrote 74.5 MB of WAL with random UUIDs, 19.2 MB with UUIDv7 and 17.9 MB with bigint, and the random-UUID insert took 1,669 ms against 382 ms and 333 ms.

Along the way you will also switch an existing UUIDv4 table to time-ordered keys with a one-line change that finishes in milliseconds and rewrites nothing, generate UUIDv7 values in Python 3.14, see why sorting by a UUIDv7 key does not give you the order in which rows became visible (a polling reader missed about 39 percent of committed rows in my test, bigint keys included), learn what a time-ordered ID gives away, and run a small audit script that finds risky keys in your own database.

Everything below ran on PostgreSQL 18.6 with Python 3.14.10 and 3.13.14 on Windows 11, and I tested no other versions. uuidv7() is new in PostgreSQL 18: the release notes list a “uuidv7() function for generating timestamp-ordered UUIDs” among the headline features. On PostgreSQL 17 or older the function does not exist and the lab stops at Step 1.

The words you need first

A few terms come up in every step, so here they are in plain language before you meet them in commands.

• Primary key. The column that identifies each row and can never repeat. PostgreSQL enforces that rule by building a B-tree index on it.
• Page and B-tree index. PostgreSQL stores tables and indexes in 8 KB pieces called pages. A B-tree index keeps its keys in sorted order across leaf pages, and a new key must go into the leaf page where it sorts. Keys that arrive in increasing order always land in the last leaf page. Keys that arrive in random order land in random leaf pages all over the index.
• WAL (write-ahead log). Before PostgreSQL changes a data page, it writes a description of the change to a log. The log is what makes crash recovery, replication and point-in-time backups possible, so every extra byte of WAL is a byte that has to be flushed to disk, shipped to replicas and archived.
• Checkpoint and full-page image. A checkpoint is a moment when PostgreSQL makes sure every changed page has reached the data files. The documentation for full_page_writes explains the catch: the server “writes the entire content of each disk page to WAL during the first modification of that page after a checkpoint.” That whole-page copy is called a full-page image (FPI), and it is 8 KB of WAL for a single changed row.
• UUID versions. A UUID is a 128-bit identifier written as 32 hexadecimal digits in five groups. Version 4 fills 122 of those bits with random data. Version 7 puts a 48-bit Unix timestamp, counted in milliseconds, in front, so a value created later sorts later.

The IETF document that defines UUID versions, RFC 9562, names the problem this tutorial measures. It says “UUID versions that are not time ordered, such as UUIDv4 (described in Section 5.4), have poor database-index locality,” which means new values created in succession are not close to each other in the index. Its next sentence adds: “The resulting negative performance effects on the common structures used for this (B-tree and its variants) can be dramatic.” A phrase like that is easy to repeat and hard to size, so you will size it.

What you need before you start

• A PostgreSQL 18 server you can destroy, plus psql, initdb and pg_ctl. I used the 18.6 Windows binaries; any 18.x on any operating system should behave the same. The pgstattuple extension (part of PostgreSQL’s standard contrib modules) must be installable.
• Python 3.14 or newer for Step 10, which calls uuid.uuid7(). The other scripts ran on Python 3.13.14. You need psycopg 3 (I used 3.3.8, which needs Python 3.10 or newer) and pytest (I used 9.2.0).
• About 4 GB of free disk space and about 45 minutes, of which roughly 5 minutes is waiting for loads.
• Comfort with SELECT, INSERT and CREATE TABLE. Nothing else is assumed.

Every size in this article that says MB uses 1,048,576 bytes, the way psql prints it, and every timing is milliseconds measured by PostgreSQL itself.

Step 1: Start a scratch server and check the version

Create the server

The commands below create a throwaway cluster in a folder called keylab and start it on port 55432 so it cannot collide with a PostgreSQL you already run. Use your own paths. On Windows these were the same commands with .exe suffixes.

initdb -D keylab -U postgres --auth=trust --locale=C -E UTF8
pg_ctl -D keylab -l keylab.log -w -o "-p 55432 -c listen_addresses=127.0.0.1 -c shared_buffers=32MB -c autovacuum=off -c checkpoint_timeout=1h -c max_wal_size=8GB -c timezone=UTC" start
psql -X -h 127.0.0.1 -p 55432 -U postgres -c "CREATE DATABASE keys"
psql -X -h 127.0.0.1 -p 55432 -U postgres -d keys -c "CREATE EXTENSION pgstattuple"

You should see server started and then CREATE EXTENSION. The authentication setting trust lets anyone on the machine connect without a password, which is fine for a loopback-only throwaway server and wrong for anything else.

Why these settings

• shared_buffers=32MB shrinks PostgreSQL’s own page cache from its 128 MB default. A two-million-row lab is small by production standards, so a small cache lets its index behave the way a much larger index behaves on a real server whose indexes no longer fit in memory.
• autovacuum=off stops background maintenance from adding WAL and noise in the middle of a measurement. Do not copy this setting to a real server.
• checkpoint_timeout=1h and max_wal_size=8GB mean no checkpoint starts by itself during the lab. The only checkpoints are the ones you start with the CHECKPOINT command, so you decide exactly when the clock for full-page images restarts.

Make the shell convenient

Every SQL file in this tutorial runs through one shell function. The two environment settings make timestamps print in UTC and keep the buffer counters in a single process by turning off parallel workers.

export PGOPTIONS="-c timezone=UTC -c max_parallel_workers_per_gather=0"
keysql() { psql -X -q -h 127.0.0.1 -p 55432 -U postgres -d keys -v ON_ERROR_STOP=1 "$@"; }

Run the first query

Save this as 01_version.sql and run keysql -f 01_version.sql. It asks for the server version and the cache size, and then it generates one value of each UUID flavor that PostgreSQL 18 can produce.

-- 01_version.sql
SELECT version();
SHOW shared_buffers;
SELECT uuidv4() AS random_id, uuidv7() AS time_ordered_id;

                                 version                                 
-------------------------------------------------------------------------
 PostgreSQL 18.6 on x86_64-windows, compiled by msvc-19.44.35228, 64-bit
(1 row)

 shared_buffers 
----------------
 32MB
(1 row)

              random_id               |           time_ordered_id            
--------------------------------------+--------------------------------------
 6a32c049-772f-4bd6-b89a-4533ff7aaf48 | 01a1c52b-a27a-7029-bd79-14ac3b73358a
(1 row)

Check that it worked

The version line must start with PostgreSQL 18. and shared_buffers must say 32MB. If PostgreSQL answers function uuidv7() does not exist, your server is older than 18. Look at the two ids: the random one has a 4 as the first digit of its third group (that digit is the version), and the time-ordered one starts with a long run of small hex digits and has a 7 in the same position. The next step explains why.

Step 2: Open the box and read what a UUIDv7 contains

Before you trust a time-ordered ID, look at what is inside it. RFC 9562 lays out the 128 bits of a version 7 UUID like this:

  48 bits            4 bits   12 bits       2 bits   62 bits
+------------------+--------+-------------+--------+---------------------+
| unix_ts_ms       | ver=7  | rand_a      | var=10 | rand_b              |
+------------------+--------+-------------+--------+---------------------+
  milliseconds               PostgreSQL:           random
  since 1970                 1/4096 of a
                             millisecond

The first 48 bits are the clock. The version and variant bits are fixed markers. The rest is up to the implementation, and PostgreSQL’s choice matters for ordering. The comments in PostgreSQL’s own source file uuid.c say it uses the method “Replace Leftmost Random Bits with Increased Clock Precision (Method 3)” from the RFC, putting a fraction of a millisecond in the 12 rand_a bits, and that the clock it reads is forced to advance: “The returned timestamp is ensured to be at least SUBMS_MINIMAL_STEP greater than the previous returned timestamp (on this backend).” So inside one database session, each new uuidv7() value is larger than the last one, even when several are made in the same millisecond. One platform note from the same file: on Windows builds the clock has only microsecond precision, so 10 of those 12 bits carry time and 2 are filled with random bits. My lab ran on Windows.

Generate five ids in one statement

Save as 02a_anatomy.sql and run it. The uuid_extract_version() and uuid_extract_timestamp() functions are built into PostgreSQL 18, and the documentation describes uuidv7() as generating “a version 7 (time-ordered) UUID.”

-- 02a_anatomy.sql
SELECT n, id,
       uuid_extract_version(id)   AS version,
       uuid_extract_timestamp(id) AS embedded_time
FROM (SELECT n, uuidv7() AS id FROM generate_series(1, 5) AS n) AS s;

 n |                  id                  | version |       embedded_time        
---+--------------------------------------+---------+----------------------------
 1 | 01a1c52b-a2b6-738d-8595-bfcb3a4535b8 |       7 | 2026-11-10 11:15:33.686+00
 2 | 01a1c52b-a2b6-73f4-a93d-891d3d8b8499 |       7 | 2026-11-10 11:15:33.686+00
 3 | 01a1c52b-a2b6-73f8-b158-a40bb78aece0 |       7 | 2026-11-10 11:15:33.686+00
 4 | 01a1c52b-a2b6-7401-843e-c66478955f38 |       7 | 2026-11-10 11:15:33.686+00
 5 | 01a1c52b-a2b6-7407-a1cd-02011bdc460c |       7 | 2026-11-10 11:15:33.686+00
(5 rows)

Read the output from left to right. All five ids start with the same eight hex digits and the same four after the first hyphen, because they were created in the same millisecond. The first three digits after the 7 grow from row to row: that is the fraction of a millisecond. The last two groups are random. The extraction function turns the first 48 bits back into a timestamp, and all five rows show the same millisecond. The same documentation page warns that “the extracted timestamp is not necessarily exactly equal to the time the UUID was generated; this depends on the implementation that generated the UUID,” which is why you should treat it as a hint, not as a record.

Back-date an id with the shift argument

uuidv7() accepts an optional interval that shifts the embedded time. That is how you would give imported historical rows ids that sort where they belong. Save as 02c_shift.sql and run it:

-- 02c_shift.sql
SELECT uuid_extract_timestamp(uuidv7())                    AS now_id_time,
       uuid_extract_timestamp(uuidv7(interval '-1 day'))   AS shifted_id_time,
       now()                                               AS now;

        now_id_time         |      shifted_id_time       |              now              
----------------------------+----------------------------+-------------------------------
 2026-11-10 11:15:33.981+00 | 2026-11-09 11:15:33.981+00 | 2026-11-10 11:15:33.980794+00
(1 row)

The shifted id reports a time exactly one day earlier. Only the embedded clock moved. The third column, now(), is the transaction start time and is a fraction of a millisecond older than the first id because the id was generated a moment later.

Decode the bits yourself

You can check all of this without PostgreSQL. This short Python script slices a UUID’s 128-bit integer with shifts and masks into the fields in the diagram. It refuses any UUID that is not version 7, because the first 48 bits of a random UUID are not a clock.

# decode_uuid7.py
"""Split a UUID into the fields RFC 9562 defines for version 7. Pass one or more UUIDs as arguments."""
import sys
import uuid
from datetime import datetime, timezone


def decode(text):
    value = uuid.UUID(text).int
    version = (value >> 76) & 0xF
    if version != 7:
        raise ValueError(f"{text} is version {version}, not 7; its first 48 bits are not a clock")
    unix_ts_ms = value >> 80                 # top 48 bits: milliseconds since 1970-01-01 UTC
    rand_a = (value >> 64) & 0xFFF           # 12 bits after the version nibble
    variant = (value >> 62) & 0x3            # 2 bits, always 10 for RFC 9562 UUIDs
    rand_b = value & ((1 << 62) - 1)         # last 62 bits
    return {
        "unix_ts_ms": unix_ts_ms,
        "time": datetime.fromtimestamp(unix_ts_ms / 1000, tz=timezone.utc),
        "version": version,
        "rand_a": rand_a,
        "variant": f"{variant:02b}",
        "rand_b": rand_b,
    }


if __name__ == "__main__":
    for arg in sys.argv[1:]:
        try:
            info = decode(arg)
        except ValueError as exc:
            print(f"{arg}\n  {exc}")
            continue
        print(arg)
        print(f"  unix_ts_ms = {info['unix_ts_ms']}  ->  {info['time'].isoformat(timespec='milliseconds')}")
        print(f"  version    = {info['version']}")
        print(f"  rand_a     = {info['rand_a']:#05x}  ({info['rand_a']} of 4096)")
        print(f"  variant    = 0b{info['variant']}")
        print(f"  rand_b     = {info['rand_b']:#018x}")

Run it on three ids: the example UUIDv7 printed in RFC 9562 (appendix A.6), the first id from your own 02a_anatomy output, and the random id from Step 1. Substitute your own ids for the last two.

python decode_uuid7.py 017F22E2-79B0-7CC3-98C4-DC0C0C07398F 01a1c52b-a2b6-738d-8595-bfcb3a4535b8 6a32c049-772f-4bd6-b89a-4533ff7aaf48

017F22E2-79B0-7CC3-98C4-DC0C0C07398F
  unix_ts_ms = 1645557742000  ->  2022-02-22T19:22:22.000+00:00
  version    = 7
  rand_a     = 0xcc3  (3267 of 4096)
  variant    = 0b10
  rand_b     = 0x18c4dc0c0c07398f
01a1c52b-a2b6-738d-8595-bfcb3a4535b8
  unix_ts_ms = 1794309333686  ->  2026-11-10T11:15:33.686+00:00
  version    = 7
  rand_a     = 0x38d  (909 of 4096)
  variant    = 0b10
  rand_b     = 0x0595bfcb3a4535b8
6a32c049-772f-4bd6-b89a-4533ff7aaf48
  6a32c049-772f-4bd6-b89a-4533ff7aaf48 is version 4, not 7; its first 48 bits are not a clock

The RFC’s example decodes to 1645557742000 milliseconds, which is 22 February 2022 at 19:22:22 UTC (the RFC calls it 2:22:22 PM at GMT-05:00), with rand_a equal to 0xCC3. The PostgreSQL id decodes to a time with milliseconds and a rand_a of 909 out of 4,096, about 0.22 of a millisecond. The random id is refused with a message instead of a fake date.

Pin the behavior down with a few tests. Save this as test_decode_uuid7.py next to the script:

# test_decode_uuid7.py
import uuid

import pytest

from decode_uuid7 import decode

RFC_VECTOR = "017F22E2-79B0-7CC3-98C4-DC0C0C07398F"   # RFC 9562 appendix A.6


def test_rfc_vector_fields():
    info = decode(RFC_VECTOR)
    assert info["unix_ts_ms"] == 1645557742000
    assert info["time"].isoformat() == "2022-02-22T19:22:22+00:00"
    assert info["version"] == 7
    assert info["rand_a"] == 0xCC3
    assert info["variant"] == "10"
    assert info["rand_b"] == 0x18C4DC0C0C07398F


def test_refuses_a_random_uuid():
    with pytest.raises(ValueError, match="version 4"):
        decode(str(uuid.uuid4()))


def test_fields_reassemble_to_the_same_integer():
    info = decode(RFC_VECTOR)
    value = (info["unix_ts_ms"] << 80) | (7 << 76) | (info["rand_a"] << 64) | (0b10 << 62) | info["rand_b"]
    assert value == uuid.UUID(RFC_VECTOR).int


@pytest.mark.skipif(not hasattr(uuid, "uuid7"), reason="uuid.uuid7() needs Python 3.14 or newer")
def test_python_generated_uuid7_decodes_to_its_own_time():
    u = uuid.uuid7()
    assert decode(str(u))["unix_ts_ms"] == u.time

python -m pytest -q test_decode_uuid7.py

On Python 3.13.14 the result was 3 passed, 1 skipped, and on Python 3.14.10 it was 4 passed: the fourth test calls uuid.uuid7(), which exists only in Python 3.14 and newer, so it skips on older interpreters.

Gotcha: read the time, but do not parse the rest

RFC 9562 advises that “avoiding parsing UUID values unnecessarily is recommended; instead, treat UUIDs as opaquely as possible.” The decoder exists to teach you the layout. Your application should read the time with uuid_extract_timestamp() at most, and never depend on what the other bits mean, because each implementation fills them differently. You will see Python’s version differ from PostgreSQL’s in Step 10.

Step 3: Watch the ordering with your own eyes

A B-tree index is only as friendly as the order its keys arrive in, so measure that order directly. This query generates 100,000 random and 100,000 time-ordered ids in sequence, then counts how often an id is larger than the one made just before it. Save as 02b_order_test.sql and run keysql -f 02b_order_test.sql:

-- 02b_order_test.sql
WITH ids AS MATERIALIZED (
    SELECT n, uuidv4() AS v4, uuidv7() AS v7
    FROM generate_series(1, 100000) AS n
), pairs AS (
    SELECT v4 < lead(v4) OVER (ORDER BY n) AS v4_rises,
           v7 < lead(v7) OVER (ORDER BY n) AS v7_rises
    FROM ids
)
SELECT count(*) FILTER (WHERE v4_rises IS NOT NULL)          AS pairs,
       count(*) FILTER (WHERE v4_rises)                     AS uuidv4_rises,
       count(*) FILTER (WHERE v7_rises)                     AS uuidv7_rises
FROM pairs;

 pairs | uuidv4_rises | uuidv7_rises 
-------+--------------+--------------
 99999 |        49825 |        99999
(1 row)

Every one of the 99,999 consecutive UUIDv7 pairs rises. For random UUIDs about half rise and half fall (49,825 rose here), which is what random order means: each new key is as likely to belong at the beginning of the index as at the end. The MATERIALIZED keyword forces PostgreSQL to generate each id once in sequence order rather than recomputing it.

Step 4: Create three tables that differ only in the key

The tables below hold made-up orders. The only difference is the id column: a bigint that PostgreSQL numbers for you with GENERATED ALWAYS AS IDENTITY, a UUID filled by uuidv4() (the documentation lists it beside the older gen_random_uuid() as generating a version 4 UUID), and a UUID filled by uuidv7(). Save as 03_create.sql and run keysql -f 03_create.sql:

-- 03_create.sql
CREATE TABLE orders_bigint (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id integer     NOT NULL,
    status      text        NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now(),
    note        text        NOT NULL
);

CREATE TABLE orders_v4 (
    id          uuid PRIMARY KEY DEFAULT uuidv4(),
    customer_id integer     NOT NULL,
    status      text        NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now(),
    note        text        NOT NULL
);

CREATE TABLE orders_v7 (
    id          uuid PRIMARY KEY DEFAULT uuidv7(),
    customer_id integer     NOT NULL,
    status      text        NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now(),
    note        text        NOT NULL
);

\d orders_v7

                         Table "public.orders_v7"
   Column    |           Type           | Collation | Nullable | Default  
-------------+--------------------------+-----------+----------+----------
 id          | uuid                     |           | not null | uuidv7()
 customer_id | integer                  |           | not null | 
 status      | text                     |           | not null | 
 created_at  | timestamp with time zone |           | not null | now()
 note        | text                     |           | not null | 
Indexes:
    "orders_v7_pkey" PRIMARY KEY, btree (id)

The last command, \d orders_v7, prints the table definition so you can confirm the default is uuidv7() and that PostgreSQL created an index named orders_v7_pkey for the primary key. Check the same for the other two tables before moving on.

Step 5: Load two million rows into each table

Now fill the tables. The statement below inserts rows with a random customer number, a status and a 32-character note, and it asks PostgreSQL to EXPLAIN (ANALYZE, WAL, BUFFERS) the insert so that you get the cost report along with the work. The EXPLAIN documentation says the WAL option includes “the number of records, number of full page images (fpi), the amount of WAL generated in bytes and the number of times the WAL buffers became full.” The Buffers line counts pages found in shared_buffers (hit) and pages PostgreSQL had to request from the operating system (read). The table name and row count are psql variables so one file serves all three tables. Save as 04_load.sql:

-- 04_load.sql
EXPLAIN (ANALYZE, WAL, BUFFERS, COSTS OFF)
INSERT INTO :"tbl" (customer_id, status, note)
SELECT 1 + (random() * 99999)::int,
       (ARRAY['new', 'paid', 'shipped', 'delivered'])[1 + g % 4],
       md5(g::text)
FROM generate_series(1, :rows) AS g;

for t in orders_bigint orders_v4 orders_v7; do keysql -v tbl=$t -v rows=2000000 -f 04_load.sql; done

Because EXPLAIN ANALYZE really executes an INSERT, the rows are committed when it finishes. Expect the loop to take about a minute. This is the report for the UUIDv4 table:

                                              QUERY PLAN                                               
-------------------------------------------------------------------------------------------------------
 Insert on orders_v4 (actual time=23199.234..23199.235 rows=0.00 loops=1)
   Buffers: shared hit=7530083 read=482807 dirtied=519287 written=543768, temp read=3418 written=3418
   WAL: records=4009732 bytes=431443982 buffers full=16673
   ->  Subquery Scan on "*SELECT*" (actual time=68.086..4385.431 rows=2000000.00 loops=1)
         Buffers: temp read=3418 written=3418
         ->  Function Scan on generate_series g (actual time=68.081..2350.462 rows=2000000.00 loops=1)
               Buffers: temp read=3418 written=3418
 Planning:
   Buffers: shared hit=14 read=4 written=4
 Planning Time: 0.326 ms
 Execution Time: 23204.319 ms
(11 rows)

Read it from the top. Execution Time is how long the insert took. Buffers: shared hit=… read=… says how many index and table pages were found in the cache and how many were not. WAL: records=… bytes=… is how much log the statement wrote. Here are the same three numbers for all three tables:

Note on the bigint time. The bigint load was the first of the three on each of my three passes, and it took 8.4, 13.1 and 16.1 seconds, while the UUIDv4 and UUIDv7 loads that followed repeated to within about two seconds. I do not know why the first load varies, so do not compare that cell with the others.

Even on a one-time load into empty tables, the random key hurt. It took 1.9 times as long as UUIDv7, and PostgreSQL had to read 482,807 pages from outside shared_buffers because new keys kept landing on index pages that had already been pushed out of the 32 MB cache. UUIDv7 and bigint read almost nothing. The WAL volume differs less: 7.6 percent more than bigint for UUIDv7 (the keys are twice as wide) and 15.3 percent more for UUIDv4. Almost no full-page images appear in these loads (EXPLAIN leaves the count out when it is zero), because no checkpoint happened while they ran. The next steps show what changes when one does.

Step 6: Look inside the three indexes

The load numbers hint at the cause, but you can inspect the indexes directly. The pgstatindex() function from the pgstattuple extension reads an index and reports how many leaf pages it has, how full they are on average (avg_leaf_density, in percent) and how fragmented they are (leaf_fragmentation). PostgreSQL’s source file for the function says how it counts fragmentation: “If the next leaf is on an earlier block, it means a fragmentation.” In plain terms, it is the percentage of leaf pages whose next page in key order sits earlier in the file, so an index built strictly in key order scores 0. Save as 05a_inspect.sql:

-- 05a_inspect.sql
SELECT t.tbl                                              AS table_name,
       pg_size_pretty(pg_relation_size(t.tbl::regclass))   AS heap,
       pg_size_pretty(s.index_size)                        AS pkey_index,
       s.leaf_pages,
       s.avg_leaf_density                                  AS leaf_density_pct,
       s.leaf_fragmentation                                AS fragmentation_pct
FROM (VALUES ('orders_bigint'), ('orders_v4'), ('orders_v7')) AS t(tbl),
     LATERAL pgstatindex((t.tbl || '_pkey')::regclass) AS s
ORDER BY t.tbl;

keysql -f 05a_inspect.sql

  table_name   |  heap  | pkey_index | leaf_pages | leaf_density_pct | fragmentation_pct 
---------------+--------+------------+------------+------------------+-------------------
 orders_bigint | 188 MB | 43 MB      |       5465 |            90.03 |                 0
 orders_v4     | 203 MB | 76 MB      |       9689 |            71.24 |             49.86
 orders_v7     | 203 MB | 60 MB      |       7663 |            89.99 |                 0
(3 rows)

Read the numbers

• The bigint and UUIDv7 indexes are about 90 percent full and not fragmented at all. That is the default fillfactor. The CREATE INDEX documentation says: “For B-trees, leaf pages are filled to this percentage during initial index builds, and also when extending the index at the right (adding new largest key values).” It adds that “B-trees use a default fillfactor of 90.” Time-ordered keys always extend the index at the right, so they get that behavior on every insert.
• The UUIDv4 index is 71 percent full and half of its leaf pages are out of order. A random key lands in the middle of the index, a full page has to split in two, and the halves stay partly empty. It needs 9,689 leaf pages for the same two million rows that fit in 7,663 pages for UUIDv7 and 5,465 for bigint. My earlier tutorial on building a B+tree index in Python measured the same 70 percent effect in a toy tree, so what you are seeing is the data structure, not a PostgreSQL quirk.
• A UUID costs more than a bigint even in the best case. The UUIDv7 index is 60 MB against 43 MB, and the table itself is 203 MB against 188 MB, because every key is 16 bytes instead of 8 and every other table that references this key copies it. The wider key is the price of being able to create ids anywhere without asking the database.

Check the physical order of the rows

One more number explains why ordered keys help reads. After ANALYZE, the pg_stats view reports a correlation between the order of a column’s values and the physical order of the rows in the table: 1 means the table is stored in key order, and 0 means there is no relationship. Save as 05b_correlation.sql:

-- 05b_correlation.sql
ANALYZE orders_bigint;
ANALYZE orders_v4;
ANALYZE orders_v7;

SELECT tablename, attname, round(correlation::numeric, 4) AS correlation
FROM pg_stats
WHERE schemaname = 'public' AND attname = 'id' AND tablename LIKE 'orders\_%'
ORDER BY tablename;

   tablename   | attname | correlation 
---------------+---------+-------------
 orders_bigint | id      |      1.0000
 orders_v4     | id      |      0.0007
 orders_v7     | id      |      1.0000
(3 rows)

The bigint and UUIDv7 tables are stored in key order (1.0000), and the UUIDv4 table has no relationship at all (0.0007). Neighboring keys in a time-ordered table are neighboring rows, which is why a lookup of recent ids touches a small, hot part of both the index and the table. Step 8 measures that.

What if you store the UUID as text?

Plenty of codebases store identifiers as strings, and an ORM will do it silently if you let it. Save the next file as 09_text_index.sql. It builds an index on the text form of the UUIDv7 key, compares its size with the real primary-key index, compares the stored width of one value both ways, and then drops the experiment:

-- 09_text_index.sql
CREATE INDEX orders_v7_id_as_text ON orders_v7 ((id::text));

SELECT pg_size_pretty(pg_relation_size('orders_v7_pkey'))       AS uuid_index,
       pg_size_pretty(pg_relation_size('orders_v7_id_as_text')) AS text_index,
       round(pg_relation_size('orders_v7_id_as_text')::numeric
             / pg_relation_size('orders_v7_pkey'), 2)           AS ratio;

SELECT pg_column_size(id) AS uuid_bytes, pg_column_size(id::text) AS text_bytes
FROM orders_v7 LIMIT 1;

DROP INDEX orders_v7_id_as_text;

keysql -f 09_text_index.sql

 uuid_index | text_index | ratio 
------------+------------+-------
 69 MB      | 130 MB     |  1.87
(1 row)

 uuid_bytes | text_bytes 
------------+------------
         16 |         40
(1 row)

The text index is 1.87 times the size of the UUID index, and one value takes 40 bytes as text against 16 as a uuid. RFC 9562 gives the reason and the advice: “storing UUIDs as text is unnecessarily verbose, requiring 288 bits to represent 128-bit UUID values. Thus, where feasible, UUIDs SHOULD be stored within database applications as the underlying 128-bit binary value.” In PostgreSQL that means the uuid type, never text or varchar(36).

Step 7: Add rows after a checkpoint and watch the WAL

Real databases do not load millions of rows once. They take a steady stream of inserts into tables that already exist, and checkpoints keep happening. That is where the full-page images from the beginning of this tutorial matter. After a checkpoint, the first change to any page writes the whole page to WAL. A random key spreads changes across nearly every leaf page, so nearly every leaf page pays that 8 KB toll. A time-ordered key keeps changing the same one or two pages.

This file starts a checkpoint, then inserts 100,000 more rows into whichever table you name and reports the cost. Save as 06_steady.sql:

-- 06_steady.sql
CHECKPOINT;

EXPLAIN (ANALYZE, WAL, BUFFERS, COSTS OFF)
INSERT INTO :"tbl" (customer_id, status, note)
SELECT 1 + (random() * 99999)::int,
       (ARRAY['new', 'paid', 'shipped', 'delivered'])[1 + g % 4],
       md5(g::text)
FROM generate_series(1, 100000) AS g;

for t in orders_bigint orders_v4 orders_v7; do keysql -v tbl=$t -f 06_steady.sql; done

Here are the reports for the UUIDv4 table and the UUIDv7 table, first round:

                                            QUERY PLAN                                             
---------------------------------------------------------------------------------------------------
 Insert on orders_v4 (actual time=1668.332..1668.333 rows=0.00 loops=1)
   Buffers: shared hit=341806 read=61612 dirtied=63383 written=60180
   WAL: records=200392 fpi=9730 bytes=78127867 buffers full=5926
   ->  Subquery Scan on "*SELECT*" (actual time=3.474..168.298 rows=100000.00 loops=1)
         ->  Function Scan on generate_series g (actual time=3.469..89.095 rows=100000.00 loops=1)
 Planning:
   Buffers: shared hit=17 read=1
 Planning Time: 0.704 ms
 Execution Time: 1668.729 ms
(9 rows)

                                            QUERY PLAN                                             
---------------------------------------------------------------------------------------------------
 Insert on orders_v7 (actual time=381.537..381.538 rows=0.00 loops=1)
   Buffers: shared hit=204919 read=11 dirtied=1687 written=1683
   WAL: records=200385 fpi=4 bytes=20136873 buffers full=2133
   ->  Subquery Scan on "*SELECT*" (actual time=2.778..144.107 rows=100000.00 loops=1)
         ->  Function Scan on generate_series g (actual time=2.772..72.000 rows=100000.00 loops=1)
 Planning:
   Buffers: shared hit=14 read=4
 Planning Time: 0.234 ms
 Execution Time: 381.865 ms
(9 rows)

Look at the WAL: lines. The UUIDv4 insert wrote 9,730 full-page images and 78,127,867 bytes. The UUIDv7 insert wrote 4 images and 20,136,873 bytes for the same 100,000 rows. The Buffers line shows 61,612 page reads from outside the cache against 11. I ran the loop three times in a row. This table lists every run:

Read the numbers

• The WAL cost of a random key is almost entirely full-page images. Round 1 wrote 9,730 images, and the UUIDv4 index had 9,689 leaf pages when Step 6 counted them: roughly one image per leaf page, because 100,000 random keys touch nearly every one of them. The UUIDv7 and bigint inserts wrote 4 to 6 images in total, which fits a handful of pages at the right-hand edge of the index and the table changing for the first time after the checkpoint. I did not identify each image.
• Round 1 against round 1: UUIDv4 wrote 3.9 times the WAL of UUIDv7 and took 4.4 times as long, and 5.0 times as long as bigint. Those ratios held across my runs.
• The random key gets worse as the table grows. The image count climbed from 9,730 to 10,126 to 10,538 over three rounds as splits added leaf pages. The UUIDv7 and bigint numbers did not move.
• Most of the extra 55 MB is images. Subtracting the 19.2 MB that the UUIDv7 insert needed for the same rows leaves about 55 MB spread over 9,730 images, roughly 6,000 bytes each. My reading is that an image leaves out the unused gap in the middle of a page, which would explain a figure below 8,192.

Does WAL compression rescue random keys?

PostgreSQL can compress full-page images. The documentation for wal_compression is candid about the trade: “Enabling compression can reduce the WAL volume without increasing the risk of unrecoverable data corruption, but at the cost of some extra CPU spent on the compression during WAL logging and on the decompression during WAL replay.” It is a cheap thing to try, so try it. This file repeats the same insert with lz4 compression switched on for the session. Save as 11_wal_compression.sql:

-- 11_wal_compression.sql
SET wal_compression = lz4;
CHECKPOINT;

EXPLAIN (ANALYZE, WAL, BUFFERS, COSTS OFF)
INSERT INTO orders_v4 (customer_id, status, note)
SELECT 1 + (random() * 99999)::int,
       (ARRAY['new', 'paid', 'shipped', 'delivered'])[1 + g % 4],
       md5(g::text)
FROM generate_series(1, 100000) AS g;

keysql -v tbl=orders_v4 -f 06_steady.sql
keysql -f 11_wal_compression.sql

The first command is one more plain run (round 4) so the two runs are neighbors. Results:

Compression did not help much. Per image, the compressed run wrote about 3.9 percent less (5,835 bytes against 6,072 by my estimate, which subtracts the 19.2 MB the UUIDv7 run needed for the same rows and divides by the number of images), and the total went up slightly (83.9 MB against 83.2 MB) because the table had grown and the run touched 579 more pages. The insert was no faster. A random key is mostly random bytes, and random bytes do not compress. Compression is worth knowing about, but it is not a substitute for choosing a better key.

When does it start to matter?

A fair objection: your tables are not 2 million rows. The next experiment builds a UUIDv4 table and a UUIDv7 table at four sizes, runs a checkpoint, and inserts 10,000 rows into each. It reuses 04_load.sql for the load and two tiny new files. Save these as 10_sweep_create.sql and 10_sweep_steady.sql:

-- 10_sweep_create.sql
CREATE TABLE :"tbl" (
    id          uuid PRIMARY KEY DEFAULT :keydef,
    customer_id integer     NOT NULL,
    status      text        NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now(),
    note        text        NOT NULL
);

-- 10_sweep_steady.sql
CHECKPOINT;

EXPLAIN (ANALYZE, WAL, BUFFERS, COSTS OFF)
INSERT INTO :"tbl" (customer_id, status, note)
SELECT 1 + (random() * 99999)::int,
       (ARRAY['new', 'paid', 'shipped', 'delivered'])[1 + g % 4],
       md5(g::text)
FROM generate_series(1, 10000) AS g;

for n in 50000 200000 800000 3200000; do
  for kind in v4 v7; do
    t=sweep_${kind}_${n}
    keysql -v tbl=$t -v keydef="uuid${kind}()" -f 10_sweep_create.sql
    keysql -v tbl=$t -v rows=$n -f 04_load.sql
    keysql -v tbl=$t -f 10_sweep_steady.sql
  done
done

In the loop, uuid${kind}() expands to uuidv4() or uuidv7(), and psql substitutes that text into the DEFAULT clause of 10_sweep_create.sql. Expect this experiment to take about two minutes, most of it loading the 3.2-million-row tables.

At 50,000 rows the random key wrote 3.3 MB of WAL for the 10,000 inserts against 1.9 MB, and the difference in time is within the noise of a 35 to 45 ms measurement. At 3.2 million rows it wrote 42.6 MB against 1.9 MB, which is 22 times as much, and it took 368 ms against 45 ms. The UUIDv7 column does not change with table size, and the UUIDv4 column grows with the size of the index. The image counts follow the same logic as before: 10,000 random inserts into an index with roughly 15,000 leaf pages (extrapolating from Step 6) touch several thousand distinct pages, and the same 10,000 time-ordered inserts wrote three images.

The practical reading is that a random key is cheap while the index is small, and its cost scales with the table. Choose the key for the table you will have, not the table you have today.

Step 8: Read recent rows and count the cache misses

Inserts are half of a table’s life. The other half is reads, and in most applications the reads cluster on recent rows: the order a customer just placed, the session that was just created, the event that just arrived. This experiment mimics that. It picks the newest 100,000 rows of a table (the table is append-only, so the highest physical positions, ctid, are the newest rows), selects 50,000 of their ids at random, and looks each one up through the primary key. Save as 07_hot_reads.sql:

-- 07_hot_reads.sql
CREATE TEMP TABLE hot AS
SELECT id
FROM (SELECT id FROM :"tbl" ORDER BY ctid DESC LIMIT 100000) AS newest
ORDER BY random()
LIMIT 50000;

ANALYZE hot;
SET enable_hashjoin = off;
SET enable_mergejoin = off;

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT sum(o.customer_id) FROM hot JOIN :"tbl" AS o USING (id);

for t in orders_bigint orders_v4 orders_v7; do keysql -v tbl=$t -f 07_hot_reads.sql; keysql -v tbl=$t -f 07_hot_reads.sql; done

The two SET lines switch off hash and merge joins so that PostgreSQL does what an application does, one primary-key probe at a time. I ran each file twice and kept the second run, so that the operating system had already seen each table’s newest pages before the measured run. The UUIDv4 and UUIDv7 reports:

                                        QUERY PLAN                                         
-------------------------------------------------------------------------------------------
 Aggregate (actual rows=1.00 loops=1)
   Buffers: shared hit=161046 read=38954 written=29, local hit=320
   ->  Nested Loop (actual rows=50000.00 loops=1)
         Buffers: shared hit=161046 read=38954 written=29, local hit=320
         ->  Seq Scan on hot (actual rows=50000.00 loops=1)
               Buffers: local hit=320
         ->  Index Scan using orders_v4_pkey on orders_v4 o (actual rows=1.00 loops=50000)
               Index Cond: (id = hot.id)
               Index Searches: 50000
               Buffers: shared hit=161046 read=38954 written=29
 Planning:
   Buffers: shared hit=46 read=1
 Planning Time: 0.206 ms
 Execution Time: 335.309 ms
(14 rows)

                                        QUERY PLAN                                         
-------------------------------------------------------------------------------------------
 Aggregate (actual rows=1.00 loops=1)
   Buffers: shared hit=200000, local hit=320
   ->  Nested Loop (actual rows=50000.00 loops=1)
         Buffers: shared hit=200000, local hit=320
         ->  Seq Scan on hot (actual rows=50000.00 loops=1)
               Buffers: local hit=320
         ->  Index Scan using orders_v7_pkey on orders_v7 o (actual rows=1.00 loops=50000)
               Index Cond: (id = hot.id)
               Index Searches: 50000
               Buffers: shared hit=200000
 Planning:
   Buffers: shared hit=47
 Planning Time: 0.284 ms
 Execution Time: 40.003 ms
(14 rows)

Read the numbers

Each lookup visited 4 buffers (the index pages on the way down plus the table page), so every table shows 200,000 buffer visits for 50,000 lookups. The difference is where they were found. With UUIDv7 and bigint, all 200,000 were already in shared_buffers: recent keys sit in a small contiguous region of the index, and that region stays cached. With UUIDv4, the 50,000 lookups spread across the whole index (86 MB when Step 6’s check ran after three insert rounds), which cannot fit in a 32 MB cache, so 38,954 visits (19.5 percent) had to ask the operating system for the page. The query took 335.3 ms against 40.0 ms, 8.4 times as long.

One caveat keeps this honest. A read in this output means PostgreSQL asked the operating system for the page, and on this machine the operating system answered from its own file cache. On a server whose index does not fit in RAM, those reads reach the disk and the gap is wider. I did not measure a cold disk.

Step 9: Switch an existing table without rewriting it

You probably have tables that already use random UUIDs. The good news is that you do not have to migrate their rows. The ALTER TABLE documentation says of SET DEFAULT: “The new default value will only apply in subsequent INSERT or UPDATE commands; it does not cause rows already in the table to change.” So the switch is a metadata change. Save as 08_switch.sql:

-- 08_switch.sql
\timing on
ALTER TABLE orders_v4 ALTER COLUMN id SET DEFAULT uuidv7();
\timing off

SELECT column_default FROM information_schema.columns
WHERE table_name = 'orders_v4' AND column_name = 'id';

keysql -f 08_switch.sql

Time: 15.805 ms
 column_default 
----------------
 uuidv7()
(1 row)

The \timing lines print how long the command took: 15.8 ms on a 2-million-row table (it took 2.1 ms in an earlier pass), and no rewrite happened. ALTER TABLE still takes an AccessExclusiveLock for the instant it runs (I checked pg_locks), so run it at a quiet moment or with a lock_timeout as in my zero-downtime migrations tutorial. Now repeat the same 100,000-row insert three times on the switched table:

for r in 1 2 3; do keysql -v tbl=orders_v4 -f 06_steady.sql; done

The same table that wrote 83.2 MB and 11,052 full-page images before the switch wrote 21.3 MB and 6 images after it, and the insert took a quarter of the time. New rows now append at the right-hand edge while the 2 million old random keys stay where they were. Check the index:

keysql -f 05a_inspect.sql

  table_name   |  heap  | pkey_index | leaf_pages | leaf_density_pct | fragmentation_pct 
---------------+--------+------------+------------+------------------+-------------------
 orders_bigint | 216 MB | 49 MB      |       6285 |            90.03 |                 0
 orders_v4     | 284 MB | 112 MB     |      14287 |            67.66 |             42.43
 orders_v7     | 233 MB | 69 MB      |       8813 |            89.98 |                 0
(3 rows)

The index now has 14,287 leaf pages at 67.7 percent average density and 42 percent fragmentation. The old random region keeps its half-empty pages, and the pages added after the switch are dense. The index is bigger than in Step 6 because the table has received 800,000 more rows since then. The switch does not repair old pages; only new inserts benefit.

Gotcha: the default only helps code that uses the default

If your application generates the UUID itself, for example with an ORM option like default=uuid.uuid4, PostgreSQL never evaluates its default and nothing changes. Search your code for explicit id generation before you declare victory. Step 10 shows how to generate time-ordered ids from Python.

Step 10: Generate time-ordered ids in Python 3.14

Sometimes the application needs the id before the row exists: to build related rows in one go, to hand an id back to a client, or to use it as an idempotency key. Then the application generates it, and the generator must be time-ordered or you will lose everything Steps 5 to 9 showed. Python 3.14 added uuid.uuid7() to the standard library. The documentation says UUIDs produced by this function “embed a 48-bit timestamp and use a 42-bit counter to guarantee monotonicity within a millisecond.” Save this as py314_uuid7.py. It generates 200,000 ids, checks their order, shows the counter inside the first three, inserts one through psycopg, and asks PostgreSQL to read the time back.

# py314_uuid7.py
"""Generate UUIDv7 values in Python 3.14 or newer, check their order, and let PostgreSQL read the time back."""
import sys
import uuid

import psycopg

from decode_uuid7 import decode

DSN = "host=127.0.0.1 port=55432 user=postgres dbname=keys"

print("python", sys.version.split()[0])
batch = [uuid.uuid7() for _ in range(200_000)]
print("200,000 ids from one process rise strictly:", all(a < b for a, b in zip(batch, batch[1:])))
print("distinct values:", len(set(batch)))

print("first three ids, with the 42-bit counter Python keeps inside them:")
for u in batch[:3]:
    info = decode(str(u))
    counter = (info["rand_a"] << 30) | (info["rand_b"] >> 32)
    print(f"  {u}  version={u.version}  time={u.time}  counter={counter}")

with psycopg.connect(DSN, autocommit=True) as conn:
    conn.execute("CREATE TABLE IF NOT EXISTS client_ids (id uuid PRIMARY KEY)")
    conn.execute("TRUNCATE client_ids")
    conn.execute("INSERT INTO client_ids VALUES (%s)", (batch[0],))
    row = conn.execute(
        "SELECT uuid_extract_version(id), (extract(epoch FROM uuid_extract_timestamp(id)) * 1000)::bigint "
        "FROM client_ids").fetchone()
    print("PostgreSQL reads version", row[0], "and time", row[1], "| Python .time =", batch[0].time,
          "| equal:", row[1] == batch[0].time)

python py314_uuid7.py

Run it with Python 3.14 or newer, in an environment where psycopg is installed. Expected output:

python 3.14.10
200,000 ids from one process rise strictly: True
distinct values: 200000
first three ids, with the 42-bit counter Python keeps inside them:
  01a1c52e-d740-7049-b5f1-f0d3d1047622  version=7  time=1794309543744  counter=79288201427
  01a1c52e-d740-7049-b5f1-f0d4a2d8d8ee  version=7  time=1794309543744  counter=79288201428
  01a1c52e-d740-7049-b5f1-f0d5254e4e10  version=7  time=1794309543744  counter=79288201429
PostgreSQL reads version 7 and time 1794309543744 | Python .time = 1794309543744 | equal: True

Read the output

• The order holds inside one process. All 200,000 ids were distinct and each was larger than the one before.
• The counter is visible. The first three ids share one millisecond (time=1794309543744), and their counters are consecutive integers. CPython’s source comments describe the scheme: a 42-bit counter that goes up by one for each id made in the same millisecond and restarts at a random value when the clock moves on.
• The two systems agree on the time. PostgreSQL read version 7 and the same millisecond that Python reports.
• The layouts differ. PostgreSQL puts a fraction of a millisecond in the 12 bits after the version nibble. Python puts the high bits of its counter there. Both are legal under RFC 9562, which is why Step 2 told you not to parse anything except the timestamp. Across processes, ids sort by millisecond, and inside one millisecond their relative order depends on each generator’s rules.

Gotcha: older Pythons and untrusted clients

Python 3.13 and older have no uuid.uuid7() (the docs mark it “Added in version 3.14”), and my test file skips its Python-generated test there. Either upgrade, or let PostgreSQL generate the id through the column default and read it back with INSERT ... RETURNING id. Third-party packages exist, and I tested none of them. And if a client can send you its own ids, remember that the timestamp inside is only a claim: a client can choose any 48-bit time. Keep created_at as a real column that the server fills in, and do not sort business events by an id you did not generate.

Step 11: Do not use the largest id you have seen as a cursor

A time-ordered id tempts people into a pattern that looks right and fails quietly: a background worker that remembers the largest id it has processed and asks for WHERE id > last_seen ORDER BY id. It works in a test with one writer. This experiment shows what happens with four. Four writer threads insert a row into a bigint table and a UUIDv7 table in the same transaction, then wait a random 0 to 30 ms and commit. A fifth thread polls both tables with the id > last_seen pattern. At the end, the script counts committed rows the poller never saw. Save as late_commit_demo.py and run python late_commit_demo.py:

# late_commit_demo.py
"""Four writers insert rows and commit after a random pause. A poller keeps asking for "id greater than the
last id I saw". Count how many committed rows the poller never sees, for a bigint identity key and a uuidv7 key."""
import random
import threading
import time
import uuid

import psycopg

DSN = "host=127.0.0.1 port=55432 user=postgres dbname=keys"
WRITERS, PER_WRITER, MAX_PAUSE, POLL_EVERY = 4, 250, 0.030, 0.005

SETUP = """
DROP TABLE IF EXISTS ev_bigint, ev_v7;
CREATE TABLE ev_bigint (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, writer int NOT NULL);
CREATE TABLE ev_v7     (id uuid PRIMARY KEY DEFAULT uuidv7(),               writer int NOT NULL);
"""


def writer(number, seed):
    rng = random.Random(seed)
    with psycopg.connect(DSN, autocommit=True) as conn:
        for _ in range(PER_WRITER):
            with conn.transaction():
                conn.execute("INSERT INTO ev_bigint (writer) VALUES (%s)", (number,))
                conn.execute("INSERT INTO ev_v7 (writer) VALUES (%s)", (number,))
                time.sleep(rng.uniform(0, MAX_PAUSE))      # the ids exist now; the commit comes after the pause


def poller(stop, seen):
    last = {"ev_bigint": 0, "ev_v7": uuid.UUID(int=0)}
    with psycopg.connect(DSN, autocommit=True) as conn:
        while True:
            finished = stop.is_set()                         # read the flag BEFORE the last pass
            for table in last:
                rows = conn.execute(f"SELECT id FROM {table} WHERE id > %s ORDER BY id", (last[table],)).fetchall()
                if rows:
                    seen[table].update(r[0] for r in rows)
                    last[table] = rows[-1][0]
            if finished:
                return
            time.sleep(POLL_EVERY)


def main():
    with psycopg.connect(DSN, autocommit=True) as conn:
        conn.execute(SETUP)
    seen = {"ev_bigint": set(), "ev_v7": set()}
    stop = threading.Event()
    watcher = threading.Thread(target=poller, args=(stop, seen))
    watcher.start()
    workers = [threading.Thread(target=writer, args=(i, 1000 + i)) for i in range(WRITERS)]
    for w in workers:
        w.start()
    for w in workers:
        w.join()
    stop.set()
    watcher.join()
    total = WRITERS * PER_WRITER
    with psycopg.connect(DSN, autocommit=True) as conn:
        for table in ("ev_bigint", "ev_v7"):
            stored = conn.execute(f"SELECT count(*) FROM {table}").fetchone()[0]
            missed = stored - len(seen[table])
            print(f"{table:<10} committed={stored}  seen_by_poller={len(seen[table])}  never_seen={missed}"
                  f"  ({100 * missed / total:.1f} percent)")


if __name__ == "__main__":
    main()

I ran it three times. Every run printed two lines, and the table below lists all six:

About 39 percent of committed rows were never seen, and bigint and UUIDv7 behaved the same within the run-to-run spread (38.0 to 40.4 percent). The cause is not the key type. An id is assigned when the INSERT runs, and a row becomes visible to other sessions only when its transaction commits. A writer that inserted early and commits late shows up after the poller has already moved past its id.

Fix the design, not the key. For work queues, use a queue pattern in which workers claim rows by status instead of by position, as in my transactional outbox tutorial. If you must page through data, use keyset pagination over rows that are old enough that no open transaction can still commit them, and read my isolation-levels tutorial if you want the visibility rules explained.

Step 12: Know what the id gives away

Each key type leaks something different to anyone who sees an id in a URL, an email or a screenshot:

• bigint counter: volume and growth. If two receipts show order numbers 41,872,310 and 41,889,975 issued five days apart, anyone can estimate that you take about 3,500 orders a day (17,665 divided by 5).
• UUIDv7: the creation time, to the millisecond. The decoder from Step 2 turned the RFC’s example id into 22 February 2022, 19:22:22.000 UTC, and uuid_extract_timestamp() does the same in SQL. An account id in a profile URL tells the world when that account was created.
• UUIDv4: nothing. That is the one advantage a random id keeps.

None of them is a secret. RFC 9562’s security section is blunt: “Implementations SHOULD NOT assume that UUIDs are hard to guess. For example, they MUST NOT be used as security capabilities (identifiers whose mere possession grants access).” Check authorization on every object access, which my IDOR tutorial builds step by step, and generate real tokens with Python’s secrets module as in the password reset tutorial.

If the creation time of a record is sensitive, one option is to keep the compact time-ordered key as the internal primary key and add a separate random public identifier column for URLs. That index would still receive random inserts, so it pays the cost measured in Step 7 for that one index, and I did not measure this layout.

Step 13: Audit the primary keys in your own database

You now know the three costs to look for: random UUID defaults on big tables, UUIDs stored as text, and ids generated outside the database. This script reads the system catalogs and flags the first two. It prints one line per key and exits with status 1 if there is any WARN, so you can run it against a staging copy in CI. Save as keycheck.py:

# keycheck.py
"""Audit the primary keys of a PostgreSQL database for the two costs measured in this tutorial.

    python keycheck.py "host=127.0.0.1 port=55432 user=postgres dbname=keys" [--min-rows 100000]

Exit status 1 when at least one WARN is printed, so the check can run in CI against a staging copy.
"""
import re
import sys

import psycopg
from psycopg import sql

UUID_TEXT = re.compile(r"^[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}$")
RANDOM_DEFAULTS = {"gen_random_uuid()", "uuidv4()", "uuid_generate_v4()"}
TEXT_TYPES = ("text", "character varying", "character", "citext")

KEYS_SQL = """
SELECT c.oid::regclass::text AS table_name,
       greatest(c.reltuples, 0)::bigint AS est_rows,
       a.attname AS column_name,
       format_type(a.atttypid, a.atttypmod) AS column_type,
       pg_get_expr(d.adbin, d.adrelid) AS default_expr,
       i.indnkeyatts AS key_columns
FROM pg_index AS i
JOIN pg_class AS c ON c.oid = i.indrelid
JOIN pg_namespace AS n ON n.oid = c.relnamespace
JOIN pg_attribute AS a ON a.attrelid = c.oid AND a.attnum = ANY (i.indkey::int2[])
LEFT JOIN pg_attrdef AS d ON d.adrelid = c.oid AND d.adnum = a.attnum
WHERE i.indisprimary AND c.relkind IN ('r', 'p')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
ORDER BY 1, a.attnum
"""


def classify(key, min_rows=100_000):
    """key: dict with table_name, est_rows, column_type, default_expr, key_columns, sample (list of text values).
    Returns a list of (level, message) where level is WARN, INFO or OK."""
    if key["key_columns"] != 1:
        return [("INFO", f"composite primary key of {key['key_columns']} columns: not checked")]
    column_type = key["column_type"]
    default = (key["default_expr"] or "").replace("public.", "")
    big = key["est_rows"] >= min_rows
    if column_type == "uuid":
        if default in RANDOM_DEFAULTS:
            if big:
                return [("WARN", f"random uuid default on about {key['est_rows']:,} rows: new keys land on random index "
                                 "pages. Set the default to uuidv7() on PostgreSQL 18 or newer.")]
            return [("INFO", f"random uuid default, fine at about {key['est_rows']:,} rows; revisit near {min_rows:,}")]
        if default == "uuidv7()":
            return [("OK", "uuidv7() default: new keys arrive in time order")]
        if not default:
            return [("INFO", "no default: the application generates the ids; check that it makes uuid7, not uuid4")]
        return [("INFO", f"uuid key with default {default}: not classified")]
    if column_type in TEXT_TYPES or column_type.startswith(("character varying", "character(")):
        values = [v for v in key["sample"] if v]
        if values and all(UUID_TEXT.match(v) for v in values):
            return [("WARN", "uuid values stored as text: 40 bytes per key against 16 and an index that is "
                             "almost twice as large. Convert the column to the uuid type.")]
    return [("OK", f"{column_type} key")]


def audit(conn, min_rows=100_000):
    results = []
    for table_name, est_rows, column_name, column_type, default_expr, key_columns in conn.execute(KEYS_SQL).fetchall():
        sample = []
        if column_type in TEXT_TYPES or column_type.startswith(("character varying", "character(")):
            query = sql.SQL("SELECT {} FROM {} WHERE {} IS NOT NULL LIMIT 20").format(
                sql.Identifier(column_name), sql.SQL(table_name), sql.Identifier(column_name))
            sample = [row[0] for row in conn.execute(query).fetchall()]
        key = {"table_name": table_name, "est_rows": est_rows, "column_type": column_type,
               "default_expr": default_expr, "key_columns": key_columns, "sample": sample}
        for level, message in classify(key, min_rows):
            results.append((level, table_name, column_name, message))
    return results


def main(argv):
    min_rows = 100_000
    if "--min-rows" in argv:
        at = argv.index("--min-rows")
        min_rows = int(argv[at + 1])
        argv = argv[:at] + argv[at + 2:]
    with psycopg.connect(argv[0], autocommit=True) as conn:
        results = audit(conn, min_rows)
    for level, table, column, message in results:
        print(f"{level:<4} {table}.{column}: {message}")
    return 1 if any(level == "WARN" for level, *_ in results) else 0


if __name__ == "__main__":
    sys.exit(main(sys.argv[1:]))

The rules are deliberately simple. A uuid key whose default is gen_random_uuid(), uuidv4() or uuid_generate_v4() is a WARN when the table has at least 100,000 rows (adjust with --min-rows) and information below that. A key stored as text, varchar or char is a WARN when all of up to 20 sampled values look like UUIDs. A UUID key with no default is an INFO, because the application generates the ids and the script cannot see how. Composite keys are skipped. Row counts come from pg_class.reltuples, which is only as fresh as the last ANALYZE.

Create a few bad tables and run the audit

This file builds a schema with one table of each shape: a random-default table of 200,000 rows, a table whose key is a UUID stored as text, a UUIDv7 table, a bigint table and a composite-key table. Save as 12_audit_demo.sql, run it, then run the audit with a connection string for your lab database:

-- 12_audit_demo.sql
CREATE SCHEMA audit_demo;

CREATE TABLE audit_demo.sessions (
    id      uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id integer NOT NULL
);
INSERT INTO audit_demo.sessions (user_id) SELECT g FROM generate_series(1, 200000) AS g;

CREATE TABLE audit_demo.invoices (
    id          text PRIMARY KEY,
    total_cents integer NOT NULL
);
INSERT INTO audit_demo.invoices SELECT uuidv7()::text, g FROM generate_series(1, 1000) AS g;

CREATE TABLE audit_demo.events (
    id   uuid PRIMARY KEY DEFAULT uuidv7(),
    kind text NOT NULL
);

CREATE TABLE audit_demo.accounts (
    id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email text NOT NULL
);

CREATE TABLE audit_demo.line_items (
    order_id integer,
    line_no  integer,
    PRIMARY KEY (order_id, line_no)
);

ANALYZE audit_demo.sessions;
ANALYZE audit_demo.invoices;

keysql -f 12_audit_demo.sql
python keycheck.py "host=127.0.0.1 port=55432 user=postgres dbname=keys"; echo "exit status: $?"

OK   audit_demo.accounts.id: bigint key
OK   audit_demo.events.id: uuidv7() default: new keys arrive in time order
WARN audit_demo.invoices.id: uuid values stored as text: 40 bytes per key against 16 and an index that is almost twice as large. Convert the column to the uuid type.
INFO audit_demo.line_items.order_id: composite primary key of 2 columns: not checked
INFO audit_demo.line_items.line_no: composite primary key of 2 columns: not checked
WARN audit_demo.sessions.id: random uuid default on about 200,000 rows: new keys land on random index pages. Set the default to uuidv7() on PostgreSQL 18 or newer.
OK   orders_bigint.id: bigint key
OK   orders_v4.id: uuidv7() default: new keys arrive in time order
OK   orders_v7.id: uuidv7() default: new keys arrive in time order

exit status: 1

Two warnings, as designed: audit_demo.sessions has a random default on 200,000 rows, and audit_demo.invoices stores UUIDs as text. The three lab tables from earlier steps pass: orders_v4 shows uuidv7() because of the Step 9 switch.

Fix them and run it again

The first fix is the metadata-only change from Step 9. The second converts the text column to the real type, which rewrites the table under an exclusive lock. That is fine for 1,000 rows and not fine for a big busy table, where you should follow the expand-and-contract recipe from the zero-downtime migrations tutorial. Save as 13_audit_fix.sql:

-- 13_audit_fix.sql
ALTER TABLE audit_demo.sessions ALTER COLUMN id SET DEFAULT uuidv7();
ALTER TABLE audit_demo.invoices ALTER COLUMN id TYPE uuid USING id::uuid;

keysql -f 13_audit_fix.sql
python keycheck.py "host=127.0.0.1 port=55432 user=postgres dbname=keys"; echo "exit status: $?"

OK   audit_demo.accounts.id: bigint key
OK   audit_demo.events.id: uuidv7() default: new keys arrive in time order
INFO audit_demo.invoices.id: no default: the application generates the ids; check that it makes uuid7, not uuid4
INFO audit_demo.line_items.order_id: composite primary key of 2 columns: not checked
INFO audit_demo.line_items.line_no: composite primary key of 2 columns: not checked
OK   audit_demo.sessions.id: uuidv7() default: new keys arrive in time order
OK   orders_bigint.id: bigint key
OK   orders_v4.id: uuidv7() default: new keys arrive in time order
OK   orders_v7.id: uuidv7() default: new keys arrive in time order

exit status: 0

Both warnings are gone and the exit status is 0. The converted invoices key now says INFO, as it should: it has no default, so the application supplies the ids.

Test the audit and then test the tests

A checker that silently misses a risky table is worse than none, so give it tests. This file tests the rules as pure functions and one test reads a real catalog (it skips itself if the scratch server is not running). Save as test_keycheck.py:

# test_keycheck.py
import psycopg
import pytest

from keycheck import audit, classify

DSN = "host=127.0.0.1 port=55432 user=postgres dbname=keys"
SOME_UUID = "0192b3d2-7a4e-7c1a-9d2e-5b7f1c3a8e40"


def key(**changes):
    base = {"table_name": "public.t", "est_rows": 5_000_000, "column_type": "uuid",
            "default_expr": "uuidv4()", "key_columns": 1, "sample": []}
    base.update(changes)
    return base


def levels(**changes):
    return [level for level, _ in classify(key(**changes))]


def test_random_default_on_a_big_table_warns():
    assert levels() == ["WARN"]
    assert levels(default_expr="gen_random_uuid()") == ["WARN"]
    assert levels(default_expr="public.uuid_generate_v4()") == ["WARN"]


def test_random_default_on_a_small_table_is_only_information():
    assert levels(est_rows=99_999) == ["INFO"]
    assert levels(est_rows=100_000) == ["WARN"]


def test_uuidv7_default_is_fine():
    assert levels(default_expr="uuidv7()") == ["OK"]


def test_app_generated_uuid_is_flagged_for_a_look():
    assert levels(default_expr=None) == ["INFO"]


def test_uuid_stored_as_text_warns_only_when_every_sample_is_a_uuid():
    assert levels(column_type="text", default_expr=None, sample=[SOME_UUID, SOME_UUID.upper()]) == ["WARN"]
    assert levels(column_type="text", default_expr=None, sample=[SOME_UUID, "order-17"]) == ["OK"]
    assert levels(column_type="character varying(36)", default_expr=None, sample=[SOME_UUID]) == ["WARN"]
    assert levels(column_type="text", default_expr=None, sample=[SOME_UUID + "x"]) == ["OK"]


def test_integer_keys_and_composite_keys():
    assert levels(column_type="bigint", default_expr=None) == ["OK"]
    assert levels(key_columns=2) == ["INFO"]


@pytest.fixture()
def conn():
    try:
        c = psycopg.connect(DSN, autocommit=True)
    except psycopg.OperationalError:
        pytest.skip("scratch PostgreSQL server is not running")
    c.execute("DROP SCHEMA IF EXISTS kc CASCADE")
    c.execute("CREATE SCHEMA kc")
    yield c
    c.execute("DROP SCHEMA kc CASCADE")
    c.close()


def test_audit_reads_the_real_catalog(conn):
    conn.execute("CREATE TABLE kc.a (id uuid PRIMARY KEY DEFAULT uuidv4())")
    conn.execute("CREATE TABLE kc.b (id uuid PRIMARY KEY DEFAULT uuidv7())")
    conn.execute("CREATE TABLE kc.c (id text PRIMARY KEY)")
    conn.execute("INSERT INTO kc.c VALUES (uuidv7()::text), (uuidv7()::text)")
    conn.execute("CREATE TABLE kc.d (a int, b int, PRIMARY KEY (a, b))")
    conn.execute("CREATE TABLE kc.e (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY)")
    results = {(t, lvl) for lvl, t, _col, _msg in audit(conn, min_rows=0)}
    assert ("kc.a", "WARN") in results          # min_rows=0 makes the empty table count as big
    assert ("kc.b", "OK") in results
    assert ("kc.c", "WARN") in results
    assert ("kc.d", "INFO") in results
    assert ("kc.e", "OK") in results

python -m pytest -q test_keycheck.py

.......                                                                                                                                                            [100%]
7 passed in 0.18s

Passing tests only prove something if they can fail. The last script plants one bug at a time in a copy of keycheck.py, such as an off-by-one threshold, a forgotten uuid_generate_v4(), any in place of all, or a UUID pattern that no longer checks the end of the string, and confirms the test suite notices each one. Save as mutants.py in the same folder:

# mutants.py
"""Plant one bug at a time in a copy of keycheck.py and confirm the test suite notices every one."""
import pathlib
import shutil
import subprocess
import sys
import tempfile

HERE = pathlib.Path(__file__).parent
MUTANTS = [
    ("threshold off by one", "big = key[\"est_rows\"] >= min_rows", "big = key[\"est_rows\"] > min_rows"),
    ("uuid-ossp default forgotten", ', "uuid_generate_v4()"}', "}"),
    ("text check accepts one uuid", "all(UUID_TEXT.match(v) for v in values)", "any(UUID_TEXT.match(v) for v in values)"),
    ("uuid pattern not closed", '{12}$")', '{12}")'),
    ("composite keys not skipped", 'if key["key_columns"] != 1:', 'if key["key_columns"] < 0:'),
    ("schema prefix not stripped", '.replace("public.", "")', ""),
    ("uuidv7 reported as warning", '("OK", "uuidv7() default', '("WARN", "uuidv7() default'),
    ("varchar(36) not recognised", 'column_type.startswith(("character varying", "character("))', "False"),
]


def run(source):
    with tempfile.TemporaryDirectory() as tmp:
        shutil.copy(HERE / "test_keycheck.py", tmp)
        (pathlib.Path(tmp) / "keycheck.py").write_text(source, encoding="utf-8")
        done = subprocess.run([sys.executable, "-m", "pytest", "-q", "-x", "-p", "no:cacheprovider", tmp],
                              capture_output=True, text=True, cwd=tmp)
        return done.returncode


original = (HERE / "keycheck.py").read_text(encoding="utf-8")
print("unmodified code, exit status:", run(original))
caught = 0
for name, old, new in MUTANTS:
    assert original.count(old) >= 1, f"mutation target missing: {name}"
    status = run(original.replace(old, new, 1))
    caught += status != 0
    print(f"{'caught' if status else 'MISSED'}: {name}")
print(f"{caught} of {len(MUTANTS)} planted bugs caught")

python mutants.py

unmodified code, exit status: 0
caught: threshold off by one
caught: uuid-ossp default forgotten
caught: text check accepts one uuid
caught: uuid pattern not closed
caught: composite keys not skipped
caught: schema prefix not stripped
caught: uuidv7 reported as warning
caught: varchar(36) not recognised
8 of 8 planted bugs caught

All eight planted bugs were caught. My first version of this list had a ninth mutant that removed the ^ from the UUID pattern, and no test could fail for it: re.match already anchors at the start, so the change did nothing. I replaced it with the end-of-string mutant above, which a test (a UUID followed by an extra character) does catch.

A short decision guide

The mistakes this lab found

1. Storing UUIDs as text. The index was 1.87 times larger (Step 6).
2. Changing the database default while the application still supplies uuid4 values. The default is never evaluated, so nothing improves (Steps 9 and 10).
3. Treating id order as commit order. A poller missed about 39 percent of committed rows, for both key types (Step 11).
4. Treating any UUID as a secret. The RFC says they must not be used as security capabilities (Step 12).
5. Judging the key on a small table. The random key looked cheap at 50,000 rows (3.3 MB of WAL against 1.9 MB for 10,000 inserts) and cost 22 times the WAL at 3.2 million rows (Step 7).
6. Parsing the non-timestamp bits or trusting client-supplied times. Python and PostgreSQL fill those bits differently (Steps 2 and 10).

Check that everything works end to end

• Step 1: the version line starts with PostgreSQL 18. and uuidv7() returns a value whose third group starts with 7.
• Step 6: pgstatindex() reports about 90 percent leaf density for bigint and UUIDv7 and about 71 percent for UUIDv4.
• Step 7: after CHECKPOINT, a 100,000-row insert reports 9,730 full-page images for UUIDv4 and 4 for UUIDv7 (your exact numbers will differ a little, and the ratio should be large).
• Step 9: ALTER TABLE ... SET DEFAULT uuidv7() finishes in milliseconds, and the next insert reports a handful of images instead of thousands.
• Step 13: python keycheck.py ... exits with status 0 once the demo tables are fixed, pytest reports 7 passed, and mutants.py reports 8 of 8.

Clean up

Stop the scratch server and delete its folder. On Windows use pg_ctl.exe and remove the keylab folder with Explorer or Remove-Item -Recurse.

pg_ctl -D keylab -m fast stop
rm -rf keylab

What I did not measure

• One writer at a time. Many concurrent writers all inserting into the right-hand leaf page of a time-ordered index could contend with each other, and this lab, which used a single writer, says nothing about that.
• One machine, Windows 11, with a deliberately small shared_buffers and a generous operating-system cache. Linux, slower disks and bigger memory will shift the absolute numbers. The cause (random leaf pages and full-page images) does not depend on the platform, but I only confirmed it here.
• PostgreSQL 18.6 only. No older version, no partitioned tables, no replication and no uuid-ossp or third-party generators. wal_compression was tested with lz4 alone.
• Timings of a few hundred milliseconds are noisy; WAL byte counts and full-page image counts are much steadier across runs, which is why the conclusions lean on them.

Where to go next

• See the data structure behind the numbers: How to build a B+tree index in Python explains why random inserts leave pages about 70 percent full.
• Learn to read the plans used here: How to find a slow query in PostgreSQL 18 and read its EXPLAIN ANALYZE plan.
• Change a live schema safely: zero-downtime schema migrations on PostgreSQL 18.
• Read the specifications behind this tutorial: RFC 9562 (sections 2.1, 6.2, 6.12, 6.13 and 8), the PostgreSQL 18 UUID functions page and the Python uuid documentation.