{"id":1310,"date":"2026-09-28T02:34:15","date_gmt":"2026-09-28T02:34:15","guid":{"rendered":"https:\/\/affordablevpsserver.com\/blog\/?p=1310"},"modified":"2026-09-28T02:34:15","modified_gmt":"2026-09-28T02:34:15","slug":"postgresql-tuning-low-memory-vps","status":"publish","type":"post","link":"https:\/\/affordablevpsserver.com\/blog\/postgresql-tuning-low-memory-vps\/","title":{"rendered":"PostgreSQL on a Low-Memory Linux Server: Configuration Changes That Prevent Swap Thrash"},"content":{"rendered":"\n

PostgreSQL’s default configuration assumes a machine with far more RAM than a small server provides. On a 1 GB or 2 GB instance, out-of-the-box settings can push the database into swap within a day of moderate traffic, and swap thrashing on a shared virtual disk turns a 5 ms query into a 2-second one. This is a practical tuning walkthrough for memory-constrained PostgreSQL deployments.<\/p>\n\n\n\n

Baseline: What Each Setting Costs<\/h2>\n\n\n\n
Setting<\/th>Default<\/th>Memory behaviour<\/th>Sensible small-server value<\/th><\/tr><\/thead>
shared_buffers<\/code><\/td>128 MB<\/td>Fixed allocation at startup<\/td>25% of RAM, e.g. 256 MB on 1 GB<\/td><\/tr>
work_mem<\/code><\/td>4 MB<\/td>Per sort\/hash operation, can multiply<\/td>4-8 MB; keep low on shared boxes<\/td><\/tr>
maintenance_work_mem<\/code><\/td>64 MB<\/td>Per maintenance connection<\/td>64-128 MB<\/td><\/tr>
effective_cache_size<\/code><\/td>4 GB<\/td>Planner hint only, no allocation<\/td>50-75% of RAM<\/td><\/tr>
max_connections<\/code><\/td>100<\/td>Each backend ~5-10 MB<\/td>20-40, with a connection pooler<\/td><\/tr>
wal_buffers<\/code><\/td>-1 (auto)<\/td>Small fixed buffer<\/td>Leave default or 16 MB<\/td><\/tr><\/tbody><\/table>\n\n\n\n

The dangerous settings are the per-operation ones. work_mem<\/code> is allocated per sort or hash node<\/em>, per connection. With work_mem = 64MB<\/code> and 20 concurrent sort-heavy queries, PostgreSQL will attempt 1.28 GB of work memory on a 1 GB box. That is the classic path to swap death, and it happens gradually enough that people blame the provider instead of the config.<\/p>\n\n\n\n

The Tuning Sequence<\/h2>\n\n\n\n
  1. Measure current usage.<\/strong> Run free -m<\/code> and ps aux --sort=-rss | head<\/code> to see actual PostgreSQL RSS. Check vmstat 1 10<\/code> \u2014 a non-zero si<\/code>\/so<\/code> column during normal traffic means you are already swapping.<\/li>
  2. Check the log for spills.<\/strong> Set log_temp_files = 0<\/code> to log every temp file larger than 0 bytes. Frequent temp files mean work_mem<\/code> is too small for your queries; one or two large ones mean a single bad query.<\/li>
  3. Set shared_buffers to 25% of RAM.<\/strong> Edit postgresql.conf<\/code>, restart, and confirm with SHOW shared_buffers;<\/code>.<\/li>
  4. Set max_connections to 20-40 and deploy a pooler.<\/strong> PgBouncer in transaction mode lets 200 application clients share 20 database backends \u2014 the single largest memory saving available.<\/li>
  5. Cap work_mem conservatively.<\/strong> Multiply expected concurrent sort operations by work_mem<\/code> and confirm the product stays under 20% of RAM.<\/li>
  6. Set effective_cache_size to 60% of RAM.<\/strong> This is planner guidance only \u2014 it costs nothing and improves join planning.<\/li>
  7. Restart and re-measure.<\/strong> Compare vmstat<\/code> swap activity and query latency before and after.<\/li><\/ol>\n\n\n\n

    Change one group of settings at a time. If you alter all seven at once and performance improves, you learn nothing about which change mattered, and you cannot safely tune the next server.<\/p>\n\n\n\n

    Sizing Guide by Instance RAM<\/h2>\n\n\n\n
    Instance RAM<\/th>shared_buffers<\/th>work_mem<\/th>max_connections<\/th>effective_cache_size<\/th><\/tr><\/thead>
    1 GB<\/td>256 MB<\/td>4 MB<\/td>20<\/td>512 MB<\/td><\/tr>
    2 GB<\/td>512 MB<\/td>6 MB<\/td>30<\/td>1 GB<\/td><\/tr>
    4 GB<\/td>1 GB<\/td>8 MB<\/td>40<\/td>2.5 GB<\/td><\/tr>
    8 GB<\/td>2 GB<\/td>16 MB<\/td>60<\/td>5 GB<\/td><\/tr><\/tbody><\/table>\n\n\n\n

    Kernel and OS Level Adjustments<\/h2>\n\n\n\n