{"id":10348,"date":"2025-12-05T12:43:58","date_gmt":"2025-12-05T11:43:58","guid":{"rendered":"https:\/\/www.credativ.de\/?p=10348"},"modified":"2025-12-05T12:53:46","modified_gmt":"2025-12-05T11:53:46","slug":"a-deeper-look-at-old-uuidv4-vs-new-uuidv7-in-postgresql-18","status":"publish","type":"post","link":"https:\/\/www.credativ.de\/en\/blog\/postgresql-en\/a-deeper-look-at-old-uuidv4-vs-new-uuidv7-in-postgresql-18\/","title":{"rendered":"A deeper look at old UUIDv4 vs new UUIDv7 in PostgreSQL 18"},"content":{"rendered":"\n
\n

In the past there have been many discussions about using UUID as a primary key in PostgreSQL<\/a>. For some applications, even a BIGINT column does not have sufficient range: it is a signed 8\u2011byte integer with range \u22129,223,372,036,854,775,808 to +9,223,372,036,854,775,807. Although these values look big enough, if we think about web services that collect billions or more records daily, this number becomes less impressive. Simple integer values can also cause conflicts of values in distributed system, in Data Lakehouses when combining data from multiple source databases etc.<\/p>\n<\/div>\n\n\n\n

However, the main practical problem with UUIDv4 as a primary key in PostgreSQL was not lack of range, but the complete randomness of the values. This randomness causes frequent B\u2011tree page splits, a highly fragmented primary key index, and therefore a lot of random disk I\/O. There have already been many articles and conference talks describing this problem. What many of these resources did not do, however, was dive deep into the on\u2011disk structures. That\u2019s what I wanted to explore here.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

What are UUIDs<\/h4>\n\n\n\n
\n

UUID (Universally Unique Identifier) is a 16\u2011byte integer value (128 bits), which has 2^128 possible combinations (approximately 3.4 \u00d7 10^38). This range is so large that, for most applications, the probability of a duplicate UUID is practically zero. Wikipedia shows a calculation demonstrating that the probability to find a duplicate within 103 trillion version\u20114 UUIDs is about one in a billion. Another often\u2011quoted rule of thumb is that to get a 50% chance of one collision, you\u2019d have to generate roughly 1 billion UUIDs every second for about 86 years.<\/p>\n<\/div>\n\n\n\n

Values are usually represented as a 36\u2011character string with hexadecimal digits and hyphens, for example: f47ac10b-58cc-4372-a567-0e02b2c3d479<\/strong>. The canonical layout is 8\u20114\u20114\u20114\u201112 characters. The first character in the third block and the first character in the fourth block have special meaning: xxxxxxxx-xxxx-Vxxx-Wxxx-xxxxxxxxxxxx<\/strong> – V<\/strong> marks UUID version (4 for UUIDv4, 7 for UUIDv7, etc.), W<\/strong> encodes the variant in its upper 2 or 3 bits (the layout family of the UUID).<\/p>\n\n\n\n

Until PostgreSQL 18, the common way to generate UUIDs in PostgreSQL was to use version\u20114 (for example via gen_random_uuid() or uuid_generate_v4() from extensions). PostgreSQL 18 introduces native support for the new time\u2011ordered UUIDv7 via uuidv7() function, and also adds uuidv4() as a built\u2011in alias for older gen_random_uuid() function. UUID version 4 is generated completely randomly (except for the fixed version and variant bits), so there is no inherent sequence in the values. UUID version 7 generates values that are time\u2011ordered, because the first 48 bits contain a big\u2011endian Unix epoch timestamp with roughly millisecond granularity, followed by additional sub\u2011millisecond bits and randomness.<\/p>\n\n\n\n

\"Slonik,<\/figure>\n\n\n\n

Test setup in PostgreSQL 18<\/h4>\n\n\n\n

I will show concrete results using a simple test setup – 2 different tables with column “id” containing generated UUID value (either v4 or v7), used as primary key, column “ord” with sequentially generated bigint, preserving the row creation order.<\/p>\n\n\n\n

\n
-- UUIDv4 (completely random keys)\nCREATE TABLE uuidv4_demo (\n    id uuid PRIMARY KEY DEFAULT uuidv4(), -- alias of gen_random_uuid()\n    ord bigint GENERATED ALWAYS AS IDENTITY\n);\n\n-- UUIDv7 (time-ordered keys)\nCREATE TABLE uuidv7_demo (\n    id uuid PRIMARY KEY DEFAULT uuidv7(),\n    ord bigint GENERATED ALWAYS AS IDENTITY\n);\n\n-- 1M rows with UUIDv4\nINSERT INTO uuidv4_demo (id) SELECT uuidv4() FROM generate_series(1, 1000000);\n\n-- 1M rows with UUIDv7\nINSERT INTO uuidv7_demo (id) SELECT uuidv7() FROM generate_series(1, 1000000);\n\nVACUUM ANALYZE uuidv4_demo;\nVACUUM ANALYZE uuidv7_demo;<\/pre>\n<\/blockquote>\n\n\n\n
Query\u2011level performance: EXPLAIN ANALYZE<\/h4>\n\n\n\n
As the first step, let’s compare the costs of ordering by UUID for the two tables:<\/p>\n\n\n\n
\n
-- UUIDv4\nEXPLAIN (ANALYZE, BUFFERS) SELECT * FROM uuidv4_demo ORDER BY id;\n\nIndex Scan using uuidv4_demo_pkey on uuidv4_demo (cost=0.42..60024.31 rows=1000000 width=24) (actual time=0.031..301.163 rows=1000000.00 loops=1)\n  Index Searches: 1\n  Buffers: shared hit=1004700 read=30\nPlanning Time: 0.109 ms\nExecution Time: 318.005 ms\n\n-- UUIDv7\nEXPLAIN (ANALYZE, BUFFERS) SELECT * FROM uuidv7_demo ORDER BY id;\n\nIndex Scan using uuidv7_demo_pkey on uuidv7_demo (cost=0.42..36785.43 rows=1000000 width=24) (actual time=0.013..96.177 rows=1000000.00 loops=1)\n  Index Searches: 1\n  Buffers: shared hit=2821 read=7383\nPlanning Time: 0.040 ms\nExecution Time: 113.305 ms<\/pre>\n<\/blockquote>\n\n\n\n
The exact buffer numbers depend on caching effects, but one thing is clear in this run: the index scan over UUIDv7 needs roughly 100 times less buffer hits and is around three times faster (113 ms vs 318 ms) for the same million\u2011row ORDER BY id. This is the first sign that UUIDv7 is a very viable solution for a primary key when we need to replace a BIGINT column with something that has a much larger space and uniqueness, while still behaving like a sequential key from the point of view of the index.<\/p>\n\n\n\n
Speed of Inserts – simple benchmarking<\/h4>\n\n\n\n
Originally I wanted to make more sophisticated tests, but even very basic naive benchmark showed huge difference in speed of inserts. I compared time taken to insert 50 million rows into empty table, then again, into the table with 50 million existing rows.<\/p>\n\n\n\n
\n
INSERT INTO uuidv4_demo (id) SELECT uuidv4() FROM generate_series(1, 50000000);\nINSERT INTO uuidv7_demo (id) SELECT uuidv7() FROM generate_series(1, 50000000);\n\n-- UUID v4                                 -- UUID v7\n                                Empty table\nInsert time: 1239839.702 ms (20:39.840)    Insert time: 106343.314 ms (01:46.343)\nTable size: 2489 MB                        Table size: 2489 MB\nIndex size: 1981 MB                        Index size: 1504 MB\n\n                            Table with 50M rows\nInsert time: 2776880.790 ms (46:16.881)    Insert time: 100354.087 ms (01:40.354)\nTable size: 4978 MB                        Table size: 4978 MB\nIndex size: 3956 MB                        Index size: 3008 MB<\/pre>\n<\/blockquote>\n\n\n\n
As we can see, speed of inserts is radically different. Insertion of the first 50 million rows into empty table took only 1:46 minutes for UUIDv7, but already 20 minutes for UUIDv4. Second batch showed even 2 times bigger difference.<\/p>\n\n\n\n
How values are distributed in the table<\/h4>\n\n\n\n
These results indicate huge differences in indexes. So let’s analyze it. First we will check how the values are distributed in the table, I use the following query for both tables (just switching the table name):<\/p>\n\n\n\n
\n
SELECT\n    row_number() OVER () AS seq_in_uuid_order,\n    id,\n    ord,\n    ctid\nFROM uuidv4_demo\nORDER BY id\nLIMIT 20;<\/pre>\n<\/blockquote>\n\n\n\n
Column seq_in_uuid_order is just the row number in UUID order, ord is the insertion order, ctid shows the physical location of each tuple in the heap: (block_number, offset_in_block).<\/p>\n\n\n\n
UUIDv4: random UUID order \u21d2 random heap access<\/h4>\n\n\n\n
How do the results look for UUIDv4?<\/p>\n\n\n\n
\n
 seq_in_uuid_order |                 id                   |   ord  |    ctid \n-------------------+--------------------------------------+--------+------------\n                 1 | 00000abf-cc8e-4cb2-a91a-701a3c96bd36 | 673969 | (4292,125)\n                 2 | 00001827-16fe-4aee-9bce-d30ca49ceb1d | 477118 | (3038,152)\n                 3 | 00001a84-6d30-492f-866d-72c3b4e1edff | 815025 | (5191,38)\n                 4 | 00002759-21d1-4889-9874-4a0099c75286 | 879671 | (5602,157)\n                 5 | 00002b44-b1b5-473f-b63f-7554fa88018d | 729197 | (4644,89)\n                 6 | 00002ceb-5332-44f4-a83b-fb8e9ba73599 | 797950 | (5082,76)\n                 7 | 000040e2-f6ac-4b5e-870a-63ab04a5fa39 | 160314 | (1021,17)\n                 8 | 000053d7-8450-4255-b320-fee8d6246c5b | 369644 | (2354,66)\n                 9 | 00009c78-6eac-4210-baa9-45b835749838 | 463430 | (2951,123)\n                10 | 0000a118-f98e-4e4a-acb3-392006bcabb8 |  96325 | (613,84)\n                11 | 0000be99-344b-4529-aa4c-579104439b38 | 454804 | (2896,132)\n                12 | 00010300-fcc1-4ec4-ae16-110f93023068 |  52423 | (333,142)\n                13 | 00010c33-a4c9-4612-ba9a-6c5612fe44e6 |  82935 | (528,39)\n                14 | 00011fa2-32ce-4ee0-904a-13991d451934 | 988370 | (6295,55)\n                15 | 00012920-38c7-4371-bd15-72e2996af84d | 960556 | (6118,30)\n                16 | 00014240-7228-4998-87c1-e8b23b01194a |  66048 | (420,108)\n                17 | 00014423-15fc-42ca-89bd-1d0acf3e5ad2 | 250698 | (1596,126)\n                18 | 000160b9-a1d8-4ef0-8979-8640025c0406 | 106463 | (678,17)\n                19 | 0001711a-9656-4628-9d0c-1fb40620ba41 | 920459 | (5862,125)\n                20 | 000181d5-ee13-42c7-a9e7-0f2c52faeadb | 513817 | (3272,113)<\/pre>\n<\/blockquote>\n\n\n\n
Values are distributed completely randomly. Reading rows in UUID order practically does not make sense here and leads directly into random heap access for queries that use the primary key index.<\/p>\n\n\n\n
UUIDv7: UUID order follows insertion order<\/h4>\n\n\n\n
On the other hand, UUIDv7 values are generated in a clear sequence:<\/p>\n\n\n\n
\n
 seq_in_uuid_order |                  id                  | ord | ctid \n-------------------+--------------------------------------+-----+--------\n                 1 | 019ad94d-0127-7aba-b9f6-18620afdea4a |   1 | (0,1)\n                 2 | 019ad94d-0131-72b9-823e-89e41d1fad73 |   2 | (0,2)\n                 3 | 019ad94d-0131-7384-b03d-8820be60f88e |   3 | (0,3)\n                 4 | 019ad94d-0131-738b-b3c0-3f91a0b223a8 |   4 | (0,4)\n                 5 | 019ad94d-0131-7391-ab84-a719ca98accf |   5 | (0,5)\n                 6 | 019ad94d-0131-7396-b41d-7f9f27a179c4 |   6 | (0,6)\n                 7 | 019ad94d-0131-739b-bdb3-4659aeaafbdd |   7 | (0,7)\n                 8 | 019ad94d-0131-73a0-b271-7dba06512231 |   8 | (0,8)\n                 9 | 019ad94d-0131-73a5-8911-5ec5d446c8a9 |   9 | (0,9)\n                10 | 019ad94d-0131-73aa-a4a3-0e5c14f09374 |  10 | (0,10)\n                11 | 019ad94d-0131-73af-ac4b-3710e221390e |  11 | (0,11)\n                12 | 019ad94d-0131-73b4-85d6-ed575d11e9cf |  12 | (0,12)\n                13 | 019ad94d-0131-73b9-b802-d5695f5bf781 |  13 | (0,13)\n                14 | 019ad94d-0131-73be-bcb0-b0775dab6dd4 |  14 | (0,14)\n                15 | 019ad94d-0131-73c3-9ec8-c7400b5c8983 |  15 | (0,15)\n                16 | 019ad94d-0131-73c8-b067-435258087b3a |  16 | (0,16)\n                17 | 019ad94d-0131-73cd-a03f-a28092604fb1 |  17 | (0,17)\n                18 | 019ad94d-0131-73d3-b4d5-02516d5667b5 |  18 | (0,18)\n                19 | 019ad94d-0131-73d8-9c41-86fa79f74673 |  19 | (0,19)\n                20 | 019ad94d-0131-73dd-b9f1-dcd07598c35d |  20 | (0,20)<\/pre>\n<\/blockquote>\n\n\n\n
Here, seq_in_uuid_order, ord, and ctid all follow each other nicely – ord increases by 1 for each row, ctid moves sequentially through the first heap page, and UUIDs themselves are monotonic because of the timestamp prefix. For index scans on the primary key, this means Postgres can walk the heap in a much more sequential way than with UUIDv4.<\/p>\n\n\n\n
How sequential are these values statistically?<\/h4>\n\n\n\n
After VACUUM ANALYZE, I ask the planner what it thinks about the correlation between id and the physical order:<\/p>\n\n\n\n
\n
SELECT\n    tablename,\n    attname,\n    correlation\nFROM pg_stats\nWHERE tablename IN ('uuidv4_demo', 'uuidv7_demo')\nAND attname = 'id'\nORDER BY tablename, attname;<\/pre>\n<\/blockquote>\n\n\n\n
Result:<\/p>\n\n\n\n
\n
   tablename | attname | correlation \n-------------+---------+---------------\n uuidv4_demo |      id | -0.0024808696\n uuidv7_demo |      id |             1<\/pre>\n<\/blockquote>\n\n\n\n
The statistics confirm what we just saw:<\/p>\n\n\n\n