Skip to content
Tutorials Center
My learning

Topics

Lesson 65 of 90 ยท Performance Tuning

Configuration Tuning

Tune the PostgreSQL settings that matter: shared_buffers, work_mem, maintenance_work_mem, effective_cache_size, WAL and checkpoint settings, and connection limits.

A fresh PostgreSQL installation is configured to start on almost anything, including a laptop with a gigabyte of free memory. That is great for compatibility and poor for performance: the defaults for shared_buffers, work_mem and the WAL settings are deliberately small. On a real server a handful of settings, changed with a little arithmetic, usually give a bigger improvement than hours of query tuning.

This lesson covers the settings that matter, what each one actually does, and a safe way to change them. Remember the order of work: first fix the queries and indexes that pg_stat_statements points at, then adjust the configuration. The configuration files themselves are read-only here, while the live examples do the sizing arithmetic in SQL so you can try your own numbers.

Changing settings safely

Settings live in postgresql.conf (and in postgresql.auto.conf, which ALTER SYSTEM writes). Each setting has a context that says when a change takes effect:

SQL
SELECT name, setting, unit, context, source
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'max_connections', 'max_wal_size');
  • postmaster: needs a server restart (shared_buffers, max_connections, wal_buffers).
  • sighup: reload is enough (max_wal_size, checkpoint_timeout, most autovacuum settings).
  • user or superuser: can be changed per session with SET, per role or database with ALTER ROLE ... SET, or per transaction with SET LOCAL (work_mem, random_page_cost).
SQL
ALTER SYSTEM SET max_wal_size = '4GB';
SELECT pg_reload_conf();
SHOW max_wal_size;

Change one thing at a time and measure; several simultaneous changes make it impossible to know which one helped. Keep the configuration in version control, and use a tool such as PGTune or your cloud provider's recommended parameter group as a starting point, not as the final answer.

shared_buffers and the operating system cache

shared_buffers is PostgreSQL's own page cache, shared by all backends. The default is 128MB, far too small for a dedicated server. The common starting point is about 25 percent of RAM, sometimes up to 40 percent on large machines. More is not always better, because PostgreSQL also relies on the operating system's file cache, and data can end up cached twice; very large values also make checkpoints heavier.

Changing it needs a restart. If the machine runs other memory-hungry services, subtract their share first, and consider huge_pages = try on Linux to lower page-table overhead for large settings.

effective_cache_size is not an allocation at all. It is a hint telling the planner how much data is probably cached in total (PostgreSQL buffers plus the OS cache), which influences whether it prefers index scans. A good starting point is 50 to 75 percent of RAM. Setting it too low makes the planner pessimistic about index scans.

Where the memory goes

shared_buffers One shared cache, about 25% of RAM.
OS cache Extra caching PostgreSQL relies on.
work_mem Per sort or hash, per query.
maintenance_work_mem Vacuum and index builds.

Only shared_buffers is reserved up front.

1 / 2

work_mem: the dangerous one

work_mem (default 4MB) is the memory a single sort or hash operation may use before it spills to temporary files. The trap is in the multiplication. One complex query may have several sort and hash nodes, parallel workers each get their own allowance, and every connection can run such a query at the same time:

TEXT
worst case โ‰ˆ work_mem ร— (sort/hash nodes per query) ร— (concurrent queries)

With work_mem = 64MB, 200 connections and 3 memory-hungry nodes per query, the worst case is about 37.5 GB, more than many servers have, and the Linux OOM killer may then stop PostgreSQL. In practice not every query hits the limit at the same moment, but outages do happen exactly during peak load.

SQL A starting budget from RAM and connection count

Press Run (or Ctrl + Enter) to see the result here.

The three servers get 2,048, 8,192 and 16,384 MB of shared_buffers, and 10 MB, 20 MB and 81 MB of work_mem respectively. Notice that the busiest server (200 connections) gets a smaller allowance per node than a quieter one with the same memory, which is the right behavior: more concurrency means each operation gets less. The numbers are conservative on purpose; raising work_mem for specific heavy queries with SET LOCAL is safer than raising it everywhere.

Spot spills with log_temp_files = 0 in the log, or temp_blks_written in pg_stat_statements, and raise work_mem only if spills are frequent on important queries. hash_mem_multiplier (default 2.0 since PostgreSQL 15) lets hash nodes use that multiple of work_mem.

SQL Worst-case memory at different work_mem values

Press Run (or Ctrl + Enter) to see the result here.

At 64 MB the worst case is 37.5 GB, more than the machine has; at 256 MB it is 150 GB. The default 4 MB and a modest 16 MB fit comfortably. The verdict column compares the worst case with 75 percent of RAM, which leaves room for the shared buffers and the operating system.

maintenance_work_mem and autovacuum

