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

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 <jon(dot)reis(at)conevity(dot)com>
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
>