Use pgtune command (may need to install the tool) for an initial more optimized setup of the postgresql for your system. It looks up your memory settings and makes a reasonable start. Our priority is to ensure data protection and reduce maintenance costs. We have two strategies for that. For short term problems we try to have upgrade plans. For long term we try to size and be prepared 12-18 months in the future. Our focus is figuring out trends and replicating our load against our data. It is a lot less stressful place to be.
Since human costs are orders of magnitude higher than hardware costs it is usually faster and cheaper to just allocate more hardware resources. Of course that works only up to a point.
Connections and Memory
This particular server allowed up to 900 simultaneous connections. It is far more than is normally recommended by pgtune, but it was part of our requirements.
max_connections = 900
This on a system with 32G of RAM. We normally used around half the memory for the database.
shared_buffers = 16GB
Maintenance memory is set pretty high. This allows the autovacuum to finish quickly. Yes, we run the auto vacuum as opposed to scheduling it. We've never had a problem performance problems due to that.
work_mem = 1GB # min 64kB maintenance_work_mem = 1GB # min 1MB
Our priority is data protection, performance second. In the end it is much cheaper to allocate more CPU / Ram to the server if needed. I've been debating whether to increase wal_buffers to 16MB (the entire WAL could fit in memory). Not too sure if it is worth it.
wal_level = hot_standby # minimal, archive, or hot_standby # (change requires restart) fsync = on # turns forced synchronization on or off wal_buffers = 8MB # min 32kB # (change requires restart) wal_writer_delay = 200ms # 1-10000 milliseconds
Checkpoint setup. These settings worked well for us. Checkpoint_timeout is needed for our SLA. That means the standby is never more than 15 minutes out of date of primary. On some systems this number is quite a bit lower e.g. when the standby is available for search queries.
checkpoint_segments = 16 # in logfile segments, min 1, 16MB each checkpoint_timeout = 15min # range 30s-1h checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0
# - Archiving - archive_mode = on # allows archiving to be done # (change requires restart) archive_command = '/usr/local/bin/omnipitr-archive -l /var/log/postgresql/omnipitr.log \ -s /postgres/omnipitr/state -dr gzip=/postgres/WAL/ -dr gzip=rsync://prprod-stbydb01/WAL/ \ -db /var/tmp/omnipitr/dstbackup -t /var/tmp/omnipitr/ -v "%p"' archive_timeout = 600 # force a logfile segment switch after this # number of seconds; 0 disables
For most purposes we leave it at default setting, other than giving it more memory to work with. We have played with it for some large databases (1.5TB+ for research), but never needed to change it for any of our production deployments.