UUID v7 in PostgreSQL 18

For years, developers using PostgreSQL faced a frustrating tradeoff when choosing primary key types. Auto-incrementing integers gave you great performance but exposed sequential IDs in URLs and made distributed systems harder to build. Random UUIDs (v4) provided global uniqueness and URL obscurity but caused terrible index fragmentation that degraded database performance as tables grew.

PostgreSQL 18 changes this with native support for UUID v7, a new UUID variant that combines the best of both approaches. UUID v7 generates globally unique identifiers that are naturally ordered by time, eliminating the B-tree index problems that plagued random UUIDs while maintaining all their distributed-system benefits.

This guide shows you how UUID v7 works, why it performs dramatically better than UUID v4, and how to use it effectively in PostgreSQL 18.

Prerequisites

Before proceeding with this article, ensure you have PostgreSQL 18 installed on your system:

psql --version

psql (PostgreSQL) 18.0 (Ubuntu 18.0-1.pgdg24.04+3)

This guide assumes basic familiarity with PostgreSQL, primary keys, database indexes, and UUID data types. You should be comfortable executing SQL commands and interpreting query results.

Understanding the UUID v4 performance problem

UUID v4 generates completely random 128-bit values. When used as a primary key, each insert targets a random location in PostgreSQL's B-tree index instead of appending to the end like sequential integers do. This creates several performance problems:

Excessive page splits: Sequential IDs cause 10-20 page splits per million records. UUID v4 causes 5,000-10,000+ splits—that's 500 times more. Each split triggers additional I/O operations.

Write amplification: A single insert can trigger 5-10 times more physical I/O due to cascading page splits and B-tree rebalancing.

Index bloat: Random inserts fragment the index, causing pages to average 69% full instead of approaching 100%, wasting disk space.

Poor cache utilization: Random inserts spread data across many pages instead of concentrating on a few hot pages, making your buffer cache less effective.

Buildkite observed a 50% reduction in Write Ahead Log generation after switching from random UUIDs to time-ordered ones. The performance impact becomes particularly noticeable once your tables grow larger than available RAM.

How UUID v7 solves the problem

UUID v7 was standardized in RFC 9562 (published May 2024) to address these performance issues. The key innovation: encoding a Unix timestamp in the most significant bits.

Here's the bit structure:

 0                   1                   2                   3
 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|                           unix_ts_ms                          |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|          unix_ts_ms           |  ver  |       rand_a          |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|var|                        rand_b                             |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|                            rand_b                             |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

The components:

• unixtsms (48 bits): Unix timestamp in milliseconds (approximately 8,900 years before overflow)
• ver (4 bits): Version field set to 0111 (7 in decimal)
• rand_a (12 bits): Random data or sub-millisecond timestamp fraction
• var (2 bits): Variant field set to 10 for RFC 9562 compliance
• rand_b (62 bits): Additional random data for uniqueness

Because the timestamp occupies the most significant bits, UUID v7 values sort naturally by creation time. This transforms the B-tree insertion pattern from completely random to primarily sequential, eliminating most performance problems.

PostgreSQL 18's UUID v7 implementation

PostgreSQL 18 implements UUID v7 with an important enhancement: the 12-bit rand_a field stores sub-millisecond precision rather than purely random data. This serves two purposes:

1. Provides a monotonically increasing counter within each millisecond
2. Ensures monotonicity even when the system clock moves backward

If you generate multiple UUID v7 values within the same millisecond from the same PostgreSQL backend, they're guaranteed to be in strictly increasing order. This guarantee only applies within a single backend—UUIDs from different connections aren't guaranteed to be monotonic relative to each other, though they'll typically be ordered correctly due to the timestamp.

Generating UUID v7 values

Let's start using UUID v7 in PostgreSQL 18. The database provides a simple uuidv7() function that generates new UUID v7 values.

Start by creating a test database to experiment with UUID v7:

sudo -u postgres createdb uuid_test

Connect to the database:

sudo -u postgres psql uuid_test

Then create a test table to experiment with:

