Default PostgreSQL configurations are designed to run on almost any hardware. That means they're terribly optimized for your specific two-gigabyte RAM virtual private server. If you leave shared_buffers at the default twelve megabytes, your database ignores most of the memory you pay for and hits the disk for queries that should live entirely in RAM. You'll see high IO wait times and sluggish web responses as soon as your application gets any real traffic. Tuning Postgres on a small instance isn't about complex enterprise settings. It's about matching memory limits to your actual hardware constraints.
When you spin up a fresh Linux instance, the default package manager installation points to a postgresql.conf file untouched by human hands. It's built to boot on a machine with 512 megabytes of RAM without crashing. You don't want a safe configuration. You want one that saturates your CPU and memory limits without triggering the kernel out-of-memory killer. If you manage long-running background tasks alongside your database, you'll also want to look at Systemd Services for Long-Running Processes to ensure your services restart cleanly when resource limits get tight.
Configuring Shared Buffers and Work Memory
The single most important setting in your configuration file is shared_buffers. This defines how much dedicated memory PostgreSQL uses for caching tables and indexes. The standard recommendation is twenty-five percent of your total system RAM. If you run a four-gigabyte VPS, set this to one gigabyte. Don't push it to fifty percent or higher because the operating system still needs free memory for the file system cache and other background tasks. If you set it too high, your server will start swapping to disk under load, which brings performance to a complete halt.
Next, you need to configure work_mem. Unlike shared_buffers, which is global, work_mem is allocated per operation within a query, such as a sort or a hash join. If a query requires multiple sorts, it might allocate several times the work_mem value. Setting this too high on a VPS with limited RAM invites out-of-memory kills from the kernel. Keep it modest at four to sixteen megabytes for general web applications, and only raise it for specific reporting sessions where complex analytical queries run in isolation.
Here's what your configuration block should look like in your main settings file:
shared_buffers = 512MB
work_mem = 8MB
maintenance_work_mem = 128MB
effective_cache_size = 1536MB
The maintenance_work_mem setting handles heavy operations like index creation and vacuuming. Since these run infrequently, you can give them a bit more breathing room without starving your running queries. The effective_cache_size doesn't actually allocate memory. It tells the query planner how much total memory is available for disk caching across both Postgres buffers and the operating system page cache. Keep it around seventy-five percent of your total system RAM so the planner prefers index scans over sequential table scans.
Managing Connection Limits and Memory Bloat
Every single connection to PostgreSQL spawns a dedicated server process. Each process consumes around ten to twenty megabytes of memory just by existing, even if it's sitting idle waiting for a web request. If your web framework opens a hundred connections by default, you've just burned two gigabytes of RAM before executing a single query. On a small VPS, this is a fast track to disaster. You need to drop your max_connections setting and introduce a connection pooler.
Never point your web application directly to Postgres if you have more than a handful of worker processes. Instead, install PgBouncer in front of it. PgBouncer maintains a lean pool of actual database connections while accepting hundreds of client connections from your application. You configure it in transaction pooling mode so connections return to the pool the moment a single query finishes executing.
Here's a standard configuration for PgBouncer to get you started:
[databases]
my_app = host=127.0.0.1 port=5432 dbname=my_app_production
[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 = 500
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 5
With this setup, your application connects to port 6432 instead of 5432. The pooler holds the heavy backend connections steady at twenty active slots while clients queue up seamlessly behind them. You'll stop seeing connection refused errors during traffic spikes, and your memory usage graph will flatten out. Remember to drop the database's internal max_connections down to something safe like fifty, since only PgBouncer and a few admin tools will talk to it directly now.
Verifying Performance and Monitoring Resource Usage
Once you restart your database and pooler, you can't just walk away and assume it's fine. You have to verify that your cache hit ratio is actually improving. Connect to your database shell and run a quick query against the internal statistics views to see how often your tables are served from RAM instead of disk.
Here's the diagnostic query you should run after a few hours of production traffic:
SELECT
sum(heap_blks_read) as disk_reads,
sum(heap_blks_hit) as buffer_hits,
round(sum(heap_blks_hit) * 100.0 / nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0), 2) as cache_hit_ratio
FROM pg_statio_user_tables;
If your cache hit ratio is sitting below ninety-five percent, your shared_buffers is still too small, or your database is simply too large for the VPS tier you bought. When you hit that wall, no amount of configuration tuning will save you. You'll need to add indexes for missing foreign keys or scale up your instance memory. Keep an eye on your disk space too, especially when logs grow large over time. Setting up proper log rotation with tools explained in Mastering Logrotate Configuration will keep your root partition from filling up and crashing the entire database service in the middle of the night.
