I don't know if it will necessarily be of much use in partition pruning, but it should work fairly well as a choice of clustered primary key together with block range indexes.
On Wed, Oct 22, 2025 at 12:53 PM Jonathan Reis <[email protected]> wrote: > 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 >
