Identifying Performance Bottlenecks

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68

-- =====================================================
-- BASIC EXPLAIN: Shows query execution plan
-- =====================================================
 
EXPLAIN SELECT * FROM orders WHERE user_id = 12345;
 
-- PostgreSQL output example:
--                          QUERY PLAN
-- -----------------------------------------------------------
-- Index Scan using idx_orders_user_id on orders
--   (cost=0.43..8.45 rows=1 width=100)
--   Index Cond: (user_id = 12345)
 
-- KEY METRICS TO EXAMINE:
-- 1. Scan type: Index Scan (good) vs Seq Scan (usually bad)
-- 2. Cost: Lower is better; first number is startup cost
-- 3. Rows: Estimated rows to process
-- 4. Width: Bytes per row (SELECT * = wide)
 
 
-- =====================================================
-- EXPLAIN ANALYZE: Actually runs the query with timing
-- =====================================================
 
EXPLAIN ANALYZE SELECT * FROM orders 
WHERE user_id = 12345 AND status = 'pending';
 
-- PostgreSQL output example:
-- Index Scan using idx_orders_user_id on orders
--   (cost=0.43..8.45 rows=1 width=100)
--   (actual time=0.025..0.027 rows=3 loops=1)
--   Index Cond: (user_id = 12345)
--   Filter: (status = 'pending')
--   Rows Removed by Filter: 47
-- Planning Time: 0.102 ms
-- Execution Time: 0.045 ms
 
-- CRITICAL OBSERVATIONS:
-- 1. "Filter: ..." means post-index filtering (wasteful)
-- 2. "Rows Removed by Filter: 47" means index isn't selective enough
-- 3. A composite index on (user_id, status) would be more efficient
 
 
-- =====================================================
-- RED FLAGS IN EXPLAIN OUTPUT
-- =====================================================
 
-- 1. SEQUENTIAL SCAN on large table
EXPLAIN SELECT * FROM orders WHERE email = 'user@example.com';
-- → Seq Scan on orders (cost=0.00..145782.00 rows=1 width=100)
-- FIX: Add index on email column
 
-- 2. NESTED LOOP with high row estimates
EXPLAIN SELECT * FROM orders o 
JOIN order_items oi ON o.id = oi.order_id
WHERE o.created_at > '2024-01-01';
-- → Nested Loop (cost=...) (actual rows=5000000)
-- FIX: Ensure both sides of join have appropriate indexes
 
-- 3. SORT operation (expensive for large sets)
EXPLAIN SELECT * FROM products ORDER BY price DESC LIMIT 10;
-- → Sort (cost=12345.67...) Sort Method: external merge
-- "external merge" means data didn't fit in memory
-- FIX: Index on (price DESC) for top-N queries
 
-- 4. High ROWS REMOVED BY FILTER
-- Indicates index is being used but isn't selective
-- Consider composite index or different query structure

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78

-- =====================================================
-- COMPOSITE INDEX: Column order matters!
-- =====================================================
 
-- Query pattern: Find orders by user, then filter by status
SELECT * FROM orders 
WHERE user_id = 123 AND status = 'pending' 
ORDER BY created_at DESC;
 
-- GOOD: Matches query pattern (leftmost prefix rule)
CREATE INDEX idx_orders_user_status_created 
ON orders(user_id, status, created_at DESC);
-- This index supports:
-- - WHERE user_id = ?
-- - WHERE user_id = ? AND status = ?
-- - WHERE user_id = ? AND status = ? ORDER BY created_at
 
-- BAD: Wrong column order
CREATE INDEX idx_orders_status_user 
ON orders(status, user_id);
-- Cannot efficiently satisfy: WHERE user_id = 123
-- (would need to scan all statuses)
 
 
-- =====================================================
-- PARTIAL INDEX: Index only what you query
-- =====================================================
 
-- You only ever query pending orders
SELECT * FROM orders WHERE status = 'pending' AND user_id = 123;
 
