Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 - Mailing list pgsql-performance

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