Set Appropriate Connection Limits

Why This Matters

Too many connections exhaust memory and degrade performance. Each Postgres connection uses 1-3MB of RAM plus additional memory for queries. Setting max_connections too high can crash your database.

The Problem

-- Default max_connections = 100, but often increased blindly
SHOW max_connections;  -- 500 (way too high for 4GB RAM)

-- Each connection uses 1-3MB RAM
-- 500 connections * 2MB = 1GB just for connections!
-- Out of memory errors under load

The Solution

-- Formula: max_connections = (RAM in MB / 5MB per connection) - reserved
-- For 4GB RAM: (4096 / 5) - 10 = ~800 theoretical max
-- But practically, 100-200 is better for query performance

-- Recommended settings for 4GB RAM
ALTER SYSTEM SET max_connections = 100;

-- Also set work_mem appropriately
-- work_mem * max_connections should not exceed 25% of RAM
ALTER SYSTEM SET work_mem = '8MB';  -- 8MB * 100 = 800MB max

Monitor connection usage:

SELECT count(*), state FROM pg_stat_activity GROUP BY state;