{"id":1114,"date":"2026-09-12T22:39:53","date_gmt":"2026-09-12T22:39:53","guid":{"rendered":"https:\/\/virtualserversvps.com\/blog\/?p=1114"},"modified":"2026-09-12T22:39:53","modified_gmt":"2026-09-12T22:39:53","slug":"tuning-postgresql-small-vps-shared-buffers-wal-max-connections","status":"publish","type":"post","link":"https:\/\/virtualserversvps.com\/blog\/tuning-postgresql-small-vps-shared-buffers-wal-max-connections\/","title":{"rendered":"Tuning PostgreSQL for a Small VPS: shared_buffers, WAL, and max_connections"},"content":{"rendered":"\n

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<\/code>, wal_buffers<\/code>\/checkpoint behavior, and max_connections<\/code>. 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.<\/p>\n\n\n\n

Know Your Budget Before Touching a Setting<\/h2>\n\n\n\n

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

Instance<\/th>shared_buffers<\/th>effective_cache_size<\/th>work_mem<\/th>max_connections<\/th><\/tr><\/thead>
1 GB RAM<\/td>256MB<\/td>512MB<\/td>4MB<\/td>20<\/td><\/tr>
2 GB RAM<\/td>512MB<\/td>1GB<\/td>8MB<\/td>30<\/td><\/tr>
4 GB RAM<\/td>1GB<\/td>2GB<\/td>12MB<\/td>50<\/td><\/tr>
8 GB RAM<\/td>2GB<\/td>5GB<\/td>16MB<\/td>80<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

1. shared_buffers: A Quarter of RAM, Not More<\/h2>\n\n\n\n

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

# postgresql.conf \u2014 2 GB VPS\nshared_buffers = 512MB\neffective_cache_size = 1GB     # tells the planner how much RAM the OS + PG can cache\n\n# Verify actual page cache usage\nfree -h\ngrep -E 'Buffers|Cached' \/proc\/meminfo<\/code><\/pre>\n\n\n\n
effective_cache_size<\/code> allocates nothing \u2014 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.<\/p>\n\n\n\n
2. max_connections: The Silent Memory Bomb<\/h2>\n\n\n\n
Each backend process can allocate up to work_mem<\/code> per sort or hash operation<\/em>, and a single query may use several. With max_connections = 200<\/code> and work_mem = 64MB<\/code> (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<\/strong>.<\/p>\n\n\n\n
# Keep real PostgreSQL connections small; let PgBouncer multiplex clients\nmax_connections = 30\nwork_mem = 8MB            # per sort\/hash node\nmaintenance_work_mem = 64MB\n\n# Then run PgBouncer in front:\n# \/etc\/pgbouncer\/pgbouncer.ini\n[databases]\napp = host=127.0.0.1 port=5432 dbname=app\n\n[pgbouncer]\nlisten_port = 6432\npool_mode = transaction\nmax_client_conn = 400\ndefault_pool_size = 20<\/code><\/pre>\n\n\n\n
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<\/code> for idle-in-transaction sessions that defeat pooling:<\/p>\n\n\n\n
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;\n-- 'idle in transaction' with a long age is a bug in your application<\/code><\/pre>\n\n\n\n
3. WAL and Checkpoints: Stop the Periodic Stalls<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
# WAL settings for a busy 2-4 GB VPS\nwal_buffers = 16MB\nmin_wal_size = 256MB\nmax_wal_size = 1GB          # larger = fewer checkpoints, more WAL disk used\ncheckpoint_timeout = 15min\ncheckpoint_completion_target = 0.9   # spread writes across 90% of the interval\nsynchronous_commit = on              # keep on for durability; off only for bulk loads\nwal_compression = on                 # saves WAL disk at a small CPU cost<\/code><\/pre>\n\n\n\n
On a 4 vCPU box, let the checkpointer parallelize: checkpoint_flush_after = 256kB<\/code> and backend_flush_after = 256kB<\/code> reduce the size of individual write bursts. If your provider caps disk IOPS, these are the settings that keep you under the cap.<\/p>\n\n\n\n
4. Planner Costs for SSD-Backed VPS Storage<\/h2>\n\n\n\n
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:<\/p>\n\n\n\n
random_page_cost = 1.1      # default 4.0 is for HDDs\neffective_io_concurrency = 200\ndefault_statistics_target = 100<\/code><\/pre>\n\n\n\n
Always run ANALYZE<\/code> after any change in table size, or let autovacuum handle it \u2014 bad statistics negate every planner tweak. This is the same disk-aware reasoning behind our VPS performance tuning guides<\/a> for web stacks.<\/p>\n\n\n\n
A Complete postgresql.conf Block for a 2 GB \/ 2 vCPU VPS<\/h2>\n\n\n\n
max_connections = 30\nshared_buffers = 512MB\neffective_cache_size = 1GB\nwork_mem = 8MB\nmaintenance_work_mem = 64MB\nwal_buffers = 16MB\nmin_wal_size = 256MB\nmax_wal_size = 1GB\ncheckpoint_timeout = 15min\ncheckpoint_completion_target = 0.9\nrandom_page_cost = 1.1\neffective_io_concurrency = 200\nlog_min_duration_statement = 200ms   # log slow queries\nshared_preload_libraries = 'pg_stat_statements'<\/code><\/pre>\n\n\n\n
5. Autovacuum: Tune It, Never Disable It<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
autovacuum_max_workers = 2\nautovacuum_naptime = 30s\nautovacuum_vacuum_cost_delay = 20ms   # slower, less disruptive\nautovacuum_vacuum_scale_factor = 0.1  # vacuum at 10% dead rows\n\n-- check what is being vacuumed\nSELECT relname, n_dead_tup, last_autovacuum\nFROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;<\/code><\/pre>\n\n\n\n
Keep the cost delay high so an autovacuum run never competes with foreground queries for your capped disk IOPS \u2014 it simply takes longer, which is exactly the trade you want on a shared plan.<\/p>\n\n\n\n
Verifying the Results<\/h2>\n\n\n\n