{"id":631,"date":"2026-07-17T22:17:54","date_gmt":"2026-07-17T22:17:54","guid":{"rendered":"https:\/\/virtualserversvps.com\/blog\/?p=631"},"modified":"2026-10-07T22:03:10","modified_gmt":"2026-10-07T22:03:10","slug":"postgresql-performance-tuning-vps","status":"publish","type":"post","link":"https:\/\/virtualserversvps.com\/blog\/postgresql-performance-tuning-vps\/","title":{"rendered":"PostgreSQL on a Small VPS: Sizing shared_buffers, work_mem and Connections from Real RAM"},"content":{"rendered":"
\n\n\n

PostgreSQL ships with defaults tuned for a machine with far more RAM than a typical VPS. On a 1\u20134 GB instance those defaults are not merely suboptimal \u2014 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.<\/p>\n\n\n\n

Start by Measuring, Not Guessing<\/h2>\n\n\n\n

Before touching postgresql.conf<\/code>, record the real numbers. free -m<\/code> 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:<\/p>\n\n\n\n

free -m\nps -eo rss,comm --sort=-rss | head\nsudo -u postgres psql -c \"SHOW shared_buffers; SHOW work_mem; SHOW max_connections;\"<\/code><\/pre>\n\n\n\n
A 2 GB VPS running nginx, PHP-FPM and a systemd journal might leave 1.6 GB genuinely free. That figure \u2014 not the 2048 MB sticker on the plan \u2014 is your budget.<\/p>\n\n\n\n
shared_buffers: Cap at 25% of Usable RAM<\/h2>\n\n\n\n
shared_buffers<\/code> is PostgreSQL’s own page cache. Allocate roughly 25% of the RAM available to the database<\/em>, 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.<\/p>\n\n\n\n
VPS RAM<\/th>RAM after services<\/th>shared_buffers<\/th>effective_cache_size<\/th><\/tr><\/thead>
1 GB<\/td>~700 MB<\/td>192 MB<\/td>512 MB<\/td><\/tr>
2 GB<\/td>~1.5 GB<\/td>384 MB<\/td>1 GB<\/td><\/tr>
4 GB<\/td>~3.2 GB<\/td>768 MB<\/td>2 GB<\/td><\/tr>
8 GB<\/td>~6.5 GB<\/td>1536 MB<\/td>4 GB<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
effective_cache_size<\/code> allocates nothing \u2014 it only informs the planner how much data the OS will likely keep cached. Set it to roughly 50\u201375% of total RAM so the planner correctly prefers index scans over sequential reads on hot tables.<\/p>\n\n\n\n
work_mem: The Multiplication That Bites<\/h2>\n\n\n\n
work_mem<\/code> is not a global cap. Each sort, hash join, or bitmap operation can claim one work_mem<\/code> slot, and a single complex query can hold several. With max_connections = 100<\/code> and work_mem = 64MB<\/code>, worst-case resident memory is measured in gigabytes. Size it from the bottom up:<\/p>\n\n\n\n