-- Identify the "Current Reality" of the engine.
-- This is the single most important query in your performance toolkit.
-- Run it first, every time. It tells you WHERE the database is spending time.
SELECT 
    state,
    wait_event_type,          -- NULL = actively using CPU; non-NULL = waiting
    wait_event,               -- Specific lock/IO/event the backend is stalled on
    count(*) as count,
    round(100.0 * count(*) / sum(count(*)) over(), 2) as pct  -- percentage of active backends
FROM pg_stat_activity
WHERE state != 'idle'         -- Exclude connections doing nothing
GROUP BY 1, 2, 3
ORDER BY 4 DESC;

# Check context switches per second
vmstat 1
# 'cs' column > 100k/sec usually indicates a problem (e.g., too many connections)

iostat -xy 1
# If await > 5ms on an NVMe drive, the storage is saturated.
# If %util is 100% but rkB/s is low, you have a Random I/O bottleneck.

# Profile all postgres backends for 60 seconds.
# -F 99: Sample at 99 Hz (not 100, to avoid aliasing with periodic tasks).
# -g: Capture full call stacks (essential for useful flamegraphs).
# -p: Target only postgres backend processes.
perf record -F 99 -g -p $(pgrep -d',' postgres) -- sleep 60

# Generate the SVG (requires Brendan Gregg's FlameGraph tools)
perf script | ./stackcollapse-perf.pl | ./flamegraph.pl > db_profile.svg
# Open db_profile.svg in a browser. Click on frames to zoom in.

-- Identify queries with the highest I/O impact.
-- This is your "top offenders" list. Fix these first.
SELECT 
    query,
    calls,                        -- How often this query runs
    round(total_exec_time::numeric, 2) as total_ms,  -- Cumulative wall-clock time
    shared_blks_read + shared_blks_written as io_blocks,  -- Total disk I/O in 8KB blocks
    round(100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0), 2) as hit_ratio
FROM pg_stat_statements
ORDER BY io_blocks DESC          -- Sort by I/O impact, not just time
LIMIT 10;

-- Find queries that are spilling to disk.
-- Any row in this result is a query that could be dramatically faster
-- with more work_mem or a query rewrite that reduces intermediate results.
SELECT query, temp_blks_read, temp_blks_written
FROM pg_stat_statements
WHERE temp_blks_read > 0
ORDER BY 2 DESC;

WITH RECURSIVE blockers AS (
  SELECT 
    pid, 
    pg_blocking_pids(pid) AS pids 
  FROM pg_stat_activity 
  WHERE cardinality(pg_blocking_pids(pid)) > 0
  UNION ALL
  SELECT 
    s.pid, 
    pg_blocking_pids(s.pid) 
  FROM pg_stat_activity s 
  JOIN blockers b ON s.pid = ANY(b.pids)
)
SELECT 
  pid, 
  state, 
  wait_event, 
  query 
FROM pg_stat_activity 
WHERE pid IN (SELECT unnest(pids) FROM blockers)
  AND pid NOT IN (SELECT pid FROM blockers);

# pgbouncer.ini for high performance

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
# Connection pooling mode
# "transaction" returns connection to pool after each transaction completes.
# "session" holds connection for entire client session (like no pooling).
# "statement" returns after each statement (breaks multi-statement transactions).
pool_mode = transaction  # Best for most workloads; use "session" if you need prepared statements

# Pool sizing -- the most important tuning parameter
default_pool_size = 20      # Connections per user/database pair (start low, increase if needed)
min_pool_size = 5           # Keep some connections warm (avoids cold-start latency)
reserve_pool_size = 5       # Extra for traffic bursts
reserve_pool_timeout = 3    # Seconds to wait before dipping into the reserve pool

# Connection limits
max_client_conn = 1000      # Max client connections to pgbouncer (can be very high)
max_db_connections = 50     # Max connections to actual database (this is the real constraint)

# Timeouts
query_timeout = 30          # Kill query if longer than 30s
client_idle_timeout = 600   # Close idle clients after 10min
server_idle_timeout = 60    # Close idle server connections

# Performance
tcp_keepalive = 1
tcp_keepidle = 60
tcp_keepintvl = 10
tcp_keepcnt = 3

# Logging
log_connections = 0         # Reduce log spam in production
log_disconnections = 0
log_pooler_errors = 1

# Admin
admin_users = pgbouncer_admin
stats_users = pgbouncer_stats

-- Memory settings for different workload types

-- OLTP workload (many small queries, many connections)
shared_buffers = '8GB'              -- ~25% of RAM. Above 8-16GB, diminishing returns
                                    -- because the OS page cache handles the rest.
