PostgreSQL's default configuration is conservative, designed to run reasonably on minimal hardware — tuning key parameters to match your actual VPS resources can substantially improve performance.
Key Memory Parameters
sudo nano /etc/postgresql/16/main/postgresql.confshared_buffers
shared_buffers = 2GBPostgreSQL's dedicated memory cache — a common starting recommendation is roughly 25% of total system RAM, though this varies with workload specifics.
effective_cache_size
effective_cache_size = 6GBAn estimate of memory available for disk caching (including OS-level caching) — doesn't allocate memory itself, but helps the query planner make better decisions; typically set to 50-75% of total system RAM.
work_mem
work_mem = 32MBMemory available per query operation (sorts, hash joins) — too low causes excessive disk-based operations for complex queries; too high risks memory exhaustion with many concurrent connections, since this is allocated per operation, potentially multiple times per query.
maintenance_work_mem
maintenance_work_mem = 512MBMemory for maintenance operations (VACUUM, index creation) — can be set higher than work_mem since these operations run less frequently and less concurrently.
Connection and Concurrency Settings
max_connections = 100Set based on actual expected concurrent connections — excessive max_connections without adequate memory per connection can cause resource exhaustion; consider connection pooling (see How to Set Up Database Connection Pooling) rather than simply raising this indefinitely.
Write-Ahead Log (WAL) Settings
wal_buffers = 16MB
checkpoint_completion_target = 0.9Spreading checkpoint I/O over more time (checkpoint_completion_target closer to 1.0) reduces I/O spikes at checkpoint time, generally smoother for production workloads.
Storage-Specific Tuning (SSD/NVMe)
random_page_cost = 1.1The default assumes spinning disk characteristics; on SSD/NVMe storage (standard for modern VPS), lowering this closer to seq_page_cost (default 1.0) better reflects actual random I/O performance, improving query planner decisions.
Applying Configuration Changes
sudo systemctl restart postgresqlMost memory-related settings require a restart; some can be reloaded without a full restart (sudo systemctl reload postgresql) — check PostgreSQL's documentation for which specific parameters require which action.
Using pgtune for a Starting Point
Several online "pgtune" style calculators generate a reasonable starting configuration based on your specific RAM, CPU count, and workload type — a good starting point to then refine based on actual observed performance.
Monitoring Query Performance
See How to Monitor Database Performance and Slow Queries for identifying specific slow queries that configuration tuning alone won't fix — often, query/index optimization matters more than server-level tuning for genuinely slow specific operations.
Testing Changes Incrementally
Change one or a few related parameters at a time and measure actual impact, rather than applying many changes simultaneously — makes it much easier to understand what specifically helped (or hurt) performance.
Common Errors
PostgreSQL fails to start after configuration change — check sudo journalctl -u postgresql for the specific error; often a memory setting exceeding what's actually available on the VPS.
Continue Reading
- How to Set Up Database Connection Pooling (PgBouncer, ProxySQL)
- How to Monitor Database Performance and Slow Queries
- How to Choose the Right VPS Specs for a Database Server
Browse more articles in Databases.
