Introduction

In recent versions of PostgreSQL, we have seen many features focused on improving performance and scalability. One such important addition in PostgreSQL 18 is UUIDv7 support.UUIDs are widely used in modern applications, especially in distributed systems where generating unique identifiers without coordination is important. Most applications today rely on UUIDv4, which is simple and widely supported.

However, as systems grow and handle large volumes of data, the limitations of UUIDv4 become more visible, particularly in terms of insert performance and index efficiency.To address this, PostgreSQL 18 introduces UUIDv7, a time-ordered UUID format designed to improve database performance while maintaining global uniqueness.

Understanding UUID (v4 vs v7)

A UUID (Universally Unique Identifier) is a 128-bit value used to uniquely identify records in a database. It is commonly used as a primary key because it can be generated without any central coordination, making it ideal for distributed systems.

The Hidden Problem with UUIDv4

UUIDv4 is one of the most popular choices for primary keys in PostgreSQL. It is simple, globally unique, and requires no coordination between servers, making it ideal for distributed systems.

However, it has a serious hidden problem.

UUIDv4 generates completely random 128-bit values. While this randomness ensures uniqueness, it creates performance challenges inside PostgreSQL.

Here’s what actually happens:

• Every new UUIDv4 is inserted at a random location in the B-tree index
• This leads to frequent page splits and index fragmentation
• Index pages become scattered across disk
• Cache efficiency drops, increasing I/O and CPU usage

Over time, this results in slower inserts and growing index size, especially in large tables or high-write workloads. In real-world tests on large datasets (for example, tens of millions of rows), UUIDv4 can lead to noticeably slower insert performance and higher index overhead compared to sequential or time-ordered identifiers.

Meet UUIDv7: A Smarter Alternative

UUIDv7 is a newer version of UUID designed to address the performance limitations of UUIDv4. It combines a timestamp with random bits, making the generated values time-ordered while still globally unique.

Unlike UUIDv4, UUIDv7 values are not completely random. The timestamp portion ensures that new IDs are generated in increasing order, which aligns better with how PostgreSQL indexes work.

Here’s what changes with UUIDv7:

• New values are inserted near the end of the B-tree index
• Page splits are significantly reduced
• Index structure remains more compact and organized
• Cache efficiency improves, reducing I/O and CPU usage

As a result, UUIDv7 provides much better insert performance and more efficient indexing, especially in high-write workloads and large tables. In simple terms, UUIDv7 keeps the benefits of UUIDs while improving how data is stored and accessed inside PostgreSQL.

Where UUIDv7 Makes the Biggest Impact

UUIDv7 provides the most benefit in workloads where UUIDv4’s randomness creates performance issues, especially in systems with high insert rates, large datasets, and time-based access patterns.

UUIDv7 works best in systems with high write activity, large tables, and frequent queries on recent data, where it can significantly improve insert performance, reduce index fragmentation, and enhance overall query efficiency.

UUIDv7 in PostgreSQL 18 (Built-in Support & Usage)

PostgreSQL 18 introduces native support for UUIDv7, allowing time-ordered UUID generation without requiring extensions. This makes it easier to adopt UUID-based primary keys while improving index behavior compared to UUIDv4.

Native UUIDv7 Functions

PostgreSQL 18 provides built-in functions such as uuidv7() and uuid_extract_timestamp().

postgres=# SELECT version();
                                                                                  version                                                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 18.3 (Ubuntu 18.3-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04.3) 11.4.0, 64-bit
(1 row)
postgres=# SELECT uuidv7();
               uuidv7               
--------------------------------------
0196ea4a-6f32-7fd0-a9d9-9c815a0750cd
(1 row)
postgres=# SELECT uuid_extract_timestamp(uuidv7());
              uuid_extract_timestamp              
--------------------------------------------
2025-05-19 20:50:40.381+00
(1 row)

The generated UUIDv7 value contains an embedded timestamp, which ensures that values are mostly ordered by creation time and improves index locality.

Table Creation with UUIDv7 Primary Key

UUIDv7 can be directly used as a default value for primary keys.

postgres=# INSERT INTO orders (customer_id, amount) 
           SELECT gen_random_uuid(), random()*1000 
           FROM generate_series(1,10000);
INSERT 0 10000
postgres=# SELECT id, uuid_extract_timestamp(id), order_date 
           FROM orders LIMIT 3;
                                   id                                   | uuid_extract_timestamp |      order_date       
---------------------------------------------------------------------+-----------------------
 0196ea4a-6f32-7fd0-a9d9-9c815a0750cd                           | 2025-05-19 20:50:40 | 2025-05-19 20:50:40
 0196ea4a-6f33-7fd0-a9d9-9c815a0750cd                           | 2025-05-19 20:50:40 | 2025-05-19 20:50:40
 0196ea4a-6f34-7fd0-a9d9-9c815a0750cd                           | 2025-05-19 20:50:40 | 2025-05-19 20:50:40
