Postgres Tuning for Homelabbers: Ten Settings That Matter
The defaults were written for a machine you don't own
Contents
The first time I put a self-hosted photo library on Postgres and watched a search take four seconds, I assumed I had written a bad query. I had not. The query was fine. The database was running with the settings it shipped with, and those settings assume it is sharing a cramped virtual machine with a dozen other tenants who would all riot if Postgres grabbed a gigabyte of RAM for itself. On my own hardware, with 32 GB of memory and an NVMe drive doing nothing else, that caution is wasted. Postgres was politely refusing to use the machine I had given it.
That is the thing to understand before you touch a single parameter. The Postgres defaults are conservative on purpose, because the project cannot know whether it is starting on a Raspberry Pi or a 64-core server, so it picks numbers that will at least boot anywhere. Your job as the person who owns the box is to tell it the truth about the hardware. Ten settings do the overwhelming majority of that work. The rest is diminishing returns and folklore.
Where these settings live
Everything below goes in postgresql.conf (find it with SHOW config_file; from a psql session) or, if you run Postgres in Docker, as -c flags or a mounted config. A handful reload with SELECT pg_reload_conf();; the memory-allocation ones need a full restart, which I will flag. Change a few things at a time and measure, so that when something regresses you know which knob did it.
Assume for the worked numbers a machine with 16 GB RAM, an SSD or NVMe, and Postgres as the main workload. Scale the memory figures proportionally for your own box.
1. shared_buffers — how much RAM Postgres caches in
This is the big one. shared_buffers is the pool of memory Postgres uses for its own page cache: the hot rows and index pages it keeps in RAM so it never touches the disk for them. The default is a timid 128 MB. On a dedicated box, set it to roughly 25% of system RAM and stop there. More than about 40% tends to backfire, because Postgres relies on the operating system’s own file cache for a second layer, and starving the OS to feed shared_buffers gives you two half-full caches instead of one useful one.
| |
Requires a restart. This single change turned my four-second search into something under a second, because the index finally stayed resident in memory between queries instead of being evicted every time.
2. effective_cache_size — a hint, not an allocation
effective_cache_size allocates nothing. It is a hint to the query planner about how much memory is likely available for caching across the whole system, counting both shared_buffers and the OS file cache. When the planner believes lots of cache is available, it favours index scans over sequential scans, because it assumes the index pages are probably already in RAM. Set it high — around 50–75% of total RAM — and let the planner make smart choices.
| |
Reloads without a restart. Getting this wrong in the low direction is a common cause of Postgres inexplicably choosing a full table scan when a perfectly good index exists.
3. work_mem — the per-operation sort and hash budget
work_mem is the amount of memory a single sort, hash or similar operation may use before it spills to a temporary file on disk. The default 4 MB is tiny, and disk spills are slow. The catch is that this is per operation, per connection, so a complex query with three sorts running across ten connections can multiply your setting by thirty. That is how people accidentally invent an out-of-memory kill.
The safe approach is a modest global value with room to raise it for specific heavy sessions:
| |
To find out whether you need more, catch the spills in the act:
| |
If the plan mentions Sort Method: external merge Disk: 24000kB, that operation wanted more than it was given and went to disk. Raise work_mem for that workload — you can even set it for one session with SET work_mem = '128MB'; before a known-heavy report — and the spill disappears.
4. maintenance_work_mem — for the housekeeping jobs
Index builds, VACUUM, and ALTER TABLE use a separate budget called maintenance_work_mem. These run rarely and one or two at a time, so you can be generous where you were careful with work_mem. A larger value makes CREATE INDEX on a big table dramatically faster and lets autovacuum clean more in a single pass.
| |
On a restore of a large database — where every index is rebuilt from scratch — bumping this to 1 GB or more temporarily can turn a coffee break into a quick pause.
5. max_connections — smaller than you think
Every connection costs memory and scheduling overhead, and Postgres handles concurrency through processes rather than lightweight threads. The instinct is to crank max_connections up to 500 so nothing ever gets refused. That instinct is wrong, and it is the single most common way homelabbers hurt their own performance. A box with a handful of CPU cores does genuine parallel work for only a few queries at once; the rest queue and thrash.
Keep the number sane:
| |
If your applications open connections aggressively — a fleaky ORM, several containers, a serverless-style workload — the fix is a connection pooler in front of the database, which I cover in PgBouncer: connection pooling before you think you need it. Pooling lets fifty clients share five real Postgres backends, which the hardware can genuinely serve.
6. WAL and checkpoints — stop the write stalls
The Write-Ahead Log records every change before it lands in the data files, and periodically Postgres runs a checkpoint that flushes accumulated changes to disk. The default max_wal_size is small, which triggers frequent checkpoints, and a checkpoint is an I/O storm. On a write-heavy workload you feel these as periodic stalls. Widening the WAL lets checkpoints happen less often and spread their work out.
| |
checkpoint_completion_target = 0.9 tells Postgres to smear a checkpoint’s writes across 90% of the interval before the next one, flattening the spike into a gentle ramp. This is one of the highest-value changes for anything that writes continuously — a metrics database, a busy application backend. It also matters for point-in-time recovery, which leans on the WAL and which I dig into in Database Backups Done Right: Dumps, WAL, and PITR.
7. random_page_cost — you have an SSD, say so
This setting encodes how expensive the planner believes a random disk read is, relative to a sequential one. The default of 4.0 dates from spinning rust, where seeking the head across a platter was genuinely four times costlier than reading in order. On an SSD or NVMe, random reads are nearly as cheap as sequential ones, and leaving the default biases the planner away from index scans it should be using.
| |
Dropping random_page_cost to 1.1 is often the change that finally convinces Postgres to use an index it had been stubbornly ignoring. effective_io_concurrency tells it how many concurrent I/O requests the storage can absorb; SSDs handle hundreds, so 200 is a reasonable figure that helps bitmap heap scans prefetch.
8. synchronous_commit — the honest trade-off
By default Postgres waits for each committed transaction to be physically flushed to disk before telling the client it succeeded. That guarantees durability: if the power dies one millisecond after your commit returns, the data is safe. Turning synchronous_commit = off lets Postgres acknowledge the commit before the flush, which can multiply write throughput several times over.
The cost is precise and worth stating plainly. You never corrupt the database — Postgres stays consistent — but a crash can lose the last fraction of a second of committed transactions that had not yet reached disk. For financial records, keep it on. For a home metrics store or a media catalogue where losing the last 200 milliseconds of writes after a power cut is survivable, turning it off is a legitimate and large win.
| |
This one reloads live, so you can toggle it per session and test.
9. autovacuum — feed it, don’t fight it
Postgres never overwrites a row in place; an UPDATE writes a new version and marks the old one dead. Autovacuum is the background process that reclaims those dead rows and keeps table statistics fresh for the planner. On a busy table the default autovacuum is too lazy, so dead rows pile up — this is table bloat, and it makes everything slower while it grows. The fix is to let autovacuum run more eagerly and give it more workers.
| |
The scale-factor change is the important one. The default of 0.2 means a table is only vacuumed after 20% of its rows are dead; on a million-row table that is 200,000 dead rows before anything happens. Dropping it to 0.05 keeps the table clean. For an extremely hot table you can override these per-table with ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = 0.01); rather than making every table aggressive.
10. log_min_duration_statement — see the slow queries
The final setting tunes nothing directly, and it is the one I install first on every new database, because you cannot fix what you cannot see. log_min_duration_statement logs any query that runs longer than a threshold, giving you a hit-list of what to optimise.
| |
Every statement slower than 250 ms now lands in the log with its duration, database and user. Tail that log for a day of normal use and the two or three genuinely slow queries reveal themselves, usually pointing at a missing index. For long-term analysis the pg_stat_statements extension aggregates this beautifully, but the log line alone gets you 80% of the value with zero setup.
Troubleshooting
Postgres refuses to start after editing the config. Almost always a memory setting that overcommits — shared_buffers larger than RAM, or a work_mem × max_connections product the kernel will not honour. Check the startup log; it names the offending parameter. If you are locked out entirely, most settings can be overridden on the command line to get back in: pg_ctl start -o "-c shared_buffers=256MB".
The changes did nothing. You probably edited a config file Postgres is not reading, or changed a restart-only setting and only reloaded. Confirm what is actually live with SELECT name, setting, source FROM pg_settings WHERE name = 'shared_buffers'; — the source column tells you whether the value came from your file or a default.
Out-of-memory kills after tuning. The classic cause is work_mem set high while many connections run complex queries. Total worst-case memory is roughly shared_buffers + (work_mem × active complex operations). Lower work_mem globally and raise it only per session, or put a pooler in front to cap real backends.
A query still ignores an index. Run EXPLAIN (ANALYZE, BUFFERS) and read the plan. If it sequential-scans a large table, check random_page_cost (should be ~1.1 on SSD) and effective_cache_size (should be high). Also run ANALYZE yourtable; — stale statistics make the planner guess badly.
Is it worth it?
For a database that mostly idles, honestly, the defaults are fine and you can skip all of this. The moment Postgres becomes a workload you actually wait on — a self-hosted app backend, a metrics store, a search index over your own data such as the retrieval layer in a local RAG stack — these ten settings are the difference between hardware that feels sluggish and hardware that feels instant. None of them is exotic, none takes more than a minute to apply, and the first three alone (shared_buffers, effective_cache_size, work_mem) will win back most of what the conservative defaults were leaving on the table. Apply them, watch your slow-query log for a week, and let the evidence guide the rest.
Related Content
