Hello PostgreSQL performance team,
I’m evaluating the new UUIDv7 type in PostgreSQL v18 and would like advice
on its suitability for time-based partitioning and related planner behavior.
*Context*
I have a large message/event table where each row is identified by a uuidv7
primary key. Because UUIDv7 embeds a timestamp component in its most
significant bits, I’m considering using it as the partition key instead of
a separate timestamptz column.
*Questions*
1.
*Partitioning on UUIDv7 ranges*
-
Is range partitioning by UUIDv7 considered practical or advisable for
time-based data?
-
Will the planner efficiently prune partitions when queries filter by
UUIDv7 ranges (e.g., WHERE id BETWEEN uuidv7_floor(timestamp1) AND
uuidv7_floor(timestamp2) that align with time periods?
-
Are there known drawbacks—such as statistics accuracy, correlation
estimation, or index selectivity—when using UUIDv7 as a surrogate for
timestamptz?
2.
*Conversion between timestamptz and UUIDv7*
-
Is there a built-in or community-recommended method to convert
between timestamptz and uuidv7 values? I am currently using this
CREATE OR REPLACE FUNCTION uuidv7_floor(ts timestamptz)
RETURNS uuid
LANGUAGE sql
IMMUTABLE
AS $$
WITH ms AS (
SELECT floor(extract(epoch FROM ts) * 1000)::bigint AS ms
),
h AS (
SELECT lpad(to_hex(ms), 12, '0') AS h FROM ms
)
SELECT (
substr(h.h,1,8) || '-' ||
substr(h.h,9,4) || '-' ||
'7000' || '-' || -- version 7 + rand_a all zero
'8000' || '-' || -- variant '10' + rest zero
'000000000000' -- zero node
)::uuid
FROM h;
$$;
*Example*
CREATE TABLE message (
id uuidv7 PRIMARY KEY,
payload jsonb,
received_at timestamptz DEFAULT now()
)PARTITION BY RANGE (id);
I’d appreciate any insight into whether UUIDv7 is a good candidate for
partitioning from a performance standpoint, and how well partition pruning
behaves in practice.
Best regards,
Jon
