postgresql.conf: Memory, Connections, First Edits

The factory postgresql.conf is a polite guest. It assumes a small shared machine and will not steal RAM. On a dedicated VPS that is the wrong default. PostgreSQL 16 will run, and it will seq-scan, and it will spill sorts to disk, and you will blame the query. The first edits are memory and connections. Everything else — autovacuum, WAL, logging — comes after the postmaster can actually use the Intel Xeon Platinum and High-Speed SSD you paid for.

This post is the short list for a single-node cluster on Ubuntu, 4 to 16 GB of RAM, data directory on High-Speed SSD. It is not a warehouse config. It is the five knobs that change behaviour on the first restart.

Size shared_buffers and effective_cache_size

shared_buffers is PostgreSQL's own page cache. A working starting point on a dedicated VPS is 25 percent of RAM, capped around 8 GB until you measure. effective_cache_size is not an allocation. It tells the planner how much OS cache it can assume. Set it to roughly 75 percent of RAM so index scans look cheaper than they do with the 4 GB factory guess.

# /etc/postgresql/16/main/conf.d/memory.conf
shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 16MB
maintenance_work_mem = 256MB
wal_buffers = 64MB
huge_pages = off

The example fits an 8 GB VPS. On 4 GB, use shared_buffers = 1GB and effective_cache_size = 3GB. On 16 GB, shared_buffers = 4GB and effective_cache_size = 12GB. Do not set shared_buffers to half of RAM. The operating system still needs cache for files and WAL, and the kernel is good at that on High-Speed SSD.

work_mem is per sort or hash, per node, per connection. 16 MB looks small until 80 connections each build two hashes. 80 times 2 times 16 MB is 2.5 GB of extra RAM on a busy minute. Raise work_mem for a single session when a report needs it, not globally because one query spilled.

maintenance_work_mem is for VACUUM, CREATE INDEX, and ALTER TABLE. 256 MB is a sane floor on 8 GB. CREATE INDEX CONCURRENTLY on a large table can use more; raise it in that session.

Cap max_connections; do not inflate it

max_connections is the setting that quietly kills a VPS. Each connection has a backend, stacks, and a slice of work_mem. The factory 100 is already high for a 4 GB box. Application servers that open a connection per request will exhaust it and then the next request waits. The fix is PgBouncer in front, not 400 backends.

# /etc/postgresql/16/main/conf.d/connections.conf
max_connections = 60
superuser_reserved_connections = 3
idle_in_transaction_session_timeout = 30s
statement_timeout = 60s
lock_timeout = 15s

60 backends plus a pooler is enough for most single-app VPS deployments. If pg_stat_activity shows 50 idle connections and 4 active, you do not need more max_connections. You need a pool. Idle-in-transaction is a row lock that never finishes; 30 seconds is a loud timeout, and that is the point. statement_timeout of 60 seconds stops a runaway report from holding CPU until you notice.

Apply with a reload when the setting allows it. max_connections and shared_buffers need a restart.

sudo install -m 644 /dev/null /etc/postgresql/16/main/conf.d/memory.conf
sudo systemctl restart postgresql@16-main
sudo -u postgres psql -c "SHOW shared_buffers;"
sudo -u postgres psql -c "SHOW max_connections;"
sudo -u postgres psql -c "SHOW effective_cache_size;"

Put site-local knobs in conf.d so a package upgrade does not fight your edits in the main file. Confirm SHOW matches the file. If SHOW still prints 128MB, the include_dir is not loaded or you restarted the wrong unit.

Log the slow queries before you tune more

You cannot tune what you cannot see. After memory and connections, turn on duration logging. log_min_duration_statement of 500 ms is a start. Drop it to 100 ms once the noise is gone. log_checkpoints and log_lock_waits explain stalls that look like slow SQL.

# /etc/postgresql/16/main/conf.d/logging.conf
logging_collector = on
log_min_duration_statement = 500
log_checkpoints = on
log_lock_waits = on
log_temp_files = 0
log_line_prefix = '%m [%p] %u@%d '

log_temp_files = 0 logs every temp file, which is how you catch work_mem that is too low. Do not set random_page_cost to 1.0 on a wish. High-Speed SSD can justify 1.1, not a pretend that every page is free. Leave seq_page_cost at 1.0. Change random_page_cost only after EXPLAIN shows the planner refusing a good index.

checkpoint_completion_target of 0.9 and max_wal_size of 2GB reduce checkpoint spikes on a write-heavy app. Those are second-wave edits. If you change twenty knobs at once you will not know which one moved p99.

Listen_addresses belongs in this file only as a reminder: it is a bind, not a performance setting. Keep it on the private IP from the install guide. A faster cluster that listens on 0.0.0.0 is still a public database.

Takeaway

25 percent shared_buffers, 75 percent effective_cache_size, a low max_connections, small global work_mem, then slow-query logs. That is the first pass. You can size an 8 GB Ubuntu VPS on Netbay in Lucknow DC01 in under 60 seconds and drop these conf.d files in before you load data — netbayhosts.in.