PostgreSQL Performance Tuning on a VPS: Memory, Query Optimization, and Connection Pooling

PostgreSQL is the go-to open-source relational database for applications requiring advanced concurrency, data integrity, and analytical capabilities. However, on a VPS with limited resources, PostgreSQL’s conservative defaults can waste memory and produce sluggish queries. This guide covers practical PostgreSQL tuning for VPS environments — memory allocation, query optimization, connection pooling, and maintenance routines.

Memory Configuration: The postgresql.conf Foundation

PostgreSQL’s memory settings in postgresql.conf have the single biggest impact on performance. On a VPS, RAM is your scarcest resource — allocate it carefully.

shared_buffers

This is the in-memory cache for database pages. Set it to 25% of total VPS RAM. For a 2GB VPS:

shared_buffers = 512MB

Going above 25% leaves insufficient memory for OS cache, connections, and query operations. Below 15% forces excessive disk reads.

effective_cache_size

Tell PostgreSQL how much memory the OS and filesystem cache can provide. Set this to 75% of total RAM:

effective_cache_size = 1536MB

This value doesn’t allocate memory — it helps the query planner estimate whether index scans or sequential scans are cheaper. Underestimating leads to unnecessary sequential scans.

work_mem

Controls memory per sort, hash join, or aggregation operation. Set conservatively because each concurrent query can use multiple work_mem slots:

work_mem = 16MB   # 2GB VPS
work_mem = 32MB   # 4GB VPS
work_mem = 64MB   # 8GB VPS

If you see temporary files written to disk during EXPLAIN ANALYZE, increase work_mem gradually. Watch for “work_mem: N bytes wanted” in logs to detect disk spills.

Connection Pooling with PgBouncer

Each PostgreSQL connection consumes about 5-10MB of RAM. A web application with 100 concurrent connections uses 500MB-1GB before any queries run. PgBouncer solves this by multiplexing connections.

Install and configure PgBouncer:

sudo apt install pgbouncer

# /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 = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
default_pool_size = 25
max_client_conn = 200

With pool_mode = transaction, PgBouncer reuses connections between transactions — perfect for web applications. This cuts PostgreSQL’s active connections from 100+ to just 25, saving hundreds of megabytes of RAM. Check our VPS comparison table for providers with enough RAM to run PostgreSQL efficiently.

Query Optimization Techniques

Identify Slow Queries

Enable query logging in postgresql.conf:

log_min_duration_statement = 200  # Log queries taking 200ms+
log_connections = on
log_disconnections = on

Then analyze with pgBadger for formatted reports:

sudo apt install pgbadger
pgbadger /var/log/postgresql/postgresql-16-main.log -o report.html

Index Strategy

• B-tree indexes for equality and range queries on high-cardinality columns.
• BRIN indexes for large tables where data is physically ordered (time-series data). BRIN indexes use 100x less space than B-tree on append-only tables.
• GIN indexes for full-text search and JSONB queries.
• Remove unused indexes — they slow down writes and consume disk. Use pg_stat_user_indexes to find them.

Maintenance and Vacuuming

PostgreSQL’s MVCC creates dead tuples that must be reclaimed. Configure autovacuum aggressively on a VPS:

autovacuum_max_workers = 3
autovacuum_naptime = 30s
autovacuum_vacuum_scale_factor = 0.01
autovacuum_vacuum_threshold = 50

Monitor vacuum activity:

SELECT schemaname, relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000;

Quick-Reference Tuning Cheat Sheet

Parameter	1GB VPS	2GB VPS	4GB VPS
shared_buffers	256MB	512MB	1GB
effective_cache_size	768MB	1536MB	3GB
work_mem	8MB	16MB	32MB
maintenance_work_mem	64MB	128MB	256MB
random_page_cost	1.1 (SSD)	1.1 (SSD)	1.1 (SSD)
effective_io_concurrency	200	200	200

Adjust random_page_cost to 1.1 if your VPS uses NVMe or SSD storage (default 4.0 is for HDDs). This tells the planner that random I/O is fast and encourages index usage. For more PostgreSQL-friendly VPS plans, compare storage types and RAM configurations on our site.