Connection pooling and configuration

-- PostgreSQL connection configuration

-- View max connections
SHOW max_connections;

-- Set max connections (requires restart)
-- postgresql.conf:
-- max_connections = 200

-- Reserved connections for superuser
-- superuser_reserved_connections = 3

-- View current connections
SELECT count(*) FROM pg_stat_activity;

-- Connections by database
SELECT
  datname,
  count(*) AS connections,
  max_conn,
  ROUND(100.0 * count(*) / max_conn, 2) AS pct_used
FROM pg_stat_activity
CROSS JOIN (SELECT setting::int AS max_conn FROM pg_settings WHERE name = 'max_connections') mc
GROUP BY datname, max_conn
ORDER BY connections DESC;

-- Connections by user
SELECT
  usename,
  count(*) AS connections
FROM pg_stat_activity
GROUP BY usename
ORDER BY connections DESC;

-- Connections by application
SELECT
  application_name,
  count(*) AS connections,
  state
FROM pg_stat_activity
GROUP BY application_name, state
ORDER BY connections DESC;

-- Set connection limit per database
ALTER DATABASE mydb CONNECTION LIMIT 100;

-- Set connection limit per user
ALTER ROLE app_user CONNECTION LIMIT 50;

-- Terminate idle connections
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND state_change < CURRENT_TIMESTAMP - INTERVAL '1 hour'
  AND pid != pg_backend_pid();

-- Connection pooling with application
-- Example: Node.js with pg pool
/*
const { Pool } = require('pg');

const pool = new Pool({
  host: 'localhost',
  port: 5432,
  database: 'mydb',
  user: 'app_user',
  password: 'password',
  max: 20,                    // Maximum pool size
  min: 5,                     // Minimum pool size
  idleTimeoutMillis: 30000,   // Close idle clients after 30s
  connectionTimeoutMillis: 2000, // Return error after 2s if no connection available
});

// Execute query
const result = await pool.query('SELECT * FROM users WHERE id = $1', [userId]);

// Transaction
const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('INSERT INTO orders ...');
  await client.query('UPDATE inventory ...');
  await client.query('COMMIT');
} catch (e) {
  await client.query('ROLLBACK');
  throw e;
} finally {
  client.release();
}
*/

-- PgBouncer admin commands (connect to pgbouncer admin console)
-- psql -p 6432 -U pgbouncer pgbouncer

-- SHOW pools;
-- SHOW clients;
-- SHOW servers;
-- SHOW stats;
-- RELOAD;  -- Reload config
-- PAUSE;   -- Pause all queries
-- RESUME;  -- Resume queries
-- KILL database;  -- Kill all connections to database

-- Connection pooling sizing calculation
-- Recommended pool size = ((core_count * 2) + effective_spindle_count)
-- For SSD: pool_size ≈ core_count * 2
-- Example: 4 cores = 8-10 connections per pool

-- Monitor connection pool saturation
-- Track: wait time, queue depth, connection acquisition time
-- Alert if: wait time > 100ms, acquisition time > 50ms

-- MySQL connection pooling with ProxySQL
/*
mysql_servers:
(
  {
    address="localhost"
    port=3306
    hostgroup=0
    max_connections=200
  },
  {
    address="replica1"
    port=3306
    hostgroup=1
    max_connections=200
  }
)

mysql_users:
(
  {
    username = "app_user"
    password = "password"
    default_hostgroup = 0
    max_connections = 100
    active = 1
  }
)
*/

-- MySQL connection limits
SHOW VARIABLES LIKE 'max_connections';
SET GLOBAL max_connections = 500;

-- Current connections
SHOW PROCESSLIST;
SELECT count(*) FROM information_schema.PROCESSLIST;

-- Connection errors
SHOW GLOBAL STATUS LIKE 'Aborted_connects';
SHOW GLOBAL STATUS LIKE 'Connection_errors%';