effective_cache_size = '24GB'       -- Tell the planner how much total cache exists
                                    -- (shared_buffers + OS cache). Does NOT allocate memory.
work_mem = '64MB'                   -- Conservative for OLTP. Many connections = many
                                    -- concurrent operations, each consuming work_mem.
maintenance_work_mem = '2GB'        -- For VACUUM, CREATE INDEX. Only a few run at once,
                                    -- so this can be much larger than work_mem.
wal_buffers = '64MB'                -- WAL insertion buffer. Default is too small for
                                    -- write-heavy workloads. 64MB is a safe upper bound.

-- OLAP workload (few large queries, few connections)
shared_buffers = '32GB'             -- Larger because analytics scans more data.
effective_cache_size = '96GB'       
work_mem = '2GB'                    -- Much larger because OLAP queries have complex
                                    -- sorts and hash joins. Acceptable because there
                                    -- are only 5-20 concurrent queries, not 500.
maintenance_work_mem = '4GB'
max_parallel_workers_per_gather = 8 -- Parallelism is critical for large scans/aggregations.

-- Work_mem calculation for hash joins:
/*
 * Memory needed = (inner_rows × tuple_width × hash_overhead)
 * 
 * For 1M rows, 100 bytes/row:
 * Memory = 1,000,000 × 100 × 1.5 ≈ 150MB
 * 
 * Set work_mem = 256MB to fit with margin
 * 
 * Warning: work_mem is PER OPERATION
 * Complex query with 5 sorts/hashes = 5 × work_mem
 * With 100 connections: potential 5 × 256MB × 100 = 128GB!
 */

-- Current locks (who's blocking whom)
SELECT 
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_statement,
    blocking_activity.query AS blocking_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity 
    ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks 
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity 
    ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

-- Lock types and their conflicts
/*
┌─────────────────────────────────────────────────────────────────────────────┐
│                      LOCK CONFLICT MATRIX                                    │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│ Requested →   ACCESS  ROW    ROW    SHARE  SHARE  SHARE   EXCL   ACCESS    │
│ Held ↓        SHARE   SHARE  EXCL   UPDATE ROW    UPDATE         EXCL      │
│                                            EXCL   EXCL                       │
│ ─────────────────────────────────────────────────────────────────────────── │
│ ACCESS SHARE    -       -      -      -      -      -       -      X        │
│ ROW SHARE       -       -      -      -      -      -       X      X        │
│ ROW EXCLUSIVE   -       -      -      -      -      X       X      X        │
│ SHARE UPDATE EX -       -      -      -      X      X       X      X        │
│ SHARE           -       -      -      -      X      X       X      X        │
│ SHARE ROW EXCL  -       -      X      X      X      X       X      X        │
│ EXCLUSIVE       -       X      X      X      X      X       X      X        │
│ ACCESS EXCL     X       X      X      X      X      X       X      X        │
│                                                                              │
│ X = Conflict (must wait)                                                    │
│ - = No conflict (can proceed)                                               │
│                                                                              │
│ Common operations and their locks:                                          │
│ • SELECT: ACCESS SHARE                                                      │
│ • INSERT/UPDATE/DELETE: ROW EXCLUSIVE                                       │
│ • CREATE INDEX CONCURRENTLY: SHARE UPDATE EXCLUSIVE                         │
│ • VACUUM FULL, REINDEX: ACCESS EXCLUSIVE                                    │
│                                                                              │
└─────────────────────────────────────────────────────────────────────────────┘
*/

-- Enable deadlock logging
-- In postgresql.conf:
-- log_lock_waits = on
-- deadlock_timeout = 1s

-- Check logs for deadlock patterns:
/*
LOG:  process 12345 detected deadlock while waiting for ShareLock on 
      transaction 987654 after 1000.123 ms
DETAIL:  Process holding the lock: 12346. Wait queue: .
CONTEXT:  while updating tuple (0,42) in relation "orders"
STATEMENT:  UPDATE orders SET status = 'shipped' WHERE id = 100

LOG:  process 12345 still waiting for ShareLock on transaction 987654 
      after 1000.456 ms
DETAIL:  Process holding the lock: 12346. Wait queue: 12345.
CONTEXT:  while updating tuple (0,42) in relation "orders"
STATEMENT:  UPDATE orders SET status = 'shipped' WHERE id = 100
*/

-- Analyze deadlock from logs:
/*
 Deadlock scenario:
 
 Transaction A:                  Transaction B:
 UPDATE orders WHERE id=100      UPDATE orders WHERE id=200
 (holds lock on row 100)         (holds lock on row 200)
 ...                             ...
 UPDATE orders WHERE id=200      UPDATE orders WHERE id=100
 (waits for B's lock)            (waits for A's lock)
 
 ↓ DEADLOCK
 
 Solution: Consistent lock ordering
 - Always acquire locks in same order (e.g., by id ASC)
 - Or use SELECT ... FOR UPDATE with explicit ordering
*/

-- Step 1: Identify CPU-heavy queries
SELECT 
    pid,
    usename,
    state,
    wait_event_type,
    wait_event,
    left(query, 100)
FROM pg_stat_activity
WHERE state = 'active'
  AND wait_event_type IS NULL  -- Not waiting = actually running
ORDER BY backend_start;

-- Step 2: Check pg_stat_statements
SELECT 
    query,
    calls,
    mean_exec_time,
    total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Step 3: Profile with perf
-- perf record -g -p <pid> -- sleep 30
-- perf report

-- Common causes:
-- • Missing index (ExecSeqScan in profile)
-- • Complex expression evaluation (ExecEvalExpr)
-- • Hash table operations (hash_search)
-- • Sort operations (tuplesort_*)

-- Step 1: Check wait events
SELECT 
    wait_event_type,
    wait_event,
    count(*)
FROM pg_stat_activity
WHERE state = 'active'
  AND wait_event_type IN ('IO', 'BufferPin')
GROUP BY wait_event_type, wait_event;

-- Step 2: Check buffer cache hit ratio
SELECT 
    sum(heap_blks_read) as heap_read,
    sum(heap_blks_hit) as heap_hit,
    sum(heap_blks_hit) / 
        nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0) as ratio
