:Best practices for performance optimization of AnalyticDB for PostgreSQL in Serverless mode

Select the number of compute nodes

The following figure shows the deployment architecture and resource specifications of AnalyticDB for PostgreSQL in Serverless mode.

If you have high requirements for read performance, you can select the number of compute nodes based on the specifications shown in the preceding figure.

For example, the DADI cache of a compute node that has 4 CPU cores provides 4 GB of RAM memory and 240 GB of local SSD capacity. If 1 TB of hot data is available, at least five compute nodes are required. In AnalyticDB for PostgreSQL in Serverless mode, the number of compute nodes must be an even number. Therefore, you must select at least six compute nodes.

Prefetch hot data after scale-in or scale-out

The following steps are required for node scale-in or scale-out of AnalyticDB for PostgreSQL in Serverless mode:

1. Initialize resources.
2. Synchronize the metadata of system tables.
3. Lock the data and modify the data distribution information.
4. Unlock the data and clear resources.
5. Restore the local cache asynchronously.

Data can be read and written after Step 4 is complete. However, the local cache may be hit at a low rate because the local cache is asynchronously loaded. We recommend that you manually prefetch the tables that are required for high performance after scale-in or scale-out to accelerate access. Execute the following SQL statement to prefetch a table:

SELECT count(*) FROM <hot_table>;