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?

Answer 1

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

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