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 Fr
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 q
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
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
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
co
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
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 ta
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
I don't know if it will necessarily be of much use in partition pruning,
but it should work fairly well as a choice of clustered primary key
together with block range indexes.
On Wed, Oct 22, 2025 at 12:53 PM Jonathan Reis
wrote:
> Hello PostgreSQL performance team,
>
> I’m evaluating the new UU
On Wed, 22 Oct 2025 at 23:53, Jonathan Reis wrote:
> Will the planner efficiently prune partitions when queries filter by UUIDv7
> ranges (e.g., WHERE id BETWEEN uuidv7_floor(timestamp1) AND
> uuidv7_floor(timestamp2) that align with time periods?
It depends. What are timestamp1 and timestamp2?
Hello PostgreSQL performance team,
I’m evaluating the new UUIDv7 type in PostgreSQL v18 and would like advice
on its suitability for time-based partitioning and related planner behavior.
*Context*
I have a large message/event table where each row is identified by a uuidv7
primary key. Because UUI
11 matches
Mail list logo
