PostgreSQL VPS Sizing and Tuning: Memory, shared_buffers, WAL and Storage

Size a PostgreSQL server so the working set, the tables and indexes your queries touch most often, fits in memory. Then tune a small number of settings: shared_buffers, effective_cache_size, work_mem, maintenance_work_mem and the WAL and checkpoint settings. Most performance problems after that are query problems, not hardware problems.

Step 1: estimate the working set

SELECT pg_size_pretty(pg_database_size(current_database()));
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;

The total database size is an upper bound. The working set is usually smaller: recent rows, hot indexes and lookup tables. Aim for memory that holds the working set with room to spare.

Step 2: sizing starting points

Step 3: tune the core settings

• shared_buffers: PostgreSQL's own cache. The PostgreSQL wiki suggests starting around a quarter of system memory on a dedicated database server.
• effective_cache_size: a planner hint for how much memory is available for caching, including the operating system cache. Often set to around half to three quarters of memory.
• work_mem: memory per sort or hash operation, per connection. Raise it carefully, because many connections multiply it.
• maintenance_work_mem: memory for VACUUM and index builds. Higher values speed up maintenance.
• max_connections: keep it modest and put a connection pooler such as PgBouncer in front of busy applications.

Step 4: WAL and checkpoints

The write-ahead log protects every change. The PostgreSQL documentation explains max_wal_size, checkpoint_timeout and checkpoint_completion_target. Spreading checkpoints out avoids bursts of writes that stall queries. Watch the server log for warnings that checkpoints are happening too frequently.

Step 5: storage

Every StreamData VPS uses RAID NVMe storage, which suits PostgreSQL's mix of random reads and WAL writes. Measure your own storage with fio or pgbench rather than relying on anyone's headline figure, and keep enough free space for VACUUM, index rebuilds and backups.

Step 6: backups

Use pg_dump for logical backups of smaller databases, or base backups with WAL archiving for point-in-time recovery. Store copies off the server. StreamData's backup policy leaves backups to the customer unless a written order includes them.

When to move to dedicated

Move when CPU or I/O wait stays high through the working day after queries are tuned, or when you need more memory than the largest VPS. The dedicated server inventory includes AMD EPYC 7262 and 7282 servers with 128 GB DDR4 and 2 TB NVMe.

A worked example

A SaaS application with a 40 GB database, of which roughly 10 GB is touched daily, running on an SD-6Core-16GB VPS:

Apply the changes, restart, and compare query latency and cache hit ratio over a normal week. Adjust one setting at a time so you know which change helped.

Autovacuum

PostgreSQL relies on VACUUM to reclaim space and keep statistics fresh. Leave autovacuum on. On busy tables, lower the scale factor so vacuum runs before tables bloat, and watch for long-running transactions, which stop vacuum from cleaning up.

Monitoring that tells you when to resize

• Cache hit ratio from pg_stat_database. A falling ratio under normal load means the working set no longer fits.
• I/O wait on the VPS. Sustained wait after query tuning points at storage or memory.
• Replication or backup duration. If nightly backups start to overlap business hours, the database has grown past its current plan.
• Connection count and waiting sessions. Many waiting sessions usually mean locks or pool exhaustion, not a lack of CPU.

Frequently asked questions

Should PostgreSQL share a VPS with the application?

For small deployments, yes. Separate them once the database needs its memory for cache, or when you need independent scaling.

Is more CPU or more RAM better?

For most application databases, RAM first. A working set that fits in memory avoids disk reads entirely.

How do I find slow queries?

Enable pg_stat_statements and sort by total time. Fix the top few queries before buying hardware.

Sources

• PostgreSQL Documentation: Resource Consumption
• PostgreSQL Documentation: Write Ahead Log
• PostgreSQL wiki: Tuning Your PostgreSQL Server
• PostgreSQL Documentation: pg_dump