-- Instead of indexing all orders:
CREATE INDEX idx_orders_pending 
ON orders(user_id) 
WHERE status = 'pending';
 
-- Benefits:
-- 1. Much smaller index (only pending orders)
-- 2. Faster to update (most inserts become delivered)
-- 3. Query planner uses it for matching queries
 
 
-- =====================================================
-- COVERING INDEX: Never touch the table
-- =====================================================
 
-- Query only needs user_id and total
SELECT user_id, total FROM orders WHERE user_id = 123;
 
-- Covering index includes queried columns
CREATE INDEX idx_orders_user_covering 
ON orders(user_id) INCLUDE (total, status, created_at);
 
-- Database reads ONLY from index, never accesses table
-- Massive performance improvement for read-heavy queries
 
 
-- =====================================================
-- INDEX ANALYSIS: Find missing and unused indexes
-- =====================================================
 
-- PostgreSQL: Find slow queries (pg_stat_statements extension)
SELECT query, calls, mean_time, total_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 20;
 
-- PostgreSQL: Find missing indexes (tables with seq scans)
SELECT relname, seq_scan, idx_scan,
       seq_scan - idx_scan AS delta
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan
ORDER BY delta DESC;
 
-- PostgreSQL: Find unused indexes (candidates for removal)
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexrelname NOT LIKE 'pg_%';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74

# =====================================================
# THE N+1 PROBLEM: Death by a thousand queries
# =====================================================
 
# ANTI-PATTERN: N+1 queries
def get_orders_with_items_bad(user_id: int):
    # 1 query to get orders
    orders = db.query("SELECT * FROM orders WHERE user_id = ?", user_id)
    
    for order in orders:
        # N queries to get items (one per order!)
        items = db.query("SELECT * FROM order_items WHERE order_id = ?", order.id)
        order.items = items
    
    return orders
 
# If user has 100 orders: 1 + 100 = 101 queries
# Each query has network latency (~1ms) = 101ms minimum
# At 1000 orders: 1+ seconds just in query overhead
 
 
# SOLUTION 1: JOIN (single query)
def get_orders_with_items_join(user_id: int):
    return db.query("""
        SELECT o.*, oi.*
        FROM orders o
        LEFT JOIN order_items oi ON o.id = oi.order_id
        WHERE o.user_id = ?
    """, user_id)
# 1 query regardless of order count
# Trade-off: Duplicate order data per item row
 
 
# SOLUTION 2: Batch loading (two queries)
def get_orders_with_items_batch(user_id: int):
    # Query 1: Get all orders
    orders = db.query("SELECT * FROM orders WHERE user_id = ?", user_id)
    order_ids = [o.id for o in orders]
    
    # Query 2: Get all items for all orders at once
    items = db.query(
        "SELECT * FROM order_items WHERE order_id IN (?)", 
        order_ids
    )
    
    # Group items by order in application
    items_by_order = group_by(items, 'order_id')
    for order in orders:
        order.items = items_by_order.get(order.id, [])
    
    return orders
# Always exactly 2 queries, regardless of order count
 
 
# SOLUTION 3: ORM eager loading (framework-specific)
# SQLAlchemy example
def get_orders_with_items_eager(user_id: int):
    return (
        session.query(Order)
        .options(joinedload(Order.items))  # or subqueryload()
        .filter(Order.user_id == user_id)
        .all()
    )
# ORM handles the batching automatically
 
 
# =====================================================
# DETECTING N+1 QUERIES
# =====================================================
 
# 1. Query logging - look for repeated similar queries
# 2. APM tools (New Relic, Datadog) show query count per request
# 3. Database: pg_stat_statements shows query call counts
# 4. Rule of thumb: >10 queries per API request is suspicious

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112

# =====================================================
# CONNECTION POOL CONFIGURATION PRINCIPLES
# =====================================================
 
