PostgreSQL 11: The evolution of partitioning from Postgres 9.6 to Postgres 11

Great Friday everyone! Less and less time is left 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 process of PostgreSQL 11 , impressive work has been done to improve table partitioning. Table partitioningIs a function that has existed in PostgreSQL for quite some time, but, so to speak, it didn’t exist until version 10, in which it became a very useful function. We previously stated that table inheritance is our implementation of partitioning, and it 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 triggers to do this for you. Partitioning using inheritance was 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 unsolvable when using the old method with inheritance. This led to the emergence of a much more powerful tool that allows us to split the data horizontally!

Feature Comparison

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




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

Performance

Here we also have good news! A new method for deleting sections has been added . This new algorithm can determine suitable sections by looking at the query condition WHERE. The previous algorithm, in turn, tested each section to determine if it could match the condition WHERE. This led to an additional increase in planning time as the number of sections increased.

In 9.6, with partitioning by inheritance, routing 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 to execute. With declarative partitioning added in version 10, this has become much faster.

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



Query performance on this table to search for one indexed record and run DML to manipulate one record (using only 1 processor):



Here we see that the performance of each operation has increased significantly since PG 9.6. Queries SELECTlook much better, especially those that can exclude multiple sections during query scheduling. This means that the scheduler can skip most of the work that he should have done before. For example, paths for unnecessary sections are no longer built.

Conclusion

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

Here is such a short, but rather interesting article. Share your comments, and do not forget to sign up for an open house day , in which the course program will be described in detail.