Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1vByY9-007V1J-VB for pgsql-performance@arkaria.postgresql.org; Thu, 23 Oct 2025 16:52:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1vByY7-00ANWf-06 for pgsql-performance@arkaria.postgresql.org; Thu, 23 Oct 2025 16:52:26 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1vByY6-00ANWW-I8 for pgsql-performance@lists.postgresql.org; Thu, 23 Oct 2025 16:52:25 +0000 Received: from mail-il1-x12c.google.com ([2607:f8b0:4864:20::12c]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vByY3-003Nri-1g for pgsql-performance@lists.postgresql.org; Thu, 23 Oct 2025 16:52:24 +0000 Received: by mail-il1-x12c.google.com with SMTP id e9e14a558f8ab-430ca464785so10576585ab.0 for ; Thu, 23 Oct 2025 09:52:23 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1761238342; x=1761843142; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=mwnm5op2ecPIGqZNS2DU/CXvrACAKQl4oTIgmJNJMZY=; b=DbuBHMgQSQDPh924qN5VJ3suW+pIBQ2hEa7PXl9ciU4vqT6xAry7609oRLQMISu3K3 8i2kIbON4uflL4rhUmlq1vI43sP+WZmige8YeXgfD7V9H6+8YDgQbHmwVJ1LffyiGIDf 3PeOFA02lwvm67V8qcJvoTaMzPHJ0VF9hxnncC55AK6kegyPIPTzHbxDiIU65LHEIgSL z5mrOWV0C5+M+d+bO/KpaIxkpM6vaUSzUPRkXvM1tUUeFqhrqejKPuNSbHetDfCs9+YV MCJWe/j3ZGG87CEgWoYSfodSWttwTgUTYRDoIovM2kuJeETn44c0GpbnOhTMTcDKHQJN 99UA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1761238342; x=1761843142; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=mwnm5op2ecPIGqZNS2DU/CXvrACAKQl4oTIgmJNJMZY=; b=HO0mo1XNvbF70upM5ul3Ip47L7InULn/c//E519ZGNJcb4G+MULdIfgTQgr8/gGF9J WrbpAZP7TERNGNWGKIGeXCDP7X3UguBDX25gDmyBxih7utoPW7uVmGFXPp17dM2bg7/h zYz/Bn31UU55eoybTW4E7dJkpsVWHJDR+9xB3ocp6PBoW0+qhFEPlrOYf+hJFN63rc17 5XzQOgJjmIavRinnzuYlx6Y37ft6TM4b/IwhQ4800m+zDQ68wpI7M7xoXgR+qgew+DIS Z+BSm/xOG3VJH0/3eOmNgQhszZ9pxuuwPqKtAJyXJrCQ7tDVFS1E8V9sDYJHw11ennkJ sNTw== X-Forwarded-Encrypted: i=1; AJvYcCUdfMPSVA/rymOeUXm7UIWCLJcgVxP/APVKCjs959kpQB92oqzydbh30OkUbAvs4QCg+mU7YeoC+hrzAWiiIsatTA==@lists.postgresql.org X-Gm-Message-State: AOJu0YxOwL30No0APE5DArqLJxsVtDsHiGXrx2JDBTYbfUY5aolcbu4s 3AhklGQjw4y/PU10X5lq67OLlmfSKNs+wFKioo9GUHyRzpwl3sM5odB22TPKetwEQ3FrrWLNXCH ZYfPeE48bSTpITHzIffeqcSqT5EoB/UY= X-Gm-Gg: ASbGncvrZ72/HC6z4xe459OLbzwktpM2Z1CD7csso8kNAgnLoQrh41R5az4cKznCt2U 6kwa7rWATubH27RRN0jzdiO6RqXxDPYVed55du9u8UlQjwT1jmPxyiC2QeXuequuORC3RtD21nJ nNZBZr+GC2tSdGX8KSfsRCfYoW6gWES4+I/SkfupEDRRqp/dDpYrnmQu/MTzp/olgfQvZQJUIO0 EoY5+jEGDsBbsqXFghAXXqROa2L4BGuLMeI+iGxOosVqIewroiL6xjoo1v82DpxmR2lCgE/SFF2 faqNBrmkcyetxsPdhD0= X-Google-Smtp-Source: AGHT+IEDaq/qMnYYX+3s64SDU1vXYMBspd6O+xAEcjlV8fV5S33GSPXZoTPWJftn8ZZYGU0qExjO8qj+DAjHVr6/ofY= X-Received: by 2002:a05:6e02:749:b0:431:d951:ab83 with SMTP id e9e14a558f8ab-431dc2329dbmr40347865ab.32.1761238342460; Thu, 23 Oct 2025 09:52:22 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Greg Sabino Mullane Date: Thu, 23 Oct 2025 12:51:47 -0400 X-Gm-Features: AWmQ_bkxSSn3KUs1jGl0niCLnZmIm1AY63-rHV-oib6JhWLYSI26OL2DkAR2bvc Message-ID: Subject: Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 To: Olof Salberger Cc: Jonathan Reis , pgsql-performance@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000c46af50641d641c8" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000c46af50641d641c8 Content-Type: text/plain; charset="UTF-8" I think from a practical standpoint, partitioning directly on uuidv7 is going to cause problems. You can't directly see the partition constraints, you have to do tricks like your floor function to make it work, and you have to be super careful in how you construct your where clauses. However, what if you partition by the extracted timestamp? That way, queries are simplified, timestamps will not span multiple tables, partitions are human-readable again, and you can use pg_partman once more. Untested for large-scale performance, but something like this: \set ON_ERROR_STOP on drop schema if exists gregtest cascade; create schema gregtest; set search_path = gregtest; create table message ( id uuid -- ... plus other columns ) partition by range (uuid_extract_timestamp(id)); create table message_2025_10_22 partition of message for values from ('2025-10-22') to ('2025-10-23'); create table message_2025_10_23 partition of message for values from ('2025-10-23') to ('2025-10-24'); create table message_2025_10_24 partition of message for values from ('2025-10-24') to ('2025-10-25'); create index m_2025_10_22_id on message_2025_10_22 (uuid_extract_timestamp(id)); create index m_2025_10_23_id on message_2025_10_23 (uuid_extract_timestamp(id)); create index m_2025_10_24_id on message_2025_10_24 (uuid_extract_timestamp(id)); -- Today: insert into message select uuidv7() from generate_series(1, 111_000); -- Yesterday: insert into message select uuidv7('-1 day') from generate_series(1, 222_000); -- Tomorrow: insert into message select uuidv7('+1 day') from generate_series(1, 333_000); set random_page_cost = 1.1; -- SSD rulez vacuum analyze message; select count(id) from only message; select count(id) from message_2025_10_22; select count(id) from message_2025_10_23; select count(id) from message_2025_10_24; explain select * from message where uuid_extract_timestamp(id) = '2025-10-23 10:23:45'; explain select * from message where uuid_extract_timestamp(id) between '2025-10-23 23:00:00' and '2025-10-24 03:00:00'; Which gives this output when run: count ------- 0 count -------- 222000 count -------- 111000 count -------- 333000 QUERY PLAN ----------------------------------------------------------------------------------------------------- Index Scan using m_2025_10_23_id on message_2025_10_23 message (cost=0.29..5.29 rows=160) Index Cond: (uuid_extract_timestamp(id) = '2025-10-23 10:23:45-04'::timestamptz) QUERY PLAN ------------------------------------------------------------------------------------------------------- Append (cost=0.29..5.04 rows=2) -> Index Scan using m_2025_10_23_id on message_2025_10_23 message_1 (cost=0.29..2.51 rows=1) Index Cond: ((uuid_extract_timestamp(id) >= '2025-10-23 23:00:00-04'::timestamptz) AND (uuid_extract_timestamp(id) <= '2025-10-24 03:00:00-04'::timestamptz)) -> Index Scan using m_2025_10_24_id on message_2025_10_24 message_2 (cost=0.30..2.52 rows=1) Index Cond: ((uuid_extract_timestamp(id) >= '2025-10-23 23:00:00-04'::timestamptz) AND (uuid_extract_timestamp(id) <= '2025-10-24 03:00:00-04'::timestamptz)) Cheers, Greg -- Crunchy Data - https://www.crunchydata.com Enterprise Postgres Software Products & Tech Support --000000000000c46af50641d641c8 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
I think from a practical standpoint, part= itioning directly on uuidv7 is going to cause problems. You can't direc= tly see the partition constraints, you have to do tricks like your floor fu= nction to make it work, and you have to be super careful in how you constru= ct your where clauses. However, what if you partition by the extracted time= stamp? That way, queries are simplified, timestamps will not span multiple = tables, partitions are human-readable again, and you can use pg_partman onc= e more. Untested for large-scale performance, but something like this:
=
\set ON_ERROR_STOP on

drop s= chema if exists gregtest cascade;
create schema gregtest;
set search_= path =3D gregtest;

create table message (
=C2=A0 id uuid
=C2= =A0 -- ... plus other columns
) partition by range (uuid_extract_timesta= mp(id));

create table message_2025_10_22 partition of message for va= lues from ('2025-10-22') to ('2025-10-23');
create table= message_2025_10_23 partition of message for values from ('2025-10-23&#= 39;) to ('2025-10-24');
create table message_2025_10_24 partitio= n of message for values from ('2025-10-24') to ('2025-10-25'= ;);

create index m_2025_10_22_id on message_2025_10_22 (uuid_extract= _timestamp(id));
create index m_2025_10_23_id on message_2025_10_23 (uui= d_extract_timestamp(id));
create index m_2025_10_24_id on message_2025_1= 0_24 (uuid_extract_timestamp(id));

-- Today:
insert into message = select uuidv7() from generate_series(1, 111_000);
-- Yesterday:
inser= t into message select uuidv7('-1 day') from generate_series(1, 222_= 000);
-- Tomorrow:
insert into message select uuidv7('+1 day'= ) from generate_series(1, 333_000);

set random_page_cost =3D 1.1; --= SSD rulez
vacuum analyze message;

select count(id) from only mes= sage;
select count(id) from message_2025_10_22;
select count(id) from= message_2025_10_23;
select count(id) from message_2025_10_24;

ex= plain select * from message where uuid_extract_timestamp(id) =3D '2025-= 10-23 10:23:45';

explain select * from message where uuid_extrac= t_timestamp(id)
=C2=A0 between '2025-10-23 23:00:00' and '20= 25-10-24 03:00:00';


= Which gives this output when run:

=C2=A0count
-------
=C2=A0 =C2=A0 =C2=A00

=C2=A0count=
--------
=C2=A0222000

=C2=A0count
--------
=C2=A0111000=

=C2=A0count
--------
=C2=A0333000


=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0QUERY PLAN
---------------------------------------------------= --------------------------------------------------
=C2=A0Index Scan usin= g m_2025_10_23_id on message_2025_10_23 message =C2=A0(cost=3D0.29..5.29 ro= ws=3D160)
=C2=A0 =C2=A0Index Cond: (uuid_extract_timestamp(id) =3D '= 2025-10-23 10:23:45-04'::timestamptz)


=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 QUERY PLAN
---------------------------------= ----------------------------------------------------------------------
Append =C2=A0(cost=3D0.29..5.04 rows= =3D2)
=C2=A0 =C2=A0-> =C2= =A0Index Scan using m_2025_10_23_id on message_2025_10_23 message_1 =C2=A0(= cost=3D0.29..2.51 rows=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond= : ((uuid_extract_timestamp(id) >=3D '2025-10-23 23:00:00-04'::ti= mestamptz)
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0AND (uuid_extract_ti= mestamp(id) <=3D '2025-10-24 03:00:00-04'::timestamptz))
=C2= =A0 =C2=A0-> =C2=A0Index Scan using m_2025_10_24_id on message_2025_10_2= 4 message_2 =C2=A0(cost=3D0.30..2.52 rows=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0Index Cond: ((uuid_extract_timestamp(id) >=3D '2025-10-23 = 23:00:00-04'::timestamptz)
= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0AND (uuid_extract_timestamp(id) <=3D '2025-10-24 03:00:00-04'= ::timestamptz))



Cheers= ,
Greg

--
Enterprise Postgres Software Products & Tech Suppor= t

--000000000000c46af50641d641c8--