PostgreSQL®- postgres.conf Parameter

For the read performance of the NGA queries (dpGetPeriod, dpGetPeriodSplit...), parameters must be changed in the file <PG installation path>/<PG version>/postgres.conf.
Note: Note that these parameters are set automatically for a PostgreSQL® database backend managed by WinCC OA.
However, set the following parameters for a DBAdmin-managed PostgreSQL® database backend:
Table 1. postgres.conf Parameters
Parameter Description
shared_buffers This parameter determines how much memory is dedicated to the server for caching data. It is recommended to set this to 25% of your total RAM.

As WinCC OA is deployed in range of very small to very big servers, at the first start of a managed NGA project, the available physical RAM size is detected and the shared buffer size is defined by main memory RAM * 0,25.

The shared_buffers parameter must be an integer and can be specified either in MB or in GB unit. e.g.

"shared_buffers = 128MB" or "shared_buffers = 2GB".
work_mem This setting controls the amount of memory allocated for internal sort operations and hash tables before writing to temporary disk files. Adjust this based on your workload and the number of concurrent connections. Set the parameter

work_mem: "work_mem = 128MB"
max_parallel_workers_per_gather Parallelizing query execution, which can significantly speed up large, complex queries by distributing the workload across multiple CPU cores. Set the parameter

max_parallel_workers_per_gather:

"max_parallel_workers_per_gather = 4"
random_page_cost This parameter helps the query planner estimate the cost of random disk page accesses, which are typically more expensive than sequential accesses due to the additional seek time involved. Set the parameter

random_page_cost :

"random_page_cost = 1"
maintenance_work_mem This parameter allocates memory for maintenance tasks, which can help speed up operations like vacuuming and indexing by allowing them to process more data in memory rather than relying on slower disk I/O. Detect also the available physical RAM size and set the parameter to

main memory-RAM * 0,03125 e.g. "maintenance_work_mem = 1 GB"