1

I'm attempting to merge a table into a partition, the merge works but the performance isn't good. I've read this link, in particular this quote....

Before running the ATTACH PARTITION command, it is recommended to create a CHECK constraint on the table to be attached describing the desired partition constraint. That way, the system will be able to skip the scan to validate the implicit partition constraint. Without such a constraint, the table will be scanned to validate the partition constraint while holding an ACCESS EXCLUSIVE lock on the parent table. One may then drop the constraint after ATTACH PARTITION is finished, because it is no longer necessary.

Here's the SQL I'm using....

ALTER TABLE mess_201811 ADD CONSTRAINT const_201811 CHECK ( loadedtime >=  DATE '2018-11-01' AND loadedtime < DATE '2018-11-23' );
ALTER TABLE mess ATTACH PARTITION mess_201811 FOR VALUES FROM ('2018-11-01') TO ('2018-11-23'); 
ALTER TABLE mess_201811 DROP CONSTRAINT const_201811 ;

As close as possible I've copied my SQL from the link mentioned. The three commands all work without problem, the issue is the time it takes. Adding the constraint takes about 60 seconds (fair enough, there are about 2 million records in the table). However the ATTACH PARTITION command also takes about 60 seconds, this suggests to me that table is being scanned a second time validate the partition. My understand was that with the CHECK constraint in place the ATTACH should be a simple DDL statement and would only take a fraction of a second.

The problem is that ATTACH PARTITION requires an exclusive lock on the table mess which prevents inserting records for 60 seconds.

I'm using postgres v10.5. the definition for table mess ends with

PARTITIONED BY loadedtime; 

At the time of my testing there were no other users on the database and no lock issues.


Following jjanes solution, I've checked the original documentation, the example quoted does use a NOT NULL field constraint although it make no mention of this being mandatory. I'm hoping that the additional constraint won't have a noticeable effect on normal inserts to this table.

  • Can you show a full reproducible test case, including the CREATE TABLE commands? – jjanes Nov 25 '18 at 15:16
2

I can only reproduce this if mess_201811.loadedtime is nullable, in which case it has to be scanned for NULL values upon attachment. If the column is NOT NULL, then the scan upon attachment is skipped.

  • That seems to do the trick - I've changed loadedTime to NOT NULL and the attachment scan is skipped, as suggested. – Hemel Nov 25 '18 at 19:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.