DB‎ > ‎Postgres‎ > ‎

PostgreSQL mostly tuned parameters

Thank you for visiting this page, this page has been update in another link PostgreSQL mostly tuned parameters
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 changed. 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. 

listen_addresses = 'localhost'

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>'

max_connections = 100

max_connections = 2000

Really depends on your application, I set it to 2000 for most of connections are short lifetime SQL.

shared_buffers = 32MB and 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.
 
shared_buffers = 3GB
effective_cache_size = 16GB

work_mem = 1MB and  aintenance_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

work_mem = 32MB
maintenance_work_mem = 256MB

checkpoint_segments = 3

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

checkpoint_segments=32

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
My setting is archive
wal_level = archive

ARCHIVE

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

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

autovacuum_max_workers
autovacuum_vacuum_threshold = 500
autovacuum_vacuum_cost_limit = 200



Comments