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
10for 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:
- Provides a monotonically increasing counter within each millisecond
- 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.
This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.