Scaling PostgreSQL performance using table partitioning

7 min readAug 15, 2018

We have a project which demands keeping detailed statistics data for different events including information about date and time, user’s IP, HTTP referrer, parameters that are passed and so on. In the beginning the information was stored in MySQL database server, but as it grew fast we decided to move it in a separate server with other database system which could handle it — we chose PostgreSQL.

The detailed statistics data for the current day is stored on a fast MySQL DB server. At the end of the day we take the data and move it to the PostgreSQL server with a simple mysql-dump to postgresql-import script. Since statistics are a constant value for the time being, we wrote another script which piled the daily data in other MySQL table and this way we didn’t need to look at the big detailed statistics data table in order to count everything again and again.

Now our clients open the statistics page and they see the aggregated statistics data. Each data row has a link to the detailed statistics which are stored, as we said, in a PostgreSQL table. The search in the said table is based on several criteria so it was convenient to add an index that would boost the process. So far so good!

Everything was fine in the first few months until the amount of data increased again especially when we began keeping it for longer period of time — a year and above (from 2017–01–01). At that time our table had 666,454,166 rows in total and was about 350 GB of size — raw, without indices.

Having a table of such size isn’t a problem itself, but can cause other issues like:

query performance starting to degrade;
indices take much longer to update;
maintenance tasks, such as vacuum, also become uncommonly long;
daily backups take too much time.

So depending on how we needed to manage the stored information, Postgres table partitioning was a great way to improve query performance and deal with large amount of data over time without having to choose a different database system.

Partitioning and inheritance

Table partitioning is a good solution in our case. We take one massive table and split it into many smaller ones. These smaller tables are called partitions or child tables. Operations like backups, SELECTs, and DELETEs can be performed with the individual partitions or with all of the partitions. Partitions can also be dropped or exported as single transactions.

Master table

Master partition table is the template that the child tables derive from. This is an ordinary table, but it doesn’t contain any data and requires a trigger in order to execute the queries leading to the relevant child table. There is a one-to-many relationship between a master table and child tables, so to speak there is one master table and many child tables.

Child tables

These tables inherit their structure from the master table and also belong to a single master table. The child tables contain the full data.

Partition Function and trigger

The partition function is a stored procedure that determines which child table should accept a new record. The master table has a trigger which calls a partition function. There are two typical approaches for sending records to the designated child tables — by date values and by fixed values (like geo locations). In our case since the statistics data is time sensitive information, we will use the date values criteria.

Configuring table partitions

Here is what we did:

Created a master table
Created a trigger function
Created a table trigger

The master table

In order to store the information about the tracked objects and events we created a master table called detailed statistics.

CREATE TABLE public.detailed_statistics (
    id integer NOT NULL,
    widget_id integer,
    offer_type varchar(16) not null,
    offer_offer_id integer,
    offer_creative_id integer,
    event_type types,
    event_revenue numeric(15,5),
    event_cost numeric(15,5),
    event_url text,
    event_parameters text,
    user_device varchar(32) not null,
    user_os varchar(64) not null,
    user_browser varchar(64) not null,
    user_remote_addr varchar(15) not null,
    created_at timestamp with time zone,
    day date
);

For one month this table may be filled with millions of rows and this is the reason we wanted to partition it — to improve query performance.

The trigger function

The trigger function we created performs as it follows:

Creates child partition tables if such doesn’t exist yet;
The name of each child partition table is given following the same logic — public.detailed_statistics_yYYYYmMM — the partitions are determined by the values in the day column and we will have one partition per calendar month;
Creates indexes where needed;

So here is the function syntax:

CREATE OR REPLACE FUNCTION public.statistics_partition_function()
    RETURNS TRIGGER 
AS $BODY$
    DECLARE
    partition_name text;
    partition_date TIMESTAMP;
    partition_enddate TIMESTAMP;
    BEGIN    --Takes the current inbound day value and determines when midnight is for the given date
    partition_date := date_trunc('month', NEW.created_at);
    partition_name := 'detailed_statistics_y' || to_char(partition_date, 'YYYYmMM');    -- Check if the partition exists
    PERFORM 1
    FROM   pg_catalog.pg_class c
    JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    WHERE  c.relkind = 'r'
    AND    c.relname = partition_name
    AND    n.nspname = 'public';    -- Create the partition if doesn't exist
    IF NOT FOUND THEN
        partition_enddate:=partition_date::timestamp + INTERVAL '1 month';
        EXECUTE 'CREATE TABLE IF NOT EXISTS public.' || quote_ident(partition_name) || ' (
            CHECK ( day >= ' || quote_literal(partition_date) || '::date AND day < ' || quote_literal(partition_enddate) || '::date )
        ) INHERITS (public.detailed_statistics)';        -- Table permissions are not inherited from the parent.
        EXECUTE 'ALTER TABLE public.' || quote_ident(partition_name) || ' OWNER TO postgres';        -- Indexes are defined per child, so we assign a default index that uses the partition columns
        EXECUTE 'CREATE INDEX ' || quote_ident(partition_name||'_dewoo') || ' ON public.' || quote_ident(partition_name) || ' USING btree (day DESC, event_type, widget_id, offer_type, offer_offer_id)';
    END IF;    -- Insert the current record into the correct partition, which we are sure will now exist.
    EXECUTE 'INSERT INTO public.' || quote_ident(partition_name) || ' VALUES ($1.*)' USING NEW;
    RETURN NULL;
    END;