maintenance_work_mem (default 64MB) applies to maintenance operations: VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY. Only a few of these run at once, so you can be generous: 512MB to 2GB is typical on a server with plenty of RAM, and building a large index is noticeably faster. Each autovacuum worker (default 3, autovacuum_max_workers) may also use it, so autovacuum_work_mem lets you give workers a separate, smaller limit. See VACUUM, Autovacuum and Bloat for autovacuum behavior itself.

WAL and checkpoints

Every change is first written to the write-ahead log (WAL). Periodically a checkpoint flushes all dirty pages to the data files, so that crash recovery has less WAL to replay. Checkpoints are heavy on the disks, so the goal is for them to be infrequent and spread out:

Setting Default Guidance
max_wal_size 1GB Raise to 4GB-16GB on write-heavy systems so checkpoints are triggered by time rather than by WAL volume
min_wal_size 80MB Keeps recycled WAL files around for bursts
checkpoint_timeout 5min 15min is common; longer means fewer checkpoints and longer crash recovery
checkpoint_completion_target 0.9 Spreads the writes across the interval (this is the default since PostgreSQL 14)
wal_compression off lz4 or zstd (version dependent) can cut WAL volume at some CPU cost
synchronous_commit on off makes commits faster but can lose the last moments of commits after a crash (never corrupts data)

How do you know whether checkpoints are too frequent? Compare timed checkpoints with requested ones. A requested checkpoint means max_wal_size filled up before the timeout:

SQL
-- PostgreSQL 17 and later
SELECT num_timed, num_requested, write_time, sync_time, buffers_written
FROM pg_stat_checkpointer;

Before version 17 the same counters (checkpoints_timed, checkpoints_req) are in pg_stat_bgwriter. If num_requested is a large share of the total, raise max_wal_size. Also keep log_checkpoints = on (the default since PostgreSQL 15), and read the log lines, which say how many buffers were written and how long it took.

A larger max_wal_size costs disk space for WAL and longer crash recovery, but not correctness. Replication slots and wal_keep_size can keep extra WAL on disk too; see Streaming and Logical Replication.

Connection limits

max_connections defaults to 100. Each connection is a separate server process using memory (a few megabytes at least, more with large catalogs or heavy queries), and every active backend competes for CPU. Raising the limit to 1,000 or more is rarely the right answer: throughput typically drops once active queries far exceed CPU cores, because of context switching and lock contention.

Better approaches, covered in Connection Pooling:

  • Put a pooler such as PgBouncer in front, keeping a few dozen real connections.
  • Size application pools deliberately instead of giving every service 100 connections.
  • Use superuser_reserved_connections (default 3) so admins can always log in, and set timeouts such as idle_in_transaction_session_timeout to stop forgotten transactions from holding resources.

Changing max_connections needs a restart, and a standby server must have a value at least as large as the primary.

A few other settings worth knowing

  • random_page_cost (default 4.0) should usually be 1.1 to 1.5 on SSD, as described in Statistics and the Query Planner.
  • effective_io_concurrency and maintenance_io_concurrency control prefetching; modern SSDs benefit from higher values (the default is 16 from PostgreSQL 18 on).
  • PostgreSQL 18 adds io_method (worker by default, io_uring on supported Linux systems) for asynchronous I/O; the default is a sensible starting point.
  • max_parallel_workers_per_gather (default 2) controls parallel query; analytic workloads may benefit from more, OLTP often from less.
  • shared_preload_libraries loads extensions such as pg_stat_statements at start.

Never copy a configuration from the internet or from a different machine without checking the arithmetic against your RAM, connection count and workload. The most common outage caused by tuning is an over-generous work_mem combined with many connections.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is a common starting value for shared_buffers on a dedicated database server?

    PostgreSQL also benefits from the operating system cache, so about a quarter of RAM is the usual starting point, adjusted by measurement.
  2. 2. Why is a large global work_mem risky?

    The limit applies to each operation separately. Many nodes in many concurrent queries can together exceed physical memory.
  3. 3. pg_stat_checkpointer shows most checkpoints are requested, not timed. What does that suggest?

    A requested checkpoint happens when WAL fills max_wal_size before the timeout. Raising max_wal_size lets the timer drive checkpoints instead.
  4. 4. Which setting is only a planner hint and allocates no memory?

    effective_cache_size tells the planner how much data is likely cached overall. The others reserve or limit real memory.

Summary

  • Check each setting's context: some need a restart, others only a reload or can be set per session or role.
  • Start shared_buffers near 25 percent of RAM and effective_cache_size near 50-75 percent; the latter is only a hint.
  • work_mem applies per sort or hash node per query, so budget it against connections; raise it with SET LOCAL for heavy queries.
  • Give maintenance_work_mem hundreds of megabytes for faster vacuum and index builds.
  • Raise max_wal_size and use a longer checkpoint_timeout so checkpoints are timed and spread out; watch requested checkpoints.
  • Keep max_connections modest and pool connections instead of raising it.
  • Change one setting at a time, measure, and keep the configuration under version control.

Next: Connection Pooling.