Connection Pooling and Configuration Tuning

-- postgresql.conf tuning (typical values for 16 GB RAM server)
-- shared_buffers = 4GB          -- PostgreSQL buffer cache (~25% of RAM)
-- effective_cache_size = 12GB   -- hint for planner (OS cache + shared_buffers)
-- work_mem = 64MB               -- per sort/hash operation (be careful with many connections)
-- maintenance_work_mem = 1GB    -- for VACUUM, CREATE INDEX, etc.
-- max_connections = 100         -- keep low; use PgBouncer for more app connections
-- checkpoint_completion_target = 0.9
-- wal_buffers = 64MB
-- random_page_cost = 1.1        -- SSD: lower value encourages index scans

-- Check current configuration
SHOW shared_buffers;
SHOW work_mem;
SHOW max_connections;
SELECT name, setting, unit FROM pg_settings WHERE name LIKE '%mem%';

-- Monitor active connections
SELECT pid, usename, application_name, state, query_start,
       NOW() - query_start AS duration, LEFT(query, 80) AS query_preview
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

-- Kill a long-running query (use with care)
SELECT pg_cancel_backend(pid);   -- sends SIGINT (graceful)
SELECT pg_terminate_backend(pid); -- sends SIGTERM (forceful)

-- Check cache hit ratio (should be > 99% for OLTP)
SELECT
  ROUND(100.0 * blks_hit / NULLIF(blks_hit + blks_read, 0), 2) AS cache_hit_ratio
FROM pg_stat_database
WHERE datname = current_database();