AnalyticDB for PostgreSQL V6.0 is an online database service developed based on the open source Greenplum 6.0 kernel. This service offers an improved capability for processing concurrent transactions in real-time data warehousing scenarios. Transaction locks are optimized to support hybrid transaction/analytical processing (HTAP) services. The following sections describe the new features of AnalyticDB for PostgreSQL V6.0.

Kernel update

The Greenplum kernel is updated from 4.3 to 6.0, and the PostgreSQL kernel is updated from 8.2 to 9.4. New features are as follows:

  • The JSONB data type is supported to offer more JSON processing functions and higher processing efficiency.
  • The UUID data type is supported.
  • GINs and SP-GiST indexes are supported to allow high-performance fuzzy match and retrieval.
  • Schema- and column-level permissions can be granted and managed.
  • VACUUM statements are supported. When they are executed to reclaim storage space, pages with locks are skipped temporarily. A round-robin algorithm is used to check pages with locks. If their locks are released, VACUUM statements are executed to reclaim their storage space. This reduces blocking.
  • Cross-database query and access are supported over database links.
  • Recursive common table expressions (CTEs) are supported to allow recursive queries of SQL statements. These CTEs are used for hierarchical or tree-structure data.
  • PL/SQL is enhanced to support RETURN QUERY EXECUTE statements. This way, SQL statements can be dynamically executed, and you can define anonymous blocks.

HTAP improvement

The global deadlock detection mechanism is introduced to check and eliminate global deadlocks by dynamically collecting and analyzing lock information. In this situation, both the update and modification operations of heap tables can only be completed with fine-grained row locks. In addition, the modification, deletion, and query operations can be simultaneously performed for a large number of tables. This improves the concurrency and throughput of the system. Transaction locks are optimized to reduce competition for resources when transactions start and end. This way, high-throughput transaction processing is supported in addition to high-performance OLAP.
  • In typical OLTP scenarios, 100,000 transactions per second (TPS) are allowed in TPC-C.
  • 150,000 TPS are supported for SELECT operations in TPC-B.
  • 50,000 TPS are supported for INSERT operations in TPC-B.
  • 20,000 TPS are supported for UPDATE operations in TPC-B.

OLAP

  • Replicated tables are supported. For dimension tables in data warehouses, replicated tables can be created by using DISTRIBUTED REPLICATED clauses. This reduces data transmission and improves query efficiency.
  • The Zstandard compression algorithm is supported. This algorithm offers compression and decompression performance three times better than the zlib algorithm.