Great Friday everyone! Less time left before the launch of the course , so today we are sharing the translation of another useful material on the topic.
On development stage impressive work has been done to improve table partitioning. Partitioning tables is a feature that has existed in PostgreSQL for quite a long time, but, so to speak, it did not exist until version 10, in which it became a very useful feature. We previously stated that table inheritance is our implementation of partitioning, and this is true. Only this way forced you to do most of the work manually. For example, if you wanted tuples to be inserted into sections during INSERTs, you would have to set up triggers to do this for you. Partitioning with inheritance was very slow and complex to develop additional features on top of it.
In PostgreSQL 10, we saw the birth of "declarative partitioning" - a feature designed to solve many problems that were unsolvable using the old method with inheritance. This has led to a much more powerful tool that allows us to split data horizontally!
Feature comparison
PostgreSQL 11 has an impressive set of new features that help improve performance and make partitioned tables more transparent to applications.
1. Using limiting exceptions
2. Adds only nodes
3. Only for a partitioned table referencing a non-partitioned
4. Indexes must contain all the key columns of the section
5. Section restriction on both sides must match
Performance
Here we also have good news! Added new method . This new algorithm can determine suitable partitions by looking at the query condition WHERE. The previous algorithm, in turn, tested each section to determine if it could meet the condition WHERE. This led to an additional increase in planning time as the number of sections increased.
In 9.6, with inheritance partitioning, routing tuples into partitions was usually done by writing a trigger function that contained a series of IF statements to insert a tuple into the correct partition. These functions could be very slow to execute. With declarative partitioning added in version 10, this is much faster.
Using a partitioned table with 100 partitions, we can evaluate the performance of loading 10 million rows into a table with 1 BIGINT column and 5 INT columns.
The performance of a query on this table to look up a single indexed record and execute DML to manipulate a single record (using only 1 processor):
Here we can see that the performance of each operation has increased significantly since PG 9.6. Requests SELECT look much better, especially those that are able to exclude many partitions during query planning. This means that the scheduler can skip most of the work it should have been doing before. For example, paths for unnecessary sections are no longer built.
Conclusion
Table partitioning is starting to become a very powerful feature in PostgreSQL. It allows you to quickly bring 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, meaning that the required data can be accessed much more efficiently. The improvements made in this release would not have been possible without the developers, reviewers, and committers who have been working tirelessly on all of these features.
Thanks to them all! PostgreSQL 11 looks fantastic!
Here is such a short but quite interesting article. Share your comments and don't forget to subscribe , within which the program of the course will be described in detail.
Source: habr.com