Upgrade your Partitioning from Inheritance to Declarative
Before PostgreSQL 10, Postgres users partitioned their data using inheritance based partitioning. The method used constraints to define the partitions and rules or triggers to route the data to appropriate partition. A user had to write and maintain code for all of that. PostgreSQL 10 introduced declarative partitioning, which is much easier to set up and requires almost no maintenance.
PostgreSQL 11 is adding a number of partitioning related enhancements that work with declarative partitioning. Users who have implemented inheritance based partitioning would want to move to declarative partitioning (after upgrading to v11, of course) to benefit from those features. Here's how they can do so.
You may have created a parent table and several child tables, one per partition, triggers, rules, and constraints as required. Here's an example setup similar to the one described in the PostgreSQL documentation.
\d+ measurement Table "inh_part.measurement" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description -----------+---------+-----------+----------+---------+---------+--------------+------------- city_id | integer | | not null | | plain | | logdate | date | | not null | | plain | | peaktemp | integer | | | | plain | | unitsales | integer | | | | plain | | Child tables: measurement_y2006m02, measurement_y2006m03, measurement_y2006m04, measurement_y2006m05, measurement_y2006m06
Here's how a child looks:
\d+ measurement_y2006m03 Table "inh_part.measurement_y2006m03" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description -----------+---------+-----------+----------+---------+---------+--------------+------------- city_id | integer | | not null | | plain | | logdate | date | | not null | | plain | | peaktemp | integer | | | | plain | | unitsales | integer | | | | plain | | Indexes: "measurement_y2006m03_logdate" btree (logdate) Check constraints: "measurement_y2006m03_logdate_check" CHECK (logdate >= '2006-03-01'::date AND logdate < '2006-04-01'::date) Inherits: measurement
Moving to declarative partitioning
One could simply create a partitioned table and required number of partitions, then create indexes and other objects on this partitioned table except the constraints, rules, and triggers used for inheritance partitioning, and then copy the data from the inheritance parent to the partitioned table using SELECT INTO. A user may optimize data movement by copying data from child-table to the corresponding partition again using SELECT INTO. But PostgreSQL offers something better, an ability to ATTACH an existing table as a partition to a partitioned table. This method is faster compared to other methods since there is no data movement involved. In the experiment I ran with a few MBs of partitioned data, it was 2X faster. As the data grows, data movement takes a longer time, even if you move data from child-tables to partitions. The time to ATTACH child-tables as partitions, however, doesn't increase with the size of data. Here are the steps:
Continue reading this post here at the community forum Postgres Rocks.
Ashutosh Bapat is a Database Developer at EnterpriseDB.