You can improve the performance of the PostgreSQL database in several ways.
The following two parameters take control of automatic vacuum operations and by default, these parameters are commented while installing the Sentinel Rapid Deployment server and you have to remove the comment and set the values.
vacuum_cost_delay: Determines the length of time that the process will sleep when the cost limit has been exceeded. For example, you can set this value to 100.
vacuum_cost_limit: Determines the accumulated cost that will cause the vacuuming process to sleep. For example, you can set this value to 10000.
If you set the value of these parameter to a non zero value, it will reduce the I/O impact of vacuum and analyze command on the normal database activity. They might be negligible performance impact while running the reports, since the vacuum takes more time than earlier.
By default, the autovacuum process is set to true and runs periodically to recover the disk space and update the planner statistics. When the database size increases, autovacuum is not able to maintain all the database objects. In such cases, if the performance is slow, run the AnalyzePartitions.sh script as a cron job. This cron job should be set by the user who owns the Sentinel Rapid Deployment processes.
For example:
30 11 * * * $ESEC_HOME/bin/AnalyzePartitions.sh
Where:
30 is the time in minutes.
11 is the time in hours.
ESEC_HOME is the absolute path of the database.
In this example, the script runs daily at 11:30.
Avoid scheduling archiving to occur during reporting. If you schedule both processes together, reporting enters a waiting state because of PostgreSQL bugs and starts processing the data after the archive job is complete. This change impacts the performance of the database.