Summary: In this tutorial, you will learn how to use PostgreSQL 18's new UUIDv7 support to generate timestamp-ordered UUIDs that improve B-tree index performance while maintaining global uniqueness.

Introduction to UUIDv7

PostgreSQL 18 introduces native support for UUIDv7, a new UUID format specified in RFC 9562. UUIDv7 addresses performance limitations that have made developers hesitant to use UUIDs as primary keys in database applications.

Traditional UUIDs (like UUIDv4) are completely random, which creates problems for database indexes. When you insert records with random UUIDs as primary keys, the database must insert new rows at random locations in B-tree indexes rather than at the end. This causes index page splits, reduces cache efficiency, and can significantly impact insert performance.

UUIDv7 solves this problem by incorporating a timestamp as the most significant part of the UUID, making new UUIDs naturally sortable by creation time. This allows B-tree indexes to perform sequential inserts, similar to auto-incrementing integers, while maintaining the global uniqueness and distributed generation benefits of UUIDs.

Understanding UUIDv7 Structure

UUIDv7 uses a 128-bit structure that combines temporal and random components:

  • 48 bits: Unix timestamp in milliseconds
  • 12 bits: Sub-millisecond timestamp fraction for additional ordering
  • 62 bits: Random data for uniqueness
  • 6 bits: Version and variant identifiers

This structure ensures that UUIDs generated later in time will have lexicographically larger values, making them naturally sortable and optimal for B-tree indexes.

Some of the key benefits of UUIDv7 include chronological ordering, reduced index fragmentation, and improved cache locality. This makes UUIDv7 particularly suitable for applications that require high insert rates and efficient querying.

New UUID Functions in PostgreSQL 18

PostgreSQL 18 introduces several new UUID-related functions to work with UUIDv7. These functions allow you to generate, manipulate, and extract information from UUIDs easily.

Let's explore the key functions:

uuidv7() Function

The uuidv7() function generates new UUIDv7 values:

-- Generate a UUIDv7 with current timestamp
SELECT uuidv7();

-- Example output: 0197f96c-b278-7f64-a32f-dae3cabe1ff0

You can also generate UUIDs for different timestamps by providing an interval:

-- Generate a UUIDv7 for 1 hour ago
SELECT uuidv7(INTERVAL '-1 hour');

-- Generate a UUIDv7 for 30 minutes in the future
SELECT uuidv7(INTERVAL '30 minutes');

uuidv4() Alias

For consistency, PostgreSQL 18 adds uuidv4() as an alias for the existing gen_random_uuid() function:

-- These are equivalent
SELECT gen_random_uuid();
SELECT uuidv4();

This allows you to generate random UUIDs (UUIDv4) alongside the new timestamp-ordered UUIDv7.

Enhanced uuid_extract_timestamp()

The existing uuid_extract_timestamp() function now supports UUIDv7 in addition to UUIDv1:

-- Extract timestamp from a UUIDv7
SELECT uuid_extract_timestamp(uuidv7());

-- Example output: 2025-06-30 12:20:49.409+00

This function returns the timestamp when the UUID was generated, allowing you to track when records were created.

uuid_extract_version() Function

You can determine which UUID version you're working with:

-- Check UUID version
SELECT uuid_extract_version(uuidv7());  -- Returns 7
SELECT uuid_extract_version(uuidv4());  -- Returns 4

This is useful for validating UUIDs and ensuring compatibility with your application logic.

Setting Up UUIDv7 in Your Database

To start using UUIDv7 in PostgreSQL 18, you need to ensure your database is configured correctly. Let's go through the steps to enable UUIDv7 support and create tables that utilize it.

Creating Tables with UUIDv7 Primary Keys

Here's how to create a table using UUIDv7 as the primary key:

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuidv7(),
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

The id column is defined as a UUID with a default value generated by the uuidv7() function. This ensures that every new user record will automatically receive a timestamp-ordered UUID.

Inserting Data

When you insert data, the UUIDv7 is automatically generated:

INSERT INTO users (username, email)
VALUES ('alice', 'alice@example.com');

INSERT INTO users (username, email)
VALUES ('bob', 'bob@example.com');

-- View the results ordered by ID (chronological order)
SELECT id, username, uuid_extract_timestamp(id) as uuid_timestamp
FROM users
ORDER BY id;

This will show the users in the order they were created, thanks to the timestamp-ordered nature of UUIDv7.

Performance Benefits

With UUIDv7, you can expect significant performance improvements over traditional UUIDs, especially in B-tree indexes. The timestamp-ordered structure allows for more efficient inserts and better cache utilization.

B-tree Index Efficiency

UUIDv7's timestamp-ordered structure provides significant benefits for B-tree indexes. Let's compare the performance of UUIDv4 and UUIDv7 in a PostgreSQL database.

-- Create a test table to demonstrate performance
CREATE TABLE performance_test (
    id_v4 UUID DEFAULT uuidv4(),
    id_v7 UUID DEFAULT uuidv7(),
    data TEXT DEFAULT 'sample data'
);

