So basically we have a very large table in Postgres 11 DB which has hundreds of millions of data since the table was added. Now we are trying to convert it into a range based partition table based on created_at column (timestamp - not nullable).
As suggested in the Postgres Partitioning Documentation, I tried adding a check constraint on the same table before actually running the attach partition. So after that if I run attach partition, ideally it should have taken very less time as it should skip the validation due to presence of respective constraint on the table, but I see it is still taking lot more time. My partition range and the constraint looks something like this:
alter table xyz_2020 add constraint temp_check check (created_at >= '2020-01-01 00:00:00' and created_at < '2021-01-01 00:00:00');
ALTER TABLE xyz ATTACH PARTITION xyz_2020 FOR VALUES FROM ('2020-01-01 00:00:00') TO ('2021-01-01 00:00:00');
Here xyz_2020 is my existing big table which got renamed from xyz. And xyz is the new master table created like the old table. So I want to understand what could be the possible reasons that attach partition might still be taking lot more time.
Edit: We are creating a new partitioned table and trying to attach the old table as one of its partition.