In late September 2025, PostgreSQL 18 was released. It received the long-awaited built-in function uuidv7(). The uuidv7() function generates UUID version 7 (UUIDv7) identifiers of the binary data type uuid in accordance with the international standard RFC 9562. These identifiers are recommended for use as primary keys. If necessary, the timestamp with the time zone can be extracted from them using the uuid_extract_timestamp() function.
UUIDv7 combines the global uniqueness of primary keys, a negligibly low probability of collisions (unacceptable random matches), and ordering by the generation timestamp. This is achieved without using centralized coordination or MAC addresses. The risk of collisions is no higher than with the previously most popular (random) UUID version 4 type.
Due to ordering by generation timestamp, UUIDv7 results in significantly higher performance and smaller index sizes compared to UUIDv4. The most significant bits of UUIDv7 identifiers can be used as a partition key.
UUIDv7 provides the same performance for CRUD database operations as when using auto-increment (the serial type and its modern equivalent GENERATED ... AS IDENTITY). The time to generate a UUIDv7 identifier is approximately a thousand times less than the record insertion time, so the UUIDv7 generation rate does not affect database performance.
Using UUIDv7 eliminates the fundamental drawbacks of auto-increment:
difficulty merging data from different database tables that use identical keys
the need to generate new keys and synchronize them with keys from the data source when exporting and importing data or when records are generated in parallel by multiple processes (microservices)
the need for intermediate tables when merging data
potential key collision errors during data merges
exposure of the record count in a database table
vulnerability to brute-force attacks to guess valid keys
inability to perform full-text searches by identifier on the web
Unlike third-party PostgreSQL extensions for UUIDv7 generation and unlike UUIDv7 generation in applications, the built-in function ensures ease of use and monotonicity (increasing order) of identifiers generated within sub-millisecond intervals. This monotonicity within a millisecond is necessary for troubleshooting, for keyset pagination, log searching, use in time-series databases, etc.
Implementation features of the uuidv7() function in PostgreSQL 18:
has an additional 12-bit sub-millisecond timestamp segment with a precision of about 250 nanoseconds (on macOS, it's 10-bit with a precision of about 1 microsecond)
uses neither a mutex, which could potentially limit performance, nor atomic variables
uses the timestamp as a counter under critical conditions
contains an optional parameter (of the interval type) for shifting the date and time forward or backward, with timestamp overflow protection similar to a circular buffer
requires the use of a cryptographically secure pseudo-random number generator (CSPRNG)
Using the timestamp as a counter under critical conditions guarantees monotonicity and uniqueness when generating within a single process, even if the system clock is temporarily unavailable or rolled back, and at arbitrarily high generation rates. When generating in parallel across multiple processes (microservices), monotonicity is generally ensured due to the additional 12-bit sub-millisecond timestamp segment. Minor violations of monotonicity do not affect database performance.
Offsetting the timestamp value using the parameter allows masking the actual record creation date, prevents lock contention when generating UUIDv7 data in parallel across multiple processes, and improves monotonicity when generating UUIDv7 data on remote clients. If the timestamp value was offset using the parameter during UUIDv7 generation, the uuid_extract_timestamp() function will output an offset date and time value.
Theoretically, offsetting the timestamp value also allows guaranteeing uniqueness when generating in different processes (microservices). But there is no real need for this. After all, the extended timestamp together with the long random segment ensure a negligibly low probability of UUIDv7 identifier collisions.
Example of use:
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;