# SIZING A CONNECTION POOL
# 
# Key insight: Optimal pool size is usually SMALLER than you think.
# Formula from PostgreSQL wiki:
#   pool_size = ((core_count * 2) + effective_spindle_count)
#
# For a typical DB server with 8 cores and SSD:
#   pool_size = (8 * 2) + 1 = 17 connections
#
# For application-side pools (many app instances):
#   per_instance_pool = max_db_connections / app_instance_count
 
 
from sqlalchemy import create_engine
from sqlalchemy.pool import QueuePool
 
# Production connection pool configuration
engine = create_engine(
    "postgresql://user:pass@db-host:5432/mydb",
    
    # Core pool parameters
    poolclass=QueuePool,
    pool_size=10,           # Steady-state connections maintained
    max_overflow=5,         # Additional connections under load
    # Total max = pool_size + max_overflow = 15
    
    # Connection lifecycle
    pool_timeout=30,        # Seconds to wait for available connection
    pool_recycle=1800,      # Recycle connections after 30 minutes
    pool_pre_ping=True,     # Verify connection liveness before use
)
 
 
# =====================================================
# DIAGNOSING CONNECTION ISSUES
# =====================================================
 
# Symptom: Connection timeout errors
# Possible causes:
# 1. Pool exhausted - queries holding connections too long
# 2. Connection leak - connections not returned to pool
# 3. Pool too small for load
# 4. Database max_connections reached
 
# PostgreSQL: Check current connections
# SELECT count(*) FROM pg_stat_activity;
# SELECT usename, count(*) FROM pg_stat_activity GROUP BY usename;
 
# PostgreSQL: Check connection state
# SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
# States: active, idle, idle in transaction, idle in transaction (aborted)
 
 
# =====================================================
# CONNECTION LEAK DETECTION
# =====================================================
 
# A connection leak occurs when code acquires a connection
# but fails to return it (often due to unhandled exceptions)
 
# ANTI-PATTERN: Connection leak
def bad_query():
    conn = pool.get_connection()
    result = conn.execute("SELECT * FROM users")
    # What if execute() throws an exception?
    # Connection is never returned to pool!
    return result
    # Connection implicitly returned... sometimes
 
# CORRECT: Always use context managers
def good_query():
    with pool.connect() as conn:
        result = conn.execute("SELECT * FROM users")
        return result
    # Connection ALWAYS returned, even if exception occurs
 
# SQLAlchemy scoped sessions (for web frameworks)
from sqlalchemy.orm import scoped_session, sessionmaker
 
Session = scoped_session(sessionmaker(bind=engine))
 
# In request handler:
def handle_request():
    try:
        result = Session.query(User).all()
        return result
    finally:
        Session.remove()  # Return connection to pool
 
 
# =====================================================
# MONITORING CONNECTION POOL HEALTH
# =====================================================
 
# Key metrics to monitor:
# 1. Pool utilization: connections_in_use / pool_size
#    - > 80% sustained: consider increasing pool
#    - 100% with waiters: pool exhaustion
#
# 2. Connection wait time: time blocked waiting for connection
#    - > 100ms average: pool pressure
#    - Spikes correlate with slow queries holding connections
#
# 3. Connection churn: connections created/destroyed per second
#    - High churn: pool too small or connection leaks
#
# 4. Idle connections: connections sitting unused
#    - Many idle in small pool: possibly wasted resources

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66

-- =====================================================
-- POSTGRESQL: Finding Lock Contention
-- =====================================================
 
-- View current locks and what's waiting
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_query,
    blocking_activity.query AS blocking_query
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;
 
-- Simpler view: long-running queries (potential lock holders)
SELECT pid, now() - pg_stat_activity.query_start AS duration, query
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 seconds'
  AND state = 'active';
 
-- Check for "idle in transaction" connections (holding locks)
SELECT pid, usename, state, query, 
       now() - xact_start AS transaction_duration
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY transaction_duration DESC;
 
 
-- =====================================================
-- MYSQL: Finding Lock Contention  
-- =====================================================
 
