How to Optimize PostgreSQL Performance on a VPS

Why Tune PostgreSQL?

Default PostgreSQL settings are deliberately conservative to run on minimal hardware. On a Breeze with dedicated resources, tuning key parameters can dramatically improve query throughput and response times for your applications.

Prerequisites

• A Breeze with at least 2 GB RAM
• PostgreSQL 14+ installed
• Root or sudo access

Step 1: Adjust Memory Settings

Edit the main configuration file:

sudo nano /etc/postgresql/16/main/postgresql.conf

Set these values based on your Breeze RAM (example for 4 GB):

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

Step 2: Tune WAL and Checkpoints

wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 2GB
min_wal_size = 512MB

Step 3: Optimize Connection Handling

max_connections = 100
random_page_cost = 1.1
effective_io_concurrency = 200

Set random_page_cost to 1.1 if your Breeze uses SSD storage, which most do.

Step 4: Restart and Verify

sudo systemctl restart postgresql
sudo -u postgres psql -c "SHOW shared_buffers;"

Additional Recommendations

• Use pg_stat_statements extension to identify slow queries
• Run ANALYZE regularly to keep statistics current
• Consider connection pooling with PgBouncer for high-concurrency workloads
• Enable log_min_duration_statement = 500 to log queries slower than 500ms