Right, let’s talk about postgresql.conf. This is the file. The big one. The conductor of the PostgreSQL orchestra, and if you mess it up, the whole symphony can sound like a bunch of cats fighting in a bag. It’s a simple text file, but its contents are anything but. You’ll find it in your data directory (/var/lib/postgresql/data/ on a typical Linux install, or wherever you told initdb to put it).

The first thing you’ll notice is that it’s… long. And full of comments. Don’t panic. You don’t need to touch 90% of these settings on day one. The designers, in a rare moment of clarity, have sensibly commented everything. The problem is, they’ve also left most settings at their conservative, “will-run-on-a-Raspberry-Pi-from-1999” defaults. Our job is to make it sing for your actual hardware.

The Three Musketeers: Memory, Checkpoints, and The Planner

These three settings will give you 80% of your performance gains. Get them right first.

shared_buffers is how much memory PostgreSQL allocates to cache data. The old, tragically wrong advice was to set this to 25% of your RAM. Please don’t. On a dedicated database server, start with 15-25% of your total RAM. Why not more? Because your operating system also does a brilliant job of caching files in its own memory (the “OS cache”). PostgreSQL and the OS work in tandem, not in competition. Setting it too high starves the OS and can actually hurt performance.

# Good starting point on a server with 16GB RAM dedicated to PostgreSQL
shared_buffers = 4GB

Next, work_mem. This is the memory available for each operation like sorting or hashing per operation. The key here is that a complex query can have many operations, so this can add up fast. The default is pathetically small (4MB). Bumping this up is the single easiest way to prevent unnecessary disk spills (where PostgreSQL gives up on using RAM and writes temp files to disk, which is brutally slow).

# A more reasonable starting point. Monitor temp file usage after.
work_mem = 32MB

Finally, maintenance_work_mem. This is memory for, you guessed it, maintenance operations like VACUUM, CREATE INDEX, and CLUSTER. The default is 64MB, which is a joke for any modern database. Giving this a healthy value makes your VACUUM operations fly.

# 1GB is a good start for larger databases.
maintenance_work_mem = 1GB

Checkpoints: Taming the I/O Beast

Checkpoints are where PostgreSQL guarantees that all modified data files are written to disk. It’s a crucial durability feature, but it can be a major source of I/O spikes if configured poorly. The two key settings are checkpoint_timeout and max_wal_size.

A longer timeout and a larger WAL size mean fewer checkpoints, which means less I/O disruption. But it also means a longer recovery time if your server crashes, as it has to replay more WAL files. The default checkpoint_timeout of 5 minutes is too frequent for most systems.

# Smoother I/O, less frantic writing every 5 minutes.
checkpoint_timeout = 15min
# Let it use up to 4GB of WAL files before forcing a checkpoint.
max_wal_size = 4GB

The Planner’s Crystal Ball: Statistics

The query planner is a brilliant piece of software, but it’s not clairvoyant. It relies on statistics about your data to make good choices. The default default_statistics_target (100) is often too low for modern applications with complex data distributions. If you see the planner choosing bizarrely bad join paths, increasing this (and running ANALYZE) is your first port of call.

# Double the default. If you have very complex JSONB queries, go higher.
default_statistics_target = 200

How to Actually Change Stuff (Without Restarting Everything)

Here’s a pro move. You don’t always need a full restart. Many parameters can be reloaded on the fly. The SHOW and SET commands are your friends.

First, check the current setting and see if it requires a restart:

SELECT name, setting, context FROM pg_settings WHERE name = 'shared_buffers';

The context column is the key. If it says postmaster, you need a full restart. If it says sighup or user, you can reload it.

To change a setting without a restart, you can:

  1. Edit postgresql.conf.
  2. Then, from your OS command line, run: pg_ctl reload -D /your/data/directory/
  3. Or, connect with psql and run: SELECT pg_reload_conf();

For user context variables, you can even set them live for your session (or globally) right inside psql:

-- Just for this session
SET work_mem = '64MB';

-- Or for everyone, forever (but still check it into your config file!)
ALTER SYSTEM SET work_mem = '64MB';
SELECT pg_reload_conf();

The ALTER SYSTEM command is fantastic—it writes to a file called postgresql.auto.conf which is read after your main file, so your carefully managed postgresql.conf remains the source of truth. It’s perfect for quick, temporary tweaks. Just remember to permanently codify any successful experiments back into your main configuration file.