Tuning the PostgreSQL Database

From Observer GigaFlow Support | VIAVI Solutions Inc.
Revision as of 10:02, 23 November 2017 by Kevin Wilkie (Talk | contribs)

Jump to: navigation, search

Tuning of Postgres is done via the postgresql.conf file.

  • Make a copy of the original file should changes to the file cause any problems (allowing you to revert)
  • Edit file as appropriate and save
  • Restart Postgres
  • Restart applications requiring Postgres


Settings based on available RAM

shared_buffers=1/4 of the available memory

effective_cache_size = 1/2 of available memory

Standard Settings

effective_cache_size = 2GB
shared_buffers = 1000MB
autovacuum_max_workers = 1
checkpoint_completion_target = 0.9
checkpoint_segments = 32
log_min_duration_statement = 5000
synchronous_commit = off 
work_mem = 10MB
wal_buffers = 10MB
commit_delay = 10
max_locks_per_transaction = 3000
max_wal_size = 2000MB
max_connections = 150
maintenance_work_mem = 64MB


Example for a server with 16GB of RAM

effective_cache_size = 8GB
shared_buffers = 4000MB
autovacuum_max_workers = 1
checkpoint_completion_target = 0.9
checkpoint_segments = 32
log_min_duration_statement = 5000
synchronous_commit = off 
work_mem = 10MB
wal_buffers = 10MB
commit_delay = 10
max_locks_per_transaction = 3000
max_wal_size = 2000MB
max_connections = 150
maintenance_work_mem = 128MB

Housekeeping

vacuum pg_depend;vacuum full pg_constraint;vacuum full pg_inherits;