Tuning the PostgreSQL Database

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;

Creating a ramdisk of the stats table

mkdir -p /db/ramdisk
chown postgres:postgres /db/ramdisk
chmod 777 /db/ramdisk
chmod +t /db/ramdisk

#in /etc/fstab add
tmpfs   /db/ramdisk     tmpfs   defaults,noatime,nosuid,nodev,size=30M  0       0
#In postgresql.conf, add :
stats_temp_directory = '/db/ramdisk'
#Restart server

Using a RAM disk for the postgres stats

mkdir -p /db/ramdisk
chmod 777 /db/ramdisk
chmod +t /db/ramdisk
cd /db
chown postgres:postgres ramdisk

Add the following to /etc/fstab

tmpfs   /db/ramdisk     tmpfs   defaults,noatime,nosuid,nodev,size=30M  0       0

Add the following to the postgresql.conf file

stats_temp_directory = '/db/ramdisk'

Run the following

mount /db/ramdisk/
systemctl restart postgresql-10
/etc/init.d/rosd restart

You should now see files in the /db/ramdisk folder