Exploring PostgreSQL 18's new UUIDv7 support

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.

avn service create -t pg --cloud do-nyc --plan pg:free-1-1gb 'crab-store' -c 'pg_version=18'

Once that's done, we can create two tables, with the only difference being that one table uses UUIDv4 and the other uses UUIDv7.

CREATE TABLE crab_inventory_4 
(id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
crab_type VARCHAR(75) NOT NULL,
acquisition TIMESTAMP DEFAULT NOW; 

CREATE TABLE crab_inventory_7 
(id UUID PRIMARY KEY DEFAULT uuidv7(), 
crab_type VARCHAR(75) NOT NULL);

CREATE OR REPLACE FUNCTION insert_random_crabs_explain(table_name TEXT, num_rows INT) 
RETURNS TABLE(query_plan TEXT) AS $$ 
BEGIN 
    RETURN QUERY EXECUTE format(
        '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;

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.

-- create 10000 entries with uuidv4
> EXPLAIN ANALYZE (SELECT * FROM insert_random_crabs('crab_inventory_4', 10000));

+-----------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN                                                                                                            |
|-----------------------------------------------------------------------------------------------------------------------|
| Function Scan on insert_random_crabs  (cost=0.25..0.26 rows=1 width=4) (actual time=88.996..88.997 rows=1.00 loops=1) |
|   Buffers: shared hit=40220 dirtied=397 written=103                                                                   |
| Planning Time: 0.033 ms                                                                                               |
| Execution Time: 89.084 ms                                                                                             |
+-----------------------------------------------------------------------------------------------------------------------+

> table crab_inventory_4 limit 3;

+--------------------------------------+-------------+----------------------------+
| id                                   | crab_type   | acquisition                |
|--------------------------------------+-------------+----------------------------|
| bdf3b756-938b-454c-a9a6-252279baa310 | King Crab   | 2025-10-08 15:23:27.002235 |
| 2fd0efc3-5e30-4116-883c-5d5836a6bf0e | Spider Crab | 2025-10-08 15:23:27.002235 |
| f7db0247-8741-4156-9e95-b37a23989bae | Spider Crab | 2025-10-08 15:23:27.002235 |
+--------------------------------------+-------------+----------------------------+

Now let's do the same thing with the table using UUIDv7.

-- 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    |
+--------------------------------------+----------------+

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.

-- 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                                                                                                                              |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+

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.

# /// script
# requires-python = ">=3.14"
# ///

import uuid

print(uuid.uuid7())


# Gives us this id - 0199c489-c5b3-70aa-9ad0-37afd22ef141

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.

-- Check the verion of the id we created in Python
SELECT uuid_extract_version ('0199c489-c5b3-70aa-9ad0-37afd22ef141');
 
uuid_extract_version
----------------------
                    7

-- Comparing that against a PG generated id
SELECT uuid_extract_version (uuidv7()); 

uuid_extract_version
----------------------
                    7

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.

Postgres 18 comes with a lot more exciting features, and you can leverage these exciting new features and explore what else Postgres 18 has to offer with Aiven today.