PostgreSQL’s defaults are deliberately conservative — they let it start on a tiny machine. On a dedicated VPS, a few settings make a large difference. But the biggest gains usually come from indexes and queries, so tune with measurements, not folklore.

Settings live in postgresql.conf (Debian/Ubuntu: /etc/postgresql/<version>/main/). Better: put overrides in conf.d/ or use ALTER SYSTEM, which writes postgresql.auto.conf:

ALTER SYSTEM SET shared_buffers = '1GB';
SELECT pg_reload_conf();     -- some settings need a restart instead

Check a setting and whether it needs a restart:

SELECT name, setting, unit, context FROM pg_settings WHERE name = 'shared_buffers';

context = postmaster means restart required.

Memory

shared_buffers — PostgreSQL’s own cache. Start at ~25% of RAM on a dedicated database server (e.g. 1 GB on 4 GB). More isn’t always better, because the OS page cache also caches data. Restart required.

effective_cache_size — not an allocation, just a hint to the planner about how much data the OS and PostgreSQL can cache together. Set to 50–75% of RAM. Affects whether indexes look attractive.

work_mem — memory per sort/hash operation, per query node, per connection. Default 4 MB is low for analytics, but the total can multiply quickly: 100 connections × several operations × 64 MB can exhaust RAM. Start with 16–32 MB; raise per session for heavy reports (SET work_mem = '256MB';). Look for “external merge Disk” in EXPLAIN ANALYZE output as a sign it’s too small.

maintenance_work_mem — for VACUUM, CREATE INDEX, ALTER TABLE. 256 MB–1 GB speeds up maintenance.

Example for a 4 GB VPS dedicated to PostgreSQL:

shared_buffers = 1GB
effective_cache_size = 3GB
work_mem = 16MB
maintenance_work_mem = 256MB

If the web app shares the server, reduce these accordingly.

WAL and checkpoints

wal_buffers = 16MB
min_wal_size = 1GB
max_wal_size = 4GB
checkpoint_completion_target = 0.9

A larger max_wal_size means fewer checkpoints, smoothing write I/O spikes on write-heavy workloads. Watch the logs for “checkpoints are occurring too frequently”.

Storage

On SSD storage, tell the planner random reads are cheap:

random_page_cost = 1.1
effective_io_concurrency = 200

The default random_page_cost = 4 assumes spinning disks and can make the planner avoid good index scans.

Connections

Each PostgreSQL connection is a separate process using several MB of memory. Hundreds of connections hurt performance even when idle.

max_connections = 100

If your app (or several app servers, PHP-FPM workers, serverless functions) needs more, put PgBouncer in front instead of raising the limit — see PgBouncer connection pooling.

Autovacuum

PostgreSQL’s MVCC leaves dead row versions after updates and deletes; autovacuum cleans them and updates planner statistics. Don’t disable it. On busy tables, make it more aggressive:

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.02, autovacuum_analyze_scale_factor = 0.01);

Details: VACUUM and autovacuum.

Find slow queries

Enable pg_stat_statements — the single most useful tuning tool:

shared_preload_libraries = 'pg_stat_statements'

After a restart:

CREATE EXTENSION pg_stat_statements;
SELECT round(total_exec_time::numeric, 0) AS total_ms, calls,
       round(mean_exec_time::numeric, 1) AS mean_ms, left(query, 80) AS query
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

Also log slow statements:

log_min_duration_statement = 500     # ms

Then EXPLAIN (ANALYZE, BUFFERS) the worst queries and add the right indexes — see database indexing basics.

Useful checks

-- Cache hit ratio (aim for > 99% on OLTP workloads)
SELECT sum(blks_hit) * 100.0 / nullif(sum(blks_hit) + sum(blks_read), 0) AS hit_pct FROM pg_stat_database;

-- Tables with the most sequential scans (missing indexes?)
SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables ORDER BY seq_scan DESC LIMIT 10;

-- Unused indexes (cost writes, give nothing)
SELECT relname, indexrelname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0;

Configuration helpers

Tools like PGTune suggest starting values from RAM, CPU and workload type. Treat them as a starting point, then adjust based on monitoring.

OS level

When the working set no longer fits in memory despite good indexes, add RAM — our VPS plans scale up to 40 GB.

  • PostgreSQL
  • Performance
  • Databases