AnalyticDB:Product introduction

SQL compatibility and protocol support

AnalyticDB for PostgreSQL supports JDBC and ODBC connections and is fully compatible with the SQL:2003 syntax standard, PostgreSQL, and Greenplum, and partially compatible with Oracle syntax. AnalyticDB for PostgreSQL provides PL/pgSQL stored procedures, Apache MADLib for machine learning, and PostGIS for geometry analysis.

Use standard SQL to perform hybrid analysis across all three data types:

• Structured data: tables, relational schemas

• Semi-structured data: JSON and JSONB format

• Unstructured data: images, audio files

Deployment options

Performance benchmarks

AnalyticDB for PostgreSQL ranks first in the TPC Benchmark-H (TPC-H) 30 TB cost-effectiveness list. It also passes both the TPC Benchmark-C (TPC-C) transactional performance test and the TPC Benchmark-DS (TPC-DS) 100 TB 640-node analytical performance test, both organized by the China Academy of Information and Communications Technology (CAICT).

Architecture

AnalyticDB for PostgreSQL uses a two-tier architecture: coordinator nodes handle query planning and transaction management, while compute nodes handle query execution and data storage. The nodes are interconnected for data transmission.

Both coordinator nodes and compute nodes maintain multiple replicas for high availability and data reliability. Scale out either node type to increase the concurrency and throughput of read and write queries.

Components

Segment — the execution unit

Each segment handles SQL execution and data storage. To minimize round-trips to coordinator nodes, the segment's local catalog stays synchronized with the global catalog. A local transaction manager coordinates transactions within the segment, and a buffer pool caches reads and writes to improve I/O performance.

The query executor uses a vectorized execution engine and just-in-time (JIT) compilation, improving calculation performance by multiple times compared with the row-by-row volcano model.

Storage types

Choose the storage type based on your workload:

• Row store tables: Optimized for live write, update, and delete operations, point queries, and range queries. Data is stored by row, orderable by primary key. Supports B-tree, bitmap, and GIN indexes. Transactions are managed using Multi-Version Concurrency Control (MVCC).

• Column store tables: Optimized for batch analytical queries where updates and deletes are infrequent. Data is stored by column with high compression. Supports B-tree indexes for point queries and lightweight block range indexes using min/max values. Multi-column sorting enables efficient composite condition filtering.

• External tables: Query metadata stored in local system tables, and data stored in Object Storage Service (OSS), Hadoop Distributed File System (HDFS), or Apache Hive without loading it into the database. Supports ORC, Parquet, CSV, and JSON formats. ORC and Parquet files support column filtering and predicate pushdown for faster analytical queries. External tables are partitionable.

Partitioned tables can mix storage types across partitions. For example, use row store for the current month's partition (frequent writes), column store for archived months (batch reads), and OSS external tables for older partitions with low query volume.

Data distribution

When creating a table, choose a data distribution strategy to control how rows are spread across compute nodes. Even distribution maximizes I/O performance, storage utilization, and query efficiency.

After distributing data across compute nodes, partition the data on each node to narrow the scope of specific queries. AnalyticDB for PostgreSQL supports range and list partitioning, and multi-level partitions. In the following figure, a hash table is distributed across three nodes on the id column. Range partitioning is then applied on the date column, followed by list partitioning on the city column. Each partition stores its own data and indexes.

Database objects

AnalyticDB for PostgreSQL is an object-relational database. Customize objects and their properties — data types, functions, operators, domains, and indexes — to model complex data structures.

Database objects include tables, views, functions, sequences, indexes, partitioned child tables, and external tables. Objects are organized into schemas:

• Schema: A logical namespace within a database. After a database is created, a public schema is created by default. All users and roles can access public, and objects created without an explicit schema are placed there.

• Database: A physical collection of database objects. To connect to a database (for example, through the Data Management (DMS) console), a user must specify which database to connect to.

• Users and roles: Control instance-level permissions and manage access to all objects. By default, a user cannot view objects they don't own. Grant the required permissions to allow access to objects in other schemas. Every user has permission to create objects in the public schema by default.

AnalyticDB for PostgreSQL stores all objects as system metadata on both coordinator and compute node servers.