Thread: Clarification of behaviour when dropping partitions
Hi team,
I have these two partitioned tables, with referential integrity. The tables are structured in such a way that we have 1 to 1 mapping between their partitions. This is achieved with a foreign key.
```
CREATE TABLE parent (
partition_date date NOT NULL,
id uuid NOT NULL,
external_transaction_id uuid NOT NULL,
CONSTRAINT parent_pkey
PRIMARY KEY (id, partition_date),
CONSTRAINT parent_external_transaction_id_key
UNIQUE (external_transaction_id, partition_date)
) PARTITION BY RANGE (partition_date);
CREATE TABLE parent_2024_12_01
PARTITION OF public.parent
FOR VALUES FROM ('2024-12-01') TO ('2024-12-02');
CREATE TABLE parent_2024_12_02
PARTITION OF public.parent
FOR VALUES FROM ('2024-12-02') TO ('2024-12-03');
CREATE TABLE parent_2024_12_03
PARTITION OF public.parent
FOR VALUES FROM ('2024-12-03') TO ('2024-12-04');
CREATE TABLE parent_2024_12_04
PARTITION OF public.parent
FOR VALUES FROM ('2024-12-04') TO ('2024-12-05');
CREATE TABLE parent_2024_12_05
PARTITION OF public.parent
FOR VALUES FROM ('2024-12-05') TO ('2024-12-06');
CREATE TABLE child (
partition_date date NOT NULL,
transaction_id uuid NOT NULL,
key text NOT NULL,
value text NOT NULL,
CONSTRAINT child_pkey
PRIMARY KEY (transaction_id, key, partition_date),
CONSTRAINT child_transaction_id_fkey
FOREIGN KEY (transaction_id, partition_date)
REFERENCES parent (id, partition_date)
) PARTITION BY RANGE (partition_date);
CREATE TABLE child_2024_12_01
PARTITION OF child
FOR VALUES FROM ('2024-12-01') TO ('2024-12-02');
CREATE TABLE child_2024_12_02
PARTITION OF public.child
FOR VALUES FROM ('2024-12-02') TO ('2024-12-03');
CREATE TABLE child_2024_12_03
PARTITION OF public.child
FOR VALUES FROM ('2024-12-03') TO ('2024-12-04');
CREATE TABLE child_2024_12_04
PARTITION OF public.child
FOR VALUES FROM ('2024-12-04') TO ('2024-12-05');
CREATE TABLE child_2024_12_05
PARTITION OF public.child
FOR VALUES FROM ('2024-12-05') TO ('2024-12-06');
partition_date date NOT NULL,
id uuid NOT NULL,
external_transaction_id uuid NOT NULL,
CONSTRAINT parent_pkey
PRIMARY KEY (id, partition_date),
CONSTRAINT parent_external_transaction_id_key
UNIQUE (external_transaction_id, partition_date)
) PARTITION BY RANGE (partition_date);
CREATE TABLE parent_2024_12_01
PARTITION OF public.parent
FOR VALUES FROM ('2024-12-01') TO ('2024-12-02');
CREATE TABLE parent_2024_12_02
PARTITION OF public.parent
FOR VALUES FROM ('2024-12-02') TO ('2024-12-03');
CREATE TABLE parent_2024_12_03
PARTITION OF public.parent
FOR VALUES FROM ('2024-12-03') TO ('2024-12-04');
CREATE TABLE parent_2024_12_04
PARTITION OF public.parent
FOR VALUES FROM ('2024-12-04') TO ('2024-12-05');
CREATE TABLE parent_2024_12_05
PARTITION OF public.parent
FOR VALUES FROM ('2024-12-05') TO ('2024-12-06');
CREATE TABLE child (
partition_date date NOT NULL,
transaction_id uuid NOT NULL,
key text NOT NULL,
value text NOT NULL,
CONSTRAINT child_pkey
PRIMARY KEY (transaction_id, key, partition_date),
CONSTRAINT child_transaction_id_fkey
FOREIGN KEY (transaction_id, partition_date)
REFERENCES parent (id, partition_date)
) PARTITION BY RANGE (partition_date);
CREATE TABLE child_2024_12_01
PARTITION OF child
FOR VALUES FROM ('2024-12-01') TO ('2024-12-02');
CREATE TABLE child_2024_12_02
PARTITION OF public.child
FOR VALUES FROM ('2024-12-02') TO ('2024-12-03');
CREATE TABLE child_2024_12_03
PARTITION OF public.child
FOR VALUES FROM ('2024-12-03') TO ('2024-12-04');
CREATE TABLE child_2024_12_04
PARTITION OF public.child
FOR VALUES FROM ('2024-12-04') TO ('2024-12-05');
CREATE TABLE child_2024_12_05
PARTITION OF public.child
FOR VALUES FROM ('2024-12-05') TO ('2024-12-06');
```
I have a scheduled job that removes old partitions with simply `DROP TABLE`. It processes the child table first, then the parent table. For example
First transaction (works fine and quick):
```
DROP TABLE child_2024_12_01;
```
Second transaction (slow, degrading performance):
Here, I had to detach the partition first because of the inherited references
```
ALTER TABLE parent DETACH PARTITION parent_2024_12_01;
DROP TABLE parent_2024_12_01;
DROP TABLE parent_2024_12_01;
```
I noticed the job was taking a long time and affecting the performance of the database.
I noticed the job was taking a long time and affecting the performance of the database.
After debugging, I found this query used internally by Postgres.
```
SELECT fk."transaction_id", fk."partition_date"
FROM "public"."child" fk
JOIN "public"."parent_2024_12_01" pk ON
(pk."id" OPERATOR(pg_catalog.=) fk."transaction_id" AND pk."partition_date" OPERATOR(pg_catalog.=) fk."partition_date")
WHERE ((pk.partition_date IS NOT NULL)
AND (pk.partition_date >= '2024-12-01'::date) AND (pk.partition_date < '2024-12-02'::date))
AND (fk."transaction_id" IS NOT NULL AND fk."partition_date" IS NOT NULL)
FROM "public"."child" fk
JOIN "public"."parent_2024_12_01" pk ON
(pk."id" OPERATOR(pg_catalog.=) fk."transaction_id" AND pk."partition_date" OPERATOR(pg_catalog.=) fk."partition_date")
WHERE ((pk.partition_date IS NOT NULL)
AND (pk.partition_date >= '2024-12-01'::date) AND (pk.partition_date < '2024-12-02'::date))
AND (fk."transaction_id" IS NOT NULL AND fk."partition_date" IS NOT NULL)
```
Which of course is not able to do partition pruning on the child table. Wondering if this is somehow the expectation or an edge case. One would have expected the optimiser to target the child partition with the available foreign key.
Postgres Version: 14.10
Thanks.
Thanks.
On Wed, 2024-12-04 at 14:22 +0100, Bolaji Wahab wrote:
> I have these two partitioned tables, with referential integrity. The tables
> are structured in such a way that we have 1 to 1 mapping between their
> partitions. This is achieved with a foreign key.
>
> CREATE TABLE parent (
> partition_date date NOT NULL,
> id uuid NOT NULL,
> external_transaction_id uuid NOT NULL,
>
> CONSTRAINT parent_pkey
> PRIMARY KEY (id, partition_date),
>
> CONSTRAINT parent_external_transaction_id_key
> UNIQUE (external_transaction_id, partition_date)
> ) PARTITION BY RANGE (partition_date);
>
> CREATE TABLE parent_2024_12_01
> PARTITION OF public.parent
> FOR VALUES FROM ('2024-12-01') TO ('2024-12-02');
>
> CREATE TABLE parent_2024_12_02
> PARTITION OF public.parent
> FOR VALUES FROM ('2024-12-02') TO ('2024-12-03');
>
> [...]
>
> CREATE TABLE child (
> partition_date date NOT NULL,
> transaction_id uuid NOT NULL,
> key text NOT NULL,
> value text NOT NULL,
>
> CONSTRAINT child_pkey
> PRIMARY KEY (transaction_id, key, partition_date),
>
> CONSTRAINT child_transaction_id_fkey
> FOREIGN KEY (transaction_id, partition_date)
> REFERENCES parent (id, partition_date)
> ) PARTITION BY RANGE (partition_date);
>
> CREATE TABLE child_2024_12_01
> PARTITION OF child
> FOR VALUES FROM ('2024-12-01') TO ('2024-12-02');
>
> CREATE TABLE child_2024_12_02
> PARTITION OF public.child
> FOR VALUES FROM ('2024-12-02') TO ('2024-12-03');
I recommend that you don't create the foreign key constraint between the
partitioned tables, but between the individual partitions.
That will make detaching and dropping partitions easier, and you will have
the same integrity guarantees.
Yours,
Laurenz Albe
Yes, this is what I have done.
But the whole point of declaring the foreign key constraint on the partitioned table is to have it automatically created on subsequent/future partitions.
On Wed, Dec 4, 2024 at 6:20 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Wed, 2024-12-04 at 14:22 +0100, Bolaji Wahab wrote:
> I have these two partitioned tables, with referential integrity. The tables
> are structured in such a way that we have 1 to 1 mapping between their
> partitions. This is achieved with a foreign key.
>
> CREATE TABLE parent (
> partition_date date NOT NULL,
> id uuid NOT NULL,
> external_transaction_id uuid NOT NULL,
>
> CONSTRAINT parent_pkey
> PRIMARY KEY (id, partition_date),
>
> CONSTRAINT parent_external_transaction_id_key
> UNIQUE (external_transaction_id, partition_date)
> ) PARTITION BY RANGE (partition_date);
>
> CREATE TABLE parent_2024_12_01
> PARTITION OF public.parent
> FOR VALUES FROM ('2024-12-01') TO ('2024-12-02');
>
> CREATE TABLE parent_2024_12_02
> PARTITION OF public.parent
> FOR VALUES FROM ('2024-12-02') TO ('2024-12-03');
>
> [...]
>
> CREATE TABLE child (
> partition_date date NOT NULL,
> transaction_id uuid NOT NULL,
> key text NOT NULL,
> value text NOT NULL,
>
> CONSTRAINT child_pkey
> PRIMARY KEY (transaction_id, key, partition_date),
>
> CONSTRAINT child_transaction_id_fkey
> FOREIGN KEY (transaction_id, partition_date)
> REFERENCES parent (id, partition_date)
> ) PARTITION BY RANGE (partition_date);
>
> CREATE TABLE child_2024_12_01
> PARTITION OF child
> FOR VALUES FROM ('2024-12-01') TO ('2024-12-02');
>
> CREATE TABLE child_2024_12_02
> PARTITION OF public.child
> FOR VALUES FROM ('2024-12-02') TO ('2024-12-03');
I recommend that you don't create the foreign key constraint between the
partitioned tables, but between the individual partitions.
That will make detaching and dropping partitions easier, and you will have
the same integrity guarantees.
Yours,
Laurenz Albe
On Wed, 2024-12-04 at 23:00 +0100, Bolaji Wahab wrote: > On Wed, Dec 4, 2024 at 6:20 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote: > > On Wed, 2024-12-04 at 14:22 +0100, Bolaji Wahab wrote: > > > I have these two partitioned tables, with referential integrity. The tables > > > are structured in such a way that we have 1 to 1 mapping between their > > > partitions. This is achieved with a foreign key. > > > > I recommend that you don't create the foreign key constraint between the > > partitioned tables, but between the individual partitions. > > > > That will make detaching and dropping partitions easier, and you will have > > the same integrity guarantees. > > Yes, this is what I have done. > But the whole point of declaring the foreign key constraint on the partitioned > table is to have it automatically created on subsequent/future partitions. Sure, but then you have to accept the disadvantage that it becomes more difficult to detach partitions. I think it is less pain to create the constraint on the partition level. Yours, Laurenz Albe
Yes, right. I wonder if the team sees an opportunity for some optimization here, supporting such a scenario efficiently. I can't think of any downsides to it but I may be missing something.
On Thu, Dec 5, 2024 at 2:38 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Wed, 2024-12-04 at 23:00 +0100, Bolaji Wahab wrote:
> On Wed, Dec 4, 2024 at 6:20 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > On Wed, 2024-12-04 at 14:22 +0100, Bolaji Wahab wrote:
> > > I have these two partitioned tables, with referential integrity. The tables
> > > are structured in such a way that we have 1 to 1 mapping between their
> > > partitions. This is achieved with a foreign key.
> >
> > I recommend that you don't create the foreign key constraint between the
> > partitioned tables, but between the individual partitions.
> >
> > That will make detaching and dropping partitions easier, and you will have
> > the same integrity guarantees.
>
> Yes, this is what I have done.
> But the whole point of declaring the foreign key constraint on the partitioned
> table is to have it automatically created on subsequent/future partitions.
Sure, but then you have to accept the disadvantage that it becomes more
difficult to detach partitions. I think it is less pain to create the
constraint on the partition level.
Yours,
Laurenz Albe