How to migrate from inheritance-based partitioning to declarative partitioning in PostgreSQL
Partitioning is one of the coolest features in the latest PostgreSQL versions.
PostgreSQL 10 introduced native partitioning and more recent versions have continued to improve upon this feature.
This article provides a guide to move from inheritance based partitioning to declarative partitioning, using the native features found in PostgreSQL 11+.
The process itself moves the child tables, detaching them from the inheritance partition and attaching to the new declarative partitioned table. Note these instructions do not cover moving to declarative partitioning on PostgreSQL 10 since some key features that make this migration easier were not yet implemented.
Also, this procedure assumes that the parent table in the inheritance partitioning is empty.
The following steps are tested on a PostgreSQL 12 instance, but can also be used on PostgreSQL 11.
Initial notes used in this article
Suppose we have a table partitioned with the old method of inheritance, with child tables, triggers, constraints and so on, as in the following example:
sales=# \d+ orders Table "public.orders" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ------------+-----------------------------+-----------+----------+---------+----------+--------------+------------- product_id | integer | | not null | | plain | | address | text | | not null | | extended | | order_time | timestamp without time zone | | not null | | plain | | Triggers: orders_insert_trigger BEFORE INSERT ON orders FOR EACH ROW EXECUTE FUNCTION orders_insert() Child tables: orders_2018, orders_2019, orders_2020 Access method: heap
A child table looks like this:
sales=# \d+ orders_2018 Table "public.orders_2018" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ------------+-----------------------------+-----------+----------+---------+----------+--------------+------------- product_id | integer | | not null | | plain | | address | text | | not null | | extended | | order_time | timestamp without time zone | | not null | | plain | | Indexes: "orders_2018_idx" btree (order_time) Check constraints: "orders_2018_order_time_check" CHECK (order_time >= '2018-01-01'::date AND order_time < '2019-01-01'::date) Inherits: orders Access method: heap
Overview of the steps to move child partitions
The way we recommend moving this partitioning schema to a declarative one is by following these steps:
- Create a new table with the same columns as the parent table from the inheritance schema
- For each child table in the inheritance schema perform these steps
- Detach the child table from its parent using
- Attach the former child table to the new declarative partition using the
- Detach the child table from its parent using
- Create needed indexes on the new parent table. Also views, functions and other objects
- Drop the original parent table used with inheritance partitioning
- Rename the new parent table so it replaces the old one
Preparing for the migration to declarative partitioning
Before starting the migration process, it’s important to halt any processes that may be automatically maintaining your partitions sets (creating new child tables and/or applying retention policies).
Make sure you disable all maintenance jobs that could perform changes directly or indirectly on the set of tables to migrate, be that through cronjobs or execution of tools like
As first step, we suggest to take a logical backup at database level of the table partitioned through inheritance and all the child tables. For example, you could run something like that:
pg_dump -F c -f sales.dmp sales -v
This is not mandatory, but it’s strongly suggested in order to be able to restore the data in case something goes wrong while performing the next steps.
Next, we need to create a new parent table using native partitioning, taking care to use the same definition of the old parent table.
We have to specify the column we want to use as the partition key in the
PARTITION BY clause. This choice should be natural, once we have checked the constraints on the child tables.
In the example above the constraints are based on the
order_time field. So we can partition
FOR RANGE using
order_time as the partition key, in this way:
CREATE TABLE orders_part ( product_id INT NOT NULL, address TEXT NOT NULL, order_time TIMESTAMP NOT NULL ) PARTITION BY RANGE (order_time);
Moving child tables to the new declarative schema
It is a good idea to take the next steps in a single transaction. By doing so, if you run into any issues before you’ve completed the migration process, you can simply rollback and return to the state your database was before the transaction started.
So, please, run:
The first major step in this migration process now is to remove all the child tables from the old parent, using the
NO INHERIT clause, using the following statement in the case of our example above:
ALTER TABLE orders_2018 NO INHERIT orders;
This has to be done for each child table.
Now we need to insert the boundary values for the existing child tables using the
ATTACH PARTITION command in the declarative partitioning, as below:
ALTER TABLE orders_part ATTACH PARTITION orders_2018 FOR VALUES FROM ('2018-01-01 00:00:00') TO ('2019-01-01 00:00:00');
Again, we need to perform such a step for each child table for our declarative partitioning.
You may also need to create indexes on the new parent table. From PostgreSQL 11 this can be done by adding the index only once for the partitioned table and it automatically applies to all partitions, existing and future.
In this example, all the child-tables already had the required index and PostgreSQL was intelligent enough not to create a new index similar to another index already existing. So, it will suffice just creating an index on the partitioned table, so that the optimizer knows about it.
CREATE INDEX orders_time ON orders (order_time);
If you have some other SQL objects (such as views, constraints, etc…) on the inheritance-based parent table, don’t forget to recreate them on the new parent table.
And remember that if you still have data in the inheritance parent table you will have to move those rows separately. You can set a
DEFAULT partition for that, although that’s discouraged. Instead we suggest finding such rows and creating new child tables which they can fit in. You’ll have to move them manually to the appropriate partition.
Finally, you can drop the inheritance parent table and all the objects created on it, and rename the new partitioned table with the same name of the inheritance parent, in order that all the queries, functions, etc. continue to work properly on the new partitioned table instead of on the old one:
DROP TABLE orders CASCADE; ALTER TABLE orders_part RENAME TO orders;
At this point you can drop the constraints on the child tables:
ALTER TABLE orders_2018 DROP CONSTRAINT orders_2018_order_time_check;
\d+ command on the parent table and on the individuals partitions to check that everything went fine:
sales_part=# \d+ orders Partitioned table "public.orders" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ------------+-----------------------------+-----------+----------+---------+----------+--------------+------------- product_id | integer | | not null | | plain | | address | text | | not null | | extended | | order_time | timestamp without time zone | | not null | | plain | | Partition key: RANGE (order_time) Indexes: "orders_time" btree (order_time) Partitions: orders_2018 FOR VALUES FROM ('2018-01-01 00:00:00') TO ('2019-01-01 00:00:00'), orders_2019 FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2020-01-01 00:00:00'), orders_2020 FOR VALUES FROM ('2020-01-01 00:00:00') TO ('2021-01-01 00:00:00')
The migration process should be completed now but don’t forget to commit your work:
A special “Thank you” to my new colleague Ashutosh Bapat, who wrote this blog article, from which I took a cue and that I adapted to PostgreSQL 12.
Much appreciate for the valuable article, Caterina
It is nice guide to implement declarative partitioning migration from inheritance-based one.
For next step, I’m looking for automated adding partition solution that’s hard to find.
Keep inspiring PostgreSQL users with your expertise…