-- Create indexes
CREATE INDEX idx_v4 ON performance_test (id_v4);
CREATE INDEX idx_v7 ON performance_test (id_v7);

-- Insert test data
INSERT INTO performance_test (data)
SELECT 'test record ' || i
FROM generate_series(1, 100000) i;

The above creates a table with both UUIDv4 and UUIDv7 columns, allowing you to compare their performance.

Comparing Insert Performance

You can observe the difference in index behavior by examining page splits and index bloat:

-- Check index statistics
SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_blks_read,
    idx_blks_hit
FROM pg_statio_user_indexes
WHERE indexrelname IN ('idx_v4', 'idx_v7');

This query will show you how many index blocks were read and hit for both UUIDv4 and UUIDv7 indexes. You should see that the UUIDv7 index has fewer page splits and better cache utilization due to its natural ordering.

Sorting Performance

UUIDv7's natural ordering eliminates the need for additional timestamp columns in many cases:

-- This query benefits from the natural ordering of UUIDv7
SELECT id_v7, data
FROM performance_test
ORDER BY id_v7
LIMIT 10;

-- Compare with UUIDv4 (no meaningful order)
SELECT id_v4, data
FROM performance_test
ORDER BY id_v4  -- Random order, no chronological meaning
LIMIT 10;

The first query will return results in the order they were created, while the second will return random results. This is helpful for applications that need to display records in the order they were created without additional timestamp columns.

Practical Usage Patterns

The timestamp-ordered nature of UUIDv7 makes it suitable for various application scenarios. Some common use cases include:

Multi-tenant Applications

UUIDv7 is particularly useful in multi-tenant applications where you need globally unique identifiers:

CREATE TABLE tenant_records (
    id UUID PRIMARY KEY DEFAULT uuidv7(),
    tenant_id UUID NOT NULL,
    record_type VARCHAR(50) NOT NULL,
    data JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create a composite index that benefits from UUIDv7 ordering
CREATE INDEX idx_tenant_records_tenant_created
ON tenant_records (tenant_id, id);

With the above setup, you can efficiently query records by tenant while maintaining chronological order.

Distributed Systems

UUIDv7 works well in distributed systems where multiple services generate IDs:

-- Each service can generate UUIDs independently
-- while maintaining chronological ordering
CREATE TABLE distributed_events (
    event_id UUID PRIMARY KEY DEFAULT uuidv7(),
    service_name VARCHAR(50) NOT NULL,
    event_type VARCHAR(50) NOT NULL,
    payload JSONB,
    processed_at TIMESTAMP WITH TIME ZONE
);

-- Natural ordering by creation time across all services
SELECT event_id, service_name, event_type
FROM distributed_events
ORDER BY event_id
LIMIT 100;

This allows you to maintain a global event log that is naturally ordered by creation time, making it easier to process events in the correct sequence.

Best Practices

With the introduction of UUIDv7, consider the following factors when designing your database schema:

When to Use UUIDv7

Use UUIDv7 when you need:

  • Globally unique identifiers across distributed systems
  • Natural chronological ordering
  • Better B-tree index performance than UUIDv4
  • Protection against sequential ID enumeration attacks

When to Stick with Auto-incrementing IDs

Consider staying with SERIAL or BIGSERIAL when:

  • Your application is not distributed
  • You need the smallest possible primary key size
  • You frequently perform range scans on the primary key
  • Legacy systems require integer keys

Current Limitations and Considerations

With PostgreSQL 18 beta releases, there are some limitations and considerations to keep in mind when using UUIDv7:

Clock Synchronization

UUIDv7 relies on system clocks, so ensure your servers have synchronized time:

  • Use NTP or similar time synchronization
  • Be aware of clock skew in distributed environments
  • Consider the impact of leap seconds or clock adjustments

Timestamp Precision

UUIDv7 uses millisecond precision for timestamps:

-- The extracted timestamp may differ slightly from the actual generation time
SELECT
    uuidv7() as uuid,
    CURRENT_TIMESTAMP as actual_time,
    uuid_extract_timestamp(uuidv7()) as extracted_time;

This means that if you generate multiple UUIDs within the same millisecond, they will still be unique but may not reflect the exact order of creation. This is generally acceptable for most applications, but be aware of this behavior in high-frequency scenarios.

Migration Planning

When migrating from UUIDv4 to UUIDv7:

  • Plan for application changes to handle the new format
  • Consider the impact on existing indexes and queries
  • Test performance thoroughly with your specific workload
  • Update any external systems that depend on UUID format

Summary

PostgreSQL 18's UUIDv7 support addresses long-standing performance concerns with using UUIDs as primary keys. Thanks to the global uniqueness benefits of UUIDs with the B-tree index efficiency of chronologically ordered values, UUIDv7 provides a practical solution for modern distributed applications.

The timestamp-ordered nature of UUIDv7 makes it particularly suitable for applications that need both global uniqueness and efficient database operations. Combined with PostgreSQL 18's other performance improvements like skip scans and async I/O, UUIDv7 enables more efficient database designs for distributed systems.