Happy Friday to everyone! The countdown to the course launch is getting shorter. , so today weāre sharing a translation of another useful resource on the topic.
During development significant work has been done to improve table partitioning. Table Partitioning ā is a feature that has existed in PostgreSQL for quite some time, but, so to speak, it essentially didnāt exist until version 10, where it became a very useful feature. Previously, we stated that table inheritance was our implementation of partitioning, and thatās true. However, this method required you to do much of the work manually. For instance, if you wanted tuples to be inserted into partitions during INSERTs, you had to set up triggers to do that for you. Partitioning with inheritance was very slow and complicated for developing additional features on top of it.
In PostgreSQL 10, we witnessed the birth of 'declarative partitioning' ā a feature designed to resolve many issues that were unsolvable with the old inheritance method. This led to a much more powerful tool that allows us to partition data horizontally!
Feature Comparison
PostgreSQL 11 introduced an impressive set of new features that help enhance performance and make partitioned tables more transparent for applications.
1. Using restrictive exceptions
2. Adds only nodes
3. Only for partitioned tables referencing non-partitioned ones
4. Indexes must include all key columns of the partition
5. The restriction on the partition on both sides must match
Performance
Here we also have good news! A new method . This new algorithm can determine suitable partitions by reviewing the query condition WHERE. The previous algorithm, on the other hand, checked each partition to see if it could meet the condition WHERE. This led to additional increased planning time as the number of partitions grew.
In version 9.6, with partitioning through inheritance, routing tuples to the partition was typically done by writing a trigger function that contained a series of IF statements to insert the tuple into the correct partition. These functions could be very slow in execution. With declarative partitioning added in version 10, this process has become much faster.
Using a partitioned table with 100 partitions, we can assess the performance of loading 10 million rows into a table consisting of 1 BIGINT column and 5 INT columns.
The performance of querying this table to find a single indexed record and performing DML to manipulate one record (using only 1 processor):
Here we see that the performance of each operation significantly increased after PG 9.6. The queries SELECT look much better, especially those capable of excluding multiple partitions during query planning. This means the planner can skip a large portion of the work it had to do before. For instance, paths for unnecessary partitions are no longer built.
Conclusion
Table partitioning is becoming a very powerful feature in PostgreSQL. It allows for quick online data output and transitioning to offline without waiting for slow massive DML operations to complete.This also means that related data can be stored together, making access to the required data much more efficient. The improvements made in this version would not have been possible without the developers, reviewers, and committers who have tirelessly worked on all these features.
Thanks to all of them! PostgreSQL 11 looks simply fantastic!
Here's a short but quite interesting article. Share your comments, and also don't forget to sign up for the , where the course program will be presented in detail.
Source: habr.com
