How to fix FATAL: sorry, too many clients already in PostgreSQL

-- Check the current max_connections setting
SHOW max_connections;

-- Check how many connections are currently active
SELECT count(*) FROM pg_stat_activity;

-- See detailed connection info
SELECT pid, usename, state, query
FROM pg_stat_activity
ORDER BY state;

max_connections | 100
count           | 100  (or more if you have reserved slots filled)

-- Find idle connections that have been idle for over 10 minutes
SELECT pid, usename, state, state_change
FROM pg_stat_activity
WHERE state = 'idle'
AND now() - state_change > interval '10 minutes';

-- Terminate a specific connection (replace 12345 with PID)
SELECT pg_terminate_backend(12345);

-- Terminate ALL idle connections (use carefully!)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
AND pid <> pg_backend_pid();

-- Check PostgreSQL version and current setting
SELECT version();
SHOW max_connections;

-- Increase max_connections (requires restart)
ALTER SYSTEM SET max_connections = 200;

sudo systemctl restart postgresql

brew services restart postgresql

docker restart <container_id>

# Edit the config file
sudo nano /etc/postgresql/14/main/postgresql.conf

# Find and change the line:
# max_connections = 100
# To:
max_connections = 200

# Restart PostgreSQL
sudo systemctl restart postgresql

# Check available memory
free -h

# Install PgBouncer
sudo apt-get install pgbouncer  # Debian/Ubuntu
brew install pgbouncer           # macOS

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

[pgbouncer]
pool_mode = transaction  # or session
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3
max_db_connections = 100

# Start PgBouncer
pgbouncer /etc/pgbouncer/pgbouncer.ini

# Install PgPool
sudo apt-get install pgpool2

# Configure /etc/pgpool2/pgpool.conf
num_init_children = 32
max_pool = 4
child_life_time = 300

services:
  postgres:
    image: postgres:15
    environment:
      POSTGRES_DB: myapp
      POSTGRES_PASSWORD: secret
    ports:
      - "5432:5432"

  pgbouncer:
    image: pgbouncer:1.17
    depends_on:
      - postgres
    ports:
      - "6432:6432"
    environment:
      PGBOUNCER_DATABASES_HOST: postgres
      PGBOUNCER_DATABASES_PORT: 5432
      PGBOUNCER_DATABASES_DBNAME: myapp

// WRONG: Creates new client on each request
app.get('/api/users', async (req, res) => {
  const client = new pg.Client(dbConfig);
  await client.connect();  // New connection - leaks if not closed!
  const result = await client.query('SELECT * FROM users');
  // Missing: await client.end();
  res.json(result.rows);
});

// CORRECT: Reuse a single client instance
const client = new pg.Client(dbConfig);
await client.connect();

app.get('/api/users', async (req, res) => {
  const result = await client.query('SELECT * FROM users');
  res.json(result.rows);
});

// prisma.ts - Create singleton
import { PrismaClient } from '@prisma/client';

const globalForPrisma = global as unknown as { prisma: PrismaClient };

export const prisma =
  globalForPrisma.prisma ||
  new PrismaClient({
    log: ['query'],
  });

if (process.env.NODE_ENV !== 'production')
  globalForPrisma.prisma = prisma;

// Usage - reuse the singleton
const users = await prisma.user.findMany();

# WRONG: Creates new engine per request
def get_users():
    engine = create_engine('postgresql://user:pass@localhost/myapp')
    with engine.connect() as conn:
        pass  # Creates new connection pool each request

# CORRECT: Reuse engine with connection pooling
engine = create_engine(
    'postgresql://user:pass@localhost/myapp',
    poolclass=QueuePool,
    pool_size=10,
    max_overflow=20,
)

def get_users():
    with engine.connect() as conn:
        pass  # Reuses from pool

// lib/db.ts
import { PrismaClient } from '@prisma/client';

const globalForPrisma = global as unknown as { prisma: PrismaClient };

export const prisma =
  globalForPrisma.prisma ||
  new PrismaClient({
    log: process.env.NODE_ENV === 'development' ? ['error', 'warn'] : ['error'],
  });

if (process.env.NODE_ENV !== 'production') {
  globalForPrisma.prisma = prisma;
}

import { prisma } from '@/lib/db';

export async function GET() {
  const users = await prisma.user.findMany();
  return Response.json(users);
}

# settings.py
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'CONN_MAX_AGE': 600,  # Connection pooling TTL
        'OPTIONS': {
            'connect_timeout': 10,
        }
    }
}

# Next.js - run without hot reload
NODE_ENV=development next dev --no-swc

-- Adjust reserved connections for superuser operations
ALTER SYSTEM SET superuser_reserved_connections = 3;

-- Log connection usage
ALTER SYSTEM SET log_connections = on;
ALTER SYSTEM SET log_disconnections = on;

-- Restart PostgreSQL to apply changes
-- (Run: sudo systemctl restart postgresql)

-- After restart, check connection history in PostgreSQL log:
-- tail -f /var/log/postgresql/postgresql-15-main.log

-- See connection distribution by user and database
SELECT
  datname as database,
  usename as user,
  count(*) as connection_count
FROM pg_stat_activity
GROUP BY datname, usename
ORDER BY connection_count DESC;

-- See which queries are using connections
SELECT
  pid,
  usename,
  datname,
  state,
  query,
  state_change
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY state_change;

#!/bin/bash
# Check connections every 5 minutes
while true; do
  CONN_COUNT=$(psql -t -c "SELECT count(*) FROM pg_stat_activity;")
  MAX_CONN=100
  THRESHOLD=$((MAX_CONN - 10))

  if [ "$CONN_COUNT" -gt "$THRESHOLD" ]; then
    echo "WARNING: $CONN_COUNT of $MAX_CONN connections in use!"
    # Send alert to monitoring service
  fi

  sleep 300
done