PostgreSQL is renowned as one of the most advanced open-source databases. In September, PostgreSQL 16.0 was officially released, featuring enhancements and optimizations in performance, logical replication, developer experience, monitoring, permissions, and security. ApsaraDB for RDS PostgreSQL[1] now supports PostgreSQL 16.0. AliPG has also improved security, cost, and operations based on PostgreSQL 16.0, adding various kernel and plugin features to meet specific business requirements.
For more information, see ApsaraDB RDS: Lifecycles of major engine versions[2].
PostgreSQL 16.0 has undergone improvements and optimizations in performance, logical replication, developer experience, monitoring, permissions, and security. Notably, performance enhancements include improved efficiency in bulk data loading with the COPY command, and reduced frequency of full-table freezes through vacuum policy modifications. Logic replication now supports parallel application of large transactions and binary formats for faster initial table synchronization. Developer experience benefits from additional SQL/JSON syntax and improved text sorting support. The monitoring capabilities have been enhanced with the addition of the pg_stat_io view for I/O statistics and improved readability of auto_explain. Finer-grained permission control and more flexible security configurations are provided in terms of permissions and security.
However, as a commercial database product, PostgreSQL 16.0 still has room for improvement in terms of security, cost, and maintainability. ApsaraDB RDS for PostgreSQL's in-house developed kernel, AliPG, provides various capabilities to address these areas based on PostgreSQL 16.0.
For scenarios requiring data encryption such as backup and recovery, query and analysis, or storage with special security compliance requirements, AliPG offers the table-level transparent data encryption (TDE) feature[3]. This feature encrypts and decrypts data files in real-time to protect user data privacy. Transparent data encryption prevents potential attackers from directly accessing sensitive information stored on disk by performing static data encryption at the database layer. All encryption and decryption operations are performed in memory, with data remaining plaintext in memory and ciphertext on disk. This effectively mitigates data breaches caused by disk theft in user business scenarios. The TDE feature is seamless to users, requiring no changes to the way the database is used and incurring no adaptation costs.
AliPG provides the tablespace-level transparent page compression (TPC) feature[4] to reduce storage space usage and improve database read performance. TPC performs real-time I/O compression and decompression on pages in the buffer pool. Data is automatically compressed when written to disk and decompressed when read. By compressing data, disk I/O is effectively reduced, minimizing storage requirements, improving cache efficiency, and accelerating data transmission. Online tests have shown that enabling TPC results in an average storage cost reduction of 50% and an average I/O usage reduction of 50%. Read scenarios also experience increased throughput, with some scenarios seeing up to a 100% improvement.
For business scenarios with a large number of persistent connections, such as SaaS, AliPG provides the cache eviction feature[5] that releases infrequently used caches based on the Least Recently Used (LRU) policy. This reduces memory usage of connections and resolves the issue of excessive memory consumption for native PG persistent connections. AliPG also offers SQL throttling, which limits the maximum concurrency of specific types of SQL statements. This feature ensures that the database can handle burst traffic or excessive resource consumption of SQL statements without encountering severe performance problems.
AliPG also incorporates various proprietary and third-party plugins to offer users a wide range of additional features. Please refer to the list of Extensions supported by ApsaraDB RDS for PostgreSQL[7] for more details. Here are some examples:
1. Ganos spatio-temporal engine (in-house)
The PostgreSQL Ganos spatio-temporal engine provides a set of data types, functions, and stored procedures. With this engine, users can efficiently store, index, query, and analyze spatiotemporal data in ApsaraDB for RDS PostgreSQL.
2. Fully encrypted database (in-house)
Users can encrypt sensitive data columns in their database tables. The data in these columns will be transmitted, calculated, and stored as ciphertext. Fully encrypted databases can address data security risks in various application scenarios, such as platform security operations and maintenance, compliance with sensitive data regulations, and data fusion from multiple sources.
3. index_adviser (in-house)
The index_adviser extension assists users in determining which columns should be indexed to enhance performance for a given workload.
4. oss_fdw for external data file reading and writing (in-house)
This feature enables the loading of data from OSS to ApsaraDB RDS for PostgreSQL or writing data in the opposite direction.
5. PgBouncer lightweight connection pool (third-party, open-source)
PgBouncer is utilized to optimize database connection performance and improve resource utilization. By reusing existing database connections in the connection pool, PgBouncer prevents performance overhead caused by frequent connection establishment and closing. This connection reuse mechanism significantly reduces the load on the database server, resulting in improved response speed and concurrency performance. This feature is ideal for scenarios that involve a large number of short-lived connections with frequent establishment and closing.
6. pg_jieba for Chinese word segmentation (third-party, open-source, combined with in-house features)
This plugin facilitates full-text search in Chinese and supports customized dictionaries.
7. pgvector for vector search (third-party, open-source)
The pgvector extension enables the storage of vector data and facilitates vector similarity matching, thereby providing fundamental data support for AI products.
[1] What is Apsaradb RDS for PostgreSQL: https://www.alibabacloud.com/help/en/rds/apsaradb-rds-for-postgresql/what-is-apsaradb-rds-for-postgresql
[2] ApsaraDB RDS: Lifecycles of major engine versions: https://www.alibabacloud.com/help/en/rds/apsaradb-rds-for-postgresql/lifecycles-of-major-engine-versions
[3] Transparent data encryption: https://www.alibabacloud.com/help/en/rds/apsaradb-rds-for-postgresql/overview-10
[4] Use the TPC feature: https://www.alibabacloud.com/help/en/rds/apsaradb-rds-for-postgresql/use-the-transparent-page-compression-feature
[5] Use the cache eviction feature: https://www.alibabacloud.com/help/en/rds/apsaradb-rds-for-postgresql/use-the-cache-eviction-feature
[6] Use the rds_ccl extension to perform SQL throttling on an ApsaraDB RDS for PostgreSQL instance: https://www.alibabacloud.com/help/en/rds/apsaradb-rds-for-postgresql/use-the-rds-ccl-extension-to-perform-sql-throttling-on-an-apsaradb-rds-for-postgresql-instance-1694574102030
[7] Extensions supported by ApsaraDB RDS for PostgreSQL: https://www.alibabacloud.com/help/en/rds/apsaradb-rds-for-postgresql/extensions-supported-by-apsaradb-rds-for-postgresql
Build an All-in-one Real-time Data Warehouse (Code-level) Based on AnalyticDB for PostgreSQL
ApsaraDB - January 19, 2024
ApsaraDB - October 20, 2020
Alibaba Clouder - January 11, 2021
Alibaba Cloud Data Intelligence - November 27, 2024
digoal - April 29, 2021
Alibaba Cloud Community - March 16, 2022
ApsaraDB RDS for PostgreSQL
An on-demand database hosting service for PostgreSQL with automated monitoring, backup and disaster recovery capabilities
Learn More
PolarDB for PostgreSQL
Alibaba Cloud PolarDB for PostgreSQL is an in-house relational database service 100% compatible with PostgreSQL and highly compatible with the Oracle syntax.
Learn More
ApsaraDB RDS for MySQL
An on-demand database hosting service for MySQL with automated monitoring, backup and disaster recovery capabilities
Learn More
ApsaraDB RDS for MariaDB
ApsaraDB RDS for MariaDB supports multiple storage engines, including MySQL InnoDB to meet different user requirements.
Learn MoreMore Posts by ApsaraDB
