Page tree
Skip to end of metadata
Go to start of metadata

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:

Write a comment…