Sizing a dedicated server for Postgres: cores, memory, NVMe

Start with the working set

The single most important number is the size of the data your queries actually touch, not the total database size. If the working set fits in memory, almost every read is served from cache and the disk only matters for writes and checkpoints. If it does not, every query is an I/O question.

Measure it: look at the cache hit ratio in pg_stat_database and the sizes of your hot tables and indexes. A hit ratio below 99 percent on a read-heavy workload means the working set is spilling to disk.

Memory

• Aim for memory of at least the working set plus 25 percent, and ideally the whole database if it is under about 400 GB.
• Set shared_buffers to about 25 percent of memory; the operating system cache does the rest and is better at it.
• work_mem is per sort, per connection: with 96 threads and a connection pooler, 64 to 256 MB is usually safe on 256 GB.
• ECC memory is not optional for a database. Silent bit flips corrupt data without a trace.

In practice a database under 2 TB with a sensible working set is comfortable on 256 GB. Above that, or with heavy analytics, 512 GB pays for itself in avoided I/O.

Storage

Datacentre NVMe is the reason dedicated Postgres is fast. A managed cloud volume might offer 3,000 to 16,000 IOPS as a baseline; a single datacentre NVMe drive offers hundreds of thousands, with latency measured in tens of microseconds rather than milliseconds.

• Mirror two drives (RAID 1) for a single server; the second copy protects against drive failure, not against mistakes, which is what backups are for.
• Size for three times the current database: Postgres needs space for bloat, indexes, WAL and the occasional full-table rewrite.
• Datacentre-edition drives have power-loss protection and endurance ratings; consumer drives do not belong under a database.
• Separate WAL onto its own drive only if you have measured contention; on NVMe it rarely matters.

CPU

Postgres uses one process per connection and parallelises large queries across workers. More cores mean more concurrent queries and faster analytics. A 48-core Zen 4 EPYC at 3.8 GHz boost is more single-thread performance than most cloud database instances offer at any price, and 96 threads is enough for several hundred active connections behind a pooler.

A worked example

The figures on the left vary by provider and discount; the point is the shape. The dedicated option is cheaper and has an order of magnitude more I/O headroom, and the database tuning is done by someone whose job it is.

Operations you still need

• Streaming replication to a second server with automatic failover.
• Point-in-time recovery from WAL archives, tested.
• Vacuum and bloat monitoring; autovacuum defaults are too timid for busy tables.
• Major version upgrades once a year, with a rehearsal.
• Query monitoring with pg_stat_statements to catch the regressions before users do.

All of these are part of the managed Postgres add-on and the Cluster 3 package at Infraexa; on Core packages they are set up at handover and reviewed quarterly.