Instance selection and cluster planning

Instance resource modes

• Engine version

  The elastic storage mode supports the following engine versions:

  • 6.0 Standard Edition: A standard engine built on a customized version of the open-source Greenplum Database kernel. It is suitable for general-purpose business scenarios.

  • 7.0 Standard Edition: Built on PostgreSQL 12, Version 7.0 offers improved performance, expanded functionality, and enhanced enterprise-grade features and security.

• Node specifications

  AnalyticDB for PostgreSQL uses a Massively Parallel Processing (MPP) architecture consisting of coordinator and compute nodes. The coordinator node is the entry point for the instance, responsible for handling SQL requests, routing and distributing queries, and processing result sets. Compute nodes are responsible for SQL processing and data storage. Each compute node processes and stores a data shard, which corresponds to a data partition in the MPP architecture.

  When you create or upgrade an instance, you need to select the coordinator node resources, compute node specifications, number of compute nodes, and Storage Class.

  • Coordinator node resources: We recommend that the number of CUs for the coordinator node match the number of CPU cores in a single compute node.

    To add more coordinator node resources after instance creation, see Manage coordinator node resources.

  • Compute node specifications:

    • 2 cores and 16 GB of memory per node. Suitable for low-concurrency scenarios.

    • 4 cores and 32 GB of memory per node. Recommended for general-purpose, medium-concurrency scenarios.

    • 8 cores and 64 GB of memory per node. Suitable for high-concurrency scenarios.

    • 16 cores and 128 GB of memory per node. Suitable for very high-concurrency scenarios.

  • Number of compute nodes: In an MPP architecture, data processing capacity scales linearly with the number of compute nodes, ensuring stable response times as data volume grows. You can select the appropriate number of nodes based on your raw data volume and application scenario.

  • Storage Class:

    • ESSD: Delivers high I/O performance for demanding analytical workloads.

      Storage type	Cores per node	Memory	Storage range	Recommended use case
      ESSD	2	16 GB	50 GB to 1 TB	Low-concurrency scenarios. Supports 4 to 128 nodes.
      ESSD	4	32 GB	50 GB to 2 TB	High-concurrency scenarios. Supports 4 to 128 nodes.

    • The recommended ratio for Cores:Memory:Storage is 1:8:80. To ensure optimal instance performance, we recommend a storage-to-memory ratio of 20 or less for each compute node. This helps maintain a high cache hit rate.

  • Data Encryption:

    • If your scenario requires data security and compliance, enable disk encryption. By default, encryption is disabled. You can enable disk encryption only when you create an instance and cannot enable it afterward. To enable this feature, you must select ESSD or Ultra Disk as the storage type. Once enabled, disk encryption cannot be disabled. Snapshots of the encrypted instance, and any new instances created from those snapshots, automatically inherit the encryption property.

    • Encryption can affect storage I/O performance, which may reduce the instance's response time and throughput.

Selecting an instance configuration

For a high-performance analytics scenario with 5 TB of raw data and more than 100 concurrent queries, we recommend an instance with an ESSD storage type with specifications of 4 cores and 32 GB of memory per node. Assuming a 2:1 compression ratio, the workload requires about 6.25 TB of storage to maintain 80% utilization. Therefore, a 32-node configuration with 200 GB of storage per node (6.4 TB total) is a suitable choice. By default, AnalyticDB for PostgreSQL uses a primary/secondary high-availability architecture with dual data replicas.

Row-oriented and column-oriented storage

AnalyticDB for PostgreSQL supports both row-oriented storage and column-oriented storage for table data. Each has advantages and disadvantages in different scenarios and directly impacts instance performance and storage space. When you create a table, you can specify its storage format to organize data by row or by column.

• Consider using row-oriented storage for Online Transaction Processing (OLTP) workloads with frequent data updates or real-time writes (INSERT, UPDATE, DELETE). For mixed OLTP and Online Analytical Processing (OLAP) workloads, you can use a partitioned table, for example, by partitioning based on a time dimension. We recommend using no more than 200 partitions to avoid impacting SQL query optimization performance. If your workload involves frequent queries on recent, detailed data, row-oriented storage is a good choice. For 1 TB of raw data, the size after ingestion into a row-oriented table is typically around 1 TB. We recommend planning for 2 TB of total user data space to account for data replicas, indexes, logs, and temporary files. If high query performance is required, you can increase the number of instance nodes to add more CPU and memory resources and improve query performance.

• Consider using column-oriented storage for OLAP workloads with batch Extract, Transform, Load (ETL) scenarios where data is infrequently updated (UPDATE, DELETE). It is also ideal for batch data ingestion and queries that aggregate or join a few columns from a full table scan. If you need a high compression ratio, choose column-oriented storage. It offers a high compression ratio of 2:1 to 5:1, meaning 1 TB of raw data can be compressed to 0.5 TB or less. In this case, planning 1 TB of user data space for every 1 TB of raw data is a good starting point, as this accounts for both compression and data replication.

• AnalyticDB for PostgreSQL also supports OSS foreign tables. You can use gzip to compress data on external storage and offload data that does not require real-time computation to further reduce in-database storage costs.

Related documentation

• AnalyticDB for PostgreSQL V7.0

• Serverless mode

• Instance specifications