This topic describes AnalyticDB for PostgreSQL instance specifications and provides recommendations.
Storage types
- High-performance SSD: provides better I/O capabilities and higher analysis performance.
- High-capacity HDD: provides larger storage capacity at a lower cost.
| Storage type | Number of cores per node | Memory | Storage space | Description |
|---|---|---|---|---|
| High-performance SSD | 1 | 8 GB | 80 GB | These specifications are recommended for low-concurrency scenarios that require less than 5 concurrent queries and less than 32 nodes. These specifications are available for 2 to 128 nodes. |
| High-performance SSD | 4 | 32 GB | 320 GB | These specifications are recommended for high-performance SSD storage and available for 8 to 4,096 nodes. |
| High-capacity HDD | 2 | 16 GB | 1 TB | These specifications are recommended for low-concurrency scenarios that require less than 5 concurrent queries and less than 8 nodes. These specifications are available for 4 to 32 nodes. |
| High-capacity HDD | 4 | 32 GB | 2 TB | These specifications are recommended for high-capacity HDD storage and available for 8 to 4,096 nodes. |
A single instance can have up to 4,096 nodes. In the massively parallel processing (MPP) architecture, each node is a partition that is used to store and process a portion of data on the instance. You can add nodes to increase the storage capacity and maintain a stable query response time.
Recommendations on how to select instance specifications
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 supports data storage to Object Storage Service (OSS) external tables. You can use the gzip utility to compress data that is not needed for real-time computing and then upload the data to OSS buckets to reduce storage costs.
- Storage type
- If high performance is your primary concern, we recommend that you choose the SSD storage type.
- If large storage capacity is your primary 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 suitable only for an instance that has 32 nodes or less and processes a small amount concurrent queries. The HDD configuration that supports two cores per node is suitable only for an instance that 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 linearly increase in proportion with the number of nodes. However, the query response time remains constant when 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 store and column store
AnalyticDB for PostgreSQL supports two storage models: row store and column store. You can specify a storage model when you create a table.
- If you want to write data in real time or frequently update data by executing INSERT, UPDATE, and DELETE statements, we recommend that you choose row store.
If you choose row store, 1 TB of raw data requires about 1 TB of storage space. However, the indexes, logs, and temporary files generated during computing also occupy storage space. Therefore, we recommend that you reserve 2 TB of storage space for every 1 TB of raw data. To improve query performance, you can add nodes to increase available CPU and memory resources.
- In batch extract, transform, and load (ETL) scenarios, we recommend that you choose column store. Data is rarely updated by executing UPDATE and DELETE statements and most queries require aggregations and joins of table data based on only a small amount of columns
Column store provides a compression ratio in the range of 1:5 to 1:2. For example, if 1 TB of raw data is reduced to 0.5 TB or less after compression, you need to reserve only 1 TB of storage space for user data.
Examples
If you want to process 5 TB of raw data with high performance to respond to more than 100 concurrent queries, we recommend that you choose the SSD storage type to support 4 cores per node and 32 nodes per instance. In this scenario, a total of 10 TB of storage space is available for user data.