How to fix FATAL: out of memory in PostgreSQL

-- Connect to PostgreSQL
psql -U postgres

-- Show current work_mem setting (per operation)
SHOW work_mem;

-- Show shared_buffers (shared cache for all connections)
SHOW shared_buffers;

-- Show max_connections (each connection uses memory)
SHOW max_connections;

-- Show maintenance_work_mem (used by VACUUM, CREATE INDEX)
SHOW maintenance_work_mem;

-- Show effective_cache_size (query planner hint)
SHOW effective_cache_size;

# Linux: check free memory and swap
free -h

# Show memory breakdown
cat /proc/meminfo | grep -E "MemTotal|MemAvailable|SwapTotal|SwapFree"

# Check if swap is being used (high usage indicates memory pressure)
vmstat 1 5

# Docker/Kubernetes: check container limits
docker stats <container-id>  # For Docker containers
kubectl top pod <pod-name>    # For Kubernetes pods

Typical issue: work_mem=1GB with 100 connections = 100GB potential memory usage

Safe calculation:
- Available RAM = 32GB
- shared_buffers = 8GB (25%)
- Reserve for OS = 4GB
- Available for work_mem = 20GB
- If max_connections = 100, then work_mem should be 20GB / 100 = ~200MB

-- Enable logging of temporary files created
SET log_temp_files = 0;  -- Log all temp files (0 = no size limit)

-- Run a problematic query and check logs
SELECT * FROM large_table ORDER BY complex_expression;

-- Check PostgreSQL logs
tail -f /var/log/postgresql/postgresql.log
-- Look for "temporary file" entries indicating spilled sorts

-- Set to a safer value for this session
SET work_mem = '64MB';

-- Now run the problematic query
SELECT * FROM your_table ORDER BY column;

# Edit PostgreSQL configuration
sudo nano /etc/postgresql/15/main/postgresql.conf

# Find the line (or add it):
# work_mem = '4MB'                    # old value
work_mem = '64MB'                     # new value

# For systems with many connections, use even lower:
# work_mem = '32MB'   # 100 connections × 32MB = 3.2GB max
# work_mem = '16MB'   # 200 connections × 16MB = 3.2GB max

# Save and restart PostgreSQL
sudo systemctl restart postgresql

# Verify the change took effect
sudo -u postgres psql -c "SHOW work_mem;"

services:
  postgres:
    image: postgres:15
    environment:
      - POSTGRES_INITDB_ARGS="-c work_mem=64MB -c shared_buffers=512MB"

docker run -d \
  -e POSTGRES_INITDB_ARGS="-c work_mem=64MB" \
  postgres:15

Small server (2GB RAM, few connections):
  work_mem = 32MB

Medium server (16GB RAM, 50 connections):
  work_mem = 100MB (16GB / 50 connections / safety factor)

Large server (128GB RAM, 200 connections):
  work_mem = 200MB (128GB / 200 connections / safety factor)

Very high concurrency (500+ connections):
  work_mem = 16-32MB (risk of spilling to disk but prevents OOM)

-- Calculate buffer cache hit ratio
SELECT
  sum(heap_blks_read) as heap_read,
  sum(heap_blks_hit)  as heap_hit,
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM pg_statio_user_tables;

-- Should be > 99% for good performance
-- If < 90%, shared_buffers might be too small

# Edit PostgreSQL configuration
sudo nano /etc/postgresql/15/main/postgresql.conf

# Find and modify:
shared_buffers = '2GB'    # Old value (25% of RAM)
# Change to:
shared_buffers = '1GB'    # Reduced value

# Restart PostgreSQL
sudo systemctl restart postgresql

# Verify
sudo -u postgres psql -c "SHOW shared_buffers;"

-- Enable query logging
ALTER SYSTEM SET log_min_duration_statement = 1000;  -- Log queries > 1 second
ALTER SYSTEM SET log_statement = 'all';              -- Log all queries

-- Reload configuration
SELECT pg_reload_conf();

-- Or edit postgresql.conf and restart

-- Get detailed execution plan
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE country = 'US')
ORDER BY order_date DESC;

-- Look for:
-- - "Seq Scan" on large tables (add indexes)
-- - "Sort" or "Hash" with high memory usage (add indexes, adjust query)
-- - "Nested Loop" with large inner tables (refactor JOIN)

-- Create index on frequently filtered columns
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_status ON orders(status) WHERE status != 'completed';

-- Verify index is used
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

-- Bad: Fetch all rows then sort in memory
SELECT * FROM orders ORDER BY amount DESC;  -- May use work_mem

-- Good: Add LIMIT to reduce memory usage
SELECT * FROM orders ORDER BY amount DESC LIMIT 1000;

-- Better: Let database do pagination efficiently
SELECT * FROM orders ORDER BY amount DESC LIMIT 1000 OFFSET 0;

-- Bad: Multi-step JOIN with sorts (combines memory allocations)
SELECT c.id, o.id, COUNT(*)
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
LEFT JOIN items i ON o.id = i.order_id
GROUP BY c.id, o.id
ORDER BY COUNT(*) DESC;

-- Better: Use CTE to reduce intermediate result sets
WITH customer_orders AS (
  SELECT customer_id, COUNT(*) as order_count
  FROM orders
  GROUP BY customer_id
)
SELECT c.id, order_count
FROM customers c
LEFT JOIN customer_orders co ON c.id = co.customer_id
ORDER BY order_count DESC;

# Run EXPLAIN from command line
sudo -u postgres psql << EOF
EXPLAIN (ANALYZE, BUFFERS, FORMAT json)
SELECT * FROM orders WHERE created_at > '2024-01-01';
EOF

# Look for high "Memory" or "Peak Memory" in output

-- See active connections
SELECT pid, usename, application_name, state, query_start
FROM pg_stat_activity
WHERE state != 'idle';

-- Count by user
SELECT usename, count(*) FROM pg_stat_activity GROUP BY usename;

-- See max allowed
SHOW max_connections;

# Edit configuration
sudo nano /etc/postgresql/15/main/postgresql.conf

# Find max_connections (default 100)
max_connections = 200      # Old value

# Reduce to:
max_connections = 100      # Or appropriate for your system

# Safe calculation: max_connections × work_mem should be < 50% of RAM
# Example: 32GB RAM → 50% = 16GB → with 64MB work_mem → max 256 connections

# Use pgBouncer or pgpool to multiplex connections
# This allows 1000+ client connections using only 100 backend connections

# Install pgBouncer
sudo apt install pgbouncer

# Configure /etc/pgbouncer/pgbouncer.ini
[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
max_client_conn = 1000        # Client connections
default_pool_size = 20        # Backend connections per database
reserve_pool_size = 5

# Start pgBouncer
sudo systemctl start pgbouncer

# Connect through pgBouncer (port 6432 instead of 5432)
psql -h localhost -p 6432 -d mydb

# See current overcommit policy
cat /proc/sys/vm/overcommit_memory

# Possible values:
# 0 = Heuristic overcommit (default, usually OK)
# 1 = Always allow (permissive, risk of OOM killer)
# 2 = Strict, never overcommit (may cause allocation failures)

# If it's 2, check the overcommit ratio
cat /proc/sys/vm/overcommit_ratio
# Default is 50. You can increase it:
# sudo sysctl -w vm.overcommit_ratio=80

# Allow more overcommit by increasing ratio
sudo sysctl -w vm.overcommit_ratio=100
sudo sysctl -w vm.panic_on_oom=0  # Prevent panic, let OOM killer handle it

# Make permanent
echo "vm.overcommit_ratio=100" | sudo tee -a /etc/sysctl.conf
sudo sysctl -p

# Check current swap
free -h

# Create swap file if needed (for cloud instances without swap)
sudo fallocate -l 4G /swapfile
sudo chmod 600 /swapfile
sudo mkswap /swapfile
sudo swapon /swapfile

# Make permanent in /etc/fstab
echo "/swapfile none swap sw 0 0" | sudo tee -a /etc/fstab

# Set memory limits in docker-compose.yml
services:
  postgres:
    image: postgres:15
    mem_limit: '4g'           # Hard limit
    memswap_limit: '6g'       # Swap included

# Or in Kubernetes
resources:
  limits:
    memory: "4Gi"
  requests:
    memory: "2Gi"

-- Show autovacuum settings
SHOW autovacuum;
SHOW autovacuum_work_mem;
SHOW autovacuum_max_workers;

-- autovacuum_work_mem defaults to maintenance_work_mem (usually high)
-- With multiple workers, this multiplies

# Edit postgresql.conf
sudo nano /etc/postgresql/15/main/postgresql.conf

# Find or add:
autovacuum_work_mem = '256MB'        # Instead of maintenance_work_mem
autovacuum_max_workers = 2           # Reduce parallel workers
maintenance_work_mem = '512MB'       # Maintenance operations (VACUUM, CREATE INDEX)

# Restart
sudo systemctl restart postgresql

-- Manually trigger vacuum outside peak hours
VACUUM ANALYZE;

-- Or use pg_cron extension
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- Vacuum at 3 AM
SELECT cron.schedule('vacuum-jobs', '0 3 * * *', 'VACUUM ANALYZE;');

# Check current hardware
free -h
nproc                    # CPU cores
uname -m                 # Architecture

# For cloud servers, consider upgrading instance type
# AWS: t3.medium → t3.large → t3.xlarge
# Azure: Standard_B2s → Standard_B4ms → Standard_D4s_v3
# GCP: e2-medium → e2-standard-2 → e2-standard-4

Development/Testing:
  - 2GB RAM
  - 1-2 CPU cores
  - Basic SSD

Small production (< 100 connections):
  - 8GB RAM
  - 2-4 CPU cores
  - Fast SSD (IOPS: 1000+)

Medium production (100-500 connections):
  - 32GB RAM
  - 8 CPU cores
  - Fast SSD (IOPS: 5000+)
  - Dedicated network

Large production (500+ connections):
  - 64GB+ RAM
  - 16+ CPU cores
  - NVME SSD
  - Network optimization

# Create 8GB swap file
sudo fallocate -l 8G /swapfile
sudo chmod 600 /swapfile
sudo mkswap /swapfile
sudo swapon /swapfile

# Check
free -h

# Make permanent
echo "/swapfile none swap sw 0 0" | sudo tee -a /etc/fstab

# Note: Swap is much slower than RAM, but prevents crashes