PostgreSQL has a not very long list of parameters to let you tune for your dedicated task/server, most of time, PostgreSQL can do amazing job on default configuration or with a little bit tweaking. The example below has been working perfectly in my production for years(of course, tweaking a bit every time new server/release comes in).

Here are most of parameters I tuned. It's been used for heavy duty meta data file server, not just for DB, 36GB memory, two mirrored SSDs, 65GB db size, 24 CPUs.


No doubt, you need to change it to let PostgreSQL know what IP address(es) to listen on. If your postgres is not just used for localhost, add or change it accordingly. Also, you need to setup access rules in pg_hba.conf.

listen_addresses = 'localhost,<dbserver>'
default value: "localhost"


max_connections = 2000
default: max_connections = 100

This parameter really depends on your application, I set it to 2000 for most of connections are short lifetime SQL, and connection are reused.

Buffer size

shared_buffers = 3GB
effective_cache_size = 16GB
shared_buffers = 32MB
effective_cache_size = 128MB

These two are most confusing parameters for people, I have another article PostgreSQL database buffer cache and OS cache, tells you more details. 

Work memory

work_mem = 32MB
maintenance_work_mem = 256MB

work_mem = 1MB and  maintenance_work_mem = 16MB

work_mem is for each connection, while maintenance_work_mem is for maintenance tasks for example, vacuum, create, index etc..  Set work_mem big is good for sorting types of query, but not for small query. This has to be considered with max_connections


checkpoint_segments = 32

Maximum number of log file segments between automatic WAL checkpoints (each segment is normally 16 megabytes)


wal_level = archiv
wal_level = minimal

wal_level determines how much information is written to the WAL. The default value is minimal, which writes only the information needed to recover from a crash or immediate shutdown. archive adds logging required for WAL archiving


Not mandatery for all cases. Here is my setting

archive_mode = on
archive_command = '/bin/cp -p %p /home/backups/archivelogs/%f </dev/null'


autovacuum is a quite hot topic in Postgres, for most of time, global autovacuum doesn't work well, so I have another article  Postgresql table level auto autovacuum

track_counts = on
autovacuum = on
autovacuum_vacuum_threshold = 500
autovacuum_vacuum_cost_limit = 200



Comments powered by CComment