FROM pg_statio_user_tables;
-- Should be > 0.99 for OLTP

-- Step 3: Check checkpoint frequency
SELECT 
    checkpoints_timed,
    checkpoints_req,  -- Requested = forced = BAD
    buffers_checkpoint,
    buffers_backend   -- Backend writes = BAD, should be 0
FROM pg_stat_bgwriter;

-- Step 4: System level
-- iostat -xz 1
-- iotop

-- Common causes:
-- • shared_buffers too small
-- • Checkpoint too aggressive (checkpoint_completion_target low)
-- • seq scans on large tables
-- • Bloated tables need VACUUM

-- Step 1: Check current locks
SELECT 
    locktype,
    relation::regclass,
    mode,
    granted,
    pid,
    left(query, 50) as query
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT granted
ORDER BY relation;

-- Step 2: Find blocking queries
SELECT * FROM pg_blocking_pids(12345);  -- Replace with waiting PID

-- Step 3: Check for long-running transactions
SELECT 
    pid,
    now() - xact_start as xact_duration,
    now() - query_start as query_duration,
    state,
    left(query, 60)
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

-- Step 4: Kill if necessary
SELECT pg_terminate_backend(12345);  -- Last resort

-- Prevention:
-- • Use statement_timeout and idle_in_transaction_session_timeout
-- • Use lock_timeout
-- • Avoid long transactions in OLTP

-- Find queries consuming the most CPU time
SELECT 
    left(query, 100) as query_preview,
    calls,
    total_exec_time::numeric(12,2) as total_exec_ms,
    mean_exec_time::numeric(10,2) as mean_exec_ms,
    (total_exec_time / sum(total_exec_time) OVER ()) * 100 as pct_total_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Check currently running expensive queries
SELECT 
    pid,
    usename,
    application_name,
    now() - query_start as duration,
    state,
    left(query, 100) as query_preview
FROM pg_stat_activity
WHERE state = 'active' 
  AND query NOT LIKE '%pg_stat_activity%'
ORDER BY query_start;

-- Get the full query text if truncated
SELECT query FROM pg_stat_statements WHERE queryid = <identified_queryid>;

-- Check execution plan
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
<paste the problematic query here>;

-- Check when tables were last analyzed
SELECT 
    schemaname,
    relname,
    last_analyze,
    last_autoanalyze,
    n_live_tup,
    n_dead_tup
FROM pg_stat_user_tables
WHERE relname IN (
    -- List tables from the problematic query
    'users', 'orders', 'products'
)
ORDER BY last_analyze NULLS FIRST;

-- If statistics are stale, run ANALYZE
ANALYZE users;
ANALYZE orders;
-- Check if plan improves

-- Identify specific PIDs
SELECT 
    pid,
    usename,
    left(query, 80),
    now() - query_start as duration
