MIME-Version: 1.0
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>
 <cc128c674065c06ebc0d1eb5e76fa2598e78c1c9.camel@cybertec.at>
 <CAApHDvqcjH9y_ofuBB8XnFA1JPZEcYSLzGss1+i9xPTcmcHLjQ@mail.gmail.com>
In-Reply-To: 
 <CAApHDvqcjH9y_ofuBB8XnFA1JPZEcYSLzGss1+i9xPTcmcHLjQ@mail.gmail.com>
From: Jonathan Reis <jon.reis@conevity.com>
Date: Thu, 23 Oct 2025 19:14:15 -0700
Message-ID: 
 <CAE_7N376toqc-RBpER=p4goYvjb57xinEoCGHJ2Y70aDQJwn5w@mail.gmail.com>
Subject: Re: Performance implications of partitioning by UUIDv7 range in
 PostgreSQL v18
To: David Rowley <dgrowleyml@gmail.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>,
 Greg Sabino Mullane <htamfids@gmail.com>,
	Olof Salberger <olof.salberger@gmail.com>,
 pgsql-performance@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000066c8430641de1d82"
Archived-At: 
 <https://www.postgresql.org/message-id/CAE_7N376toqc-RBpER%3Dp4goYvjb57xinEoCGHJ2Y70aDQJwn5w%40mail.gmail.com>
Precedence: bulk

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

Thank you all for your input on this. Here is a summary of what I have
learned from you all.

Approach 1: partition on uuid_extract_timestamp(id)

Pros: No need for custom function to convert from timestamptz to uuidv7
      Partitions are human-readable
      Can use pg_partman
Cons: Cannot have a primary key on id
      Cannot use foreign keys

Approach 2: partition on id

Pros: Can have a primary key on id
      Can have foreign key constraints

Cons  Require custom function to convert from timestamptz to uuidv7
      Partitions ranges must be decoded to be human-readable
      Can't use pg_partman (this is true?)