MIME-Version: 1.0
References: 
 <CAE_7N37MopcS6SYmsphE7UxQ9bJTyvgsmVb-NyCncy7fzkiC4Q@mail.gmail.com>
 <CAE_7N37Amq_G6bZDpv9tbxcZJNGVhSv8mt2MJgdZTqGO5Pdbnw@mail.gmail.com>
In-Reply-To: 
 <CAE_7N37Amq_G6bZDpv9tbxcZJNGVhSv8mt2MJgdZTqGO5Pdbnw@mail.gmail.com>
From: Olof Salberger <olof.salberger@gmail.com>
Date: Wed, 22 Oct 2025 23:05:41 +0200
Message-ID: 
 <CAD39LRHMO7nEKhub=RyeZH=+Qw=qo6gSNuByDLWsR9-EDmGjhg@mail.gmail.com>
Subject: Re: Performance implications of partitioning by UUIDv7 range in
 PostgreSQL v18
To: Jonathan Reis <jon.reis@conevity.com>
Cc: pgsql-performance@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000008164530641c5ae4b"
Archived-At: 
 <https://www.postgresql.org/message-id/CAD39LRHMO7nEKhub%3DRyeZH%3D%2BQw%3Dqo6gSNuByDLWsR9-EDmGjhg%40mail.gmail.com>
Precedence: bulk

--0000000000008164530641c5ae4b
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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=E2=80=AFPM Jonathan Reis <jon.reis@conevity.c=
om>
wrote:

> Hello PostgreSQL performance team,
>
> I=E2=80=99m evaluating the new UUIDv7 type in PostgreSQL v18 and would li=
ke
> 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=E2=80=99m 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=E2=80=94such as statistics accuracy, corr=
elation
>       estimation, or index selectivity=E2=80=94when using UUIDv7 as a sur=
rogate 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=E2=80=99d appreciate any insight into whether UUIDv7 is a good candidat=
e for
> partitioning from a performance standpoint, and how well partition prunin=
g
> behaves in practice.
>
> Best regards,
> Jon
>

--0000000000008164530641c5ae4b
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">I don&#39;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=20
together with block range indexes.</div><br><div class=3D"gmail_quote gmail=
_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Wed, Oct 22, 202=
5 at 12:53=E2=80=AFPM Jonathan Reis &lt;<a href=3D"mailto:jon.reis@conevity=
.com">jon.reis@conevity.com</a>&gt; wrote:<br></div><blockquote class=3D"gm=
ail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,=
204,204);padding-left:1ex"><div dir=3D"auto"><div class=3D"gmail_quote" dir=
=3D"auto"><div dir=3D"ltr" class=3D"gmail_attr"><span style=3D"font-family:=
&quot;trebuchet ms&quot;,sans-serif">Hello PostgreSQL performance team,</sp=
an></div><div dir=3D"ltr"><div class=3D"gmail_default" style=3D"font-family=
:trebuchet ms,sans-serif">
<p>I=E2=80=99m evaluating the new <code>UUIDv7</code> type in PostgreSQL v1=
8 and would like advice on its suitability for time-based partitioning and =
related planner behavior.</p>
<p><strong>Context</strong><br>
I have a large message/event table where each row is identified by a <code>=
uuidv7</code> primary key. Because UUIDv7 embeds a timestamp component in i=
ts most significant bits, I=E2=80=99m considering using it as the partition=
 key instead of a separate <code>timestamptz</code> column.</p>
<p><strong>Questions</strong></p>
<ol>
<li>
<p><strong>Partitioning on UUIDv7 ranges</strong></p>
<ul>
<li>
<p>Is range partitioning by UUIDv7 considered practical or advisable for ti=
me-based data?</p>
</li>
<li>
<p>Will the planner efficiently prune partitions when queries filter by UUI=
Dv7 ranges (e.g., <code>WHERE id BETWEEN=C2=A0</code>uuidv7_floor(<code>tim=
estamp1) AND=C2=A0</code>uuidv7_floor(<code>timestamp2)</code>=C2=A0that al=
ign with time periods?</p></li>
<li>
<p>Are there known drawbacks=E2=80=94such as statistics accuracy, correlati=
on estimation, or index selectivity=E2=80=94when using UUIDv7 as a surrogat=
e for timestamptz?</p>
</li>
</ul>
</li>
<li>
<p><strong>Conversion between timestamptz and UUIDv7</strong></p>
<ul>
<li>
<p>Is there a built-in or community-recommended method to convert between <=
code>timestamptz</code> and <code>uuidv7</code> values? I am currently usin=
g this<br><br>CREATE OR REPLACE FUNCTION uuidv7_floor(ts timestamptz)<br>=
=C2=A0 RETURNS uuid<br>=C2=A0 LANGUAGE sql<br>=C2=A0 IMMUTABLE<br>AS $$<br>=
WITH ms AS (<br>=C2=A0 SELECT floor(extract(epoch FROM ts) * 1000)::bigint =
AS ms<br>),<br>=C2=A0 =C2=A0 =C2=A0h AS (<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0SEL=
ECT lpad(to_hex(ms), 12, &#39;0&#39;) AS h FROM ms<br>=C2=A0 =C2=A0 =C2=A0)=
<br>SELECT (<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0substr(h.h,1,8) || &#39;-=
&#39; ||<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0substr(h.h,9,4) || &#39;-&#39=
; ||<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0&#39;7000&#39; || &#39;-&#39; || =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 -- version 7 + rand_a all zero<br>=C2=A0 =C2=A0=
 =C2=A0 =C2=A0 =C2=A0&#39;8000&#39; || &#39;-&#39; || =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 -- variant &#39;10&#39; + rest zero<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0&#39;000000000000&#39; =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -- zero nod=
e<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0)::uuid<br>FROM h;<br>$$;</p></li>
</ul>
</li>
</ol>
<p><strong>Example</strong></p>
<pre><div><div><div><div></div></div></div><div dir=3D"ltr"><code><span>CRE=
ATE</span> <span>TABLE</span> message (
  id uuidv7 <span>PRIMARY KEY</span>,
  payload <span>jsonb</span>,
  received_at <span>timestamptz</span> <span>DEFAULT</span> now()
)
<span>PARTITION BY RANGE</span> (id);
</code></div></div></pre>
<p>I=E2=80=99d appreciate any insight into whether UUIDv7 is a good candida=
te for partitioning from a performance standpoint, and how well partition p=
runing behaves in practice.</p>
<p>Best regards,<br>
Jon</p></div></div>
</div></div>
</blockquote></div>

--0000000000008164530641c5ae4b--