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 \u2014 memory allocation, query optimization, connection pooling, and maintenance routines.<\/p>\n\n\n\n
PostgreSQL’s memory settings in This is the in-memory cache for database pages. Set it to 25% of total VPS RAM<\/strong>. For a 2GB VPS:<\/p>\n\n\n\n Going above 25% leaves insufficient memory for OS cache, connections, and query operations. Below 15% forces excessive disk reads.<\/p>\n\n\n\n Tell PostgreSQL how much memory the OS and filesystem cache can provide. Set this to 75% of total RAM<\/strong>:<\/p>\n\n\n\n This value doesn’t allocate memory \u2014 it helps the query planner estimate whether index scans or sequential scans are cheaper. Underestimating leads to unnecessary sequential scans.<\/p>\n\n\n\n Controls memory per sort, hash join, or aggregation operation. Set conservatively because each concurrent query can use multiple work_mem slots:<\/p>\n\n\n\n If you see temporary files written to disk during 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.<\/p>\n\n\n\n Install and configure PgBouncer:<\/p>\n\n\n\n With Enable query logging in postgresql.conf:<\/p>\n\n\n\n Then analyze with pgBadger for formatted reports:<\/p>\n\n\n\n PostgreSQL’s MVCC creates dead tuples that must be reclaimed. Configure autovacuum aggressively on a VPS:<\/p>\n\n\n\n Monitor vacuum activity:<\/p>\n\n\n\npostgresql.conf<\/code> have the single biggest impact on performance. On a VPS, RAM is your scarcest resource \u2014 allocate it carefully.<\/p>\n\n\n\nshared_buffers<\/h3>\n\n\n\n
shared_buffers = 512MB<\/code><\/pre>\n\n\n\neffective_cache_size<\/h3>\n\n\n\n
effective_cache_size = 1536MB<\/code><\/pre>\n\n\n\nwork_mem<\/h3>\n\n\n\n
work_mem = 16MB # 2GB VPS\nwork_mem = 32MB # 4GB VPS\nwork_mem = 64MB # 8GB VPS<\/code><\/pre>\n\n\n\nEXPLAIN ANALYZE<\/code>, increase work_mem gradually. Watch for “work_mem: N bytes wanted” in logs to detect disk spills.<\/p>\n\n\n\nConnection Pooling with PgBouncer<\/h2>\n\n\n\n
sudo apt install pgbouncer\n\n# \/etc\/pgbouncer\/pgbouncer.ini\n[databases]\nmydb = host=127.0.0.1 port=5432 dbname=mydb\n\n[pgbouncer]\nlisten_addr = 127.0.0.1\nlisten_port = 6432\nauth_type = scram-sha-256\nauth_file = \/etc\/pgbouncer\/userlist.txt\npool_mode = transaction\ndefault_pool_size = 25\nmax_client_conn = 200<\/code><\/pre>\n\n\n\npool_mode = transaction<\/code>, PgBouncer reuses connections between transactions \u2014 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<\/a> for providers with enough RAM to run PostgreSQL efficiently.<\/p>\n\n\n\nQuery Optimization Techniques<\/h2>\n\n\n\n
Identify Slow Queries<\/h3>\n\n\n\n
log_min_duration_statement = 200 # Log queries taking 200ms+\nlog_connections = on\nlog_disconnections = on<\/code><\/pre>\n\n\n\nsudo apt install pgbadger\npgbadger \/var\/log\/postgresql\/postgresql-16-main.log -o report.html<\/code><\/pre>\n\n\n\nIndex Strategy<\/h3>\n\n\n\n
\n
pg_stat_user_indexes<\/code> to find them.<\/li>\n<\/ul>\n\n\n\nMaintenance and Vacuuming<\/h2>\n\n\n\n
autovacuum_max_workers = 3\nautovacuum_naptime = 30s\nautovacuum_vacuum_scale_factor = 0.01\nautovacuum_vacuum_threshold = 50<\/code><\/pre>\n\n\n\nSELECT schemaname, relname, n_dead_tup, last_autovacuum\nFROM pg_stat_user_tables\nWHERE n_dead_tup > 1000;<\/code><\/pre>\n\n\n\nQuick-Reference Tuning Cheat Sheet<\/h2>\n\n\n\n