Running a small Postgres-backed service on a French VPS

Two years ago I moved a side project off a big cloud provider onto a 4 GB RAM VPS from a French host (OVH, Roubaix datacenter). The workload is small: a Postgres 15 database, about 40 GB of data, roughly 300 writes/minute at peak and a few thousand reads. Nothing dramatic. But the migration surfaced a set of issues that only show up when you stop leaning on managed services, and I want to write down what actually mattered.

The first thing that bit me was disk I/O. The VPS uses local SSD, but the default Postgres config assumed something closer to a dedicated machine. shared_buffers was 128 MB, work_mem was 4 MB, and effective_io_concurrency was 1. For my workload the biggest win was not tuning buffers but telling the planner the truth about the disk:

ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET effective_io_concurrency = 200;
ALTER SYSTEM SET shared_buffers = '1GB';

random_page_cost at 1.1 is aggressive; on spinning disks it would be wrong. On this SSD it cut a handful of sequential scans that were clearly worse than index lookups. I verified with EXPLAIN (ANALYZE, BUFFERS) on the three slowest queries before and after. The cost model change moved one report query from 2.4 s to 180 ms.

The second issue was backups. I had been relying on the provider’s snapshot feature. Snapshots are fine for whole-machine restore, but they are not a substitute for a logical backup when you need to restore a single table after a bad migration. I now run pg_dump -Fc nightly to a separate object store, and I test the restore monthly into a scratch database. The restore test caught a permissions problem in my dump script that would have made the backup useless.

The third was connection management. Without a pooler, each PHP-FPM worker opened its own connection, and at peak I hit max_connections. I put PgBouncer in transaction mode in front of Postgres. The trade-off is real: transaction mode breaks session-level features like SET outside a transaction, advisory locks held across statements, and LISTEN/NOTIFY. I had to audit my code for all three. If your app relies on session state, use session mode and accept the connection cost.

Where this falls apart: if your dataset outgrows RAM and you start hitting the disk for reads, the 4 GB box becomes the bottleneck and no amount of random_page_cost tuning fixes it. I also would not run this setup without monitoring; I use pg_stat_statements plus a simple cron that logs the top five queries by total time every hour. Without that I would have missed a slow query that crept up after a schema change.

One thing I am still unsure about: whether to move to a managed Postgres again as the data grows, or to keep tuning. The operational overhead is maybe two hours a month now, but the failure modes are all mine. What is your threshold for going back to managed?