Partitioning in PostgreSQL

Rotating your API request logs table.

Here at Semantics3, among other things, we build APIs to enable easy access to a gargantuan Ecommerce product database. Right from the beginning, we have seen the need to store an audit log for the APIs in a structured, queryable way. This lets us track usage, debug issues and run analyses on requests and responses. Specifically, our Customer Success team can use this log to track which APIs our customers use, understand the latencies that they see, find the top customers by API calls made and so on.

We use Postgres to maintain these logs tables. Let’s take a look on how we implemented partitioning for our use case. For the sake of this post, we will work with a stripped-down version of our actual logs table. The following metadata about each API call is appended to the table.

A query to find the number of server errors on October, 20 for a given customer with api_key = 'SEM3XXXXXXXXXYYYYYYYY' would look like this:

Problem Statement

In the spirit of ship fast and iterate; back in the day, when the APIs were built, we just made the API write logs to a single Postgres table. Over the years we kept appending request logs to it. However, as we hit scale and started serving millions of requests each single day, it’s easy to see how the RDS disk storage usage (and hence costs) ballooned quickly. Gradually, and at the time of this project the table was consuming over 2TB of space and was burning about 12.5GB of disk space a week! Consequently, we were maintaining an ever-growing database and were periodically allocating more and more space to the RDS instance.

The project requirement was to maintain a fixed-size database (more or less) and keep the costs in check. To make things easier for us, our Customer Success team pointed out that they often needed to access logs only from the previous three months.

Log Rotation

Seeking inspiration from the idea of rotating log files and given our case of only requiring a three-month window to be able to access logs from, the natural solution was to have a dedicated table for each month. When a table becomes old enough to go out of the three-month window, we would dump the table as a CSV, upload it to an S3 bucket and drop the table to reclaim the space. This is where the partitioning of a table comes in.

Partitioning refers to splitting what is logically one large table into smaller physical pieces. The idea is to have a master logs table, and one child table for each month constrained on the timestamp of the request. Fortunately, Postgres supports partitioning out of the box and the concept of a constraint on the request timestamp materializes as a Postgres CHECK constraint.

As a prerequisite to partitioning, it is important to understand Postgres inheritance. In a nutshell, with inheritance, you can make a Postgres table inherit the columns, indices and constraints from a parent table and still be able to run queries (with the exception of INSERT) on the parent table, which would retrieve/update/delete data from all the child tables as well.

Let’s try to EXPLAIN the query on the logs table (which is now the master table) which we used in the beginning.

As is evident from the query plan, the query began a scan on the logs table, and then went on to scan the indices for the logs_201709 and logs_201710 table to retrieve data.

Constraint Exclusion

We have a time frame for the query (Oct 20-Oct 21). A natural question that arises is given the time frame; coupled with the knowledge of CHECK constraints on the created_at column defined on each child table, why attempt to scan all the child tables at all? Using the CHECK constrains, it is straightforward to pinpoint the table, the records being queried belong to. Needless to say, Postgres has that covered for you.

api_logs=> SET constraint_exclusion = on;

From the Postgres docs — “With constraint exclusion enabled, the query planner will examine the constraints of each partition and try to prove that the partition need not be scanned because it could not contain any rows meeting the query’s WHERE clause. When the planner can prove this, it excludes the partition from the query plan.”

Now, with the constraint_exclusion enabled, the query plan looks like:

Handling Inserts

While SELECT, UPDATE, DELETE work seamlessly for queries on the parent table, the same is not the case with INSERT. Fathom an INSERT query:

api_logs=> INSERT INTO logs (api_key, statuscode, method, created_at) VALUES ('SEM3', 200, 'GET', '2017–10–05T10:33:32');

While you’d expect the above record, based on the value of created_at; to be routed to the logs_201710 table, that is not the case. For INSERTs to work seamlessly, we need to define a trigger on the parent table.

The trigger function is executed before each insertion on the logs table and the record is routed to the appropriate table based on the timestamp.

Well, October arrived and as planned we wanted to retain the data from the months of August and September and drop the magnanimous logs_201709 table in the old database. So, we spun up a new AWS RDS instance (with the intention of terminating the older one, once the migration was complete) and created logs_201708, logs_201709. One option was to pg_dump the data to disk and restore it in the new database. However, to transfer 100GB (12.5 GB * 4 Weeks * 2 Months) data, this option wasn’t ideal.

Data Migration and Streams

Incidentally, around this time, I had been reading about streams in node but hadn’t seen them being put to practice. We decided that it would be a good idea to stream data from the source to destination and reckoned this to be a good opportunity to get hands on experience with streams. For this we used the pg-copy-streams package. Basically, you establish connections to both databases, write an SQL query to select the data that you want to stream and pipe it to the table in the new DB.

Here is a typescript code snippet.

To put it all together, this is how the final tables look. Take special note of the Triggers and the Child tables section in the logs table details and the Check constraints and Inherits sections in logs_201709.

In conclusion, with this effort, we were able to turn down the heavy old database instance and have been able to cut our RDS cost (dedicated to logs) by approximately 90%.