PostgreSQL: basic tuning

In this guide: PostgreSQL: basic tuning. Do not set work_mem too high.

If your site sits behind Cloudflare/a reverse proxy, pay attention to `X-Forwarded-*` headers and scheme (http/https) to avoid redirect loops. Prefer binding apps to `127.0.0.1` and exposing only nginx publicly.

If something goes wrong: check the service is running, listening on the expected port, and that your firewall allows the connection. For web services, `nginx -t` and `journalctl -u nginx` are good starting points. The biggest pitfall is setting `work_mem` too high. It multiplies by concurrency and can eat all RAM quickly.

After completing the steps below, verify the result: service status, logs, and network reachability. This saves hours when an issue shows up later.

Below you’ll find a quick checklist, verification commands, and common pitfalls. This helps you not only “do it”, but also confirm what a correct outcome looks like.

Quick checklist

• Bind apps to `127.0.0.1` and expose nginx publicly.
• Validate `X-Forwarded-Proto`/HTTPS scheme behind Cloudflare/proxies.
• Always run `nginx -t` before reloading nginx.
• Make one small change at a time and verify the result immediately.
• Keep notes of what you changed (file/command/time).

Verify the result

# Verify / sanity checks
sudo nginx -t || true
sudo systemctl status nginx --no-pager || true
curl -fsS -I http://127.0.0.1/ | head -n 20 || true
sudo tail -n 80 /var/log/nginx/error.log 2>/dev/null || true

Common pitfalls

• Redirect loops due to wrong http/https scheme behind a proxy.
• Proxying to an upstream that listens on the wrong address/port.

• shared_buffers: 20–25% RAM (start).
• max_connections: keep reasonable + use a pooler.
• work_mem: multiplies by concurrency.