Instance specifications

Definitions

The specifications of an AnalyticDB for PostgreSQL instance are determined by the following parameters:

• Storage Type

  This parameter specifies whether to use SSD or HDD storage. The differences between these two types of storage media are as follows:

  • High-performance SSD storage: provides better I/O capabilities and higher analysis performance.
  • High-capacity HDD storage: provides larger storage capacity at a lower cost.
• Node Cores
  AnalyticDB for PostgreSQL uses the massively parallel processing (MPP) architecture. In this architecture, each instance is composed of multiple nodes and each node functions as a partition. The following table describes the specifications available for each node.

  Storage type	Number of cores per node	Memory size	Valid storage space	Total dual-copy storage space	Description
  High-performance SSD storage	1	8 GB	80 GB	160 GB	This specification is ideal if the number of concurrent queries is less than 5 and the number of nodes ranges from 4 to 64.
  High-performance SSD storage	4	32 GB	320 GB	640 GB	This is the specification recommended for high-performance SSD storage. It supports 8 to 4,096 nodes.
  High-capacity HDD storage	2	16 GB	1 TB	2 TB	This specification supports 4 to 32 nodes. It is ideal if the number of concurrent queries is less than 5 and the number of nodes is less than 8.
  High-capacity HDD storage	4	32 GB	2 TB	4 TB	This is the specification recommended for high-capacity HDD storage. It supports 8 to 4,096 nodes.

• Node Num

  An instance consists of up to 4,096 nodes. In the MPP architecture, each node is a partition used to store and process a part of the data on that instance. You can add nodes to increase the storage capacity and maintain a stable query response time.

Principles

When you create or upgrade the specifications of an AnalyticDB for PostgreSQL instance, you must configure Storage Type, Node Cores, and Node Num. AnalyticDB for PostgreSQL also supports data storage to Alibaba Cloud Object Storage Service (OSS). You can use gzip to compress data that is not needed in real-time computing and then upload it to OSS buckets. This helps reduce storage costs.

• Storage Type
  • If high performance is your first concern, we recommend that you choose the SSD storage type.
  • If large storage capacity is your first concern, we recommend that you choose the HDD storage type.
• Number of cores per node

  Each node stores and processes data from a partition of each user table. We recommend that you configure four cores for each node. The SSD configuration that supports one core per node is only suitable if the instance has 32 nodes or less and processes few concurrent queries. The HDD configuration that supports two cores per node is only suitable if the instance has eight nodes or less and processes few concurrent queries.

• Number of nodes

  AnalyticDB for PostgreSQL uses the MPP architecture. This architecture enables the data processing capability of an instance to increase linearly in proportion with its number of nodes. However, the query response time does not change even though the data volume increases. You can determine the number of nodes the instance needs based on your business scenario and the volume of raw data.

Row-oriented storage and column-oriented storage

AnalyticDB for PostgreSQL supports two storage models: row-oriented storage and column-oriented storage. You can specify a storage model when you create a table.

• If you want to write data in real time or update data frequently by executing INSERT, UPDATE, or DELETE statements, we recommend that you choose row-oriented storage.

  If you choose row-oriented storage, 1 TB of raw data requires approximately 1 TB of storage space. However, the indexes, logs, and the temporary files generated during computing also occupy storage space, so we recommend that you reserve 2 TB of storage space for user data. To improve query performance, you can add nodes to increase available CPU and memory resources.

• In batch extract, transform, load (ETL) scenarios, we recommend that you choose column-oriented storage because data is rarely updated by executing UPDATE and DELETE statements and most of the queries require aggregations and joins of table data based on a few columns.

  Column-oriented storage supports a compression ratio of up to 1:2 to 1:5. For example, 1 TB of raw data is reduced to 0.5 TB or less after compression, which means that you only need to reserve 1 TB of storage space for user data.

Examples

If you want to process 5 TB of raw data at a high performance to respond to more than 100 concurrent queries, we recommend that you choose the SSD storage type to support four cores per node and 32 nodes per instance. In this situation, a total of 10 TB of storage space is available for user data.