Scaling Up Rails Applications With PostgreSQL Table Partitioning - Part 3
After publishing recent blog posts about table partitioning - its SQL basics part and how to use in in Rails application I was asked quite a few times what is the real performance gain when using table partitioning. This is a great question, so let’s answer it by performing some benchmarks.
Setting up data for benchmarking
As the table partitioning is intended to be used in Rails application, it makes most sense to perform benchmark with ActiveRecord’s overhead as well - we want to have some real-world comparison.
In the two previous parts we were discussing orders example, so let’s do the same here. We can start with generating model:
For benchmarking use case without table partitioning we don’t really need to change much, just add an index for created_at column:
For sample data let’s start with creating 1 million orders for every year from 2016 to 2020. This should be enough to make the tables moderately big for real-world example and perform some meaningful benchmarking. Here’s the code to create these records with random date from given year:
To get even better idea about the performance difference, we could also test the queries with different amount of data. After each benchmark we could create additional 250 000 records for each year and rerun benchmarks. This could be reapeated until we reach 2 mln records in each table (10 mln in total) so that way we would have data for 5 different orders’ amount.
To have a meaningful benchmark that can be applicable to some real-world app, we need to test the queries that are likely to happen. For that purpose we can try selecting all orders, orders from particular year, from several years, from past few months and we could also try finding some orders with random id. We should also limit the amount of records we return, well, unless we want to kill the local machine ;). Counting the amount of orders for different date ranges would also be a nice addition. For partitioned tables we could also throw in some additional benchmarks comparing the performance between selecting orders from given partitioned child table and from master table and letting the PostgreSQL figure out how to handle the query (i.e. using constraint exclusion for filtering tables).
In this case we don’t really care about exact time for each query, but rather the ratio of query time (or iterations per seconds) for partitioned and not-partitioned table. Counting iterations per seconds for every query with banchmark-ips will be perfect for that. To calculate this ratio (let’s call it Partioned To Not Partioned Ratio) we would just need to divide the result from partitioned table by the result from non-partitioned table.
The amount of data is quite an important factor for this benchmark, especially with comparison to some PostgreSQL config settings. The size of orders table for different amount is the following:
From our benchmark’s perspective, shared_buffers and constraint_exclusion parameters are the crucial ones - shared_buffersdetermines how much memory can be used for caching tables and constraint_exclusion will prevent scanning all child tables if the query conditions make it clear it is not required.
Here’s the final benchmark code, for patitioned tables:
That way we’ve obtained iterations per second for different queries. Let’s calculate now the ratio of query time for partitionied and not partitioned table. To get the better idea about the relation between the ratios for different orders’ amount, I put the results on the graph (due to the proportions you may want to see them in better quality by clicking the link below each graph).
Let’s break down these benchmarks to several groups:
For selecting all records and counting them the obvious conclusion is that for partitioned tables the queries are slower: slightly slower for just selecting them and noticeably slower for counting them. For selecting all orders, the ratio of partitioned to not partitioned tables query times most likely decreases as the tables’ size grow and for counting it is not clear: the data doesn’t show any regular correlation, we could expect the ratio would also decrease for the larger amount of data, however, we can’t really tell based on this benchmark, which even suggests it could be a constant value. Nevertheless, table partitioning isn’t the best idea if the queries are primarily run across all the tables.
The results for selecting orders (queries for orders from years: 2016, 2018, 2020) only from specific date range which matches the constraints for children partitioned tabled look quite interesting: The more records we have, the better ratio (in favour of table of partitioning) we get. Table partitioning doesn’t always yield better results when the child table is not specified (see: Partitioned (master table) To Not Partitioned Ratio), but there’s a certain size of the tables when we get the ratio above 1, which means queries for partitioned tables (even without specifying a child table) are faster. When selecting from partitioned child table (see: Partitioned (child table) To Not Partitioned Ratio) the queries are faster for partitioned tables regardless of the size, which was expected. Depending on the size of the table, the difference can be quite significant, up to 28%. The data is not clear enough to be certain about the correlation with amount of orders / table size (substantial irregularity for orders from years 2020), but probably the bigger the tables are, the bigger difference of query time between partitioned and not partitioned tables, similarly to the case when the child table is not specified. The difference between explicitly running queries against a specific child table and running against a master table and relying on constraints exclusion it quite surprising: I was expecting only a slight difference, however, specifying a child table can make the queries up to 35% faster. There is a possibility this difference decreases slowly the more records there are in the tables, however, we would need more benchmarks to prove or disprove this hypothesis as there is another possibility of having costant ratio.
The general correlation for counting the records which can be put in the specific partitioned child tables is the same as for selecting the orders: the more records in the tables, the better performance table partitioning yields. When the child table is not specified, counting records can be faster for not partitioned tables until we reach a certain size where table partitioning seems to be a better choice performance-wise. For counting orders from all the years (2016, 2018, 2020) for 5 mln orders, the ratios are significantly different comparing to the values for higher amount of orders, which can’t be easily explained. It’s quite interesting that it happened for the queries for all the tables used in the benchmark, which might be worth investigating further, however, I would treat them as irrelevant in this case and not consider them at all. When the partitioned table is specified, the results are always better in favour of table partitioning - we can expect queries to be more than 2 times faster, even up to almost 3 times faster. Similarly to selecting the orders, the ratio of running queries against child table and master table is either a constant or slightly decreases as the amount of orders grows.
For selecting records form multiple children tables, the Partioned To Not Partitioned Ratio is lower than 1, which means that table partitioning would be a inferior choice for such queries, regardless of the size. However, the difference is not that significant. On the other hand, for counting the records it looks like table partitioning yields better performance, which is especially clear for 2018-2020 range, but we can’t tell what’s the correlation with the amount of orders based on the obtained results.
The performance for selecting records from last N months (here 3 and 6 accordingly) is quite similar for both partitioned and not partitioned strategies (ratio close to 1), which doesn’t change when the amount of orders grows. However, counting records is significantly slower, but the ratio most likely remains constant as the table gets bigger.
Initially, there seems to be no difference in query time between selecting orders from partitioned and not-partitioned table, but as the amount of orders grows, the performance keeps getting worse considerably, which is expected as the constraints exclusion can’t be applied in such case.
To sum up:
The larger amount of records, the better performance table partitioning yields
Table partitioning is not optimal for all type of queries, it should be mostly used when querying only a subset of records covered by a particular child table
Specifying exact child table yields much better peformance than querying master table and relying on constraint exclusion
Even though table partitioning requires some extra overhead and may be tricky to get started with, it is clear that the performance benefits of using it may outweight the costs when applying to the right queries. However, we need to be aware that it is not the perfect choice for all the queries and in some cases it can deteriorate the performance.