Horton

Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18

Laurenz Albe <laurenz.albe@cybertec.at>

View thread
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: 2025-10-23T20:38:11Z
Lists: pgsql-performance 
On Thu, 2025-10-23 at 13:11 -0700, Jonathan Reis wrote:
> 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));

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 of the
primary key.

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

Yours,
Laurenz Albe