(3 rows)

The extracted timestamp aligns closely with the actual insertion time, showing that UUIDv7 preserves temporal ordering.

Index Behavior and Size Comparison

Time-ordered inserts improve index structure and reduce fragmentation compared to UUIDv4.

postgres=# CREATE TABLE legacy_orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id UUID NOT NULL
);
CREATE TABLE
postgres=# INSERT INTO legacy_orders (customer_id) 
           SELECT gen_random_uuid() FROM generate_series(1,10000);
INSERT 0 10000
postgres=# SELECT 
  schemaname, relname, 
  pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes 
WHERE relname LIKE '%orders_pkey';
 schemaname |    relname      | index_size 
------------+-----------------+------------
 public     | orders_pkey     | 288 kB
 public     | legacy_orders_pkey | 384 kB
(2 rows)

Because UUIDv7 inserts are mostly sequential, index pages remain more compact and better organized, resulting in smaller index size and improved efficiency.

UUIDv7 in PostgreSQL 18 provides native support without extensions while generating time-ordered values that reduce index fragmentation and keep indexes more compact. This leads to better insert and query performance, allowing developers and DBAs to improve efficiency simply by changing how IDs are generated, without major changes to application logic.

Query Optimization & Index Design with UUIDv7

UUIDv7 improves both query performance and index design in PostgreSQL by generating time-ordered values, which reduce B-tree index fragmentation compared to random UUIDv4. This leads to better data locality, more efficient index scans, and faster execution of ORDER BY, LIMIT, and range-based queries.

UUIDv7 helps query optimization by allowing PostgreSQL to read data sequentially from the index instead of accessing random pages. This is especially beneficial for queries that rely on sorting or fetching recent data, where the database can avoid additional sorting and reduce random I/O.

Query Optimization with UUIDv7

UUIDv7 improves query execution in the following ways:

• Enables efficient index scans due to time-ordered values
• Improves performance of ORDER BY, LIMIT, and range queries
• Reduces need for explicit sorting operations
• Improves cache efficiency by reducing random page access

UUIDv7 Generation

UUIDv7 generates time-based IDs that follow insertion order, enabling compact indexes and efficient scans.

postgres=# SELECT uuidv7();
               uuidv7               
--------------------------------------
0196ea4a-6f32-7fd0-a9d9-9c815a0750cd
(1 row)
postgres=# SELECT uuid_extract_timestamp(uuidv7());
              uuid_extract_timestamp              
--------------------------------------------
2025-05-19 20:50:40.381+00
(1 row)

ORDER BY id Benchmark (1M Rows)

Range scans on primary keys highlight the efficiency of time-ordered UUIDs compared to random UUIDs.

postgres=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM uuidv4_demo ORDER BY id;
 Index 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 loops=1)
   Buffers: shared hit=1004700 read=30
 Execution Time: 318.005 ms
postgres=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM uuidv7_demo ORDER BY id;
 Index 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 loops=1)
   Buffers: shared hit=2821 read=7383
 Execution Time: 113.305 ms

Because UUIDv7 maintains insertion order, PostgreSQL performs more efficient index scans with reduced random page access, resulting in better query execution time.

Index Design Benefits with UUIDv7

UUIDv7 also simplifies index design and reduces overhead:

• Reduces B-tree fragmentation due to near-sequential inserts
• Keeps index pages more compact and organized
• Allows primary key (id) to act as both identifier and time-order column
• Reduces need for separate indexes such as created_at
• Improves effectiveness of composite indexes like (tenant_id, id)

Query Stats via pg_stat_statements

Query performance can also be observed using pg_stat_statements, which captures execution statistics across repeated queries.

postgres=# CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION
postgres=# INSERT INTO orders_v4 (order_date) SELECT CURRENT_TIMESTAMP FROM generate_series(1,100000);
INSERT 0 100000
postgres=# INSERT INTO orders_v7 (order_date) SELECT CURRENT_TIMESTAMP FROM generate_series(1,100000);
INSERT 0 100000
postgres=# SELECT * FROM orders_v4 ORDER BY id LIMIT 100;
... [100 rows returned]
postgres=# SELECT * FROM orders_v7 ORDER BY id LIMIT 100;
... [100 rows returned]
postgres=# SELECT query, calls, total_time, mean_time, rows FROM pg_stat_statements WHERE query LIKE '%orders_%' AND query LIKE '%ORDER BY id%' ORDER BY mean_time DESC;
                                       query                                        | calls | total_time | mean_time | rows 
-------------------------------------------------------------------------------------+-------+------------+-----------+------
 SELECT * FROM orders_v4 ORDER BY id LIMIT 100;                                      |     1 |     0.245 |     0.245 |  100
 SELECT * FROM orders_v7 ORDER BY id LIMIT 100;                                      |     1 |     0.078 |     0.078 |  100