CREATE TABLE products (
  id UUID PRIMARY KEY DEFAULT uuidv7(),
  name TEXT NOT NULL,
  price NUMERIC(10, 2),
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

Insert some test data:

INSERT INTO products (name, price)
VALUES 
  ('Wireless Keyboard', 79.99),
  ('Gaming Mouse', 59.99),
  ('USB-C Hub', 34.99),
  ('Monitor Stand', 89.99),
  ('Desk Lamp', 44.99);

Now query the results to see the generated UUID v7 values:

SELECT id, name FROM products ORDER BY created_at;

                  id                  |       name
--------------------------------------+-----------------
 01941c04-4185-7ea3-ab00-82c8a75adf41 | Wireless Keyboard
 01941c04-4185-7efe-b171-12949bdf8bd8 | Gaming Mouse
 01941c04-4186-7234-a445-6789abcdef12 | USB-C Hub
 01941c04-4186-7567-c890-1234567890ab | Monitor Stand
 01941c04-4186-7890-d123-456789012345 | Desk Lamp
(5 rows)

Notice how the UUID values start with similar prefixes (01941c04-418...). This is the timestamp portion, and it increases slightly with each insertion. The similarity indicates these records were created within milliseconds of each other.

You can also generate UUID v7 values directly without a table:

SELECT uuidv7() FROM generate_series(1, 5);

               uuidv7
--------------------------------------
 01941c04-4185-7ea3-ab00-82c8a75adf41
 01941c04-4185-7efe-b171-12949bdf8bd8
 01941c04-4186-7234-a445-6789abcdef12
 01941c04-4186-7567-c890-1234567890ab
 01941c04-4186-7890-d123-456789012345
(5 rows)

The monotonic increase in values is subtle but present. PostgreSQL guarantees these five UUIDs are in strictly increasing order because they were generated sequentially within the same backend session.

Generating UUID v7 with specific timestamps

One powerful feature of PostgreSQL 18's uuidv7() function is the ability to generate UUIDs for specific timestamps. This is useful for backfilling historical data, testing time-based behavior, or generating UUIDs that represent past or future events.

The function accepts an optional interval parameter that shifts the timestamp:

SELECT 
  uuidv7() AS current_time,
  uuidv7('-1 day'::INTERVAL) AS yesterday,
  uuidv7('1 year'::INTERVAL) AS next_year;

             current_time             |              yesterday               |              next_year
--------------------------------------+--------------------------------------+--------------------------------------
 01941c04-4185-7ea3-ab00-82c8a75adf41 | 01940f30-9d85-7ea3-ab00-82c8a75adf41 | 0195e4d4-c985-7ea3-ab00-82c8a75adf41
(1 row)

You can see how the timestamp portion changes based on the interval. The UUID for yesterday has a smaller timestamp prefix (01940f30) than the current time (01941c04), and next year has a larger one (0195e4d4).

This feature is particularly useful when migrating data from legacy systems:

INSERT INTO products (id, name, price, created_at)
SELECT 
  uuidv7((created_at - '2025-01-01'::TIMESTAMPTZ)::INTERVAL),
  name,
  price,
  created_at
FROM legacy_products;

You don't need to run this example since the legacy_products table doesn't exist. This illustrates migrating historical data while preserving chronological order.

This generates UUID v7 values that accurately reflect when each product was originally created, maintaining chronological ordering in the new table.

Extracting timestamps from UUID v7

PostgreSQL 18 enhances the uuid_extract_timestamp() function to work with UUID v7 values. This lets you extract the embedded timestamp, which is useful for debugging, auditing, or working with UUIDs from external systems.

SELECT 
  id,
  name,
  uuid_extract_timestamp(id) AS id_timestamp,
  created_at
FROM products
LIMIT 3;

                  id                  |       name        |        id_timestamp        |          created_at
--------------------------------------+-------------------+----------------------------+-------------------------------
 01941c04-4185-7ea3-ab00-82c8a75adf41 | Wireless Keyboard | 2025-01-31 10:27:41.122+00 | 2025-01-31 10:27:41.122049+00
 01941c04-4185-7efe-b171-12949bdf8bd8 | Gaming Mouse      | 2025-01-31 10:27:41.122+00 | 2025-01-31 10:27:41.122049+00
 01941c04-4186-7234-a445-6789abcdef12 | USB-C Hub         | 2025-01-31 10:27:41.123+00 | 2025-01-31 10:27:41.123051+00
(3 rows)

The extracted timestamp matches closely with the created_at column, though the UUID timestamp has only millisecond precision while the actual TIMESTAMPTZ column preserves microseconds.

You can also extract the UUID version to verify you're working with UUID v7:

SELECT 
  id,
  uuid_extract_version(id) AS version,
  uuid_extract_timestamp(id) AS timestamp
FROM products
LIMIT 1;

                  id                  | version |        timestamp
--------------------------------------+---------+---------------------------
 01941c04-4185-7ea3-ab00-82c8a75adf41 |       7 | 2025-01-31 10:27:41.122+00
(1 row)

Comparing UUID v4 and UUID v7

PostgreSQL 18 also adds uuidv4() as an alias for the existing gen_random_uuid() function. This gives you consistent naming across UUID versions and makes comparisons clearer.

Let's create two tables with identical schemas but different UUID types to see the practical differences:

CREATE TABLE orders_v4 (
  id UUID PRIMARY KEY DEFAULT uuidv4(),
  customer_id INTEGER,
  order_total NUMERIC(10, 2),
  order_date TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders_v7 (
  id UUID PRIMARY KEY DEFAULT uuidv7(),
  customer_id INTEGER,
  order_total NUMERIC(10, 2),
  order_date TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

Generate sample data for both tables:

INSERT INTO orders_v4 (customer_id, order_total)
SELECT 
  (random() * 1000)::INTEGER,
  (random() * 500 + 10)::NUMERIC(10, 2)
FROM generate_series(1, 10000);

INSERT INTO orders_v7 (customer_id, order_total)
SELECT 
  (random() * 1000)::INTEGER,
  (random() * 500 + 10)::NUMERIC(10, 2)
FROM generate_series(1, 10000);

Now compare the UUID patterns:

(SELECT 'UUID v4' AS type, id FROM orders_v4 LIMIT 5)
UNION ALL
(SELECT 'UUID v7' AS type, id FROM orders_v7 LIMIT 5);

  type   |                  id
---------+--------------------------------------
 UUID v4 | f47ac10b-58cc-4372-a567-0e02b2c3d479
 UUID v4 | 7d444840-9dc0-11d1-b245-5ffdce74fad2
 UUID v4 | 9b0c3b9a-7d4c-4a8f-9c5d-2e1f3a4b5c6d
 UUID v4 | 3e8a9f7b-6c5d-4e3f-8a2b-1c9d0e8f7a6b
 UUID v4 | 8f9e0d1c-2b3a-4c5d-6e7f-8a9b0c1d2e3f
 UUID v7 | 01941c04-4185-7ea3-ab00-82c8a75adf41
 UUID v7 | 01941c04-4185-7efe-b171-12949bdf8bd8
 UUID v7 | 01941c04-4186-7234-a445-6789abcdef12
 UUID v7 | 01941c04-4186-7567-c890-1234567890ab
 UUID v7 | 01941c04-4186-7890-d123-456789012345
(10 rows)

The UUID v4 values show no pattern—they're completely random. The UUID v7 values all start with similar prefixes because they share the same timestamp portion.

Check the index sizes:

SELECT 
  schemaname,
  relname AS tablename,
  indexrelname AS indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE relname IN ('orders_v4', 'orders_v7')
  AND indexrelname LIKE '%pkey';

 schemaname | tablename |   indexname    | index_size
------------+-----------+----------------+------------
 public     | orders_v4 | orders_v4_pkey | 424 kB
 public     | orders_v7 | orders_v7_pkey | 328 kB
(2 rows)

With just 10,000 rows, UUID v7 already shows a smaller index size due to better page fill rates. This difference becomes more dramatic as tables grow larger.

Test query performance for range scans:

EXPLAIN ANALYZE
SELECT * FROM orders_v4 
WHERE order_date > CURRENT_TIMESTAMP - INTERVAL '1 hour'
ORDER BY id
LIMIT 100;

                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.29..8.01 rows=100 width=34) (actual time=0.020..0.201 rows=100.00 loops=1)
   Buffers: shared hit=101
   ->  Index Scan using orders_v4_pkey on orders_v4  (cost=0.29..773.26 rows=10000 width=34) (actual time=0.019..0.183 rows=100.00 loops=1)
         Filter: (order_date > (CURRENT_TIMESTAMP - '01:00:00'::interval))
         Index Searches: 1
         Buffers: shared hit=101
 Planning:
   Buffers: shared hit=13 dirtied=1
 Planning Time: 0.246 ms
 Execution Time: 0.230 ms
(10 rows)

EXPLAIN ANALYZE
SELECT * FROM orders_v7 
WHERE order_date > CURRENT_TIMESTAMP - INTERVAL '1 hour'
ORDER BY id
LIMIT 100;

                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.29..5.04 rows=100 width=34) (actual time=0.057..0.088 rows=100.00 loops=1)
   Buffers: shared hit=3
   ->  Index Scan using orders_v7_pkey on orders_v7  (cost=0.29..476.29 rows=10000 width=34) (actual time=0.055..0.077 rows=100.00 loops=1)
         Filter: (order_date > (CURRENT_TIMESTAMP - '01:00:00'::interval))
         Index Searches: 1
         Buffers: shared hit=3
 Planning:
   Buffers: shared hit=9 dirtied=2
 Planning Time: 0.504 ms
 Execution Time: 0.129 ms
(10 rows)

The UUID v7 query executes faster (0.129ms vs 0.230ms) and requires significantly fewer buffer hits (3 vs 101). The time-ordering of UUID v7 values means they naturally align with the chronological order_date filter, allowing PostgreSQL to scan fewer index pages to find the matching rows.

Understanding UUID v7 limitations

While UUID v7 offers significant advantages, you should understand its limitations and tradeoffs before adopting it.

Timestamp exposure: UUID v7 embeds the creation timestamp directly in the identifier. If you're using UUIDs specifically to make record creation times non-obvious, UUID v7 defeats that purpose. Anyone can extract the timestamp using uuid_extract_timestamp() or by decoding the first 48 bits.

Uniqueness depends on randomness: UUID v7 still relies on random bits for uniqueness. While 74 bits of randomness (compared to UUID v4's 122 bits) is sufficient for most applications, extremely high-frequency generation across many systems could theoretically cause collisions. In practice, collision probability remains astronomically low.

Monotonicity guarantees are limited: PostgreSQL 18 guarantees monotonicity only within a single backend process. If you generate UUID v7 values from multiple database connections simultaneously, they are not guaranteed to be strictly increasing relative to each other, though they will typically be ordered correctly due to different millisecond timestamps.

Clock dependency: UUID v7 quality depends on your system clock. If your server's clock jumps backward (due to NTP corrections or manual changes), newly generated UUID v7 values might sort before previously generated ones. PostgreSQL's implementation mitigates this somewhat with sub-millisecond counters, but dramatic clock changes can still cause ordering anomalies.

Not a direct drop-in for auto-increment integers: While UUID v7 performs much better than UUID v4, it's still not as fast as auto-incrementing integers for pure insertion performance. The 16-byte size also means indexes and foreign keys take more space than 8-byte BIGINT columns.

Final thoughts

UUID v7 in PostgreSQL 18 represents a significant improvement for applications that need globally unique identifiers. By encoding timestamps in the most significant bits, UUID v7 transforms random UUID insertion from a performance liability into a reasonable choice for primary keys.

The benefits are clear: faster insertions, smaller indexes, better query performance, and natural chronological ordering—all while maintaining the distribution advantages and URL-obfuscation properties that make UUIDs attractive in the first place.

PostgreSQL 18's implementation adds extra value with guaranteed monotonicity within backend sessions and the convenient uuid_extract_timestamp() function for debugging and auditing. With PostgreSQL 18 now available, UUID v7 should become the default choice for new applications that need UUID-based identifiers.

For existing applications using UUID v4, consider migrating to UUID v7 for new tables while you evaluate the effort required to migrate existing data. The performance improvements are substantial enough that migration often pays for itself in reduced infrastructure costs for write-heavy workloads.

Got an article suggestion? Let us know

Next article

How Turso Eliminates SQLite's Single-Writer Bottleneck

Discover how Turso's Rust-based rewrite of SQLite introduces Multi-Version Concurrency Control (MVCC) to handle thousands of concurrent writes per second. This deep dive explains the single-writer problem, row-level versioning, and the performance gains that make SQLite viable for high-throughput applications.

→

This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.