-- Current InnoDB locks
SELECT * FROM information_schema.INNODB_LOCKS;
 
-- Lock waits
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
 
-- Combined view: who is blocking whom
SELECT 
    r.trx_id AS waiting_trx_id,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query
FROM information_schema.INNODB_LOCK_WAITS w
JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101

# =====================================================
# STRATEGIES TO REDUCE LOCK CONTENTION
# =====================================================
 
# 1. SHORTEN TRANSACTION DURATION
# =====================================
 
# ANTI-PATTERN: External calls inside transaction
def place_order_bad(order_data):
    with db.transaction():
        order = create_order(order_data)
        # Payment API call takes 2-3 seconds
        # Transaction holds locks the whole time!
        payment = payment_api.charge(order.total)  
        order.payment_id = payment.id
        commit()
 
# CORRECT: External calls outside transaction
def place_order_good(order_data):
    # Create order in transaction
    with db.transaction():
        order = create_order(order_data)
        order.status = 'pending_payment'
        commit()
    
    # External call outside transaction (no locks held)
    try:
        payment = payment_api.charge(order.total)
    except PaymentError:
        with db.transaction():
            order.status = 'payment_failed'
            commit()
        raise
    
    # Update order in new transaction
    with db.transaction():
        order.payment_id = payment.id
        order.status = 'paid'
        commit()
 
 
# 2. OPTIMISTIC LOCKING (for read-modify-write)
# =====================================
 
# Instead of SELECT FOR UPDATE (pessimistic)
def update_balance_pessimistic(user_id, amount):
    with db.transaction():
        # Locks row until transaction ends
        account = db.query(
            "SELECT * FROM accounts WHERE user_id = ? FOR UPDATE",
            user_id
        )
        account.balance += amount
        commit()
 
# Use version-based optimistic locking
def update_balance_optimistic(user_id, amount):
    while True:
        account = db.query(
            "SELECT * FROM accounts WHERE user_id = ?", 
            user_id
        )
        new_balance = account.balance + amount
        new_version = account.version + 1
        
        # Conditional update - fails if version changed
        result = db.execute("""
            UPDATE accounts 
            SET balance = ?, version = ?
            WHERE user_id = ? AND version = ?
        """, new_balance, new_version, user_id, account.version)
        
        if result.rows_affected == 1:
            return  # Success
        # Retry if version mismatch (concurrent update)
 
 
# 3. DISTRIBUTED COUNTERS (for hot row counters)
# =====================================
 
# ANTI-PATTERN: Single counter row
# UPDATE stats SET page_views = page_views + 1 WHERE page_id = 1;
# Every request contends for same row
 
# SOLUTION: Sharded counters
def increment_page_view(page_id):
    # Spread across N shard rows
    shard_id = random.randint(1, 10)
    db.execute("""
        INSERT INTO page_view_shards (page_id, shard_id, count)
        VALUES (?, ?, 1)
        ON CONFLICT (page_id, shard_id) 
        DO UPDATE SET count = count + 1
    """, page_id, shard_id)
 
def get_page_views(page_id):
    # Sum all shards when reading
    return db.query("""
        SELECT SUM(count) FROM page_view_shards 
        WHERE page_id = ?
    """, page_id)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

-- =====================================================
-- POSTGRESQL: Measuring lag on replica
-- =====================================================
 
-- Lag in bytes (how far behind in WAL)
SELECT pg_wal_lsn_diff(
    pg_last_wal_receive_lsn(), 
    pg_last_wal_replay_lsn()
) AS lag_bytes;
 
-- Lag in time (requires logical replication or application tracking)
-- On primary: write timestamp to tracking table every N seconds
-- On replica: read timestamp and compute difference
 
-- Check replication slot lag (on primary)
SELECT slot_name, 
       pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS lag_bytes
FROM pg_replication_slots;
 
 
-- =====================================================
-- MYSQL: Measuring lag on replica
-- =====================================================
 
SHOW SLAVE STATUS\G
 
-- Key field: Seconds_Behind_Master
-- 0 = caught up
-- NULL = replication not running
-- >0 = seconds of lag
 
-- More accurate with heartbeat tables:
-- Primary writes timestamp every second
-- Replica reads and computes difference
 
 
-- =====================================================
-- APPLICATION-LEVEL LAG TRACKING
-- =====================================================
 
-- More reliable than DB-level metrics for application consistency
 
-- Pattern: Write version tag, read version tag
-- 1. Application writes to primary with version token
-- 2. Application reads from replica, checks if version present
-- 3. If version not present, lag hasn't caught up -> route to primary

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107

# =====================================================
# STRATEGIES FOR REPLICATION LAG TOLERANCE
# =====================================================
 
# 1. READ-YOUR-WRITES CONSISTENCY
# =====================================
# After writing, user's subsequent reads go to primary
 
class DatabaseRouter:
    def __init__(self, primary_conn, replica_conn):
        self.primary = primary_conn
        self.replica = replica_conn
        # Track which users recently wrote
        self.write_timestamps = {}  # user_id -> timestamp
        self.write_window = 5  # seconds
    
    def execute_write(self, user_id, query):
        result = self.primary.execute(query)
        self.write_timestamps[user_id] = time.time()
        return result
    
    def execute_read(self, user_id, query):
        # If user wrote recently, read from primary
        last_write = self.write_timestamps.get(user_id, 0)
        if time.time() - last_write < self.write_window:
            return self.primary.execute(query)
        else:
            return self.replica.execute(query)
 
 
# 2. CAUSAL CONSISTENCY WITH VERSION TOKENS
# =====================================
 
class VersionedDatabaseRouter:
    """
    Track logical version; route reads to primary until
    replica catches up to required version.
    """
    
    def execute_write(self, query) -> int:
        result = self.primary.execute(query)
        # Return current primary version (e.g., LSN or sequence)
        return self.primary.execute("SELECT current_version()")
    
    def execute_read(self, query, min_version: int = None):
        if min_version is None:
            return self.replica.execute(query)
        
        # Check if replica has caught up
        replica_version = self.replica.execute("SELECT current_version()")
        if replica_version >= min_version:
            return self.replica.execute(query)
        else:
            # Replica behind; fall back to primary
            return self.primary.execute(query)
 
 
# 3. APPLICATION-LEVEL EVENTUAL CONSISTENCY
# =====================================
# Accept that reads may be stale; design for it
 
def update_user_profile(user_id, new_data):
    # Write to primary
    db.primary.execute(
        "UPDATE users SET data = ? WHERE id = ?",
        new_data, user_id
    )
    
    # Also update cache immediately
    # Cache serves as "read-your-writes" buffer
    cache.set(f"user:{user_id}", new_data, ttl=60)
 
def get_user_profile(user_id):
    # Try cache first (includes recent writes)
    cached = cache.get(f"user:{user_id}")
    if cached:
        return cached
    
    # Fall back to replica (may be stale)
    return db.replica.execute(
        "SELECT * FROM users WHERE id = ?", 
        user_id
    )
 
 
# 4. LAG-AWARE ROUTING
# =====================================
# Route to least-lagged replica
 
class LagAwareRouter:
    def __init__(self, primary, replicas):
        self.primary = primary
        self.replicas = replicas
    
    def get_healthiest_replica(self, max_lag_seconds=2):
        """Return replica with lag under threshold."""
        best = None
        best_lag = float('inf')
        
        for replica in self.replicas:
            lag = replica.get_lag_seconds()
            if lag < max_lag_seconds and lag < best_lag:
                best = replica
                best_lag = lag
        
        # Fall back to primary if all replicas lagged
        return best or self.primary