PostgreSQL Memory Settings on a Small VPS

A winding turquoise reservoir among pine-covered hills and pale eroded slopes under a hazy blue sky

PostgreSQL’s memory defaults are deliberately modest: 128 MB of shared_buffers and 4 MB of work_mem, sized to start on nearly anything. On a 4 GB VPS that also runs your app, the question isn’t how to give PostgreSQL everything. It’s how much it can have before the kernel’s OOM killer settles the argument for you.

This note covers five settings. For each one: how to read it, a starting point you can explain to someone else, whether a change needs a reload or a restart, and how to tell if it did anything. Examples come from PostgreSQL 18.

Look before you touch

SELECT name, current_setting(name) AS value, context
FROM pg_settings
WHERE name IN ('shared_buffers', 'effective_cache_size', 'work_mem',
               'maintenance_work_mem', 'max_connections')
ORDER BY name;

         name         | value |  context
----------------------+-------+------------
 effective_cache_size | 4GB   | user
 maintenance_work_mem | 64MB  | user
 max_connections      | 100   | postmaster
 shared_buffers       | 128MB | postmaster
 work_mem             | 4MB   | user

current_setting(name) is there because the raw setting column uses each parameter’s base unit. Ask for shared_buffers that way and you get 16384, with a unit of 8kB. That works out to 128 MB if you’re in the mood for arithmetic.

The context column answers the restart question before you ask it:

postmaster: takes effect only after a restart.
sighup: a reload is enough.
user or superuser: a reload is enough, and the value can also be changed for a single session with SET.

To find the file you’d be editing, run SHOW config_file;. On Debian’s packages it’s /etc/postgresql/<version>/main/postgresql.conf, and that file already contains include_dir = 'conf.d'.

What each setting is actually for

shared_buffers is PostgreSQL’s own cache of table and index pages. The docs suggest 25% of RAM as a reasonable starting value for a dedicated database server with at least 1 GB. They also say going past 40% is unlikely to help, because PostgreSQL leans on the operating system’s page cache too. A small VPS running an app isn’t dedicated, so start a bit lower. Restart required.

effective_cache_size allocates nothing. It’s the planner’s guess at how much cache one query can count on, shared_buffers and OS cache together. Set it higher and index scans look cheaper. The default of 4 GB is optimistic on a 2 GB server and timid on a 32 GB one. Reload is enough.

work_mem is the limit for each sort or hash step inside a query, before that step spills to temporary files. That one word, each, is what makes it dangerous. A query can have several such steps. Every connection can run such a query. Each parallel worker gets its own allowance. And hash steps may use work_mem × hash_mem_multiplier, which has defaulted to 2.0 since PostgreSQL 15. Reload is enough.

maintenance_work_mem is for VACUUM, CREATE INDEX, and ALTER TABLE ... ADD FOREIGN KEY. Since a session runs only one of those at a time, it can safely be much larger than work_mem, with one catch: autovacuum may use up to autovacuum_max_workers (3 by default) times this amount. Reload is enough.

max_connections defaults to 100, and PostgreSQL starts a separate server process for each connection. Three slots are held back for superusers by default (superuser_reserved_connections). Restart required.

A starting point for a 4 GB VPS

Rules of thumb for a box shared with an application, not measurements:

# /etc/postgresql/18/main/conf.d/10-memory.conf
shared_buffers = 768MB          # restart; a bit under 25% because the box isn't dedicated
effective_cache_size = 2GB      # reload; shared_buffers plus page cache the OS can likely spare
work_mem = 8MB                  # reload; per sort/hash step, per connection
maintenance_work_mem = 256MB    # reload; helps VACUUM and CREATE INDEX, autovacuum multiplies it
max_connections = 40            # restart; app pool sizes added up, plus room for psql and cron

Now do the worst-case math the docs warn about:

shared_buffers                               768 MB
work_mem      40 connections × 2 steps × 8MB 640 MB   (hash steps can take double)
autovacuum    3 workers × 256MB              768 MB
                                            -------
                                            ~2.1 GB   before your app gets a byte

That doesn’t all happen at once in normal life, but the OOM killer doesn’t grade on a curve. If the total looks uncomfortable, lower max_connections first. It shrinks the work_mem line and costs nothing if the app never needed 100 connections. If the autovacuum line bothers you, autovacuum_work_mem caps it separately (reload; -1 means “use maintenance_work_mem”).

