Tuning PostgreSQL for a Small VPS: shared_buffers, WAL, and max_connections

PostgreSQL ships with configuration defaults tuned for a developer laptop, not a 2 GB VPS. On a small instance the three settings that cause the most pain are shared_buffers, wal_buffers/checkpoint behavior, and max_connections. Get them wrong and you either starve the OS page cache, stall on checkpoints, or run out of memory the moment traffic arrives. This guide walks through each with concrete numbers for a 2 vCPU / 2 GB and a 4 vCPU / 4 GB box.

Know Your Budget Before Touching a Setting

On a VPS, RAM is the scarce resource. Split it deliberately: roughly 25% to shared_buffers, and let the kernel use the rest as page cache for the data files. Over-allocating shared_buffers is the single most common small-VPS mistake — it looks “generous” while actually shrinking the page cache that caches your hot data.

Instance	shared_buffers	effective_cache_size	work_mem	max_connections
1 GB RAM	256MB	512MB	4MB	20
2 GB RAM	512MB	1GB	8MB	30
4 GB RAM	1GB	2GB	12MB	50
8 GB RAM	2GB	5GB	16MB	80

1. shared_buffers: A Quarter of RAM, Not More

shared_buffers is PostgreSQL’s own cache of data pages. On Linux the OS page cache already caches the same files, so a huge shared_buffers duplicates work and steals memory from that cache. Set it to ~25% of RAM and stop.

# postgresql.conf — 2 GB VPS
shared_buffers = 512MB
effective_cache_size = 1GB     # tells the planner how much RAM the OS + PG can cache

# Verify actual page cache usage
free -h
grep -E 'Buffers|Cached' /proc/meminfo

effective_cache_size allocates nothing — it is a planner hint. Setting it to roughly (shared_buffers + expected page cache) makes the planner prefer index scans when it knows the working set fits in memory.

2. max_connections: The Silent Memory Bomb

Each backend process can allocate up to work_mem per sort or hash operation, and a single query may use several. With max_connections = 200 and work_mem = 64MB (the defaults on some builds), theoretical worst case exceeds 12 GB on a 2 GB box. The correct pattern on a VPS is a low connection count plus a pooler.

# Keep real PostgreSQL connections small; let PgBouncer multiplex clients
max_connections = 30
work_mem = 8MB            # per sort/hash node
maintenance_work_mem = 64MB

# Then run PgBouncer in front:
# /etc/pgbouncer/pgbouncer.ini
[databases]
app = host=127.0.0.1 port=5432 dbname=app

[pgbouncer]
listen_port = 6432
pool_mode = transaction
max_client_conn = 400
default_pool_size = 20

Transaction pooling lets 400 PHP or Node clients share 20 real backends. This cuts memory pressure and connection setup cost in one move. Watch pg_stat_activity for idle-in-transaction sessions that defeat pooling:

SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
-- 'idle in transaction' with a long age is a bug in your application

3. WAL and Checkpoints: Stop the Periodic Stalls

Default checkpoints fire every 5 minutes and force every dirty page to disk at once. On a small VPS with limited IOPS, that produces a latency spike every 5 minutes. Spread the work out by lengthening the interval and sizing the WAL window.

# WAL settings for a busy 2-4 GB VPS
wal_buffers = 16MB
min_wal_size = 256MB
max_wal_size = 1GB          # larger = fewer checkpoints, more WAL disk used
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9   # spread writes across 90% of the interval
synchronous_commit = on              # keep on for durability; off only for bulk loads
wal_compression = on                 # saves WAL disk at a small CPU cost

On a 4 vCPU box, let the checkpointer parallelize: checkpoint_flush_after = 256kB and backend_flush_after = 256kB reduce the size of individual write bursts. If your provider caps disk IOPS, these are the settings that keep you under the cap.

4. Planner Costs for SSD-Backed VPS Storage

The default planner costs assume spinning disks with expensive random reads. On NVMe they over-penalize index scans. Lowering them makes the planner pick better plans for the storage you actually have:

random_page_cost = 1.1      # default 4.0 is for HDDs
effective_io_concurrency = 200
default_statistics_target = 100

Always run ANALYZE after any change in table size, or let autovacuum handle it — bad statistics negate every planner tweak. This is the same disk-aware reasoning behind our VPS performance tuning guides for web stacks.

A Complete postgresql.conf Block for a 2 GB / 2 vCPU VPS

max_connections = 30
shared_buffers = 512MB
effective_cache_size = 1GB
work_mem = 8MB
maintenance_work_mem = 64MB
wal_buffers = 16MB
min_wal_size = 256MB
max_wal_size = 1GB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
random_page_cost = 1.1
effective_io_concurrency = 200
log_min_duration_statement = 200ms   # log slow queries
shared_preload_libraries = 'pg_stat_statements'

5. Autovacuum: Tune It, Never Disable It

Faced with an overloaded VPS, admins sometimes disable autovacuum to free up I/O. That trade buys a few quiet days and then hands you table bloat, transaction-ID wraparound risk, and steadily worse plans as statistics go stale. On a small instance, make autovacuum gentle instead of turning it off.

autovacuum_max_workers = 2
autovacuum_naptime = 30s
autovacuum_vacuum_cost_delay = 20ms   # slower, less disruptive
autovacuum_vacuum_scale_factor = 0.1  # vacuum at 10% dead rows

-- check what is being vacuumed
SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

Keep the cost delay high so an autovacuum run never competes with foreground queries for your capped disk IOPS — it simply takes longer, which is exactly the trade you want on a shared plan.

Verifying the Results

• Buffer hit ratio: SELECT sum(blks_hit)*100.0/sum(blks_hit+blks_read) FROM pg_stat_database; — target 99%+.
• Checkpoint spikes: SELECT * FROM pg_stat_bgwriter; — watch checkpoints_timed vs checkpoints_req; too many requested checkpoints means max_wal_size is too small.
• Query plans: EXPLAIN (ANALYZE, BUFFERS) confirms the planner used the indexes you expected.
• Top queries: SELECT query, mean_exec_time FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;

A tuned PostgreSQL on a modest instance frequently beats a default-configured one on double the hardware. Apply the block above, watch the four metrics for a week, and adjust work_mem upward only if you see sorts spilling to disk (log_temp_files). If the database still saturates the box, it may be time to give it its own instance — compare options on the VPS plans page and size for baseline, not peak.