7.3. Calculating Server Capacity #
You can calculate the maximum number of analytical queries per second that a server can process by dividing:
the available CPU cores by the average CPU consumption per query
the available RAM by the average RAM consumption per query
the storage read throughput by the average amount of data read per query
The smallest result is the limit.
The examples below illustrate this calculation using the results of benchmark tests performed by Postgres Pro. The server with the following characteristics is assumed:
32 CPU cores
128 GB of RAM
10 Gbit network
The total amount of data is 10 TB in a local storage with an array of NVMe RAID-0 disks and 4 GB/s read throughput or in an S3 storage with 500 MB/s read throughput.
For example, in ClickBench benchmark test (scale factor 100) with analytical queries evenly distributed over time:
Average CPU consumption per query: 7 seconds.
Average RAM consumption per query: 500 MB.
Average data read per query: 900 MB.
The limits are as follows:
The CPU limit is reached at 32 / 7 = 4.5 queries per second (16,200 queries per hour).
The RAM limit is reached at 128 / 0.5 = 256 queries per second (921,600 queries per hour).
The local storage read throughput limit is reached at 4 / 0.9 = 4.4 queries per second (15,840 queries per hour).
The S3 storage read throughput limit is reached at 0.5 / 0.9 = 0.5 queries per second (1,800 queries per hour).
Therefore, the server can process up to 4.4 analytical queries per second (15,840 queries per hour) with the local storage or up to 0.5 analytical queries per second (1,800 queries per hour) with the S3 storage.
For example, in TPC-H benchmark test (scale factor 100) with analytical queries evenly distributed over time:
Average CPU consumption per query: 34 seconds.
Average RAM consumption per query: 1.4 GB.
Average data read per query: 6.8 GB.
The limits are as follows:
The CPU limit is reached at 32 / 34 = 1 query per second (3,600 queries per hour).
The RAM limit is reached at 128 / 1.4 = 91 queries per second (327,600 queries per hour).
The local storage read throughput limit is reached at 4 / 6.8 = 0.6 queries per second (2,160 queries per hour).
The S3 storage read throughput limit is reached at 0.5 / 6.8 = 0.07 queries per second (252 queries per hour).
Therefore, the server can process up to 0.6 analytical queries per second (2,160 queries per hour) with the local storage or up to 0.07 analytical queries per second (252 queries per hour) with the S3 storage.
In both examples, the RAM consumption by analytical queries is low compared to the available RAM, so the remaining RAM can be used for caching Parquet files using the operating system file cache or the pgpro_axe cache. Caching Parquet files in RAM reduces the storage read throughput required for analytical queries that read the same data repeatedly.
These examples use standardized benchmarks. However, real workloads have different query patterns and resource consumption. To estimate actual requirements, measure the CPU, RAM, and storage read throughput consumed by your analytical queries, and apply the same calculation.