Alexander does product marketing for databases at Aiven. Throughout his career he's worked across marketing, sales, analytics, data engineering and more.
Jay is a Staff Developer Advocate at Aiven. Jay has served as a keynote speaker and an avid member of the Python Community. When away from the keyboard, Jay can often be found cheering on their favorite baseball team.
Should you use UUIDs as the primary key in your database? You might have heard they are terrible for performance, which is often true for traditional UUIDv4.
However, the introduction of UUIDv7 fixes many of the previous issues of UUIDv4. Let’s therefore explore what they are and why it might be a good idea to use them.
What is UUIDv7?
UUIDv7 is a relatively new type of Universally Unique Identifier (UUID). It was introduced to Postgres in version 18 to mitigate performance issues associated with using traditional UUIDs, UUIDv4, as database primary keys.
Unlike the traditional UUIDv4, which is completely random, UUIDv7 incorporates a timestamp as the most significant part of its 128-bit structure, allowing for natural sortability based on the creation time.
This has several advantages we will cover in the post, but rather than just read about it, you can try it out for yourself using the commands below.
Creating our Crab store
Let's create a new free Aiven for PostgreSQL® service for our demo Crab store using the Aiven CLI.
Loading code...
Once that's done, we can create two tables, with the only difference being that one table uses UUIDv4 and the other uses UUIDv7.
Loading code...
Exploring the performance benefits
To use the function we created and compare results for yourself, you can run the following commands. Let's start by analyzing the process of creating 10000 entries in our table using UUIDv4.
Loading code...
Now let's do the same thing with the table using UUIDv7.
Loading code...
Doing this, we can see that UUIDv4's completely random structure significantly impacts performance by forcing random insertions into B-tree indexes, leading to index page splits and reduced cache efficiency.
UUIDv7 solves this by including a timestamp, making new UUIDs naturally sortable by creation time. This allows indexes to perform efficient sequential inserts, similar to auto-incrementing integers. The resulting performance benefits are significant, including reduced index fragmentation, improved cache locality, and better cache utilization.
UUIDv7's ordering also simplifies queries, eliminating the need for separate timestamp columns for sorting, as it is naturally sorted by time. This structure is ideal for applications that require high insert rates and efficient querying.
We can see by sorting the UUIDv4 table by the acquisition and then sorting the UUIDv7 table by the id.
Loading code...
UUIDv7s is becoming widely available
Aside from just being in Postgres, it's also supported across a variety of programming languages and platforms. For example, since the release of Python 3.14 this month, you can use it with the Python standard library.
Loading code...
To check that it's the same structure and version as the one in Postgres, we can use the built-in uuid_extract_version function using the following commands.
Loading code...
This shows that UUIDs have a consistent format across platforms, which means that you can use them across your systems, just like the typical UUIDv4.
What can go wrong with using UUIDv7
Using UUIDv7 is generally discouraged for security when the primary key is exposed to end users in external-facing applications or APIs. The main issue is that UUIDv7 incorporates a 48-bit Unix timestamp as its most significant part, meaning the identifier itself leaks the record's creation time.
This leakage is primarily a privacy concern. Attackers can use the timing data as metadata for de-anonymization or account correlation, potentially revealing activity patterns or growth rates within an organization. While UUIDv7 still contains random data, relying on the primary key for security is considered a flawed approach. Experts recommend using UUIDv7 only for internal keys and exposing a separate, truly random UUIDv4 as an external identifier.
How to migrate from UUIDv4 to UUIDv7
When migrating from UUIDv4 to UUIDv7, it's recommended to prioritize careful migration planning, as there will likely be a need for application changes to handle the new format.
Since UUIDv7 is timestamp-ordered, unlike the random UUIDv4, consider the impact on existing indexes and queries. It's therefore recommended to test performance thoroughly with your specific workload.
A few things to be aware of are that UUIDv7 relies on system clocks, requiring clock synchronization, like NTP, and that the timestamp precision is limited to the millisecond.
Finally, it's essential to update any foreign keys and external systems that depend on the specific UUID format to make sure nothing breaks.
Conclusion
As we've seen, the introduction of UUIDv7 in Postgres 18 addresses the performance drawbacks associated with using the completely random UUIDv4 as a primary key. By incorporating a timestamp, UUIDv7 ensures new identifiers are naturally sortable, leading to efficient sequential inserts, improved cache utilization, and reduced index fragmentation.
Just remember the important privacy caveat: due to the leaked creation time, UUIDv7 is best used for internal keys, not external-facing applications.
CREATETABLE crab_inventory_4
(id UUID PRIMARYKEYDEFAULT gen_random_uuid(),crab_type VARCHAR(75)NOTNULL,acquisition TIMESTAMPDEFAULT NOW;CREATETABLE crab_inventory_7
(id UUID PRIMARYKEYDEFAULT uuidv7(),crab_type VARCHAR(75)NOTNULL);CREATEORREPLACEFUNCTION insert_random_crabs_explain(table_name TEXT, num_rows INT)RETURNSTABLE(query_plan TEXT)AS $$
BEGINRETURN QUERY EXECUTEformat('EXPLAIN ANALYZE
INSERT INTO %I (crab_type)
SELECT CASE (random() * 9)::int
WHEN 0 THEN ''Dungeness Crab''
WHEN 1 THEN ''Blue Crab''
WHEN 2 THEN ''Hermit Crab''
WHEN 3 THEN ''Fiddler Crab''
WHEN 4 THEN ''Coconut Crab''
WHEN 5 THEN ''Snow Crab''
WHEN 6 THEN ''King Crab''
WHEN 7 THEN ''Stone Crab''
WHEN 8 THEN ''Spider Crab''
ELSE ''Horseshoe Crab''
END
FROM generate_series(1, %s)', table_name, num_rows
);END;$$ LANGUAGE plpgsql;
-- create 10000 entries with uuidv7
> EXPLAIN ANALYZE (SELECT * FROM insert_random_crabs('crab_inventory_7', 10000));
+-----------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
|-----------------------------------------------------------------------------------------------------------------------|
| Function Scan on insert_random_crabs (cost=0.25..0.26 rows=1 width=4) (actual time=64.746..64.746 rows=1.00 loops=1) |
| Buffers: shared hit=20415 dirtied=112 written=112 |
| Planning Time: 0.035 ms |
| Execution Time: 64.834 ms |
+-----------------------------------------------------------------------------------------------------------------------+
EXPLAIN 4
Time: 0.094s
> table crab_inventory_7 limit 3;
+--------------------------------------+----------------+
| id | crab_type |
|--------------------------------------+----------------|
| 0199c9cc-31a7-7703-9703-91b7151a3698 | Dungeness Crab |
| 0199c9cc-31a8-7442-805c-e4b64c3766de | Stone Crab |
| 0199c9cc-31a8-74be-a3d4-1f2c18a91629 | Spider Crab |
+--------------------------------------+----------------+
-- Sorting using a timestamp (UUIDv4 primary key)
> EXPLAIN ANALYZE SELECT * from crab_inventory_4
ORDER BY acquisition;
+-------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
|-------------------------------------------------------------------------------------------------------------------------------|
| Sort (cost=6799.80..6949.80 rows=60000 width=35) (actual time=30.339..47.615 rows=60000.00 loops=1) |
| Sort Key: acquisition |
| Sort Method: external merge Disk: 2952kB |
| Buffers: shared hit=503, temp read=369 written=371 |
| -> Seq Scan on crab_inventory_4 (cost=0.00..1100.00 rows=60000 width=35) (actual time=0.016..4.946 rows=60000.00 loops=1) |
| Buffers: shared hit=500 |
| Planning: |
| Buffers: shared hit=25 |
| Planning Time: 0.397 ms |
| Execution Time: 52.249 ms |
+-------------------------------------------------------------------------------------------------------------------------------+
-- Sorting using the UUIDv7 primary key
> EXPLAIN ANALYZE SELECT * from crab_inventory_7
ORDER BY id;
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
|--------------------------------------------------------------------------------------------------------------------------------------------------------|
| Index Scan using crab_inventory_7_pkey on crab_inventory_7 (cost=0.29..1574.29 rows=60000 width=27) (actual time=0.012..14.191 rows=60000.00 loops=1) |
| Index Searches: 1 |
| Buffers: shared hit=673 |
| Planning: |
| Buffers: shared hit=17 |
| Planning Time: 0.173 ms |
| Execution Time: 16.597 ms |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
# /// script# requires-python = ">=3.14"# ///import uuid
print(uuid.uuid7())# Gives us this id - 0199c489-c5b3-70aa-9ad0-37afd22ef141
-- Check the verion of the id we created in PythonSELECT uuid_extract_version ('0199c489-c5b3-70aa-9ad0-37afd22ef141');
uuid_extract_version
----------------------7-- Comparing that against a PG generated idSELECT uuid_extract_version (uuidv7());
uuid_extract_version
----------------------7