Scale for other sizes by the same reasoning, not by multiplying these numbers. If the app genuinely needs hundreds of connections, put a pooler such as PgBouncer in front rather than raising max_connections. The PostgreSQL docs suggest the same thing when memory gets tight.

For the one report that needs a big sort, raise work_mem for that session instead of for everyone:

SET work_mem = '128MB';   -- this session only
-- or, for every new session of a dedicated reporting role:
ALTER ROLE reporting SET work_mem = '64MB';

Apply it: ALTER SYSTEM or a conf.d file

There are two sane ways to apply changes. Pick one per setting, or future-you will spend a while finding the second one.

ALTER SYSTEM SET shared_buffers = '768MB'; writes to postgresql.auto.conf in the data directory. It works the same whether PostgreSQL came from a package or a Docker image.
A file in conf.d/, like the one above. Files load in name order and the last value wins.

The catch: postgresql.auto.conf is read after everything else and overrides it. If an old ALTER SYSTEM set work_mem, your new conf.d file will appear to do nothing. Ask the server where a value came from:

SELECT name, current_setting(name), sourcefile, sourceline
FROM pg_settings
WHERE name = 'work_mem';

Before reloading, check that the files parse:

SELECT sourcefile, sourceline, name, setting, error
FROM pg_file_settings
WHERE error IS NOT NULL;

                  sourcefile                   | sourceline |     name      | setting |                error
-----------------------------------------------+------------+---------------+---------+--------------------------------------
 /etc/postgresql/18/main/conf.d/10-memory.conf |          2 | shared_bufers | 768MB   | unrecognized configuration parameter

That query earns its keep. On reload, a file with errors is ignored and the log says contains errors; no changes were applied. On restart, the same typo is FATAL and PostgreSQL doesn’t come back up. You want to find it now, not then.

Then reload, and see what’s still waiting:

SELECT pg_reload_conf();

SELECT name, current_setting(name) AS value
FROM pg_settings
WHERE pending_restart;

      name      | value
----------------+-------
 shared_buffers | 128MB

The file says 768 MB and the server is still running 128 MB. That’s expected for a postmaster setting, and the log says it plainly: parameter "shared_buffers" cannot be changed without restarting the server. Restart during a quiet moment, then run the first query again. On Debian’s packages that’s sudo systemctl restart postgresql@18-main for one cluster, or postgresql for all of them.

Rolling back is the same process in reverse: ALTER SYSTEM RESET work_mem; or delete the conf.d file, then reload or restart according to context.

Check whether it helped

work_mem: look at the sort or hash nodes in the slow query’s plan:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders ORDER BY note;

Sort Method: external merge  Disk: 32304kB     -- work_mem = 4MB
Sort Method: quicksort  Memory: 47445kB        -- work_mem = 128MB

external merge means the sort spilled to disk. In this example the same sort wrote about 32 MB to disk but needed about 47 MB to stay in memory, so treat the Disk: figure as a hint, not a target. EXPLAIN ANALYZE runs the query for real, so the precautions in Use EXPLAIN ANALYZE Without Surprises apply.

For the whole database, temp-file counters accumulate until stats are reset:

SELECT temp_files, pg_size_pretty(temp_bytes) AS temp_bytes, stats_reset
FROM pg_stat_database
WHERE datname = current_database();

Setting log_temp_files = '10MB' logs every spill at least that large, followed by a STATEMENT: line with the query. That’s handier than a counter. A few spills aren’t a problem. A steady stream from the same query is.

shared_buffers: compare blks_hit and blks_read in pg_stat_database over a normal day, not right after the restart while the cache is still cold. A blks_read isn’t necessarily a disk read, though. It only means the page wasn’t in PostgreSQL’s buffers, and the OS cache may have served it anyway.

max_connections: compare SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'client backend'; against the limit at your busiest time. If the app logs FATAL: remaining connection slots are reserved for roles with the SUPERUSER attribute, ordinary roles have used every slot. Fix the app’s pool sizes before raising the limit.

The whole box: check free -h and look for kernel OOM kills with journalctl -k | grep -i 'out of memory'. If the kernel has killed a postgres process, something on the box wants more memory than exists, and the worst-case math above is the first place to look.

References

Cover photo: Unsplash.