What is it? #
PostgreSQL ships with conservative defaults designed to start on almost any machine, including very small ones. They are safe, and on a real server they leave a great deal of performance unused.
Tuning means telling PostgreSQL about the machine it is actually running on: how much memory it may use, how many connections to expect, how much work to do between checkpoints.
There are two configuration files. postgresql.conf holds the server's settings. pg_hba.conf holds the connection rules covered in the previous lesson.
An honest warning before any numbers: the right values depend on your RAM, your workload and your disks. The formulas below are sensible starting points to measure from, not universal truths. Change one thing, measure, then change the next.
Think of it like this #
A new oven arrives set to a low temperature that will not burn anything in any kitchen.
It works. Nothing catches fire. But if your kitchen is large and well ventilated, cooking everything at that cautious setting takes far longer than it needs to.
Tuning is telling the oven about your kitchen. And the reason to change one setting at a time is the same reason a cook does: if you change four things and the result is worse, you have learned nothing about which one did it.
Simple example #
A server with 16 GB of RAM running a web application's database, still on defaults, is using 128 MB of cache and re-reading from disk constantly.
Setting shared_buffers to 4 GB and effective_cache_size to 12 GB — two lines and a restart — often produces a larger improvement than weeks of query tuning.
Code #
-- ---------- Finding and reading the configuration ----------
SHOW config_file; -- where postgresql.conf lives
SHOW hba_file; -- where pg_hba.conf lives
SHOW data_directory; -- where the actual data is stored
SHOW shared_buffers; -- one setting
SELECT name, setting, unit, context, short_desc
FROM pg_settings
WHERE name IN ('shared_buffers','work_mem','max_connections','wal_level');
-- "context" tells you WHAT IS REQUIRED to change it:
-- postmaster -> needs a full RESTART
-- sighup -> a RELOAD is enough
-- user -> can be changed per session with SET# ---------- Changing settings ----------
# Option 1: edit postgresql.conf directly, then:
sudo systemctl reload postgresql # for sighup settings
sudo systemctl restart postgresql # for postmaster settings <- drops connections
# Option 2 (preferred): ALTER SYSTEM writes to postgresql.auto.conf,
# which overrides postgresql.conf and survives package upgrades cleanly.ALTER SYSTEM SET shared_buffers = '4GB';
ALTER SYSTEM SET work_mem = '32MB';
SELECT pg_reload_conf(); -- applies sighup settings immediately
-- Check whether anything is still waiting for a restart:
SELECT name, setting, pending_restart FROM pg_settings WHERE pending_restart;
ALTER SYSTEM RESET shared_buffers; -- undo, back to postgresql.conf
ALTER SYSTEM RESET ALL; -- undo everything set this way---------- MEMORY: the settings that matter most ----------
shared_buffers PostgreSQL's OWN cache of table and index pages.
START AT: 25% of total RAM.
16 GB RAM -> 4GB
Going far above 40% rarely helps, because the
operating system cache is also caching the same files.
Requires a RESTART.
effective_cache_size NOT an allocation — a HINT to the planner about how
much memory is available for caching overall
(PostgreSQL's own plus the OS cache).
START AT: 50-75% of total RAM.
16 GB RAM -> 12GB
Too low makes the planner avoid indexes it should use.
Reload only.
work_mem Memory for ONE sort or hash operation.
!! THIS IS THE DANGEROUS ONE.
It is per OPERATION, not per connection. A single
query with 3 sorts running on 100 connections can use
300 x work_mem.
START AT: 16MB-64MB for a typical web workload.
Raise it PER SESSION for big reports instead:
SET work_mem = '256MB';
Reload only.
maintenance_work_mem Memory for VACUUM, CREATE INDEX, ALTER TABLE.
Only a few of these run at once, so it can be large.
START AT: 512MB - 2GB.
Bigger values make VACUUM and index builds much faster.-- ---------- Example: a 16 GB server ----------
ALTER SYSTEM SET shared_buffers = '4GB'; -- 25% of RAM (restart)
ALTER SYSTEM SET effective_cache_size = '12GB'; -- 75% hint (reload)
ALTER SYSTEM SET work_mem = '32MB'; -- per operation (reload)
ALTER SYSTEM SET maintenance_work_mem = '1GB'; -- for VACUUM etc. (reload)
-- Sanity check the work_mem risk:
-- max_connections 100 x work_mem 32MB x ~2 operations = up to ~6.4GB
-- plus shared_buffers 4GB = 10.4GB of a 16GB machine. Acceptable.
-- The same calculation with work_mem = 256MB would be catastrophic.---------- CONNECTIONS ----------
max_connections Maximum simultaneous connections. DEFAULT: 100.
Each connection is a separate OS PROCESS with its own
memory. Raising this to 1000 is almost always a mistake.
USE A CONNECTION POOLER INSTEAD (PgBouncer).
200 application threads sharing 20 real database
connections performs far better than 200 real ones.
Requires a RESTART.
superuser_reserved_connections Slots kept free so an administrator can still
connect when the application has used everything up.
DEFAULT: 3. Worth keeping.---------- WAL AND CHECKPOINTS ----------
wal_level How much detail goes into the write-ahead log.
minimal - crash recovery only, NO replication/PITR
replica - DEFAULT. Enough for replication and PITR.
logical - also supports logical replication.
Leave at "replica" unless you need logical replication.
Requires a RESTART.
max_wal_size How much WAL may accumulate before a checkpoint is
forced. DEFAULT is often too small for busy systems.
START AT: 2GB - 8GB on a busy server.
LARGER = fewer checkpoints = less repeated disk writing,
but a longer crash recovery time.
checkpoint_timeout Maximum time between checkpoints. DEFAULT: 5min.
START AT: 15min on a busy server.
checkpoint_completion_target Spreads checkpoint writes over this fraction of
the interval, avoiding an I/O spike.
DEFAULT is 0.9 on modern versions. Leave it.
-- Symptom of undersized WAL settings, visible in the log:
-- "checkpoints are occurring too frequently (12 seconds apart)"
-- "consider increasing the configuration parameter max_wal_size"---------- LOGGING: configure this BEFORE you need it ----------
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d.log'
log_rotation_age = '1d'
log_rotation_size = '100MB'
log_min_duration_statement = 1000 # log any statement slower than 1000ms
# THE SINGLE MOST USEFUL LOGGING SETTING
log_checkpoints = on # checkpoint frequency and duration
log_connections = on
log_disconnections = on
log_lock_waits = on # logs waits longer than deadlock_timeout
log_temp_files = 0 # log every temp file: work_mem is too low
log_autovacuum_min_duration = 0 # log all autovacuum activity
log_line_prefix = '%m [%p] %q%u@%d ' # time, pid, user@database
# log_statement = 'all' logs EVERY statement. Useful for debugging,
# very expensive on a busy server, and it writes query text — which may
# contain personal data — to disk. Use deliberately and briefly.---------- PLANNER SETTINGS FOR SSDs ----------
random_page_cost = 1.1 # DEFAULT is 4.0, which assumes spinning disks
# where random reads are much slower than sequential.
# On SSD/NVMe there is little difference, and 4.0
# makes the planner wrongly avoid index scans.
# This one line often fixes "why is it doing a Seq Scan".
effective_io_concurrency = 200 # SSDs handle many parallel requests. Default 1.-- ---------- Per-session and per-role overrides ----------
SET work_mem = '256MB'; -- this session only
SET LOCAL work_mem = '256MB'; -- this TRANSACTION only
ALTER ROLE analyst SET work_mem = '256MB'; -- every session this role opens
ALTER DATABASE shop SET work_mem = '64MB'; -- every session in this database
-- This is the right way to give heavy reporting users more memory
-- without raising work_mem for the whole server.
RESET work_mem;How it works #
shared_buffers is PostgreSQL's own cache of table and index pages. Reading from it avoids a disk read entirely. The 25% guideline exists because the operating system also caches the same files — allocating 80% of RAM to PostgreSQL does not double the caching, it mostly duplicates it while starving everything else.
effective_cache_size allocates nothing at all. It is purely a hint to the planner about how much memory is likely available for caching across PostgreSQL and the OS together. The planner uses it to judge whether an index scan will hit cache or disk. Set too low, it concludes index scans will be expensive and chooses sequential scans instead — a common and easily fixed cause of poor plans.
work_mem is the setting to treat with respect, because it is allocated per sort or hash operation, not per connection. One query with three sorts uses three times work_mem; a hundred such connections use three hundred times. This is how a server runs out of memory after what looked like a modest change. The safe pattern is a conservative global value with per-session or per-role increases for reporting work.
The clearest signal that work_mem is too low is temporary files: when a sort does not fit in memory, PostgreSQL spills to disk, which is far slower. log_temp_files = 0 makes every such spill visible in the log.
A checkpoint writes all modified pages from shared buffers out to the data files, so the WAL before that point is no longer needed for recovery. Frequent checkpoints mean the same hot page is written repeatedly. Raising max_wal_size and checkpoint_timeout reduces that duplication, at the cost of a longer crash recovery because more WAL must be replayed. PostgreSQL will tell you directly in the log when checkpoints are too frequent.
random_page_cost deserves particular attention. The default of 4.0 encodes the assumption that a random disk read costs four times a sequential one — true for spinning disks, not for SSDs. Leaving it at 4.0 on SSD storage makes the planner systematically avoid index scans. Lowering it to around 1.1 is one of the highest-value single-line changes on modern hardware.
The context column in pg_settings tells you what a change requires: postmaster means a restart, sighup means a reload is enough, user means it can be set per session. Checking this first saves an unnecessary restart.
ALTER SYSTEM writes to postgresql.auto.conf, which is read after postgresql.conf and therefore wins. It keeps your changes separate from the packaged file, which survives upgrades more cleanly than editing postgresql.conf directly.
Real-world use #
The four memory settings plus random_page_cost deliver most of the available gain on a default installation. Start there, and measure before going further.
Change one setting at a time and record what you changed and what happened. Tuning several things at once means that when performance improves or degrades, you cannot attribute it — and you are left with a configuration nobody understands.
Resist raising max_connections. Each connection is a separate operating-system process with its own memory overhead, and hundreds of mostly idle connections cost real resources while increasing contention. PgBouncer in transaction pooling mode lets many application threads share a small number of real connections and is close to standard practice for busy PostgreSQL deployments.
Configure logging before you need it, not during an incident. log_min_duration_statement is the single most valuable setting here: it records only queries slower than your threshold, so the log stays readable while capturing exactly what matters. Add log_checkpoints, log_lock_waits and log_temp_files and most performance questions become answerable from the log alone.
Be careful with log_statement = 'all'. It is genuinely useful for short debugging sessions, but it is expensive on a busy server and writes full query text — potentially including personal data — to disk. Turn it on deliberately and turn it off again.
Tools such as PGTune generate a reasonable starting configuration from your RAM, CPU count and workload type. Treat the output as a first draft to measure against rather than a final answer, because it knows your hardware but not your queries.
Finally, keep your configuration in version control alongside your application. Six months later, "why is work_mem 64MB here" should have a recorded answer.
Common mistakes #
- Raising work_mem globally without accounting for it being per operation across all connections.
- Setting shared_buffers to most of RAM, duplicating the OS cache and starving the rest of the system.
- Leaving random_page_cost at 4.0 on SSD storage, making the planner avoid index scans.
- Raising max_connections into the hundreds instead of using a connection pooler.
- Changing several settings at once, making it impossible to tell which one helped or hurt.
Practice #
On a test server, record the current values of shared_buffers, work_mem, effective_cache_size and random_page_cost. Use pg_settings to check which of them need a restart rather than a reload. Set the four memory-related values for your machine's RAM using ALTER SYSTEM, reload or restart as required, and confirm the new values took effect. Then enable log_min_duration_statement at 500ms and log_temp_files = 0, run a large sort, and find the resulting entries in the log.