Message-ID: <cc128c674065c06ebc0d1eb5e76fa2598e78c1c9.camel@cybertec.at>
Subject: Re: Performance implications of partitioning by UUIDv7 range in
 PostgreSQL v18
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Jonathan Reis <jon.reis@conevity.com>, Greg Sabino Mullane
	 <htamfids@gmail.com>
Cc: Olof Salberger <olof.salberger@gmail.com>,
	pgsql-performance@lists.postgresql.org
Date: Thu, 23 Oct 2025 23:38:11 +0300
In-Reply-To: 
 <CAE_7N35fiC7Rq03hWutO3tPGfADQeO_gQzKHru1iNiASmUdsPQ@mail.gmail.com>
References: 
	<CAE_7N37MopcS6SYmsphE7UxQ9bJTyvgsmVb-NyCncy7fzkiC4Q@mail.gmail.com>
	 <CAE_7N37Amq_G6bZDpv9tbxcZJNGVhSv8mt2MJgdZTqGO5Pdbnw@mail.gmail.com>
	 <CAD39LRHMO7nEKhub=RyeZH=+Qw=qo6gSNuByDLWsR9-EDmGjhg@mail.gmail.com>
	 <CAKAnmmJq3_wFNp4RN15t_YRv9xARVby7=-4PrADwPuaQQYC6JA@mail.gmail.com>
	 <CAE_7N35fiC7Rq03hWutO3tPGfADQeO_gQzKHru1iNiASmUdsPQ@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-2.fc42) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/cc128c674065c06ebc0d1eb5e76fa2598e78c1c9.camel%40cybertec.at>
Precedence: bulk

On Thu, 2025-10-23 at 13:11 -0700, Jonathan Reis wrote:
> Thank you very much for your recommendations and your sample code. I orig=
inally had it your way, but then I found out this is not possible
>=20
> create table message (
> =C2=A0 id uuid PRIMARY KEY
> =C2=A0 -- ... plus other columns
> ) partition by range (uuid_extract_timestamp(id));

That's because you want to make "id" a primary key, but you can only create=
 a primary
key constraint on a partitioned table if the partitioning key is a subset o=
f the
primary key.

I recommend that you create a primary key on each partition rather than hav=
ing one
on the partitioned table.

Yours,
Laurenz Albe