PostgreSQL Performance Tuning on Cloud VPS

Why PostgreSQL Tuning Matters on Cloud VPS

A default PostgreSQL installation is configured conservatively to run on minimal hardware. On a ServerRaja Cloud VPS with modern NVMe SSDs, dedicated vCPUs, and generous RAM allocations, these defaults leave significant performance on the table. Proper tuning can yield 5-10x improvements in query throughput and reduce response times from seconds to milliseconds.

This guide covers the most impactful tuning parameters, organized by effort-to-benefit ratio, so you can get the biggest gains first.

Memory Configuration

Memory settings are the single most impactful tuning area. PostgreSQL relies heavily on shared memory for caching data, sorting, and query planning.

shared_buffers This is PostgreSQL's own shared memory cache for table and index data. Set it to 25% of total system RAM.

# postgresql.conf
# For an 8 GB RAM ServerRaja VPS:
shared_buffers = 2GB

# For a 16 GB RAM VPS: shared_buffers = 4GB

# For a 32 GB RAM VPS: shared_buffers = 8GB ```

effective_cache_size This tells the query planner how much memory is available for caching (including OS-level cache). Set it to 50-75% of total RAM.

effective_cache_size = 6GB  # For 8 GB RAM

work_mem Memory for individual sort and hash operations. Be careful—each connection can use this much memory per operation, and complex queries may use multiple.

# Conservative for 200 max connections
work_mem = 32MB

# More aggressive for fewer connections (e.g., behind PgBouncer) work_mem = 64MB ```

Formula: `work_mem = (RAM - shared_buffers) / (max_connections * 2)`

maintenance_work_mem Used for VACUUM, CREATE INDEX, and ALTER TABLE operations.

maintenance_work_mem = 1GB

Huge Pages On Linux, enabling huge pages reduces TLB misses for large shared_buffers.

# Calculate required huge pages
# shared_buffers = 4GB = 4096MB, huge page size = 2MB
# Need 2048 huge pages + ~10% overhead

echo 2250 | sudo tee /proc/sys/vm/nr_hugepages

# Make persistent echo "vm.nr_hugepages = 2250" | sudo tee -a /etc/sysctl.conf

# Tell PostgreSQL to use huge pages # In postgresql.conf: huge_pages = try ```

Write Performance Tuning

WAL (Write-Ahead Log) Settings

# Increase WAL buffer size
wal_buffers = 64MB

# Checkpoint tuning for write-heavy workloads checkpoint_completion_target = 0.9 max_wal_size = 4GB min_wal_size = 1GB ```

Asynchronous Commit For workloads that can tolerate a small window of data loss (e.g., logging, analytics):

# Per-session setting
SET synchronous_commit = off;

# Or in postgresql.conf for the instance synchronous_commit = off ```

This can improve write throughput by 2-3x, but committed transactions may be lost if PostgreSQL crashes within the `wal_writer_delay` window (~200ms).

Query Planner Tuning

Random Page Cost On SSDs (all ServerRaja VPS use NVMe), the default `random_page_cost` of 4.0 is too high. SSDs have nearly uniform access times for sequential and random reads.

random_page_cost = 1.1
seq_page_cost = 1.0

This change alone can cause the planner to choose index scans over sequential scans, dramatically improving query performance.

Parallel Query Execution

# Enable parallel queries
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4

# Lower threshold for parallel seq scans parallel_tuple_cost = 0.01 parallel_setup_cost = 100 min_parallel_table_scan_size = 8MB ```

Connection Pooling with PgBouncer

PostgreSQL creates a new process for each connection, which is expensive. PgBouncer maintains a pool of connections and multiplexes client requests.

sudo apt install pgbouncer -y
sudo nano /etc/pgbouncer/pgbouncer.ini

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

[pgbouncer] listen_addr = 127.0.0.1 listen_port = 6432 auth_type = md5 auth_file = /etc/pgbouncer/userlist.txt pool_mode = transaction max_client_conn = 1000 default_pool_size = 50 min_pool_size = 10 reserve_pool_size = 5 server_idle_timeout = 600 ```

`pool_mode = transaction` is the most efficient mode—connections are returned to the pool after each transaction completes.

Vacuum and Autovacuum Tuning

PostgreSQL uses MVCC, which creates dead tuples that need to be cleaned up by VACUUM.

# Aggressive autovacuum for write-heavy workloads
autovacuum = on
autovacuum_max_workers = 4
autovacuum_naptime = 30s
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.025
autovacuum_vacuum_cost_limit = 1000

Default `autovacuum_vacuum_scale_factor = 0.2` means autovacuum only triggers when 20% of rows are dead. For large tables, this can mean millions of dead rows. Lower it to 0.05 or even 0.01.

Check vacuum status:

SELECT
    schemaname,
    relname,
    n_dead_tup,
    n_live_tup,
    round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
    last_autovacuum,
    last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Monitoring and Diagnostics

Enable pg_stat_statements

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

-- Find slowest queries
SELECT
    query,
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS avg_ms,
    round((100 * total_exec_time / SUM(total_exec_time) OVER ())::numeric, 2) AS pct
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Cache Hit Ratio

SELECT
    sum(blks_hit) AS cache_hits,
    sum(blks_read) AS disk_reads,
    round(sum(blks_hit) * 100.0 / NULLIF(sum(blks_hit) + sum(blks_read), 0), 2) AS cache_hit_ratio
FROM pg_stat_database
WHERE datname = current_database();

The cache hit ratio should be above 99%. If it is lower, increase `shared_buffers` or investigate queries doing large sequential scans.

Lock Monitoring

SELECT
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_statement,
    blocking_activity.query AS blocking_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.relation = blocked_locks.relation
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

Complete Recommended postgresql.conf for 8GB VPS

# Memory
shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 32MB
maintenance_work_mem = 1GB

# WAL wal_buffers = 64MB checkpoint_completion_target = 0.9 max_wal_size = 4GB min_wal_size = 1GB

# Planner random_page_cost = 1.1 seq_page_cost = 1.0 effective_io_concurrency = 200

# Parallel queries max_parallel_workers_per_gather = 2 max_parallel_workers = 4

# Connections max_connections = 200

# Autovacuum autovacuum_max_workers = 3 autovacuum_vacuum_scale_factor = 0.05 autovacuum_analyze_scale_factor = 0.025

# Logging log_min_duration_statement = 200 log_checkpoints = on log_lock_waits = on shared_preload_libraries = 'pg_stat_statements' ```

After changing `postgresql.conf`, reload with:

sudo systemctl reload postgresql

Conclusion

PostgreSQL performance tuning is an iterative process. Start with the memory and planner settings in this guide—they provide the biggest improvements with the least effort. Use `pg_stat_statements` to identify your slowest queries, and monitor cache hit ratios and vacuum health regularly. On ServerRaja Cloud VPS with NVMe storage, a properly tuned PostgreSQL instance can handle tens of thousands of transactions per second.

PostgreSQL Performance Tuning on Cloud VPS: From Slow to Blazing