Recommended PostgreSQL version
If the PostgreSQL database server is used for purpose of archiving, we recommend using PostgreSQL version 9.5 or higher. These versions support the UPSERT command and allow you to set the Upsert archive parameter to 1.
PostgreSQL settings
In the postgresql.conf configuration file, we recommend changing the following settings. Numeric values are indicative and can be tuned - optimal values depend on archive load, available memory, and so on.
shared_buffers = 256MB
Parameter shared_buffers determines how much memory is dedicated to PostgreSQL to use for caching data.
work_mem = 16MB
Parameter work_mem determines how much memory is dedicated to PostgreSQL to use for in-memory sorts.
maintenance_work_mem = 128MB
Parameter maintenance_work_mem determines how much memory is dedicated to PostgreSQL to use for maintenance operations (VACUUM etc). When the structured archives that contain lots of data are used, the larger amount of maintenance memory will accelerate the maintenance.
synchronous_commit = off
Parameter synchronous_commit set to value off will cause, the COMMIT operatioin not to require synchronisation of data to disks, so it will be performed faster.
checkpoint_completion_target = 0.9
Parameter checkpoint_completion_target set to value 0.9 will cause a more balanced distribution of disk write operation during checkpoint than the default value 0.5.
effective_cache_size = 1000MB
Parameter effective_cache_size should be set to an estimate of how much memory is available for disk caching by the operating system. This value is used only by the PostgreSQL query planner. On Windows see the "System Cache" size (or "Cached" in "Physical Memory") in the Windows Task Manager's Performance tab.
autovacuum_vacuum_threshold = 100000
Parameter autovacuum_vacuum_threshold specifies the minimum number of updated or deleted rows needed to trigger a VACUUM in any one table.
autovacuum_analyze_threshold = 100000
Parameter autovacuum_analyze_threshold specifies the minimum number of inserted, updated or deleted rows needed to trigger a ANALYZE in any one table.
max_locks_per_transaction = 1000
Parameter max_locks_per_transaction specifies maximum amount of locks used by a single database transaction. This value should be equal or greater that archive parameter CommitCountActive / CommitCountPassive.
Archive settings
We recommend setting the archive parameter Upsert to value 1. This setting activates using of UPSERT command, which was implemented in PostgreSQL 9.5. This setting requires correct setting of ODBC parameters described in the following paragraph.
Archive ODBC settings
To connect to PostgreSQL database, the archive uses PostgreSQL ODBC Driver, specifically its Unicode version. The minimum recommended version of the driver is 09.06.0500.
For optimal performance, we recommend increasing the Cache Size to 10000 in Advanced Options of the archive Data Source (DSN):
On the next page of Advanced Options of archive DSN it is necessary to set Level of rollback on errors to Transaction and check Server side prepare for the proper functioning of archive:
Pridať komentár