Move partition from one table to another table PostgreSQL 10.11

Question

I am new to postgreSQL, I am working on a project where I am requested to move all the partitions older than 6 months to a legacy table so that the query on the table would be faster. I have the partition table with 10 years of data.

Lets assume if myTable is the table with current 6 months data and myTable_legacy is going to have all the data older than 6 months for up-to 10 years. The table is partitioned by monthly range

My questions that I researched online and unable to conclude are

I am currently testing before finalizing the steps, I was using below link as reference for my lab testing, and before performing the actual migration.

How to migrate an existing Postgres Table to partitioned table as transparently as possible?

 create table myTable(
 forDate date not null,
 key2 int not null,
 value int not null
 ) partition by range (forDate);


 create table myTable_legacy(
 forDate date not null,
 key2 int not null,
 value int not null
 ) partition by range (forDate);

1)Daily application query will be only on the current 6 month data. Is it necessary to move data older than 6 months to a new partition to get a better response of query. I researched online but wasn't able to find any solid evidence related to the same.

2)If performance going to be better, How to move older partitions from myTable to myTable_legacy. Based on my research, I can see that we don't have option of exchange partition in PostgreSQL.

Any help or guidance would help me proceed further with the requirement.

When I try to attach partition to mytable_legacy, I am getting error

 alter table mytable detach partition mytable_200003;
 alter table mytable_legacy attach partition mytable_200003 
      for values from ('2003-03-01') to ('2003-03-30');

results in:

ERROR: partition constraint is violated by some row
SQL state: 23514

The contents of the partition:

select * from mytable_200003;

"2000-03-02"    1   19
"2000-03-30"    15  8

 

Answer 1

It's always better to keep the production table light, One of the practices that i do is to use timestamp and write trigger function that will insert row in the other table if timestamp is less than now() (6 months old data).

Answer 2

Quote from the manual

When creating a range partition, the lower bound specified with FROM is an inclusive bound, whereas the upper bound specified with TO is an exclusive bound

^{(emphasis mine)}

So the expression to ('2003-30-03') does not allow March, 30st to be inserted into the partition.

Additionally your data in mytable_200003 is for the year 2000, not for the year 2003 (which you use in your partition definition). To specify the complete march, simply use April, 1st as the upper bound

So you need to change the partition definition to cover March 2000 not March 2003.

alter table mytable_legacy 
   attach partition mytable_200003 
   for values from ('2000-03-01') to ('2000-04-01');
                        ^ here            ^ here

Online example