Nachrichten und Blog zurück

Upgrading inheritance partitioning made easy.

An alternative way to upgrade to PostgreSQL 10 native partitioning using ALTER TABLEs and PLPGSQL.

Today’s post is a bit spontaneous, because a few days ago I read the post about PostgreSQL’s new native table partitioning explaining how to migrate from an old inheritance partitioning to the new one.

Author of the post proposed using pg_dump and pg_restore utilities. This way is quite simple and requires minimal effort. However, in some instances, especially in case of large partitions, dumping partition’s data, restoring and creating indexes might take a long time.

In this post, I would like to offer an alternative way for partitioning upgrade. This method based on some ALTER commands wrapped into PLPGSQL procedure. Of course, I should note this technique only work if you already upgraded to PostgreSQL 10.
Let’s consider a classic example, with master table and several tables which partitioned using timestamp column. Using the following commands it’s possible to create our example tables.
# CREATE TABLE events (
    id serial primary key,
    cta_point integer,
    decay_factor integer,
    created_at timestamp without time zone);
# CREATE TABLE events_201708 (LIKE events INCLUDING ALL) INHERITS (events);
# CREATE TABLE events_201709 (LIKE events INCLUDING ALL) INHERITS (events);
# CREATE TABLE events_201710 (LIKE events INCLUDING ALL) INHERITS (events);
# CREATE TABLE events_201711 (LIKE events INCLUDING ALL) INHERITS (events);

Next step is to create trigger function and the trigger itself which would use our function when new records are inserted into the table.
# CREATE OR REPLACE FUNCTION fn_insert_events() RETURNS TRIGGER AS
    $function$
    BEGIN 
    EXECUTE format($$INSERT INTO %I VALUES ($1.*)$$, 'events_'||to_char(NEW.created_at, 'YYYYMM')) USING NEW;
    RETURN NULL;
    END;
    $function$ LANGUAGE plpgsql;
# CREATE TRIGGER events_trigger BEFORE INSERT ON events FOR EACH ROW EXECUTE PROCEDURE fn_insert_events();

As last step, let’s add some generated data into our partitions.

# INSERT INTO events (cta_point,decay_factor,created_at)
SELECT (random() * 80 + 60)::int,(random() * 100 + 400)::int,i
FROM generate_series('2017-08-01'::timestamp,'2017-11-30','10 minutes') gs(i);
Check that everything is ok with data distribution. The master table should not contain any rows and partitions should be evenly filled.
# dt+

                         List of relations
Schema |     Name      | Type  |   Owner  |   Size  | Description
-------+---------------+-------+----------+---------+-------------
public |        events | table | postgres | 0 bytes |
public | events_201708 | table | postgres |  256 kB |
public | events_201709 | table | postgres |  248 kB |
public | events_201710 | table | postgres |  256 kB |
public | events_201711 | table | postgres |  240 kB |

When test partitions are ready we can proceed to preparing the upgrade procedure.
Declarative partitioning in PostgreSQL 10 supports not only CREATE TABLE syntax, but ALTER TABLE as well. Mainly, it gives an opportunity to attach or detach tables like a partition to an existing table. So, there is no barriers to handle all partitions, switch off inheritance and attach to the new master table. This is in general, but how does it actually work?

Of course, first, we must create new master table using CREATE TABLE and, specify, basic partitioning rule – using RANGE or LIST. In our case, RANGE would be an appropriate choice. Next step, is to get the list of all source table partitions which can be obtained from pg_class and pg_inherits system tables. When the list is ready we should disable inheritance and attach partition to the new master table.

There are a few points of note here. First, when we attach partitions to master table we must specify min and max values which are then used like borders for the inserted rows. This values can be obtained from partitions‘ names – in my practice, partitions‘ names indirectly point to intervals of stored rows, the most obvious examples are daily or monthly partitions in following format: YYYYMMDD, YYYY_MM, YYYY_MM_DD or similar. Tables‘ names are not universal though so it doesn’t guarantee 100% accuracy, but it’s an easy way to identify intervals and works in most cases.

Thus, we need to extract required information from table’s name and translate it into values acceptable by ALTER TABLE command. This can be done using different string or date/time builtin functions.

Another key point is sequence handling. If original table uses sequence, this one also should be attached to the new table.

Well, it seems there are no additional pitfalls to fall into and all these ideas can be implemented in the following function.

                         List of relations
Schema |     Name      | Type  |   Owner  |   Size  | Description
-------+---------------+-------+----------+---------+-------------
public |        events | table | postgres | 0 bytes |
public | events_201708 | table | postgres |  712 kB |
public | events_201709 | table | postgres |  688 kB |
public | events_201710 | table | postgres |  712 kB |
public | events_201711 | table | postgres |  664 kB |