7.2. Estimating Required Compute Resources #
You can get an approximate estimate of compute resources required to operate Postgres Pro AXE based on the results of benchmark tests performed by Postgres Pro. For the initial deployment, it is recommended to allocate at least 4 CPU cores and 24 GB of RAM.
Since each dataset and query set is unique, and query syntax can directly impact the solution performance, you may need a more precise estimation. For that, perform benchmark tests yourself on servers corresponding to hardware and software requirements, using your dataset and real analytical queries from your applications at peak workloads. During such tests, measure the following characteristics:
query execution time
number of queries executed per second
average CPU load
average amount of free RAM
average storage read throughput
With benchmark test results, calculate the average consumption of compute resources per analytical query. Then multiply the result by the required number of queries per second to obtain the total amount of compute resources.
The first priority is having sufficient RAM. If pgpro_axe lacks RAM for executing a query, intermediate data is written to temporary files — a process also known as spilling, which degrades solution performance. Temporary files are located in the directory specified by the duckdb.temporary_directory configuration parameter.
Most queries must be processed in RAM without spilling to maximize the number of queries per second. It is recommended to allocate at least 6 GB per CPU core dedicated to query execution. Exact requirements depend on the workload: heavy joins, aggregations over a large number of unique keys, and sorting large tables consume significantly more RAM.
Once RAM is sufficient to avoid spilling, query execution time depends primarily on the number of available CPU cores.
Postgres Pro AXE scales linearly: if executing 5 analytical queries per second requires 16 CPU cores, 32 GB of RAM, and 0.5 GB/s of storage read throughput, then executing 10 (5×2) queries per second requires 32 CPU cores, 64 GB of RAM, and 1 GB/s of storage read throughput.
Warning
Linear scaling applies only while CPU, RAM, and storage read throughput all remain available. Once any of these resources is exhausted, solution performance degrades significantly.
To avoid this, increase the limiting resource, redistribute the workload across the day and optimize analytical queries, or offload some workload to standby servers.
It is recommended to leave a 30% margin of the estimated compute resources for unplanned analytical queries, such as ad-hoc reports.
After deploying Postgres Pro AXE, monitor the amount of temporary files created by pgpro_axe every 24 hours and avoid peak values. Temporary files can be used for resource-intensive analytical queries with long execution time (hours).