Starting from V11,
Postgres supports Declarative Partitioning where you can divide the table into partitions with built-in Hash partitioning strategy (modulus or round-robin).
It also allows you to explicitly create the partitions using FOR VALUES FROM ... TO ...
. And you can also use sub-partitioning to further divide partitions that are expected to become larger than other partitions.
You can maintain the partition by detach the partitions from the old table and attach them to the new table in which you have new hash strategy so the row number for each partition gets reduced.
See this page for detailed docs. Here is a simple example using partition by Range (I'm not having latest version installed but it should be similar for partition by Hash):
CREATE TABLE old_table (id INTEGER, status TEXT, arr NUMERIC) PARTITION BY RANGE(arr);
CREATE TABLE old_table_small PARTITION OF old_table FOR VALUES FROM (MINVALUE) TO (25);
CREATE TABLE old_table_medium PARTITION OF old_table FOR VALUES FROM (25) TO (75);
CREATE TABLE old_table_large PARTITION OF old_table FOR VALUES FROM (75) TO (MAXVALUE);
INSERT INTO old_table VALUES (1,'ACTIVE',100), (2,'RECURRING',20), (3,'EXPIRED',38), (4,'REACTIVATED',144);
and you can see the data allocated at different partitions:
# SELECT tableoid::regclass,* FROM old_table;
tableoid | id | status | arr
------------------+----+-------------+-----
old_table_small | 2 | RECURRING | 20
old_table_medium | 3 | EXPIRED | 38
old_table_large | 1 | ACTIVE | 100
old_table_large | 4 | REACTIVATED | 144
(4 rows)
Then you decide to re-partition the old_table_large
to make each partition have less record, so you create a new table and attach the other unchanged partitions from old_table
:
CREATE TABLE new_table (id INTEGER, status TEXT, arr NUMERIC) PARTITION BY RANGE(arr);
ALTER TABLE old_table DETACH PARTITION old_table_small;
ALTER TABLE new_table ATTACH PARTITION old_table_small FOR VALUES FROM (MINVALUE) TO (25);
ALTER TABLE old_table DETACH PARTITION old_table_medium;
ALTER TABLE new_table ATTACH PARTITION old_table_medium FOR VALUES FROM (25) TO (75);
and create new partitions for the new table:
CREATE TABLE new_table_large1 PARTITION OF new_table FOR VALUES FROM (75) TO (120);
CREATE TABLE new_table_large2 PARTITION OF new_table FOR VALUES FROM (121) TO (MAXVALUE);
Then migrate the existing data to the new table
INSERT INTO new_table SELECT * from old_table;
and remove the old table
ALTER TABLE old_table RENAME TO old_table_backup;
ALTER TABLE new_table RENAME TO old_table;
DROP TABLE old_table_backup;
now with the new partition, you have
# SELECT tableoid::regclass,* FROM old_table;
tableoid | id | status | arr
------------------+----+-------------+-----
old_table_small | 2 | RECURRING | 20
old_table_medium | 3 | EXPIRED | 38
new_table_large1 | 1 | ACTIVE | 100
new_table_large2 | 4 | REACTIVATED | 144
(4 rows)