This article compares the speed and performance of queries between partitioned and non partitioned PostgreSQL tables. However, it is important to remember that the PostgreSQL tables partitioning has also another benefits, than the better performance on queries. More information about other benefits from the first part ‘Howto create PostgreSQL table partitioning (Part 1)‘. This is comparision between partitioned and non partitioned PostgreSQL tables. The same tests were carried out with and without indices, because using the indices, it is no longer very meaningful example on table, which has one billion rows of data (if the table is not partitioned).
As Part 1 (Howto create PostgreSQL table partitioning) shows, making of PostgreSQL partitioning needs a lot of SQL commands. So this Part 2 explains how SQL commands for PostgreSQL partitioning can be made with a simple PHP script. This example script make SQL for child tables, indexes, trigger function and parent table trigger. This example script can make PostgreSQL table partitioning with using Date ranges. Script can be configured with following configuration section:
PostgreSQL supports partitioning via table inheritance. So the partitioning is made in such a way that every child table inherits single parent table. Parent table is empty and it exists just to describe the whole data set. PostgreSQL partitioning can be implemented in range partitioning or list partitioning. Range partitioning can be done for example by ID ranges (like 0-100 000, 100 001-200 000, 200 001-300 000…) or Date ranges (like 2009-11-01 – 2009-11-30, 2009-12-01 – 2009-12-31…).
PostgreSQL table partitioning means splitting one large database table into smaller logical pieces. PostgreSQL table partitioning will be worthwhile only when a table would be very large. Here are a few benefits of what can be achieved with partitioning: Large tables query performance can be improved dramatically Reducing index size and making it more likely that the heavily-used parts of the indexes fit in memory Rarely used data can be moved to slower and cheaper storage media This topic is divided into three parts, which are as follows and published in the following order: Part 1.