(2 rows)

Lower execution time for UUIDv7 queries indicates improved efficiency due to better index locality and reduced random I/O.

UUIDv7 improves performance not by changing queries, but by making the underlying index more efficient, which directly benefits both query execution and index maintenance.

Benchmark: UUIDv4 vs UUIDv7

UUIDv7 significantly improves performance in PostgreSQL for index-heavy workloads by generating time-ordered values instead of random ones. This section compares UUIDv4 and UUIDv7 using real benchmarks on identical datasets.

Benchmark Table Setup

Create identical tables using UUIDv4 and UUIDv7 as primary keys.

postgres=# CREATE TABLE orders_v4 (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), created_at TIMESTAMPTZ DEFAULT NOW());
CREATE TABLE
postgres=# CREATE TABLE orders_v7 (id UUID PRIMARY KEY DEFAULT uuidv7(), created_at TIMESTAMPTZ DEFAULT NOW());
CREATE TABLE

Insert 1 million rows into both tables.

postgres=# INSERT INTO orders_v4 (created_at) SELECT NOW() FROM generate_series(1,1000000);
INSERT 0 1000000
postgres=# INSERT INTO orders_v7 (created_at) SELECT NOW() FROM generate_series(1,1000000);
INSERT 0 1000000

UUIDv4 inserts are random, while UUIDv7 inserts follow a time-ordered pattern, directly affecting index structure.

ORDER BY id Performance (1M Rows)

postgres=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders_v4 ORDER BY id;
 Index Scan using orders_v4_pkey on orders_v4 (cost=0.42..60024.31 rows=1000000 width=24) (actual time=0.031..301.163 rows=1000000 loops=1)
   Buffers: shared hit=1004700 read=30
 Execution Time: 318.005 ms
postgres=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders_v7 ORDER BY id;
 Index Scan using orders_v7_pkey on orders_v7 (cost=0.42..36785.43 rows=1000000 width=24) (actual time=0.013..96.177 rows=1000000 loops=1)
   Buffers: shared hit=2821 read=7383
 Execution Time: 113.305 ms

UUIDv7 shows significantly better performance due to improved index locality, allowing PostgreSQL to scan data more sequentially with fewer random page accesses.

Index Fragmentation Analysis

postgres=# SELECT 'v4' as type, (pgstatindex('orders_v4_pkey')).*;
 type | version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation 
------+---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+-------------------
 v4   | 4       | 2          | 40026112   | 295           | 24             | 4861       | 0           | 0             | 71               | 49.99
postgres=# SELECT 'v7' as type, (pgstatindex('orders_v7_pkey')).*;
 type | version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation 
------+---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+-------------------
 v7   | 4       | 2          | 31563776   | 295           | 20             | 3832       | 0           | 0             | 89.98            | 0

UUIDv7 maintains a more compact index with higher leaf density and minimal fragmentation, while UUIDv4 shows significant fragmentation due to random inserts.

Query Performance via pg_stat_statements

postgres=# SELECT query, calls, mean_time, total_time 
           FROM pg_stat_statements 
           WHERE query LIKE '%orders_%ORDER BY id%' 
           ORDER BY mean_time DESC;
                                        query                                         | calls | mean_time | total_time 
-------------------------------------------------------------------------+-------+-----------+------------
 SELECT * FROM orders_v4 ORDER BY id;                                      |     5 |    0.245 |      1.225
 SELECT * FROM orders_v7 ORDER BY id;                                      |     5 |    0.078 |      0.390

UUIDv7 queries consistently show lower execution time due to better index efficiency and reduced random I/O.

UUIDv4 vs UUIDv7 Benchmark Comparison

Final Thoughts

UUIDv7 brings a practical improvement to PostgreSQL by addressing one of the long-standing issues with UUIDv4 ,poor index performance due to random inserts. By introducing time-ordered UUIDs, PostgreSQL 18 allows applications to retain the benefits of globally unique identifiers while significantly improving how data is stored and accessed.

The key advantage of UUIDv7 is not in changing how queries are written, but in making the underlying index more efficient. This leads to better insert performance, reduced fragmentation, improved cache utilization, and faster query execution, especially for workloads that rely on recent data or range-based access patterns.

It is also important to note that simply upgrading to PostgreSQL 18 does not automatically convert existing UUIDv4 columns to UUIDv7. Tables that already use UUIDv4 will continue to behave the same unless explicitly changed. To benefit from UUIDv7, you must update the table definition (for example, changing the default to uuidv7()) or redesign the schema where necessary.

At the same time, UUIDv7 is not a universal replacement for UUIDv4. Its benefits are most noticeable in large tables, high-write workloads, and time-based queries. For smaller datasets or systems that rely on completely random identifiers, the impact may be minimal.