PostgreSQL performance tuning on Ubuntu: the settings pgtune computes

Where the settings for PostgreSQL performance tuning live

The main file is /etc/postgresql/16/main/postgresql.conf. On Ubuntu it ends with include_dir = 'conf.d', so a file of your own in /etc/postgresql/16/main/conf.d/ is read last and wins. ALTER SYSTEM writes to /var/lib/postgresql/16/main/postgresql.auto.conf, which wins over both. Keep each setting in one place so you don’t wonder later which value applies.

Some settings need a restart, others only a reload. PostgreSQL itself says which, in the context column: postmaster means restart, sighup means reload is enough, user can be changed even inside a session. After a reload, pending_restart shows what still waits for a restart:

If the server doesn’t come back after a restart, read the end of its log. The error names the setting and the file:

Memory: shared_buffers and effective_cache_size

shared_buffers is PostgreSQL’s own cache of tables and indexes, 128 MB by default. The PostgreSQL manual gives 25% of RAM as a reasonable start on a dedicated server with 1 GB or more, and says more than 40% is unlikely to work better: PostgreSQL also relies on the system’s file cache. It needs a restart. effective_cache_size allocates nothing: it tells the planner how much data is likely cached by PostgreSQL and the system together, and a higher value makes index scans look cheaper. 50–75% of RAM is the usual start. For a server with 8 GB only for PostgreSQL:

If the site runs on the same server, count from the memory you give to the database, not all RAM. If PostgreSQL won’t start with could not map anonymous shared memory: Cannot allocate memory, the server lacks memory for shared_buffers: lower it.

work_mem and why it multiplies, maintenance_work_mem

work_mem (4 MB by default) is memory for one sort or one hash operation. One query can run several of them at once, hash operations may take twice as much (hash_mem_multiplier is 2.0), parallel workers get their own, and every connection can do this at the same time. So total use can be many times work_mem × max_connections. A rough start: (RAM − shared_buffers) / (max_connections × 3). For 8 GB, 2 GB of shared buffers and 100 connections that is about 20 MB, so 16 MB is a careful start.

maintenance_work_mem (64 MB by default) is used by VACUUM, CREATE INDEX and ALTER TABLE ADD FOREIGN KEY. Only a few of them run at once, so it can be much larger: 5–10% of RAM, up to 1–2 GB. Autovacuum may use it in each of its workers (3 by default) unless autovacuum_work_mem is set.

log_temp_files = 0 writes a line to the log each time a query spills to a temporary file on disk, with its size. Many such lines for one query mean it needs more work_mem. Give it to that query only, not to the whole server: SET work_mem = '256MB'; in its session before a big report.

max_connections and PgBouncer

Each connection in PostgreSQL is a separate process with its own memory. max_connections is 100 by default and changes only with a restart. When all are taken, new clients get FATAL: sorry, too many clients already. Raising the limit to thousands costs memory and CPU, and a busy server often runs faster with fewer active connections. A connection pooler is the usual answer: many app connections share a few to the database. PgBouncer is in Ubuntu’s repository:

In /etc/pgbouncer/userlist.txt, each line is the user name and its password secret in double quotes. This command shows the secret:

Then restart PgBouncer:

Point the app to port 6432 instead of 5432. In transaction mode a connection goes back to the pool after each transaction, so session features such as SET for the session, advisory locks held across transactions or LISTEN don’t work. Check that your app and its driver don’t need them, otherwise use pool_mode = session.

SSD disks: random_page_cost and effective_io_concurrency

random_page_cost (4.0 by default) is how much more the planner thinks a random read costs than a sequential one. The default fits spinning disks where most data is cached. On SSD and NVMe random reads cost almost as little as sequential ones, and a lower value such as 1.1 is common: the planner then uses indexes more often. effective_io_concurrency (1 by default) says how many reads the disk can do in parallel, and the manual says the best value for SSDs might be in the hundreds. Both take effect on reload.

Before and after, run a few typical slow queries with EXPLAIN (ANALYZE, BUFFERS) and compare the plans and times. If plans got worse, go back to the old value.

Checkpoints and WAL

A checkpoint writes all changed pages to the table files. It happens every checkpoint_timeout (5 minutes by default) or when WAL grows to max_wal_size (1 GB by default), whichever comes first. With many writes, checkpoints come too often, and PostgreSQL warns in the log: checkpoints are occurring too frequently (12 seconds apart) with the hint Consider increasing the configuration parameter "max_wal_size". A larger max_wal_size and a longer timeout spread the writes out, but WAL takes more disk space and recovery after a crash takes longer. checkpoint_completion_target is already 0.9, leave it.

log_checkpoints is on by default since PostgreSQL 15, so each checkpoint leaves a line with its time and buffers written. Most should say checkpoint starting: time, not wal.

Which queries take the time: pg_stat_statements

Settings give a few tens of percent. A missing index can make a query a hundred times slower. pg_stat_statements collects statistics for every query and comes with Ubuntu’s PostgreSQL package. It needs a line in the config and a restart, then the extension in your database:

The top lines are the queries that cost the server most time in total. Run each with EXPLAIN (ANALYZE, BUFFERS) and look for Seq Scan on big tables. If shared_preload_libraries already lists other modules, add this one with a comma instead of replacing them.