Skip to main content

Advanced Database Memory Tuning

To apply database tuning settings like those below, amend the postgresql.conf file, save, and restart the database.

If you are unsure where the conf file is located, you can use the following command:

psql -U postgres -c 'SHOW config_file'

Small Profile

Database memory tuning example with values calculated based on the small profile defined below:

  • < 20 repositories

  • < 20GB total blob store size

  • single repository format type

max_connections = 200
shared_buffers = 4GB
effective_cache_size = 12GB
maintenance_work_mem = 1GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 10485kB
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
max_parallel_maintenance_workers = 2

Medium

Database memory tuning example with values calculated based on the medium profile defined below:

  • < 50 repositories

  • < 200GB total blob store size

  • a few repository formats

max_connections = 300
shared_buffers = 8GB
effective_cache_size = 24GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 10485kB
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4

Large

Database memory tuning example with values calculated based on the large profile defined below:

  • < 200 repositories

  • > 200GB total blob store size

  • diverse repository formats

max_connections = 400
shared_buffers = 8GB
effective_cache_size = 24GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 10485kB
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 16
max_parallel_workers_per_gather = 4
max_parallel_workers = 16
max_parallel_maintenance_workers = 4

For legacy OrientDB, see additional memory tuning procedures.