postgres uuidv7 postgres 18

• https://aiven.io/blog/exploring-postgresql-18-new-uuidv7-support UUIDv7 is a relatively new type of Universally Unique Identifier (UUID). It was introduced to Postgres in version 18 to mitigate performance issues associated with using traditional UUIDs, UUIDv4, as database primary keys.

Unlike the traditional UUIDv4, which is completely random, UUIDv7 incorporates a timestamp as the most significant part of its 128-bit structure, allowing for natural sortability based on the creation time.

CREATE TABLE crab_inventory_4 
(id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
crab_type VARCHAR(75) NOT NULL,
acquisition TIMESTAMP DEFAULT NOW; 

CREATE TABLE crab_inventory_7 
(id UUID PRIMARY KEY DEFAULT uuidv7(), 
crab_type VARCHAR(75) NOT NULL);

UUIDv4's completely random structure significantly impacts performance by forcing random insertions into B-tree indexes, leading to index page splits and reduced cache efficiency.

UUIDv7 solves this by including a timestamp, making new UUIDs naturally sortable by creation time. This allows indexes to perform efficient sequential inserts, similar to auto-incrementing integers.

UUIDv7's ordering also simplifies queries, eliminating the need for separate timestamp columns for sorting, as it is naturally sorted by time. This structure is ideal for applications that require high insert rates and efficient querying.

The main issue for security when the primary key is exposed to end users - is that UUIDv7 incorporates a 48-bit Unix timestamp as its most significant part, meaning the identifier itself leaks the record's creation time. Experts recommend using UUIDv7 only for internal keys and exposing a separate, truly random UUIDv4 as an external identifier.

SELECT uuid_extract_version ('0199c489-c5b3-70aa-9ad0-37afd22ef141');
 
uuid_extract_version
----------------------
                    7

-- Comparing that against a PG generated id
SELECT uuid_extract_version (uuidv7()); 

uuid_extract_version
----------------------
                    7