PostgreSQL 11: The Evolution of Partitioning from Postgres 9.6 to Postgres 11

Great Friday everyone! Less and less time remains until the launch of the “Relational DBMS” course, so today we are sharing the translation of another useful material on the topic.



During the development of PostgreSQL 11 , impressive work was done to improve the partitioning of tables. Table partitioning is a function that existed in PostgreSQL for a long time, but, if one can put it that way, in fact, it wasn’t until version 10, in which it became a very useful function. We have previously stated that table inheritance is our partitioning implementation, and this is true. Only this method forced you to do most of the work manually. For example, if you wanted tuples to be inserted into sections during INSERTs, you had to configure the triggers to do it for you. Inheritance partitioning has been very slow and difficult to develop additional functions on top of it.
')
In PostgreSQL 10, we saw the birth of "declarative partitioning" —a feature designed to solve many problems that were insoluble when using the old method with inheritance. This led to a much more powerful tool that allows us to split data horizontally!

Feature Comparison

PostgreSQL 11 has an impressive array of new features that help improve performance and make partitioned tables more transparent to applications.




1. Using limiting exceptions
2. Adds nodes only
3. Only for a partitioned table that refers to unpartitioned
4. Indexes must contain all key columns of a section.
5. The restriction on the section on both sides must match.

Performance

Here we also have good news! Added new section removal method. This new algorithm can determine the appropriate sections by looking at the WHERE query condition. The previous algorithm, in turn, tested each section to determine if it could meet the WHERE condition. This led to an additional increase in planning time as the number of sections increased.

In 9.6, with partitioning using inheritance, the routing of the tuples in a section was usually done by writing a trigger function that contained a series of IF statements to insert the tuple into the correct section. These functions could be very slow when executed. With declarative partitioning added in version 10, it began to work much faster.

Using a partitioned table with 100 partitions, we can estimate the performance of loading 10 million rows into a table from 1 BIGINT column and 5 INT columns.



Query performance on this table for searching for one indexed record and performing DML for manipulating one record (using only 1 processor):



Here we see that the performance of each operation has increased significantly after PG 9.6. SELECT queries look much better, especially those that are able to exclude multiple sections during query scheduling. This means that the planner can skip most of the work he should have done before. For example, paths for unnecessary sections are no longer constructed.

Conclusion

Table partitioning begins to become a very powerful feature in PostgreSQL. It allows you to quickly display data online and transfer it offline, without waiting for the completion of slow massive DML operations . It also means that related data can be stored together, that is, the required data can be accessed much more efficiently. Improvements made in this version would be impossible without developers, reviewers and committers who worked tirelessly on all these features.
Thanks to them all! PostgreSQL 11 looks fantastic!

This is such a short but rather interesting article. Share comments and do not forget to sign up for an open door , which will set out the course program in detail.