PostgreSQL's defaults are written for a machine from 2005 sharing its disk with everything else. On a VPS-4 with 8 GB of RAM and NVMe, ten settings turn it from cautious into fast, and none of them is exotic. These are the values we use for the PostgreSQL setups our team runs, with the symptom each one fixes so you can tell which ones you actually need.
Memory: four settings
shared_buffers = 2GB
The cache PostgreSQL manages itself. A quarter of RAM is the well-worn rule and it holds on Linux: beyond that, the kernel's page cache does the same job with less double-buffering. Symptom of the default (128 MB): high read IOPS on tables that should fit in memory.
effective_cache_size = 6GB
Not an allocation, a hint: how much memory the planner may assume is available for caching, including the kernel's page cache. Set it to about 75% of RAM. Symptom: the planner prefers sequential scans over index scans on medium tables because it believes reads are expensive.
work_mem = 32MB
Per sort or hash operation, per query, so a complex query can use several times this. With 50 connections, 32 MB is safe on 8 GB; raise it per session for reporting queries with SET work_mem = '256MB'. Symptom of the default (4 MB): EXPLAIN ANALYZE shows “external merge Disk” on sorts.
maintenance_work_mem = 512MB
Used by VACUUM, CREATE INDEX and ALTER TABLE. Bigger makes index builds and vacuum noticeably faster and it is rarely used by more than one process at a time. Symptom: index creation on a large table takes far longer than the row count suggests.
Checkpoints: the write spike fix
checkpoint_completion_target = 0.9 and max_wal_size = 4GB
PostgreSQL writes changes to the WAL first and flushes dirty pages to the data files at checkpoints. With the defaults, checkpoints come every minute or so under write load and flush in a burst; latency spikes follow. Letting the checkpoint spread over 90% of the interval and allowing 4 GB of WAL between checkpoints turns the spikes into a steady trickle. Symptom: periodic latency spikes every few minutes that line up with checkpoint starting in the log; checkpoints_req in pg_stat_bgwriter climbing faster than checkpoints_timed.
wal_compression = on
Compresses full-page images in the WAL. Costs a little CPU, saves a lot of write bandwidth, and on a VPS with an IOPS budget the trade is always worth it.
Tell the planner about NVMe
random_page_cost = 1.1 and effective_io_concurrency = 200
The default random_page_cost of 4 tells the planner that a random read costs four times a sequential one, which was true for spinning disks and is false on NVMe. At 1.1 the planner picks index scans where they win. effective_io_concurrency lets bitmap scans prefetch; 200 is the right order of magnitude for NVMe. Symptom: queries with selective WHERE clauses still choose sequential scans.
Autovacuum: keep it aggressive
autovacuum_vacuum_scale_factor = 0.05 and autovacuum_vacuum_cost_limit = 1000
The default waits for 20% of a table to be dead before vacuuming, which on a 50-million-row table means 10 million dead rows and bloated indexes. 5% keeps tables tight; raising the cost limit lets vacuum actually finish on NVMe instead of throttling itself to the disk speed of 2005. For very hot tables, set the scale factor per table with ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = 0.01). Symptom: a table's size on disk grows while its row count does not; n_dead_tup in pg_stat_user_tables in the millions.
Connections: pool them
max_connections = 100, then pgbouncer
Every PostgreSQL connection is a process with its own memory. A web app that opens 200 connections from 20 workers is spending RAM on idle processes and CPU on context switches. Leave max_connections at 100 and put pgbouncer in transaction-pooling mode in front once you pass about 50: the app keeps its 200 connections to the pooler, the database sees 20. Symptom: FATAL: sorry, too many clients already, or memory pressure with mostly idle connections in pg_stat_activity.
The whole file
# /etc/postgresql/16/main/conf.d/tuned.conf (8 GB VPS, NVMe)
shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 32MB
maintenance_work_mem = 512MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB
wal_compression = on
random_page_cost = 1.1
effective_io_concurrency = 200
autovacuum_vacuum_scale_factor = 0.05
autovacuum_vacuum_cost_limit = 1000
max_connections = 100Restart PostgreSQL after changing shared_buffers or max_connections; the rest apply with SELECT pg_reload_conf(). For a 16 GB VPS-8 double the first two lines; for a 4 GB plan halve them. The rest do not depend on RAM.
Measure before and after
Enable pg_stat_statements and look at the top queries by total time before you change anything; tuning without that view is guessing. After the change, the numbers to watch are pg_stat_bgwriter.checkpoints_req (should be near zero), the disk read rate (should drop as the cache warms), and the p99 of your application's database calls. On our fleet, this file alone typically halves the p99 of a read-heavy web application and removes the periodic write spikes entirely.