Can PostgreSQL leave more room for an application on a small VPS without giving up useful query performance? On a 512 MiB VM, a small-cache configuration peaked at 50.4 MiB PostgreSQL PSS, compared with 180.1 MiB for defaults. It also completed more queries per second in this short run.
This is one read-only workload, not a universal tuning result. A 256 MiB preparation attempt ran out of memory; the successful comparison used 512 MiB, with about 456 MiB usable and no swap. No application shared the VM.
Two approaches
Theory A uses 16 MB shared buffers, 1 MB work memory, 16 MB maintenance work memory and PostgreSQL 18's default worker I/O, relying more on the OS cache. Theory B uses 32 MB shared buffers, default query memory and synchronous I/O. Both limit connections to five, reserve one superuser slot, and disable JIT and parallel queries. Durability and autovacuum remain enabled. These are bundles, so the experiment does not isolate individual settings.
What happened
PostgreSQL 18.6 served a 222 MB fixture with four persistent active clients running point, range and aggregate queries. Each configuration had 20 seconds of warm-up and 50 seconds of measurement, in the order default, A, B.
| Metric | Default | Theory A | Theory B |
|---|---|---|---|
| Peak PSS | 180.1 MiB | 50.4 MiB | 79.7 MiB |
| Queries/s | 863 | 1,056 | 861 |
| Point p95 | 2.90 ms | 2.64 ms | 2.92 ms |
| Range p95 | 1.95 ms | 1.93 ms | 2.09 ms |
| Aggregate p95 | 98.55 ms | 74.25 ms | 103.18 ms |
Peak PSS is the highest one-second sample across idle, warm-up and measurement. PSS apportions shared pages among processes, unlike summed RSS, which counts them repeatedly. It does not include the whole VPS cost: filesystem cache and OS services need memory too.
A is the practical choice for this measured workload. It used less memory and showed no performance penalty here. The fixed order, warm cache, background services and single short run limit the conclusion. This read-only test does not evaluate sustained writes or autovacuum; a different workload may need more query memory or connections.
Try Theory A
On Debian or Ubuntu, PostgreSQL configuration normally lives under /etc/postgresql/18/main/. Use SHOW config_file; to locate the active file. Add the following there or in an included file, checking for later overrides:
max_connections = 5
superuser_reserved_connections = 1
jit = off
max_parallel_workers_per_gather = 0
max_parallel_workers = 0
shared_buffers = '16MB'
work_mem = '1MB'
maintenance_work_mem = '16MB'Restart the cluster, for example sudo systemctl restart postgresql@18-main. Verify the effective values with SHOW shared_buffers;, SHOW max_connections; and the other settings, or query pg_settings. Five slots include the reserved superuser slot, leaving at most four ordinary connections when there are no other reservations. Account for every pool and operational connection. Low work memory can cause sorts to spill. Leave fsync, autovacuum and standard WAL enabled.
Experiment record: configurations, methodology and measurements