postgresqldatabase-administration

Create additional partitions to an existing Postgres table


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.


Solution

  • 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