I have a table with 1T of data and the table has some 50 partition. Each partition holds a week of data. However I am looking to create additional partitions to hold additional weekly of data. To do this, I am creating a table and attaching with the main table. The command is as follows:
Create table test.2023w327
partition of test.main_table
For values from (‘2023-07-03’) To (‘2023-07-10’);
My question is this a right way to do it. Also would this lock the table? I don’t know yet how much time this will table since the table holds 1T of data. Suggestions please.
Yes, that is a correct way to create a partition for a range partitioned table, except that you forgot to double quote the table name (which starts with a number). However, it will require a short ACCESS EXCLUSIVE lock on the partitioned table.
To perform that operation with a lock that is less strong, you can
CREATE TABLE test.part_2023w327 (LIKE test.main_table);
ALTER TABLE test.main_table
ATTACH PARTITION test.part_2023w327
FOR VALUES FROM (‘2023-07-03’) TO (‘2023-07-10’);