FROM pg_stat_activity
WHERE query ILIKE '%<pattern from step 1>%'
  AND state = 'active';

-- Terminate them
SELECT pg_terminate_backend(pid) FROM pg_stat_activity 
WHERE query ILIKE '%<pattern>%' AND state = 'active';

-- If Seq Scan is the issue and WHERE clause is clear
CREATE INDEX CONCURRENTLY idx_users_created_at ON users(created_at);
-- CONCURRENTLY prevents table locking

-- Example: Replace full table scan with indexed lookup
-- Bad:
SELECT * FROM orders WHERE status IN ('pending', 'processing');

-- Better:
SELECT * FROM orders WHERE status = 'pending'
UNION ALL
SELECT * FROM orders WHERE status = 'processing';
-- If there's an index on status, this uses it twice instead of seq scan

-- Watch CPU and query stats
SELECT 
    sum(total_exec_time) as total_time,
    sum(calls) as total_calls
FROM pg_stat_statements;

-- Check every 30 seconds, verify total_time growth rate decreased
-- Monitor system CPU with `top` or monitoring dashboard

# Check disk I/O utilization (Linux)
iostat -x 1 5

# Look for:
# - %util near 100% (disk saturated)
# - await > 10ms (high latency)
# - r/s and w/s (read/write operations per second)

# PostgreSQL wait events

SELECT 
    wait_event_type,
    wait_event,
    count(*) as waiting_backends
FROM pg_stat_activity
WHERE wait_event IS NOT NULL
GROUP BY wait_event_type, wait_event
ORDER BY count(*) DESC;

-- Look for: DataFileRead, DataFileWrite, WALWrite

-- Queries with worst buffer hit ratios
SELECT 
    left(query, 100) as query_preview,
    calls,
    shared_blks_read as disk_reads,
    shared_blks_hit as cache_hits,
    shared_blks_read + shared_blks_hit as total_blks,
    round(100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0), 2) as cache_hit_pct
FROM pg_stat_statements
WHERE shared_blks_read > 1000  -- Significant disk activity
ORDER BY shared_blks_read DESC
LIMIT 20;

-- Identify specific tables being read from disk
SELECT 
    schemaname,
    relname as table_name,
    heap_blks_read as disk_reads,
    heap_blks_hit as cache_hits,
    heap_blks_read + heap_blks_hit as total_accesses,
    round(100.0 * heap_blks_hit / nullif(heap_blks_hit + heap_blks_read, 0), 2) as hit_ratio
FROM pg_statio_user_tables
WHERE heap_blks_read > 0
ORDER BY heap_blks_read DESC
LIMIT 20;

-- Get explain plan with buffer statistics
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
<paste problematic query>;

-- Look for:
-- • "Buffers: shared read=XXXXX" - high numbers mean disk reads
-- • Seq Scans on large tables
-- • Index scans that read too many pages
-- • Sorts or aggregations spilling to disk

-- Identify bloated tables
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size,
    n_live_tup,
    n_dead_tup,
    round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) as dead_tup_pct,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- Check if autovacuum is running
SELECT * FROM pg_stat_progress_vacuum;

-- Kill or throttle the I/O-heavy queries
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE query ILIKE '%<identified pattern>%'
  AND state = 'active'
  AND backend_type = 'client backend';

-- Or set a statement timeout for that session
ALTER DATABASE mydb SET statement_timeout = '5s';

-- If bloat is the issue
VACUUM (ANALYZE, VERBOSE) problematic_table;

-- Monitor progress
SELECT * FROM pg_stat_progress_vacuum;

-- If missing index is clear from EXPLAIN
CREATE INDEX CONCURRENTLY idx_orders_user_created 
ON orders(user_id, created_at) 
INCLUDE (status, total);  -- Covering index to avoid heap lookups

-- If you have available RAM and can restart
-- postgresql.conf:
-- shared_buffers = 8GB  (increase from current value)
-- effective_cache_size = 24GB  (hint for planner)

-- Or for immediate session-level help:
SET work_mem = '256MB';  -- For current session only

-- Watch cache hit ratio improve
SELECT 
    sum(heap_blks_hit) as cache_hits,
    sum(heap_blks_read) as disk_reads,
    round(100.0 * sum(heap_blks_hit) / 
          nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0), 2) as hit_ratio
FROM pg_statio_user_tables;

-- Check every 30 seconds, target >99%

-- Monitor I/O wait events
SELECT 
    wait_event_type,
    count(*) as waiting
FROM pg_stat_activity
WHERE wait_event_type = 'IO'
GROUP BY wait_event_type;
-- Should decrease significantly