Exploring PostgreSQL 18's new UUIDv7 support

Summary

Should you use UUIDs as the primary key in your database? You might have heard they are terrible for performance, which is often true for traditional UUIDv4. However, the introduction of UUIDv7 fixes many of the previous issues of UUIDv4. Let’s therefore explore what they are and why it might be a good idea to use them. What is UUIDv7? 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. This has several advantages we will cover in the post, but rather than just read about it, you can try it out for yourself using the commands below. Creating our Crab store Let's create a new free Aiven for PostgreSQL® service for our demo Crab store using the Aiven CLI. avn service create -t pg --cloud do-nyc --plan pg:free-1-1gb 'crab-store' -c 'pg_version=18' Once that's done, we can create two tables, with the only difference being that one table uses UUIDv4 and the other uses UUIDv7. 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); CREATE OR REPLACE FUNCTION insert_random_crabs_explain(table_name TEXT, num_rows INT) RETURNS TABLE(query_plan TEXT) AS $$ BEGIN RETURN QUERY EXECUTE format( 'EXPLAIN ANALYZE INSERT INTO %I (crab_type) SELECT CASE (random() * 9)::int WHEN 0 THEN ''Dungeness Crab'' WHEN 1 THEN ''Blue Crab'' WHEN 2 THEN ''Hermit Crab'' WHEN 3 THEN ''Fiddler Crab'' WHEN 4 THEN ''Coconut Crab'' WHEN 5 THEN ''Snow Crab'' WHEN 6 THEN ''King Crab'' WHEN 7 THEN ''Stone Crab'' WHEN 8 THEN ''Spider Crab'' ELSE ''...