ApsaraDB RDS:[New features/New specifications] ApsaraDB RDS for PostgreSQL supports PostgreSQL 17

Effective date

October 17, 2024

Feature description

Highlights of PostgreSQL 17

In PostgreSQL 17, improvements and optimizations are made from dimensions of performance, logical replication, developer experience, monitoring, permissions, and security.

• Performance

  • VACUUM

    • The new data structure TIDStore is introduced to record dead tuples. This improves the efficiency of VACUUM and reduces memory usage. TIDStore is referred to as a radix tree for internal use.

    • The memory upper limit for VACUUM is no longer 1 GB. When you delete a table that contains indexes, VACUUM records dead tuples in the memory. If the memory upper limit for VACUUM is reached, a scan for the index file is triggered. The change in the memory upper limit for VACUUM helps reduce the number of index file scans, speed up the VACUUM process, and save I/O resources.

  • WAL

    • The management of write-ahead log (WAL) locks is optimized to improve the performance of highly concurrent modifications.

  • Optimizer

    • Statistics and the WITH clause can be used at the same time to deepen optimization.

    • If B-tree indexes can be used in the IN clause, the query speed is significantly improved.

    • The system automatically removes redundant IS NOT NULL constraints for fields that contain NOT NULL constraints.

• Logical replication

  • The logical replication slot failover feature is supported.

  • Logical replication slots can be retained when you upgrade your database engine version from PostgreSQL 17 to a later version.

  • A binary tool named pg_createsubscriber is added, which can convert a physical replica into a new logical replica.

  • The performance of logical decoding in scenarios in which a large number of sub-transactions are involved is improved.

• Monitoring

  • A view named pg_stat_checkpointer is added to provide statistics on checkpoints. This helps effectively monitor the frequency at which checkpoints are saved and the resource usage.

  • A view named pg_wait_events is added to display the causes of a wait event.

  • You can execute the EXPLAIN statement to query the memory usage of an optimizer.

• Permissions and security

  • The table-level MAINTAIN permission is added, and the pg_maintain user is retained. This helps manage the permissions on table operations such as VACUUM and REINDEX.

  • The sslnegotiation parameter is added to enable direct Transport Layer Security (TLS) handshakes. This reduces the network round-trip time (RTT).

• Developer experience

  • The support for JSON is improved.

    • The JSON_TABLE() function is added. This function converts JSON data into a table in SQL statements.

    • The JSON(), JSON_SCALAR(), and JSON_SERIALIZE() JSON constructor functions are added.

    • The JSON_EXISTS(), JSON_QUERY(), and JSON_VALUE() JSON query functions are added.

  • The pg_column_toast_chunk_id() function is provided to obtain the chunk ID of a specific TOAST.

What are the benefits of ApsaraDB RDS for PostgreSQL instances that run PostgreSQL 17?

ApsaraDB RDS for PostgreSQL is fully compatible with the Community Edition of PostgreSQL. ApsaraDB RDS for PostgreSQL uses a cloud-native architecture and optimizes software and hardware to provide stable, reliable, and cost-effective database services. ApsaraDB RDS for PostgreSQL provides a wide range of extensions to meet scenario-specific business requirements.

ApsaraDB RDS for PostgreSQL fully supports PostgreSQL 17. The self-developed features of ApsaraDB RDS for PostgreSQL are compatible with PostgreSQL 17. ApsaraDB RDS for PostgreSQL also provides various self-developed kernel features to enhance the PostgreSQL version to meet your scenario-specific business requirements.

• Costs

  • (New feature) Data archiving of general ESSDs

    ApsaraDB RDS for PostgreSQL instances access Object Storage Service (OSS) buckets by using tablespaces and external file systems. You can execute DDL statements to transfer data between Enterprise SSDs (ESSDs) and OSS buckets. After you enable the data archiving feature for an RDS instance, you can store data on your RDS instance in OSS buckets based on the mass storage, security, cost-effectiveness, and high reliability of OSS. The data archiving feature allows you to access data that is infrequently accessed. After you enable the data archiving feature of ESSDs, the storage cost is 80% lower than the storage cost of performance level 1 (PL1) ESSDs. For more information, see Use the data archiving feature of general ESSDs.

    Scenario: Your RDS instance contains a large amount of data that is infrequently accessed.

  • Tablespace-level TPC

    The transparent page compression (TPC) feature compresses and decompresses page data in the buffer pool in real time. Data is automatically compressed when the data is written to a disk and decompressed when the data is read from the disk. The compression and decompression processes are imperceptible to users. The TPC feature is developed to reduce the storage usage and improve the read performance of your RDS instance. The feature reduces the disk I/O and the storage usage, improves cache efficiency, and accelerates data transmission by compressing data.

    Benefits of TPC: Storage costs are reduced by approximately 50% on average, the I/O usage is reduced by approximately 50% on average, and transactions per second (TPS) is increased by up to 100% in read scenarios. For more information, see Use the TPC feature.

    Scenarios: Your RDS instance contains a large amount of data and needs to process a large number of read requests.

• AP capabilities

  • (New feature) High-performance OLAP engine that provides the rds_duckdb extension

    The rds_duckdb extension of ApsaraDB RDS for PostgreSQL is developed based on the efficient and resource-friendly DuckDB to enhance analytical query capabilities. You can use the extension to export the on-premises tables of an ApsaraDB RDS for PostgreSQL instance as column-oriented tables and enable the Analytical Processing Query Acceleration (AP) feature. This significantly accelerates complex queries. After you enable the extension for your RDS instance, you can efficiently analyze and query data of the RDS instance. The query performance is significantly improved. For more information, see Use the rds_duckdb extension to accelerate AP queries.

    Scenarios: You want to accelerate complex and analytical data queries.

• Security

  • TDE

    All encryption and decryption operations are performed in the memory. The data in the memory is plaintext, and the data in the disk is ciphertext. This helps prevent data leaks when the disk is stolen. The usage mode of the database remains unchanged to prevent compatibility issues. Fine-grained encryption ensures data security, minimizes the performance loss caused by data encryption and decryption, and allows you to enable default encryption. For more information, see Configure TDE.

    Scenarios: You want toencrypt ata or meet specific security compliance requirements.

• O&M

  • Cache eviction

    The cache eviction feature reduces the memory usage of persistent connections in PostgreSQL in multi-table scenarios. Each time a backend process in PostgreSQL accesses a table, the metadata of the table is cached to your on-premises device, and the cache is not proactively released. This reduces subsequent disk access. As a result, persistent connections may generate a large number of caches and cause out of memory (OOM) errors. For more information, see Use the cache eviction feature.

    Scenarios: Your workloads are involved in software as a service (SaaS) scenarios in which a large number of persistent connections exist.

  • SQL throttling

    The SQL throttling feature can be used to limit the maximum number of concurrent SQL statements to prevent excessively high database loads and ensure database stability and reliability. For more information, see Use the rds_ccl extension to perform SQL throttling.

    Scenarios: Your workloads are involved in scenarios such as high-concurrency access, malicious attack prevention, and resource usage control.

• Self-developed or third-party extensions

  ApsaraDB RDS for PostgreSQL supports various self-developed and third-party extensions that provide extended features. For more information, see Supported extensions. Examples:

  • pgvector: This is a third-party open source vector search extension. The extension can be used to store vector data, perform vector similarity searches, and enrich the underlying data for AI services.

  • Ganos: Ganos is a spatio-temporal engine developed by Alibaba Cloud. Ganos provides a series of data types, functions, and stored procedures for ApsaraDB RDS for PostgreSQL to efficiently store, index, query, analyze, and compute spatial or spatio-temporal data.

  • Always-confidential database: This is a feature developed by Alibaba Cloud. The feature encrypts sensitive data columns so that the data can be transmitted, computed, and stored in ciphertext. The feature can effectively resolve data security issues in various scenarios such as platform security O&M, sensitive data compliance, and multi-source data integration.

  • index_adviser: This is an extension developed by Alibaba Cloud. The extension helps you determine the columns on which you need to create indexes to improve query performance for specific workloads.

  • oss_fdw: This is an extension that is developed by Alibaba Cloud and can be used to import data from an OSS bucket into an RDS instance. The extension can also be used to export data from an RDS instance to an OSS bucket.

  • pgbouncer: This is a third-party open source lightweight connection pool that is used to optimize the connections to an RDS instance and improve resource usage. When an application needs to connect to a PostgreSQL database, PgBouncer can reuse an existing database connection in a connection pool. This prevents performance overhead due to frequent connection establishment and closing. The connection reuse mechanism significantly reduces the load on the database server and improves the response speed and concurrency performance of the application. This feature is suitable for scenarios in which a large number of short-lived connections exist and connections are frequently established and closed.

  • pg_jieba This is a third-party open source extension that is incorporated with some features of Alibaba Cloud. The extension is used for full-text search in Chinese and supports custom data libraries.

Use RDS instances that run PostgreSQL 17

Upgrade the database engine version to PostgreSQL 17

ApsaraDB RDS for PostgreSQL provides the blue-green deployment and local upgrade modes to help you upgrade the database engine version to PostgreSQL 17.

• In the blue-green deployment mode, you can use the No cutting configuration method to verify the feasibility of an upgrade without interruptions to your workloads on the RDS instance. You can use the Cutover configuration method to upgrade the database engine version without the need to stop services. After the upgrade is complete, the system automatically changes the endpoint of the original RDS instance to the endpoint of the new RDS instance. The upgrade does not affect your workloads.

• In local upgrade mode, no new RDS instance is created. After the upgrade, the original RDS instance runs the new major engine version and inherits the original orders, name, tags, parameter settings, billing method, alert rules in CloudMonitor, and backup settings.

For more information, see Upgrade the major version.

Create an RDS instance that runs PostgreSQL 17

For more information, see Create an ApsaraDB RDS for PostgreSQL instance.