Subject: Clarification of behaviour when dropping partitions

From: Bolaji Wahab <[email-masked]>
Date: Wed, 4 Dec 2024 14:22:12 +0100

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');
```

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;
```

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)
```
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.

---

From: Laurenz Albe <[email-masked]>
Date: Wed, 04 Dec 2024 18:20:55 +0100

On Wed, 2024-12-04 at 14:22 +0100, Bolaji Wahab wrote:
> [...]

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