Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tIrke-00H9of-2c for pgsql-general@arkaria.postgresql.org; Wed, 04 Dec 2024 15:57:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tIrkb-000viV-Hp for pgsql-general@arkaria.postgresql.org; Wed, 04 Dec 2024 15:57:18 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tIpKl-00HJsb-K1 for pgsql-general@lists.postgresql.org; Wed, 04 Dec 2024 13:22:28 +0000 Received: from mail-ej1-x62d.google.com ([2a00:1450:4864:20::62d]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tIpKk-0010ln-A0 for pgsql-general@postgresql.org; Wed, 04 Dec 2024 13:22:27 +0000 Received: by mail-ej1-x62d.google.com with SMTP id a640c23a62f3a-aa578d10d50so1082746866b.1 for ; Wed, 04 Dec 2024 05:22:26 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1733318544; x=1733923344; darn=postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=LyOT5dKT2kzy9y4STZ7L+bkFRLoW4TFCAtPnBuAmYxA=; b=fqQ88OkQqRpOg9WkJmZUiGexnSZHwi/IF0ItbksSfD+gTNmCxShTbmgg/n7BXKLFUn WDsDz+DfgJoLO+t55U0dJt2w2ZVkw5S6CWDuXZ7N2G5Lr+RSG2yLrR3aX2Y0fQMYw9G+ xMJv9vNYlD60+ceb1Hrst1JQvQ/nCfRB2V/x9pkC1GTAsYq9y5DnJDddK1W+0kJw0qXW +D2FWu8aLAeYjUaY0TLHh6SFCqmbG3bkso3LsazthLOj0zcQ/TtGmC7lEJafsE0yK9tV ImPwwG795oUfzWVt4rF2lGwDUuNzcJVqb23ULplBYhS0idX5GOOrjyjXEc4Biri/PoOQ 0N6w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1733318544; x=1733923344; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=LyOT5dKT2kzy9y4STZ7L+bkFRLoW4TFCAtPnBuAmYxA=; b=Z9jCy9g3FByUxuTwXT/3OefCVL1SehxYFtIy903wZlFe6dJBPBfcORqVRVVW05iwmz +jmwQgK1YW0MPwdHaoyBIYKG71Pgb1NOzeDXT7eUtP+OiFBaE/R0HqIZtpM0UHZqY1r6 iOGHfUGAmlD52X8nERX/4sIjShlRI9vnCbTwHC4eLX7UU74onBms5rXAtZtMh44tSQHd z+7GXrGsqyaj3wJydKO1adNvjVTHUOLxb9V1L6dyaRzAliR4dvFXVgMCquWYMHQK15Qx vYyzLWT5h+Qz8HFnr/BJdwGfZwYBkZX3axR1+g9mmWkqE1qsJfY+mh6TD4r+06kM/xIO PDOQ== X-Gm-Message-State: AOJu0YxpodH4v3qhxcMdNcDutdFAJRgBPOJFRNqGcGIFIsgV9TmcM7BA cOpGrMSAKLtqj1yNJHzLZBdhhDqrvJsxsiez6dhiMFyLk4YH94lUOy1P+JujU4YL161EnxTryiL 8cYqViGQFGvCESlGeTdnEhSY6t4usyoqP4Sw= X-Gm-Gg: ASbGncs4GW53UM/YwIQ4K6nKK5B+vPhWZnpSF1IaKl0qOMIM5JYY4iaHFlUpFSVjUbV z6NT1Apy49JLN2q5Bnneh1oW0rkAqHOA= X-Google-Smtp-Source: AGHT+IGMx+pbjpQme5qPDIUyDSUFSo7vTJOxagUbAiUnyexDC9zMRBkrdaY/JjQJQLpMjEs7LkZSBP52G6r8pcCoe7c= X-Received: by 2002:a17:906:30d8:b0:aa5:f333:e4fd with SMTP id a640c23a62f3a-aa6018d9825mr463944866b.43.1733318543457; Wed, 04 Dec 2024 05:22:23 -0800 (PST) MIME-Version: 1.0 From: Bolaji Wahab Date: Wed, 4 Dec 2024 14:22:12 +0100 Message-ID: Subject: Clarification of behaviour when dropping partitions To: pgsql-general@postgresql.org Content-Type: multipart/alternative; boundary="00000000000010c576062871aceb" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000010c576062871aceb Content-Type: text/plain; charset="UTF-8" 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. --00000000000010c576062871aceb Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi team,

I have these two partitioned t= ables, with=C2=A0referential integrity. The tables are structured=C2=A0in s= uch=C2=A0a way that we have 1 to 1 mapping between their partitions. This i= s achieved with a foreign key.

```
CREAT= E TABLE parent (
=C2=A0 =C2=A0 partition_date date NOT NULL,
=C2=A0 = =C2=A0 id uuid NOT NULL,
=C2=A0 =C2=A0 external_transaction_id uuid NOT = NULL,

=C2=A0 =C2=A0 CONSTRAINT parent_pkey
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 PRIMARY KEY (id, partition_date),

=C2=A0 =C2=A0 CONSTRAINT pa= rent_external_transaction_id_key
=C2=A0 =C2=A0 =C2=A0 =C2=A0 UNIQUE (ext= ernal_transaction_id, partition_date)
) PARTITION BY RANGE (partition_da= te);

CREATE TABLE parent_2024_12_01
=C2=A0 =C2=A0 PARTITION OF pu= blic.parent
=C2=A0 =C2=A0 FOR VALUES FROM ('2024-12-01') TO (= 9;2024-12-02');

CREATE TABLE parent_2024_12_02
=C2=A0 =C2=A0 = PARTITION OF public.parent
=C2=A0 =C2=A0 FOR VALUES FROM ('2024-12-0= 2') TO ('2024-12-03');

CREATE TABLE parent_2024_12_03=C2=A0 =C2=A0 PARTITION OF public.parent
=C2=A0 =C2=A0 FOR VALUES FROM = ('2024-12-03') TO ('2024-12-04');

CREATE TABLE paren= t_2024_12_04
=C2=A0 =C2=A0 PARTITION OF public.parent
=C2=A0 =C2=A0 F= OR VALUES FROM ('2024-12-04') TO ('2024-12-05');

CRE= ATE TABLE parent_2024_12_05
=C2=A0 =C2=A0 PARTITION OF public.parent
= =C2=A0 =C2=A0 FOR VALUES FROM ('2024-12-05') TO ('2024-12-06= 9;);

CREATE TABLE child (
=C2=A0 =C2=A0 partition_date date NOT N= ULL,
=C2=A0 =C2=A0 transaction_id uuid NOT NULL,
=C2=A0 =C2=A0 key = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0text NOT NULL,
=C2=A0 =C2=A0 va= lue =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0text NOT NULL,

=C2=A0 =C2=A0 C= ONSTRAINT child_pkey
=C2=A0 =C2=A0 =C2=A0 =C2=A0 PRIMARY KEY (transactio= n_id, key, partition_date),

=C2=A0 =C2=A0 CONSTRAINT child_transacti= on_id_fkey
=C2=A0 =C2=A0 =C2=A0 =C2=A0 FOREIGN KEY (transaction_id, part= ition_date)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 REFERENCES parent (id, partition= _date)
) PARTITION BY RANGE (partition_date);

CREATE TABLE child_= 2024_12_01
=C2=A0 =C2=A0 PARTITION OF child
=C2=A0 =C2=A0 FOR VALUES = FROM ('2024-12-01') TO ('2024-12-02');

CREATE TABLE = child_2024_12_02
=C2=A0 =C2=A0 PARTITION OF public.child
=C2=A0 =C2= =A0 FOR VALUES FROM ('2024-12-02') TO ('2024-12-03');
CREATE TABLE child_2024_12_03
=C2=A0 =C2=A0 PARTITION OF public.child<= br>=C2=A0 =C2=A0 FOR VALUES FROM ('2024-12-03') TO ('2024-12-04= ');

CREATE TABLE child_2024_12_04
=C2=A0 =C2=A0 PARTITION OF = public.child
=C2=A0 =C2=A0 FOR VALUES FROM ('2024-12-04') TO (&#= 39;2024-12-05');

CREATE TABLE child_2024_12_05
=C2=A0 =C2=A0 = PARTITION OF public.child
=C2=A0 =C2=A0 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 ref= erences

```
ALTER TABLE parent DETACH PA= RTITION parent_2024_12_01;
DROP TABLE parent_2024_12_01;
```
I noticed the job was taking a long time and affecting the performanc= e of the database.

After debugging, I found this q= uery used internally by Postgres.
```
SELECT fk."t= ransaction_id", fk."partition_date"
FROM "public&qu= ot;."child" fk
JOIN "public"."parent_2024_12_0= 1" pk ON
(pk."id" OPERATOR(pg_catalog.=3D) fk."tran= saction_id" AND pk."partition_date" OPERATOR(pg_catalog.=3D)= fk."partition_date")
WHERE ((pk.partition_date IS NOT NULL)AND (pk.partition_date >=3D '2024-12-01'::date) AND (pk.partit= ion_date < '2024-12-02'::date))
AND (fk."transaction_id&= quot; IS NOT NULL AND fk."partition_date" IS NOT NULL)
<= div>```
Which of course is not able to do partition pruning on th= e child table. Wondering if this is somehow the expectation=C2=A0or an edge= case. One would have expected the optimiser to target the child partition = with the available foreign key.

Postgres Version: = 14.10

Thanks.

--00000000000010c576062871aceb--