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 1vC1fC-0084kR-OH for pgsql-performance@arkaria.postgresql.org; Thu, 23 Oct 2025 20:11:58 +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 1vC1fB-00BRWw-5p for pgsql-performance@arkaria.postgresql.org; Thu, 23 Oct 2025 20:11:56 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1vC1fA-00BRWf-MK for pgsql-performance@lists.postgresql.org; Thu, 23 Oct 2025 20:11:55 +0000 Received: from mail-lf1-x12c.google.com ([2a00:1450:4864:20::12c]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vC1f7-003rkU-08 for pgsql-performance@lists.postgresql.org; Thu, 23 Oct 2025 20:11:55 +0000 Received: by mail-lf1-x12c.google.com with SMTP id 2adb3069b0e04-57992ba129eso1660170e87.3 for ; Thu, 23 Oct 2025 13:11:52 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=conevity-com.20230601.gappssmtp.com; s=20230601; t=1761250311; x=1761855111; 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=4pkQClKrsSvfwSAHQYV86xk6we3NkgRw4JNhkVNXqWk=; b=YAgr3/s521vECahv1+tDk1jlGGhjjvXq1W86YBXTbQsr8WUG2K32yIoZANUAsQLE+E j77GwPAw7FnCcGgx/z3JZJOSQKzYYs7XzTgOkXmx7kM3WVmxWOp/q9yTg+6ffH9JzSfd aKe/uLi3NH0ErjHZPavaLzBAMt/byz7fJTO9dzG+ucfh2OLkaT8OFMQuZYz+VjYAlkBu M1ogY8uYmdjB1mfQ6IZJ1eRlgEtlcDZj+SDTZjVX1gVR6SvNrI5TArGKJQJNjBJX/L0+ kQ6tD5nX8IS5+hzlpgzkqYPY1GQIIOQ4bMgxZqsc0SUp3oqCdQfywvCwLSV2R0ZI0pMw gKSg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1761250311; x=1761855111; 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=4pkQClKrsSvfwSAHQYV86xk6we3NkgRw4JNhkVNXqWk=; b=mVxR/QVApZcL3pA55taPlDn/Iuca9bx6ViRHG3uohB6H0w0R+Xz22lYFMdphWwIFk8 zZdqG4JtiLX65j75mIafJt3iTRa05TSyBn2MetIOTrVrX9Ory+xahu9QNkDrKqPbEJUA cGmkogFiRLR64M4wcjf930/Txw8GOvkRU/p/J+G5EJAqL5x95YuzY06m9P59ds2/EnI+ rCLA9Udd1hAXa1VSWV+wYwJnMJmQED2rB6bGDm/Z02As5Vi5t6qvb/AO7k9yHenQ7MHx UHqCIgc1rWB3ixcsBNuwBff67Irj7LFJDTAYjjU2Hf56xuj7IqQH6FeoCnXV4zgsxdBY o04g== X-Forwarded-Encrypted: i=1; AJvYcCWRfFw3aRZLcyn3DI6L71qfXpZld5SDZpEZWFO9r8nBdp900JQkBXjZPEVm9amTi3vcoOOsilVQ2KIYFy5fYLlhhw==@lists.postgresql.org X-Gm-Message-State: AOJu0YxYsGMbSAUu/LuQgxEzPv+WyKgxc+1JKmy7Y/6iDCbxa+3NYWkr PYQqP/ZsmG4cLFt79rb7QZDbvAp7TpPPTCerJbuC0iwO3DPAHJ5sAuqMHvJxHkJ3vt/tLdaUHup B4IHgGYQ= X-Gm-Gg: ASbGnct+ozyFHwEuMp/wWQqe7OYV6swIiI5zdc6Er45O7BDHjYnqfnUTBclrJBKxUJ2 7enRfbhIb7wEtVpVgwGGopxW1WiO922E+yZrTsYjmicYFhpNVbI4krIVVSIHFSBEZ6Bx7DjkjJt 7n5FJWL0VKdRN77Z4jF0IX6QT/DWnAiaIGev7MDyFrkiSXLxZRwHbMWcdWhSsNKXQoqLNRigNvf U3iO4XZUimsYVS8eHWrwBN5Ivz+RfiGzrV5S41c7+PNs+QJJtJLO22mvC8hEnVmA0xtcnl1EFRF WGqO2IPkP50RjC8qJHpf08dykMKm9BwcFeaY2Koisu1Z13iKB2WjnbKe5tdamrc97+uQUKdLUIc dW6p5o5MdocKsP41Y7t8gwOjahjYK5E5+ZP4sVYTZ9yMDGGJNSqrM/wG30k2XSiU+5hAcHqNPXs ahuIzCEz8p4oRaYGfE2vMnGlK5i2Ziz/hgmVT8/qrp9T10Vygh/U50YRNMMAtNtBIr X-Google-Smtp-Source: AGHT+IGk58otK34UtVmWeFsnVAH5KkaWNSP99YA6vmr676E6nZq+qxKUhp8HEUS0qKsq7YdlWXO6Iw== X-Received: by 2002:a05:6512:10d2:b0:590:9a11:9c33 with SMTP id 2adb3069b0e04-592f5a6c476mr1321213e87.49.1761250310320; Thu, 23 Oct 2025 13:11:50 -0700 (PDT) Received: from mail-lj1-f180.google.com (mail-lj1-f180.google.com. [209.85.208.180]) by smtp.gmail.com with ESMTPSA id 2adb3069b0e04-592f4d20743sm992067e87.86.2025.10.23.13.11.48 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 23 Oct 2025 13:11:49 -0700 (PDT) Received: by mail-lj1-f180.google.com with SMTP id 38308e7fff4ca-378d6fa5aebso13458871fa.2 for ; Thu, 23 Oct 2025 13:11:48 -0700 (PDT) X-Forwarded-Encrypted: i=1; AJvYcCWaJAOVE7KDzie4kanX7MwrD1lMfMPSqodrwmFsHv/eBLNBRe2O9GKfyRrkfrDayFQKuSdFjenrRACtSkdgtQUBeA==@lists.postgresql.org X-Received: by 2002:a05:6512:3e06:b0:591:c521:6b19 with SMTP id 2adb3069b0e04-592f592c5a2mr1143974e87.25.1761250308494; Thu, 23 Oct 2025 13:11:48 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Jonathan Reis Date: Thu, 23 Oct 2025 13:11:11 -0700 X-Gmail-Original-Message-ID: X-Gm-Features: AS18NWCRzS2RD8uRrnUfy_wN5nW90KVmDC0jKh5J4722L7HyfAhI90AVU-b4cSw Message-ID: Subject: Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18 To: Greg Sabino Mullane Cc: Olof Salberger , pgsql-performance@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000ff997d0641d90af7" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ff997d0641d90af7 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Greg, Thank you very much for your recommendations and your sample code. I originally had it your way, but then I found out this is not possible create table message ( id uuid PRIMARY KEY -- ... plus other columns ) partition by range (uuid_extract_timestamp(id)); whereas, this is create table message ( id uuid PRIMARY KEY -- ... plus other columns ) partition by range (id); Also, I had a misunderstanding that in this query explain select * from message where uuid_extract_timestamp(id) between '2025-10-23 23:00:00' and '2025-10-24 03:00:00'; that having uuid_extract_timestamp(id) on the left was SARGable, but clearly it is based on your sample. Thank you again, this was very helpful. On Thu, Oct 23, 2025 at 9:52=E2=80=AFAM Greg Sabino Mullane wrote: > 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 =3D 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 =3D 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) =3D > '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=3D0.29..5.29 rows=3D160) > Index Cond: (uuid_extract_timestamp(id) =3D '2025-10-23 > 10:23:45-04'::timestamptz) > > > QUERY PLAN > > -------------------------------------------------------------------------= ------------------------------ > Append (cost=3D0.29..5.04 rows=3D2) > -> Index Scan using m_2025_10_23_id on message_2025_10_23 message_1 > (cost=3D0.29..2.51 rows=3D1) > Index Cond: ((uuid_extract_timestamp(id) >=3D '2025-10-23 > 23:00:00-04'::timestamptz) > AND (uuid_extract_timestamp(id) <=3D '2025-10-24 > 03:00:00-04'::timestamptz)) > -> Index Scan using m_2025_10_24_id on message_2025_10_24 message_2 > (cost=3D0.30..2.52 rows=3D1) > Index Cond: ((uuid_extract_timestamp(id) >=3D '2025-10-23 > 23:00:00-04'::timestamptz) > AND (uuid_extract_timestamp(id) <=3D '2025-10-24 > 03:00:00-04'::timestamptz)) > > > > Cheers, > Greg > > -- > Crunchy Data - https://www.crunchydata.com > Enterprise Postgres Software Products & Tech Support > > --000000000000ff997d0641d90af7 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Greg,

Thank you very much for your recommendations and your sampl= e code. I originally had it your way, but then I found out this is not poss= ible

create table message (
=C2= =A0 id uuid PRIMARY KEY
=C2=A0 -- ... plus other columns
) partition = by range (uuid_extract_timestamp(id));

whereas, this is

create table mes= sage (
=C2=A0 id uuid PRIMARY KEY
=C2=A0 -- ... plus other columns) partition by range (id);

Also, I = had a misunderstanding that in this query

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

that having uuid_extract_timestamp(id) on the left was SARGable, b= ut clearly it is based on your sample.

Thank you again, this was very helpful.

=C2=A0
<= div dir=3D"ltr" class=3D"gmail_attr">On Thu, Oct 23, 2025 at 9:52=E2=80=AFA= M Greg Sabino Mullane <htamfids@gm= ail.com> wrote:
I think from a practical standpoin= t, partitioning directly on uuidv7 is going to cause problems. You can'= t directly see the partition constraints, you have to do tricks like your f= loor 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 extract= ed timestamp? That way, queries are simplified, timestamps will not span mu= ltiple tables, partitions are human-readable again, and you can use pg_part= man once more. Untested for large-scale performance, but something like thi= s:

\set ON_ERROR_STOP on
drop schema 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_ti= mestamp(id));

create table message_2025_10_22 partition of message f= or 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 par= tition of message for values from ('2025-10-24') to ('2025-10-2= 5');

create index m_2025_10_22_id on message_2025_10_22 (uuid_ex= tract_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_2= 025_10_24 (uuid_extract_timestamp(id));

-- Today:
insert into mes= sage 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 =3D 1.= 1; -- SSD rulez
vacuum analyze message;

select count(id) from onl= y 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;
<= br>explain select * from message where uuid_extract_timestamp(id) =3D '= 2025-10-23 10:23:45';

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

<= /div>

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
---------------------------= --------------------------------------------------------------------------<= br>=C2=A0Index Scan using m_2025_10_23_id on message_2025_10_23 message =C2= =A0(cost=3D0.29..5.29 rows=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_202= 5_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'::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&#= 39;::timestamptz))
=C2=A0 =C2=A0-> =C2=A0Index Scan using m_2025_10_2= 4_id on message_2025_10_24 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-1= 0-24 03:00:00-04'::timestamptz))



Cheers,
Greg

--
Crunchy Data - h= ttps://www.crunchydata.com
Enterprise Postgres Software Produ= cts & Tech Support

--000000000000ff997d0641d90af7--