$BODY$
LANGUAGE plpgsql;

If the partition doesn’t exist we create it. We use CHECK to specify which rows will end up in this table, and use INHERITS to bind this child table to its parent. After that we must add INDEX on this new child table as child tables are created without indexes.

The table trigger

After the partition function has been created we also make an insert trigger which is added to the master table. This trigger calls the partition function when new records are inserted.

CREATE TRIGGER insert_detailed_statistics_trigger
    BEFORE INSERT
    ON public.detailed_statistics
    FOR EACH ROW
    EXECUTE PROCEDURE public.statistics_partition_function();

That’s it! The partitions have been created, the trigger function defined, and the trigger has been added to the master table. This is all you need to start inserting data on the statistics table and the data can be directed to the appropriate partition.

Let’s check!

Let’s conduct a simple test to check if we have made proper partitioning which will be proved by hitting only a small portion of real data.

EXPLAIN ANALYSE
SELECT * FROM detailed_statistics 
WHERE day BETWEEN '2018-05-31'::date AND '2018-06-01'::date 
AND event_type = 'click'
AND widget_id = 7194
AND offer_type = 'link'
AND offer_offer_id = 1610
LIMIT 30

And here is the result:

Limit  (cost=0.00..104.05 rows=30 width=670)
  ->  Append  (cost=0.00..193955.32 rows=550922 width=670)
        ->  Seq Scan on detailed_statistics  (cost=0.00..0.00 rows=1 width=1898)
              Filter: ((day >= '2018-05-31'::date) AND (day <= '2018-06-01'::date) AND (event_type = 'click'::types) AND (widget_id = 7194) AND ((offer_type)::text = 'linkcard'::text) AND (offer_offer_id = 1610))
        ->  Index Scan using detailed_statistics_y2018m05_decoo on detailed_statistics_y2018m05  (cost=0.57..19644.57 rows=307903 width=636)
              Index Cond: ((day >= '2018-05-31'::date) AND (day <= '2018-06-01'::date) AND (event_type = 'click'::types) AND (widget_id = 7194) AND ((offer_type)::text = 'linkcard'::text) AND (offer_offer_id = 1610))
        ->  Index Scan using detailed_statistics_y2018m06_decoo on detailed_statistics_y2018m06  (cost=0.56..174310.75 rows=243019 width=672)
              Index Cond: ((day >= '2018-05-31'::date) AND (day <= '2018-06-01'::date) AND (event_type = 'click'::types) AND (widget_id = 7194) AND ((offer_type)::text = 'linkcard'::text) AND (offer_offer_id = 1610))
Planning time: 0.987 ms
Execution time: 34.909 ms

Well, we think this solution definitely works as currently we have 16 child tables but the planner hits only two — exactly the ones we need!

Should you use table partitioning in your project too?

Table partitioning allows you to divide one very large table into many smaller tables thus dramatically increasing the performance. However, this shouldn’t be the first solution to count on when you run into problem. If you wonder whether table partitioning is the proper solution for you then maybe you should answer some questions:

Do you have a large data set stored in one table?
Is the data going to be updated after being initially inserted?
Did you make as much optimization as possible with indexes?
Do you think that the data has little value after a period of time?
Is there a small range of data that has to be queried to get the results needed?
Can the older data with little value be archived?

Table partitioning makes sense if you answered yes to all of these questions. But be careful, table partitioning requires evaluation of how you’re querying your data, planning ahead and considering your usage patterns. As long as you take these factors into account, table partitioning can create real performance boost for your queries and your project.

If you liked this article, then please subscribe to our newsletter. Every month we’ll send out an email with a few articles written by our team about the business, web development, UX design and startups. We hope they will be useful to you and will help you solve some of your digital-related problems.

Originally published on the MTR Design company website.

MTR Design is a small Bulgarian web development company with expertise in a wide range of technologies (PHP, Python, Golang, Lua, Salesforce, Node, React and React Native, Angular, VueJS, iOS and Android development). We are currently open for new projects and cooperations, so if there are any projects we can help with, please react out (use the contact form on our website, or email us at office@mtr-design.com) and we will be happy to discuss them.