Playing with Postgres Partitioning

by Tykling

17. nov 2019 16:32 UTC

Background

When database tables grow big it can help a lot to introduce table partitioning. I have been playing around with it and wanted to record my procedure for future reference.

Partitioning Script

I have partman in my Poudfiere, but I have not yet had the need for it. The script I use to create and attach partitions can be found in my Ansible roles. It is very simple, it only supports day partitioning, and I template it with Ansible to avoid too much faffing about in sh.

Existing Tables

It is possible to go from a regular to a partitioned table on a live system with no downtime. Just do it in a transaction and attach the existing table as a default partition:

mrx2db=# BEGIN;
BEGIN
mrx2db=# ALTER TABLE acs_acssession RENAME TO acs_acssession_default;
ALTER TABLE
mrx2db=# CREATE TABLE acs_acssession (LIKE acs_acssession_default INCLUDING ALL) PARTITION BY RANGE(created_date);
CREATE TABLE
mrx2db=# ALTER TABLE acs_acssession ATTACH PARTITION acs_acssession_default DEFAULT ;
ALTER TABLE
mrx2db=# COMMIT;
COMMIT
mrx2db=#

So, start a transaction, rename the existing table, create the new partitioned table, attach the old table as default partition for the new table, and commit. This means the table is now partitioned, and all existing data still exists in the old table. All all new data is also still being written to the same table.

Adding Partitions

The script should explain how to create and attach the partitions. It is simple do to if there is no data for the range the partition covers, like when creating a partition for tomorrows data:

mrx2db=# ALTER TABLE acs_acssession ATTACH PARTITION acs_acssession_2019_12_17 FOR VALUES FROM ( '2019-12-17' ) TO ( '2019-12-18' );
ALTER TABLE
mrx2db=#

It is also possible to create partitions covering existing data, but the data has to be moved to the partition before attaching it. This can all be done in a transaction as well, to avoid any downtime:

mrx2db=# BEGIN;
mrx2db=# WITH x AS (                                                                                            
    DELETE FROM acs_acssession_default WHERE created_date >= '2019-11-04' AND created_date <='2019-11-05' RETURNING *
) INSERT INTO acs_acssession_2019_11_04 SELECT * FROM x;
....
mrx2db=# ALTER TABLE acs_acssession ATTACH PARTITION acs_acssession_2019_11_04 FOR VALUES FROM ( '2019-11-04' ) TO ( '2019-11-05' );
ALTER TABLE
mrx2db=# COMMIT;
COMMIT
mrx2db=#

Make sure there is an index in place for the selection criteria or this could be a slow operation!