Conn Limits

title: Set Appropriate Connection Limits impact: CRITICAL impactDescription: Prevent database crashes and memory exhaustion tags: connections, max-connections, limits, stability

Set Appropriate Connection Limits

Too many connections exhaust memory and degrade performance. Set limits based on available resources.

Incorrect (unlimited or excessive connections):

-- 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

Correct (calculate based on resources):

-- 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;

Reference: Database Connections