public inbox for [email protected]
help / color / mirror / Atom feedFrom: Olof Salberger <[email protected]>
To: Jonathan Reis <[email protected]>
Cc: [email protected]
Subject: Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18
Date: Wed, 22 Oct 2025 23:05:41 +0200
Message-ID: <CAD39LRHMO7nEKhub=RyeZH=+Qw=qo6gSNuByDLWsR9-EDmGjhg@mail.gmail.com> (raw)
In-Reply-To: <CAE_7N37Amq_G6bZDpv9tbxcZJNGVhSv8mt2MJgdZTqGO5Pdbnw@mail.gmail.com>
References: <CAE_7N37MopcS6SYmsphE7UxQ9bJTyvgsmVb-NyCncy7fzkiC4Q@mail.gmail.com>
<CAE_7N37Amq_G6bZDpv9tbxcZJNGVhSv8mt2MJgdZTqGO5Pdbnw@mail.gmail.com>
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
>
view thread (11+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18
In-Reply-To: <CAD39LRHMO7nEKhub=RyeZH=+Qw=qo6gSNuByDLWsR9-EDmGjhg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox