PostgreSQL 10 High Performance

Partition maintenance

Now we will see how to remove old partitions of data and periodically add new partitions for new data.

If we want to detach a partition, we have to execute the following:

pgbench=# alter table orders_state detach partition orders_state_q4; 
ALTER TABLE 
pgbench=# select * from orders_state; 
 orderid | orderdate  | customerid |  tax  | state  
---------+------------+------------+-------+------- 
       1 | 2018-01-15 |          1 | 10.00 | IT 
       1 | 2018-04-15 |          1 | 10.00 | US 
       1 | 2018-07-15 |          1 | 10.00 | FR 

If we want to create and add a new partition, we use the following:

CREATE TABLE orders_state_q5 PARTITION OF orders_state FOR VALUES FROM ('2019-01-01') TO ('2019-03-01'); 

If we want to attach an existing table, we use the following:

alter ...