Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18

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