Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18
On Thu, Oct 23, 2025 at 10:14 PM Jonathan Reis wrote: > Can't use pg_partman (this is true?) > Apologies, this warrants an explanation. It turns out I was wrong to be concerned. I was worried about pg_partman being able to partition by the decoded value of a column, but it already handles that quite well. From the docs (where col3 is of type uuid): SELECT partman.create_parent( p_parent_table := 'partman_test.time_taptest_table' , p_control := 'col3' , p_interval := '1 day' , p_time_encoder := 'partman.uuid7_time_encoder' , p_time_decoder := 'partman.uuid7_time_decoder' ); This also kind of answers your other question about your uuidv7_floor function. While not native to Postgres, the uuid7_time_encoder function used above is extremely similar to yours. See https://github.com/pgpartman/pg_partman/blob/development/sql/functions/util_uuid7_time.sql Cheers, Greg -- Crunchy Data - https://www.crunchydata.com Enterprise Postgres Software Products & Tech Support
Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18
Great point. One of the main reasons we are using partitioning is to quickly drop partitions containing old data so we wouldn't be implementing foreign key constraints any way. On Thu, Oct 23, 2025 at 10:04 PM Laurenz Albe wrote: > On Fri, 2025-10-24 at 11:54 +1300, David Rowley wrote: > > On Fri, 24 Oct 2025 at 09:38, Laurenz Albe > wrote: > > > I recommend that you create a primary key on each partition rather > than having one > > > on the partitioned table. > > > > It might be worth mentioning that doing that would forego having the > > ability to reference the partitioned table in a foreign key > > constraint. > > Right, but referencing a partitioned table with a foreign key is a mixed > blessing > anyway: you could no longer drop partitions from the partitioned table > without > scanning the referencing table to verify that the foreign key is not > violated. > > Yours, > Laurenz Albe >
