In late September 2025, the release of PostgreSQL 18 introduced a significant enhancement with the addition of the built-in function uuidv7(). This function generates UUID version 7 (UUIDv7) identifiers, adhering to the international standard RFC 9562. These identifiers are particularly well-suited for use as primary keys, and they allow for the extraction of timestamps with time zones through the uuid_extract_timestamp() function.
Advantages of UUIDv7
UUIDv7 offers a combination of global uniqueness, an exceptionally low probability of collisions, and the ability to order identifiers by their generation timestamp. This is accomplished without relying on centralized coordination or MAC addresses, maintaining a collision risk comparable to the widely used UUID version 4.
The ordering by generation timestamp translates into improved performance and reduced index sizes when compared to UUIDv4. The most significant bits of UUIDv7 identifiers can also serve as a partition key, further enhancing database efficiency.
Notably, UUIDv7 provides performance metrics for CRUD operations that rival those of auto-incrementing identifiers. The generation time for a UUIDv7 identifier is approximately a thousand times shorter than the time required for record insertion, ensuring that the performance of the database remains unaffected.
Eliminating Auto-Increment Drawbacks
Utilizing UUIDv7 effectively addresses several inherent limitations associated with auto-increment identifiers:
- Challenges in merging data from different tables with identical keys.
- The necessity to generate new keys and synchronize them during data exports and imports, especially when records are created concurrently by multiple processes (microservices).
- The requirement for intermediate tables during data merges.
- Potential key collision errors during data integration.
- Exposure of the record count within a database table.
- Increased vulnerability to brute-force attacks aimed at guessing valid keys.
- Limitations in performing full-text searches by identifier on the web.
Built-In Function Features
The uuidv7() function in PostgreSQL 18 is designed with several key features:
- Incorporates a 12-bit sub-millisecond timestamp segment, achieving a precision of approximately 250 nanoseconds (or 10-bit with about 1 microsecond precision on macOS).
- Operates without a mutex, thereby avoiding potential performance bottlenecks, and does not rely on atomic variables.
- Utilizes the timestamp as a counter under critical conditions to ensure uniqueness.
- Includes an optional parameter (of the interval type) for adjusting the date and time, complete with timestamp overflow protection akin to a circular buffer.
- Requires a cryptographically secure pseudo-random number generator (CSPRNG) for secure generation.
By employing the timestamp as a counter under critical conditions, the function guarantees monotonicity and uniqueness even in scenarios where the system clock may be temporarily unavailable or reverted. When generating UUIDs in parallel across multiple processes, the additional 12-bit sub-millisecond timestamp segment generally ensures monotonicity, with minor violations not impacting overall database performance.
Offsetting the timestamp value using the parameter allows for masking the actual record creation date, mitigates lock contention during parallel UUIDv7 generation, and enhances monotonicity for remote clients. If an offset is applied during UUIDv7 generation, the uuid_extract_timestamp() function will return the adjusted date and time.
While theoretically, offsetting the timestamp could guarantee uniqueness across different processes, the combination of the extended timestamp and the long random segment already ensures an exceedingly low probability of collisions.
Example Usage
SELECT uuidv7();
-- Create clients table with UUIDv7 as primary key with masked timestamp (5500 years + 12.5 hours forward)
CREATE TABLE clients (
id uuid DEFAULT uuidv7(INTERVAL '5500 years 12 hours 30 minutes') PRIMARY KEY,
name text NOT NULL,
email text,
created_at timestamptz DEFAULT CURRENT_TIMESTAMP
);
-- Insert clients. Let the DEFAULT value generate the UUID
INSERT INTO clients (name, email) VALUES
('John Smith', 'john.smith@example.com'),
('Emma Watson', 'emma.watson@example.com'),
('Michael Brown', 'michael.brown@example.com');
-- If you need a UUID for a past date, the interval should be negative
INSERT INTO clients (id, name, email) VALUES
(uuidv7(INTERVAL '-11 years -5 hours -44 minutes'), 'James Anderson', 'james.anderson@example.com'),
(uuidv7(), 'Olivia Parker', 'olivia.parker@example.com');
-- Verify the masked timestamp
SELECT
id,
name,
email,
created_at as actual_creation_time,
uuid_extract_timestamp(id) as masked_uuid_timestamp,
uuid_extract_timestamp(id) - created_at as timestamp_shift
FROM clients
ORDER BY id;