public inbox for [email protected]  

help / color / mirror / Atom feed

From: Bolaji Wahab <[email protected]>
To: [email protected]
Subject: Clarification of behaviour when dropping partitions
Date: Wed, 4 Dec 2024 14:22:12 +0100
Message-ID: <CA+f_mLPhCa_G3nRrB4=e3trqCSQ88x56ZvfeweH-AhwS77xEGg@mail.gmail.com> (raw)

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.

view thread (2+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: Clarification of behaviour when dropping partitions
  In-Reply-To: <CA+f_mLPhCa_G3nRrB4=e3trqCSQ88x56ZvfeweH-AhwS77xEGg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox