Postgres tuned properly for a 64 GB EPYC instance

What this builds

A single Postgres instance on an E-8 that uses the hardware it was given: eight dedicated Zen 4 cores, 64 GB of registered ECC DDR5, and Gen4 NVMe with power-loss protection. Out of the box, Postgres is configured to start on a laptop from 2009. Left that way it will use about a hundredth of the memory in this machine and wonder why it is slow.

Every number below is derived rather than copied. If your instance has a different amount of memory, the arithmetic is shown so you can redo it. This is written for a workload that is mostly transactional with some reporting, which is what most people actually have regardless of what they say.

Before you start

• An E-8 or larger. Below 64 GB the ratios still hold but the absolute numbers do not, and huge pages stop being worth the trouble.
• ECC memory, which the EPYC line has and the Ryzen line does not. A memory error in shared buffers is a corrupted page written back to disk with no warning.
• Debian 13, which packages PostgreSQL 17.

1. Install and initialise

apt update && apt install -y postgresql postgresql-contrib numactl
systemctl stop postgresql
pg_dropcluster 17 main
pg_createcluster 17 main -- --data-checksums --encoding=UTF8 --locale=C.UTF-8

Data checksums have to be chosen at initialisation time and cost a couple of percent of throughput. They are the difference between a corrupt page being reported and a corrupt page being served, so take the two percent.

2. Memory

Four settings, four calculations:

• shared_buffers at a quarter of RAM: 16GB. Larger values used to be discouraged; on a dedicated machine with a modern kernel, a quarter is the well-tested starting point and a third is defensible if your working set is bigger.
• effective_cache_size at three quarters of RAM: 48GB. This allocates nothing; it tells the planner how much the kernel is likely to have cached, and setting it too low makes the planner refuse index scans it should be choosing.
• work_mem per sort node, not per connection: with 200 connections and perhaps two sorts each, 32MB is a defensible ceiling on a 64 GB box. Raise it per session for reporting queries rather than globally.
• maintenance_work_mem for vacuum and index builds: 2GB, with autovacuum_work_mem left to inherit it.

shared_buffers = 16GB
effective_cache_size = 48GB
work_mem = 32MB
maintenance_work_mem = 2GB
huge_pages = try

3. Huge pages

Sixteen gigabytes of shared buffers mapped in four-kilobyte pages means four million page table entries per backend. Two-megabyte pages reduce that by a factor of five hundred, and the win shows up as lower CPU under concurrency rather than as a headline number.

systemctl start postgresql
sudo -u postgres psql -c "SHOW shared_memory_size_in_huge_pages;"

Take the number that prints, add ten percent for headroom, and write it down:

echo "vm.nr_hugepages = 8600" > /etc/sysctl.d/60-postgres.conf
echo "vm.overcommit_memory = 2" >> /etc/sysctl.d/60-postgres.conf
echo "vm.overcommit_ratio = 90" >> /etc/sysctl.d/60-postgres.conf
echo "vm.swappiness = 1" >> /etc/sysctl.d/60-postgres.conf
sysctl --system
echo never > /sys/kernel/mm/transparent_hugepage/enabled

Explicit huge pages yes, transparent huge pages no. The second one defragments memory at unpredictable moments and produces latency spikes that people spend weeks blaming on their storage.

4. Write-ahead log and checkpoints

Default checkpoint settings force a flush every few seconds under load, which turns your NVMe into a queue of tiny synchronous writes.

wal_level = replica
max_wal_size = 16GB
min_wal_size = 2GB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
wal_compression = zstd
wal_buffers = 64MB
synchronous_commit = on
full_page_writes = on

Leave synchronous_commit on. Turning it off is the single largest throughput gain available and it means acknowledging transactions that a power loss can eat. Our drives have capacitor-backed write caches so the fsync is genuinely cheap here; buy the throughput somewhere honest instead.

5. Storage and planner

random_page_cost = 1.1
seq_page_cost = 1.0
effective_io_concurrency = 200
maintenance_io_concurrency = 200
default_statistics_target = 200
jit = off

random_page_cost at four is a spinning-disk number and it is the most common single misconfiguration in the wild. On NVMe a random read costs almost exactly what a sequential one does, and telling the planner otherwise makes it avoid indexes on large tables. JIT compilation is off because for short transactional queries it spends more time compiling than executing; turn it on per session for the analytical ones.

6. Parallelism, autovacuum and connections

max_connections = 200
max_worker_processes = 8
max_parallel_workers = 8
max_parallel_workers_per_gather = 4
max_parallel_maintenance_workers = 4

autovacuum_max_workers = 4
autovacuum_naptime = 15s
autovacuum_vacuum_scale_factor = 0.02
autovacuum_analyze_scale_factor = 0.01
autovacuum_vacuum_cost_limit = 3000

shared_preload_libraries = 'pg_stat_statements'
track_io_timing = on
log_min_duration_statement = 500ms
log_checkpoints = on
log_autovacuum_min_duration = 0

Worker counts match the eight dedicated cores, because they are dedicated. On an oversold host these numbers would be a lie and you would tune them down to whatever fraction of a core you were actually sold; that is not a problem you have here.

The default autovacuum scale factor of 0.2 means a hundred-million-row table waits for twenty million dead tuples before anything happens. Two percent instead of twenty keeps vacuum running often and briefly rather than rarely and catastrophically.

7. Pooling

Two hundred backends on eight cores is not parallelism, it is a queue with extra memory overhead. Put a pooler in front:

apt install -y pgbouncer

[databases]
app = host=/var/run/postgresql dbname=app

[pgbouncer]
listen_addr = 10.9.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
default_pool_size = 32
max_client_conn = 2000
server_idle_timeout = 60

Transaction pooling with thirty-two server connections gives you four per core, which is roughly where throughput stops improving on this silicon. Applications that use session-level features such as advisory locks or prepared statements across transactions need session pooling instead, and will get a smaller number.

systemctl restart postgresql pgbouncer

Verify it

Settings first. Anything that did not take effect will be obvious here rather than in three weeks:

sudo -u postgres psql -c "SELECT name, setting, unit FROM pg_settings WHERE name IN ('shared_buffers','effective_cache_size','work_mem','huge_pages','random_page_cost','max_wal_size');"
grep -E "HugePages_(Total|Free)" /proc/meminfo

HugePages_Free should be several thousand pages below HugePages_Total, which means Postgres actually mapped them. Equal values mean it fell back to small pages and huge_pages = try swallowed the failure silently.

Then measure. Build a dataset large enough to be interesting but small enough to sit in shared buffers:

sudo -u postgres createdb bench
sudo -u postgres pgbench -i -s 800 bench
sudo -u postgres pgbench -c 32 -j 8 -T 60 -S bench
sudo -u postgres pgbench -c 32 -j 8 -T 60 bench

On an E-8 the read-only run lands in the low hundreds of thousands of transactions per second, and the read-write run in the mid thousands. Precise figures depend on the site and the dataset, so the useful signal is the order of magnitude: if the select-only test returns tens of thousands rather than hundreds of thousands, shared_buffers did not take effect or you are still benchmarking through a cold cache on the first run.

Finally, confirm the statistics extension is loaded, because it is the thing you will actually use every week afterwards:

sudo -u postgres psql bench -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"
sudo -u postgres psql bench -c "SELECT calls, round(mean_exec_time::numeric,2) AS ms, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;"

Afterwards

Turn on WAL archiving to a second instance in another city before this database has anything in it you would miss. Restoring a base backup you have never tested is not a backup, it is a hope, and the locations page exists partly so that your standby is not in the same building as your primary.