PostgreSQL Tutorial: Partitioning table online using table inheritance

Summary: In this tutorial, you will learn how to partition an existing table online using table inheritance in PostgreSQL.

Table of Contents

Preparing the data set

Let’s start with a simple table and some data:

create table t ( a int primary key, b text );

insert into t select i, 'aaa' from generate_series(1, 1000000) i;

insert into t select i + 1000000, 'bbb' from generate_series(1, 1000000) i;

What options do we have if we want to partition this table by list for column b? We could create the new partition structure beside the current table, and then in one point in time load the existing data into the partitioned table (either by using insert into ... select * from, or by dumping and reloading). Another option would be to setup logical replication from the old table into the new partitioned table. The third option , what we’ll have a look now, is to use table inheritance as an intermediate step to partition the table almost online.

Transfer data into child tables

The first bits we need are the child tables which are supposed to hold that data for the values of ‘aaa’ and ‘bbb’:

create table t_aaa ( a int, b text check (b in ('aaa')) ) inherits ( t );
create table t_bbb ( a int, b text check (b in ('bbb')) ) inherits ( t );

The important point here is the check constraint (more on that later), and of course the inheritance. For now we’ve build the inheritance tree but the child tables do not contain any data. Before we load the child table we need to make sure, that new data arriving in the parent table gets routed to the correct child table by creating a trigger:

CREATE OR REPLACE FUNCTION tmp_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.b = 'aaa' )
    THEN
      INSERT INTO t_aaa VALUES (NEW.*);
    ELSIF ( NEW.b = 'bbb' )
    THEN
      INSERT INTO t_bbb VALUES (NEW.*);
    ELSE
      RAISE EXCEPTION 'Value out of range!';
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tmp_insert_trigger
    BEFORE INSERT ON t
    FOR EACH ROW EXECUTE PROCEDURE tmp_trigger();

A quick test to check that the trigger works:

insert into t values (-1, 'aaa');

insert into t values (-2, 'bbb');

select * from only t_aaa;
 a  |  b
----+-----
 -1 | aaa
(1 row)

select * from only t_bbb;
 a  |  b
----+-----
 -2 | bbb
(1 row)

select * from t where a in (-1, -2);
 a  |  b
----+-----
 -1 | aaa
 -2 | bbb
(2 rows)

This confirms that:

• Data is routed into the correct child table
• Data is accessible over the parent

Having that confirmed we can delete the data from the parent table and insert into the child tables (maybe in multiple batches):

with aa as ( delete from t where b = 'aaa' returning * )
  insert into t_aaa select * from aa;

with bb as ( delete from t where b = 'bbb' returning * )
  insert into t_bbb select * from bb;

We do that until the parent table is empty and all data is in the child tables:

select count(*) from only t;
 count
-------
     0
(1 row)

select count(*) from only t_aaa;
  count
---------
 1000000
(1 row)

select count(*) from only t_bbb;
  count
---------
 1000000
(1 row)

Create partitioned table

The final step is to destroy the inheritance and attach the child tables as new partitions to a newly created partitioned table, and finally rename the old one and the new one:

begin;

create table tt ( a int, b text ) partition by list(b);

alter table t_aaa no inherit t;

alter table tt attach partition t_aaa for values in ('aaa');

alter table t_bbb no inherit t;

alter table tt attach partition t_bbb for values in ('bbb');

alter table t rename to t_old;

alter table tt rename to t;

commit;

Because the check constraint matches the partition key, PostgreSQL can just attach the tables as new partitions without scanning the tables, so this is a very fast operation with a very short lock. Here’s the new table structure:

postgres=# \d+ t
                                      Partitioned table "public.t"
 Column |  Type   | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
--------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
 a      | integer |           |          |         | plain    |             |              | 
 b      | text    |           |          |         | extended |             |              | 
Partition key: LIST (b)
Partitions: t_aaa FOR VALUES IN ('aaa'),
            t_bbb FOR VALUES IN ('bbb')

Summary

But, pleas keep in mind:

• This is a really simple test case, of course this works well. In busy systems this might get more tricky.
• Routing data with a trigger might introduce some performance degradation.
• You somehow have to deal with data coming in while you go from the old to the new structure, or stop data from coming in during the last step.

See more

PostgreSQL Tutorial