PostgreSQL on a VPS: sizing memory, disk and connections

Start from the working set

On a database server, memory is first of all a cache: a page read from memory is served far faster than one read from disk, even NVMe. So the question is not only "how big is the database?" but "how much of it is read often?". A 50 GB database where only the last twelve months are queried may be working on 8 GB.

On an existing database, two queries give the total size and the largest tables, indexes included:

SELECT pg_size_pretty(pg_database_size(current_database()));

SELECT relname AS table_name,
       pg_size_pretty(pg_total_relation_size(relid)) AS size_with_indexes
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

For a database yet to be built, estimate table by table: rows expected after a year × average row size, then add indexes, which commonly weigh 30 to 100% of the data. Example: 2 million rows a month at 500 bytes each make 12 GB of data after a year, and 18 to 24 GB with indexes.

Memory: starting settings

PostgreSQL keeps pages it has read at two levels: its own cache (shared_buffers) and the operating system’s file cache. Its documentation recommends starting shared_buffers at 25% of memory on a dedicated server, and notes that going beyond 40% rarely helps: the rest serves the OS cache and sorting.

These values assume the database is alone on the server. If the application runs alongside it, subtract the app’s memory first (processes, cache, builds) and apply the percentages to what is left: an 8 GB server running a 3 GB application is tuned like a 5 GB database server.

shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 16MB
maintenance_work_mem = 512MB
random_page_cost = 1.1

shared_buffers only takes effect when PostgreSQL restarts; the other settings reload without interruption, with SELECT pg_reload_conf();.

To see whether memory is enough, read the cache hit ratio. For a business application or a website, it stays above 99%:

SELECT round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS cache_hit_ratio
FROM pg_stat_database
WHERE datname = current_database();

Connections: one process each

PostgreSQL starts one process per connection and accepts 100 by default (max_connections). Each process takes a few megabytes when idle, more when it sorts or hashes data (work_mem, sometimes several times within one query). Raising max_connections to 500 to keep up with a chatty application exposes the server to running out of memory at the first peak.

The right answer is a connection pooler: PgBouncer in transaction mode lets hundreds of application connections share a few dozen real ones. Frameworks also have their own pool (SQLAlchemy, Prisma, HikariCP): size it on the database server’s vCPU count rather than on the number of users.

Disk: size, headroom and the WAL

The disk of a PostgreSQL server holds more than tables:

• Data and indexes, and their growth over a year.
• The write-ahead log (WAL): 1 GB by default between checkpoints (max_wal_size), more during a bulk import, and more still if WAL archiving falls behind.
• Temporary files from sorts that exceed work_mem.
• Room for reorganising: VACUUM FULL and index rebuilds write a full copy before releasing the old one.
• Local exports, if you keep backups on the server before sending them elsewhere.

The rule: size after a year, plus at least 30% headroom. It is not a comfort margin: when the disk holding the WAL fills up, PostgreSQL shuts down to protect its data. Watch disk usage, and grow before 80%.

On performance, every commit waits for the WAL to be written to disk: storage latency sets write throughput. That is where an NVMe SSD makes the difference, far more than on reads, which memory already serves.

CPU: one query, one core

A PostgreSQL query runs on a single core, except for large scans that the planner parallelises. The vCPU count therefore sets how many heavy queries run at once: 2 vCPUs are enough for a business application at launch, 4 to 8 suit busy databases and heavy reporting.

Before adding vCPUs, find the queries that use them. The pg_stat_statements extension ranks them by total time:

shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT calls, round(total_exec_time) AS total_ms,
       round(mean_exec_time::numeric, 1) AS mean_ms, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Same server as the app, or a separate one?

At launch, running the database on the same server as the application is the right call: no network latency between them, a Unix socket connection, one server to back up and monitor. Split them when they compete for resources: a build or a traffic spike that flushes the database cache, a database that needs all the memory for its data, or different backup requirements.

Security and backups

• Listen locally: listen_addresses = 'localhost' as long as only the app on the server connects. If another server needs access, open port 5432 to its address only, in the firewall and in pg_hba.conf.
• Authentication: the scram-sha-256 method in pg_hba.conf, one role per application, without superuser rights.
• A daily logical backup with pg_dump, copied off the server.
• Point-in-time recovery: continuous WAL archiving (pgBackRest, WAL-G) lets you go back to the minute before a mistake, where a daily export takes you back to the day before.
• Tested restores: a backup is proven by restoring it, into a test database, at regular intervals.

pg_dump -Fc -d my_db -f my_db-$(date +%F).dump
createdb my_db_test
pg_restore -d my_db_test my_db-2026-10-29.dump