Partitioning the database

In the database used by datanommer and datagrepper one table stands out from the other ones by its size, the messages table. This can be observed in Datanommer.

One possibility to speed things up in datagrepper is to partition that table into a set of smaller sized partitions.

Here are some resources regarding partitioning postgresql tables:

Attempt #1

For our first attempt at partitioning the messages table, we thought we would partition it by year. Having a different partition for each year. We thus started by adding a year field to the table and fill it by extracting the year from the timestamp field of the table.

However, one thing to realize when using partitioned table is that each partition needs to be considered as an independant table. Meaning an unique constraint has to involve the field on which the table is partitioned. In other words, if you partition the table by a year field, that year field will need to be part of the primary key as well as any UNIQUE constraint on the table.

So to partition the messages table on year, we had to add the year field to the primary key. However, that broke the foreign key constraints on the user_messages and package_messages tables which rely on the id field to link the tables.

Attempt #2

Since partitioning on year did not work, we reconsidered and decided to partition on the id field instead using RANGE PARTITION.

We partitioned the messages table on the id field with partition of 10 million records each. This has the advantage of making each partition of similar sizes.

More resources

These are a few more resources we looked at and thought were worth bookmarking: