![]() Let's test the space usage of B-tree indexes. In addition, the REINDEX CONCURRENTLY command is introduced to recreate indexes without affecting your business. The performance of B-tree indexes with duplicate entries is also improved. For composite indexes, the space usage can be reduced by up to 40%, effectively saving disk space. ApsaraDB RDS for PostgreSQL 12 improves the overall performance of standard B-tree indexes and reduces their disk space usage. They consume some additional storage space, and their performance is crucial for queries. Indexes are query optimization policies that exchange space for time. Index Enhancementī-tree indexes are widely used in database systems which effectively reduce the amount of query data to be accessed and improve query performance. When you design the table structure, you still need to determine whether to perform partitioning and specify the number of partitions based on the scenario requirements. As the number of partitions increases, the performance of ApsaraDB RDS for PostgreSQL 12 remains relatively stable.Īlthough the performance of partitioned tables is greatly improved, there is still a gap in performance compared with that of a single table. The performance can improve by up to 150 times. As shown in the figure, the more partitions, the greater the improvement in the performance of ApsaraDB RDS for PostgreSQL 12. The following figure compares the query performance on the partition key (also the primary key) with different partition quantities. Better partition-based filtering leads to fewer partitions to process and better optimization results. This optimization is related to partition-based filtering in query criteria. ApsaraDB RDS for PostgreSQL 12 further optimizes the partition pruning feature in order to avoid loading metadata for each partition and generating internal structures, which decouples the planned query time consumption from irrelevant partitions. ApsaraDB RDS for PostgreSQL 11 introduced the partition pruning feature, allowing users to quickly locate the partitions to be accessed. The processing of each partitioned table was similar to that of a common table. In ApsaraDB RDS for PostgreSQL 10, each partitioned table was checked in sequence for required data. In earlier versions, only one line of data could be inserted each time. The improved performance of data copy is due to the support for batch insertion of partitioned tables in ApsaraDB RDS for PostgreSQL 12. As shown in the following figure, the import performance of ApsaraDB RDS for PostgreSQL 12 is always better than that of ApsaraDB RDS for PostgreSQL 11 as the number of partitions increases. Query performance significantly improves when there are a large number of partitions.Ĭreate an ApsaraDB RDS for PostgreSQL 11 instance and an ApsaraDB RDS for PostgreSQL 12 instance with the same specifications (4-core and 8 GB), and test the performance of copying 100 million lines of data with different partition quantities. ApsaraDB RDS for PostgreSQL 12 further enhances the query and data import performance of partitioned tables. ApsaraDB RDS for PostgreSQL 11 supports hash partitions and enhances partition pruning policies in the planning and execution phases to improve the query performance of partitioned tables. Users create partitioned tables by running SQL statements, which facilitates the use of partitioned tables. Support for declarative partitions was introduced in V10.0. Before ApsaraDB RDS for PostgreSQL 10.0, users needed to manually create partitioned tables by inheriting data. ![]() Partitioned Table PerformanceĪpsaraDB RDS for PostgreSQL has supported partitioned tables for a long time. ![]() This article describes some features of ApsaraDB RDS for PostgreSQL 12. For example, it has improved the high-concurrency performance for large partitioned tables by a factor of 100, optimized B-tree index space and performance, implemented the standard JSON-formatted features of SQL Server 2016, and provides support for multi-column statistics on most common values (MCVs), inline common table expressions (CTEs), and pluggable table storage APIs. ApsaraDB RDS for PostgreSQL 12 offers significant improvements in features and performance. On October 3, 2019, Alibaba Cloud released ApsaraDB RDS for PostgreSQL 12, the world's most powerful open-source database system.
0 Comments
Leave a Reply. |