Tuning PostgreSQL for VPS Workloads + PgBouncer

The Memory Mental Model

PostgreSQL memory is not Java heap. shared_buffers is not "give it everything"; it is a working cache layered on top of the operating system's own page cache. The kernel will already cache hot data files for you, and Postgres benefits more from leaving some RAM available to the OS for that than from claiming it all directly.

The other large memory pool — work_mem — is per-operation, not per-connection. A single complex query with three sorts can allocate 3 × work_mem. Multiply by your peak concurrent backends and that is your worst-case query memory budget. Get this wrong and the OOM killer eats your database.

The Memory Parameters That Matter

• • shared_buffers — cache for table and index pages. Start at 25% of RAM. Deviate down on memory-tight VPS where the OS page cache is doing more of the work.
• • effective_cache_size — planner hint, not an allocation. Set it to roughly 50–75% of RAM so the planner correctly favors index scans.
• • work_mem — sort and hash memory per operation. Compute as (RAM − shared_buffers − OS) / (max_connections × 2); raise it per-query for analytical work via SET LOCAL work_mem.
• • maintenance_work_mem — VACUUM, index builds, and pg_restore. Generous values cut maintenance windows substantially. 256 MB to 1 GB on a 4–8 GB VPS.
• • wal_buffers — leave at -1 (auto). Postgres sizes it from shared_buffers; manual tuning rarely helps.

Concrete Sizing Tables

Starting points. Measure with your workload and adjust. Numbers below assume a Postgres-only VPS with no other heavy workloads sharing the host.

Drop into /etc/postgresql/17/main/conf.d/10-tuning.conf as a separate file so you do not lose it when the package is upgraded. Restart with sudo systemctl restart postgresql@17-main.

Checkpoint and WAL Tuning

The defaults force checkpoints too often, causing periodic I/O storms. The right shape is "fewer, larger, smoother":

checkpoint_timeout = 15min
max_wal_size = 4GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9
wal_compression = on
wal_level = replica            # leave room for Part 6 streaming replication

Watch pg_stat_bgwriter over a representative workload. If checkpoints_req is climbing alongside checkpoints_timed, raise max_wal_size until it stops.

Autovacuum That Actually Matters

Default autovacuum is too lazy for write-heavy workloads. Make it more aggressive globally and dial back per-table for static tables:

autovacuum_vacuum_scale_factor = 0.05    # vacuum at 5% dead tuples (was 20%)
autovacuum_analyze_scale_factor = 0.02   # analyze at 2% changes (was 10%)
autovacuum_naptime = 30s
autovacuum_max_workers = 4
autovacuum_vacuum_cost_limit = 2000

Per-table override for a hot append-mostly table:

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_analyze_scale_factor = 0.005
);

Planner Settings for SSD Storage

The default random_page_cost = 4.0 assumes spinning disks. RamNode storage is NVMe; tell the planner:

random_page_cost = 1.1
effective_io_concurrency = 200
seq_page_cost = 1.0

Parallel Query Settings

max_worker_processes = 8
max_parallel_workers = 8
max_parallel_workers_per_gather = 4
max_parallel_maintenance_workers = 4
parallel_setup_cost = 100

Halve the worker counts on a 2-vCPU instance.

Logging for Performance Work

log_min_duration_statement = 250ms     # log anything slow
log_checkpoints = on
log_lock_waits = on
log_temp_files = 0
log_autovacuum_min_duration = 0

# pg_stat_statements + auto_explain (load via shared_preload_libraries):
shared_preload_libraries = 'pg_stat_statements,auto_explain'
auto_explain.log_min_duration = 1s
auto_explain.log_analyze = on
auto_explain.log_buffers = on

Why Connection Pooling Is Mandatory Above 50 Connections

Each Postgres backend is a full OS process with private memory, file descriptors, and a per-connection cost on the order of 5–10 MB at idle plus work_mem overhead during queries. At 200 idle connections you have spent a gigabyte of RAM doing nothing. PgBouncer fronts Postgres on port 6432, multiplexes thousands of client connections onto a small pool of real backends, and routinely cuts p99 latency in half on connection-spiky workloads.

Install PgBouncer 1.23 from PGDG

sudo apt install -y pgbouncer

PGDG ships PgBouncer 1.23+ which is the cutoff for protocol-level prepared statement support — the feature that finally makes transaction pooling safe for ORMs that prepare aggressively.

Transaction vs Session Pooling

• • session — connection is held for the entire client session. Safe but defeats most of the point of pooling.
• • transaction — connection is held for the duration of one transaction, then returned. Highest throughput. Default choice.
• • statement — connection released after every statement. Disallows multi-statement transactions; rarely the right answer.

Transaction pooling historically broke session-scoped state: prepared statements, SET, advisory locks, LISTEN/NOTIFY. PgBouncer 1.21+ resolved the prepared statement problem with protocol-level support — enable it with max_prepared_statements = 100.

Pool Sizing Math

[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3
max_prepared_statements = 100
server_idle_timeout = 600
server_lifetime = 3600

Rule of thumb: default_pool_size ≈ 2× number of vCPUs on the database. max_client_conn can be much larger because clients are cheap on the PgBouncer side.

auth_query — Drop the Static Password File

Maintaining userlist.txt per user is operational pain. Have PgBouncer ask Postgres for the password hash on demand:

-- in Postgres:
CREATE ROLE pgbouncer_auth LOGIN PASSWORD 'strong-internal-password';

CREATE OR REPLACE FUNCTION pgbouncer.user_lookup(in i_username TEXT,
  out uname TEXT, out phash TEXT) RETURNS record AS $
BEGIN
  SELECT usename, passwd FROM pg_shadow WHERE usename = i_username
    INTO uname, phash;
  RETURN;
END;
$ LANGUAGE plpgsql SECURITY DEFINER;

REVOKE ALL ON FUNCTION pgbouncer.user_lookup(TEXT) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION pgbouncer.user_lookup(TEXT) TO pgbouncer_auth;

auth_user = pgbouncer_auth
auth_query = SELECT uname, phash FROM pgbouncer.user_lookup($1)

systemd, Logs, and Live Monitoring

sudo systemctl enable --now pgbouncer
sudo journalctl -u pgbouncer -f

Connect to the admin console for live metrics:

psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer

pgbouncer=# SHOW POOLS;
pgbouncer=# SHOW STATS;
pgbouncer=# SHOW CLIENTS;

The numbers worth watching: cl_waiting (clients waiting for a server), sv_active, and total_wait_time. Sustained cl_waiting means raise the pool size.

Benchmarking the Difference with pgbench

# Initialize a 1 GB scale dataset:
sudo -u postgres pgbench -i -s 100 appdb

# Direct to Postgres on 5432 with 200 simulated clients:
pgbench -h 127.0.0.1 -p 5432 -U appuser -c 200 -j 4 -T 60 -P 5 appdb

# Through PgBouncer on 6432:
pgbench -h 127.0.0.1 -p 6432 -U appuser -c 200 -j 4 -T 60 -P 5 appdb

On a 4 GB / 2 vCPU RamNode VPS, pooling typically lifts TPS by 2–4× and lowers p95 latency by an order of magnitude under high concurrency. Capture the numbers — they make a great baseline for the rest of the series.

What's Next