[Huawei Cloud Technology Sharing] 40% performance improvement, Huawei Cloud launches PostgreSQL 12 commercial version

Posted May 26, 20204 min read

Summary: A few days ago, Huawei Cloud Database officially launched the RDS for PostgreSQL 12 version and began commercial use. This article will interpret Huawei Cloud PostgreSQL 12 version from Huawei Cloud RDS for PostgreSQL 12's four major features and architecture diagrams, and help you understand the product features of PostgreSQL 12 version in order to better serve customers.

PostgreSQL is a popular open source relational database in the world, and it is particularly excellent in ensuring data reliability and integrity. In order to provide users with more stable and reliable services, the Huawei Cloud Database team has launched RDS for PostgreSQL 12 version after careful research and development, and began commercial use a few days ago.

PostgreSQL 12 has been strengthened in all aspects since its official launch. Under the TPC-C performance test, the query performance and space utilization can be improved by about 40%on average, especially when the amount of data is large, users can use smaller The cost comes from storage features such as performance and space management, including enterprise-level security, background management and SQL/JSON support, column generation, optimized WITH query, authorization control, and pluggable table storage interface.

In addition to the official new functions, Huawei Cloud RDS for PostgreSQL 12 has designed more intimate services for the special use scenarios of Huawei Cloud users, which has greatly improved the user experience.

4 major features of Huawei Cloud RDS for PostgreSQL 12

40%performance increase

Huawei Cloud RDS for PostgreSQL version 12 has been significantly enhanced in terms of performance and ease of maintenance, especially for the indexing and partitioning subsystem, which greatly improves the efficiency of various operations access/query.

  • Index:The standard index type B-tree index is optimized so that it can better handle the overall performance of loads with higher index update frequency. In addition, the load of WAL logs that generate GiST, GIN, or SP-GiST indexes is significantly reduced. SP-GiST indexes support <-> K-NN(ie, nearest neighbor) queries for distance operations, and the CREATE STATISTICS instruction now supports the most commonly used value MCV Statistics to help those queries with non-uniformly distributed field values generate a more optimized query plan.
  • Partitioned tables:Queries for partitioned tables have also been greatly improved, especially for those with thousands of partitions, and the results only need to extract data from a few limited partitions. PostgreSQL 12 enhanced data INSERT and COPY to join the operation of the partition table, and supports the addition of partitions without blocking queries.

Enhance SQL consistency and functionality

Huawei Cloud RDS for PostgreSQL version 12 supports the function of using JSON path expressions when querying JSON documents. For documents saved in JSONB format, these queries can use the existing indexing mechanism to efficiently extract data. Support WITH query, can realize non-materialized operation processing, which is very helpful for many existing queries; introduce "generate column" function, support to store the calculated generated column.

Enhanced authorization control

Huawei Cloud RDS for PostgreSQL 12 extends the security functions, adds a GSS interface, and supports two-way encryption between the client and server.

Enhanced system management

The REINDEX CONCURRENTLY instruction of PostgreSQL version 12 allows users to perform reindexing operations without affecting the writing of new indexes. This helps users rebuild larger indexes without downtime. Use the pg \ _checksums command to turn on or off the page check function on the downgraded PostgreSQL. This function helps to check the consistency of the data written to the disk. In previous versions, this operation was only allowed to be performed at the initdb stage.

In addition, Huawei Cloud RDS for PostgreSQL 12 supports automatic backup and recovery, supports custom setting of parameters, supports one-click capacity expansion, fault flash recovery, provides multiple security strategies to protect the database and

Applicable to more scenes

Huawei Cloud RDS for PostgreSQL version 12 provides excellent performance and functionality, and is suitable for scenarios such as location application systems, research project information systems, financial insurance systems, and Internet e-commerce.

  • Location Application System

Huawei Cloud RDS for PostgreSQL 12 supports the PostGIS plug-in, providing spatial information service functions such as spatial objects, spatial indexes, spatial operation functions, and spatial operators, and is very suitable for location application products.

  • Scientific Research Project Information System

Huawei Cloud RDS for PostgreSQL 12 supports more complex data types, can store accurate data, protect data privacy based on VPN, and can customize data types. It can also transfer unused data to OBS cloud storage, saving storage costs and host space.

  • Financial Insurance System

Huawei Cloud RDS for PostgreSQL 12 uses multi-version concurrency control(MVCC) to ensure data consistency. The active and standby instance data is synchronized to achieve double data insurance to ensure that data is not lost, and cooperates with OBS to implement storage space expansion and transfer cold data to OBS Medium, further saving historical data storage costs.

  • Internet e-commerce

HUAWEI CLOUD RDS for PostgreSQL 12 has high stability in the scenario of high concurrency of Internet applications, and all operations can be completed in SQL, without the need to import data back and forth, improving development efficiency.

[Click to follow and learn about Huawei s new cloud technology for the first time ~]