MIME-Version: 1.0
References: 
 <CAE_7N37MopcS6SYmsphE7UxQ9bJTyvgsmVb-NyCncy7fzkiC4Q@mail.gmail.com>
In-Reply-To: 
 <CAE_7N37MopcS6SYmsphE7UxQ9bJTyvgsmVb-NyCncy7fzkiC4Q@mail.gmail.com>
From: Jonathan Reis <jon.reis@conevity.com>
Date: Tue, 21 Oct 2025 20:37:30 -0700
Message-ID: 
 <CAE_7N37Amq_G6bZDpv9tbxcZJNGVhSv8mt2MJgdZTqGO5Pdbnw@mail.gmail.com>
Subject: Performance implications of partitioning by UUIDv7 range in
 PostgreSQL v18
To: pgsql-performance@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000f733a30641b7096a"
Archived-At: 
 <https://www.postgresql.org/message-id/CAE_7N37Amq_G6bZDpv9tbxcZJNGVhSv8mt2MJgdZTqGO5Pdbnw%40mail.gmail.com>
Precedence: bulk

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

Hello PostgreSQL performance team,

I=E2=80=99m 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=E2=80=99m considering using it as the partition key ins=
tead 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, correl=
ation
      estimation, or index selectivity=E2=80=94when using UUIDv7 as a surro=
gate 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 candidate =
for
partitioning from a performance standpoint, and how well partition pruning
behaves in practice.

Best regards,
Jon

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

<div dir=3D"auto"><div class=3D"gmail_quote gmail_quote_container" dir=3D"a=
uto"><div dir=3D"ltr" class=3D"gmail_attr"><span style=3D"font-family:&quot=
;trebuchet ms&quot;,sans-serif">Hello PostgreSQL performance team,</span></=
div><div dir=3D"ltr"><div class=3D"gmail_default" style=3D"font-family:treb=
uchet 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>

--000000000000f733a30641b7096a--