PostgreSQL on a Small VPS: Sizing shared_buffers, work_mem and Connections from Real RAM

PostgreSQL ships with defaults tuned for a machine with far more RAM than a typical VPS. On a 1–4 GB instance those defaults are not merely suboptimal — they misallocate memory in both directions at once, over-reserving shared memory while starving per-query work areas. This guide replaces guesswork with arithmetic: measure available RAM, subtract known consumers, and assign the remainder by function.

Start by Measuring, Not Guessing

Before touching postgresql.conf, record the real numbers. free -m reports total and available memory; the “available” column is what matters because it accounts for reclaimable page cache. Then determine how much memory other services already hold:

free -m
ps -eo rss,comm --sort=-rss | head
sudo -u postgres psql -c "SHOW shared_buffers; SHOW work_mem; SHOW max_connections;"

A 2 GB VPS running nginx, PHP-FPM and a systemd journal might leave 1.6 GB genuinely free. That figure — not the 2048 MB sticker on the plan — is your budget.

shared_buffers: Cap at 25% of Usable RAM

shared_buffers is PostgreSQL’s own page cache. Allocate roughly 25% of the RAM available to the database, not to the whole machine. Going past ~40% starts crowding out the OS page cache, which PostgreSQL relies on for double-buffering of hot data. Below 15% forces excessive kernel-level reads.

VPS RAM	RAM after services	shared_buffers	effective_cache_size
1 GB	~700 MB	192 MB	512 MB
2 GB	~1.5 GB	384 MB	1 GB
4 GB	~3.2 GB	768 MB	2 GB
8 GB	~6.5 GB	1536 MB	4 GB

effective_cache_size allocates nothing — it only informs the planner how much data the OS will likely keep cached. Set it to roughly 50–75% of total RAM so the planner correctly prefers index scans over sequential reads on hot tables.

work_mem: The Multiplication That Bites

work_mem is not a global cap. Each sort, hash join, or bitmap operation can claim one work_mem slot, and a single complex query can hold several. With max_connections = 100 and work_mem = 64MB, worst-case resident memory is measured in gigabytes. Size it from the bottom up:

• Keep work_mem at 4–16 MB on a small VPS.
• Lower max_connections to 20–40 and put PgBouncer in front for application concurrency.
• Reserve larger work areas per-session for reporting queries with SET LOCAL work_mem, not in the global config.

Reducing connection count is usually a bigger win than inflating memory: every idle connection holds per-process overhead (roughly 5–10 MB with TLS) that never returns to the cache.

maintenance_work_mem and autovacuum

Set maintenance_work_mem to 64–128 MB so VACUUM and index builds finish without repeated passes. Then tune autovacuum to act before tables bloat rather than after:

autovacuum_vacuum_scale_factor = 0.05   # default 0.2 is far too lazy on small tables
autovacuum_analyze_scale_factor = 0.02
autovacuum_vacuum_cost_limit = 1000     # let vacuum finish quickly

Connection Pooling: The Cheapest Concurrency Win

PostgreSQL forks a process per connection, so every client socket costs memory that scales with max_connections, not with actual query load. A PHP or Node application that opens hundreds of short-lived connections wastes most of its RAM on idle backends. Put a pooler in front and keep the backend count small:

# pgbouncer.ini
[databases]
app = host=127.0.0.1 port=5432 dbname=app
[pgbouncer]
pool_mode = transaction
max_client_conn = 500
default_pool_size = 25
server_idle_timeout = 60

Transaction pooling routes each client to a backend only for the duration of a transaction, so 500 application connections can share 25 backends. The caveat: session-level features such as advisory locks, SET outside a transaction, and prepared statements tied to a session must be handled carefully. If you rely on those, use session pooling for that database and keep max_connections modest.

Verify with Real Measurements

After every change, reload and confirm the settings took effect, then measure. Use pg_stat_statements to find the queries responsible for the most shared-buffer reads and total time:

SELECT calls, mean_exec_time, shared_blks_read, query
FROM pg_stat_statements
ORDER BY shared_blks_read DESC LIMIT 10;

High shared_blks_read means the working set does not fit in memory — increase shared_buffers before adding indexes. High mean_exec_time with low block reads points to CPU-bound sorting, where a targeted work_mem for that query class helps.

Check Memory Pressure, Not Just Configuration

PostgreSQL’s shared_buffers is shared memory, but work areas and connections are private and can trigger the OOM killer. Read PSI pressure and the OOM log to confirm the box is not thrashing:

• cat /proc/pressure/memory — rising “full” averages precede swap storms.
• dmesg | grep -i "killed process" — any PostgreSQL PID here means you over-committed.
• vm.swappiness = 10 on a database VPS keeps the kernel from paging out hot buffers prematurely.

If you are still choosing hardware and want instances where the RAM actually delivers the IOPS a database needs, review the provider comparison before you commit. Resource sizing is cheap to change on paper and expensive to change after migration.

The Order of Operations

1. Measure available RAM after services.
2. Set shared_buffers to 25% of that, effective_cache_size to ~60% of total.
3. Cut max_connections and add a pooler.
4. Keep work_mem small globally; raise it per-session for analytics.
5. Make autovacuum more aggressive than default.
6. Validate with pg_stat_statements and PSI, then iterate.

Every one of these settings can be verified empirically. Change one variable at a time and keep the measurement before and after — that discipline is what separates a tuned database from a merely reconfigured one. When you outgrow the arithmetic, a larger instance from a reliable VPS host is the cleanest path.