PostgreSQL attaching range partition is slow

Question

I have a range partitioned table where I would like to be able to swap out partitions over the same range quickly (that is, re-create tables and swap out existing partitioned tables for them). However, it's taking a long time to attach partitions even though they have all the requisite indexes.

-- create a partitioned table
create table "partitioned" (
  "k1" INTEGER NOT NULL,
  "k2" INTEGER NOT NULL,
  PRIMARY KEY("k1", "k2")
) PARTITION BY RANGE "k1";

-- create the table to be added as a partition
create table "segment_1_000_000_to_2_000_000" (
  "k1" INTEGER NOT NULL,
  "k2" INTEGER NOT NULL,
  PRIMARY KEY("k1", "k2")
);

-- insert 300,000,000 rows into table "segment_1_000_000_to_2_000_000" with "k1" values between values 1,000,000 and 2,000,000
-- ...

-- takes 5 minutes:
alter table "partitioned" attach partition "segment_1_000_000_to_2_000_000 for values from (1000000) to (2000000);

-- takes 38 milliseconds:
alter table "partitioned" detach partition "segment_1_000_000_to_2_000_000 for values from (1000000) to (2000000);

-- takes 5 minutes:
alter table "partitioned" attach partition "segment_1_000_000_to_2_000_000 for values from (1000000) to (2000000);

For reference, no other queries are running on the table while I do this.

Furthermore, queries for min("k1") and max("k1") run very fast (~51 milliseconds) so I assume it's not attaching slow because of the range bounds check although the bounds check seems the most likely culprit for slowness.

So why is adding the partition so slow and how can it be sped up?

Laurenz Albe · Accepted Answer · 2022-12-02 18:35:15Z

7

PostgreSQL has to scan the table that is to be attached as a partition to verify that all rows fall into the partition boundaries. That can take a while for a large table. PostgreSQL can skip that check if there is already a fitting check constraint on the new partition:

ALTER TABLE segment_1_000_000_to_2_000_000
   ADD CHECK (k1 IS NOT NULL AND number >= 1000000 AND number < 2000000);

If number is defined as NOT NULL, the first check can be omitted.

You can verify that your constraint was good by setting client_min_messages to debug1, then the ALTER TABLE ... ATTACH will emit a message like

DEBUG:  partition constraint for table "DEBUG:  partition constraint for table "x1" is implied by existing constraints" is implied by existing constraints

edited Dec 2, 2022 at 18:35

answered Dec 2, 2022 at 7:41

Laurenz Albe

62.6k4 gold badges58 silver badges94 bronze badges

Oops, sorry about the table definitions. I will ammend them now. FYI your solution works perfectly!

nick314
– nick314

2022-12-02 18:23:34 +00:00
Commented Dec 2, 2022 at 18:23

Add a comment |

Stack Exchange Network

PostgreSQL attaching range partition is slow

1 Answer 1

Your Answer

Hot Network Questions

PostgreSQL attaching range partition is slow

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions