AnalyticDB for PostgreSQL - Supports Features Such as Auto-vacuum and Real-time Materialized Views and Provides Some Optimizations
May 28 2021AnalyticDB for PostgreSQL
Target customers: all AnalyticDB for PostgreSQL users. Features released: New features: AnalyticDB for PostgreSQL now supports the auto-vacuum feature. Auto-vacuum checks for tables that have a large number of INSERT, UPDATE, or DELETE operations. When necessary, auto-vacuum executes a VACUUM statement on the tables to vacuum junk data and accelerate queries. By default, when more than half of the rows of a table are deleted, auto-vacuum executes a VACUUM statement on the table to vacuum junk data. If an AnalyticDB for PostgreSQL instance has multiple coordinator nodes, auto-vacuum checks for change operations only on the primary coordinator node. Auto-vacuum cannot be triggered if change operations are performed on secondary coordinator nodes. AnalyticDB for PostgreSQL also supports the auto-analyze feature. Auto-analyze checks for tables that have a large number of INSERT, UPDATE, or DELETE operations. When necessary, auto-analyze executes an ANALYZE statement on the tables to collect their statistics after the operations. By default, when these operations are performed on more than 10% of the rows in a table, auto-analyze executes an ANALYZE statement on the table. If an AnalyticDB for PostgreSQL instance has multiple coordinator nodes, auto-analyze checks for change operations only on the primary coordinator node. Auto-analyze cannot be triggered if change operations are performed on secondary coordinator nodes. AnalyticDB for PostgreSQL also supports BRIN indexes. BRIN stands for Block Range Index and is supported for compressed append-optimized (AO) tables. For example, BRIN indexes can be used to query large-range data from an ordered table or filter out data blocks that are not needed to reduce I/O. When large datasets are involved, BRIN indexes can provide similar or superior performance but require less physical storage and generation costs compared with other indexes such as B-tree indexes. AnalyticDB for PostgreSQL also supports parallel query (beta). The rds_segment_expansion_coeff parameter is added for session-level queries. The parameter value is of the INTEGER type, and the default value is 1. This parameter specifies a multiple of cores for a single query to implement parallel query and uses a query optimizer to further optimize the query. In scenarios with low queries per second (QPS), this parameter can linearly improve the performance of compute-intensive queries that take longer than 3 seconds to execute. Typical scenarios include aggregate queries for individual tables (TPC-H Q1 or TPC-H Q6) and join operations between large and small tables. However, this parameter provides only marginal improvements for I/O-intensive queries and high disk usage scenarios, and may reduce the performance for network-intensive queries. AnalyticDB for PostgreSQL also supports real-time materialized views (beta). Real-time materialized views provide features similar to those of materialized views, but do not require you to manually execute REFRESH statements when data changes. Optimized features: Instance endpoints can be used to perform DDL operations between the BEGIN transaction block and the ROLLBACK or COMMIT transaction block. Instance endpoints can be used to perform TEMP TABLE and TEMP VIEW operations for session-level queries.