Running PostgreSQL in production
PostgreSQL will run happily on its defaults for exactly as long as your data is small. On a real dedicated server with real memory, a few settings turn it from “fine” into “fast”.
The PostgreSQL defaults assume a small, shared machine. On a dedicated server with 64 GB of RAM, the out-of-the-box settings use a fraction of it. Four parameters do most of the work.
Give it memory
shared_buffers— around 25% of RAMeffective_cache_size— around 50–75% of RAM; a hint, not an allocationwork_mem— per-sort, so raise it carefully; a big value times many connections is a lot of memorymaintenance_work_mem— higher helps vacuums and index builds
Match storage to the engine
Put the data directory on NVMe and the engine will reward you — see SAS, SATA and NVMe. Raise checkpoint_timeout and max_wal_size so Postgres is not constantly checkpointing under write load, and set random_page_cost=1.1 on SSD so the planner stops pretending seeks are expensive.
Connections are not free
Each connection is a process with its own memory. A few hundred idle connections waste real RAM. Keep max_connections modest and put a pooler in front — see connection pooling — rather than raising it into the thousands.
Back up with the WAL, not just a dump
A nightly pg_dump is a floor, not a strategy — it loses everything since the last dump. For anything important, archive the write-ahead log so you can restore to any point in time. Then test the restore, because an untested backup is a rumour.
Let autovacuum do its job
Postgres cleans up dead rows in the background. Do not turn autovacuum off; if anything, make it more aggressive on busy tables. A database that is not being vacuumed slowly bloats and slows — and the fix at that point is painful.
Still stuck? Mail support@novogara.com — an engineer answers, at any hour. Back to the knowledge base