SQL Database Scaling Patterns

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

Replication Data Flow:
 
┌─────────────────────────────────────────────────────────────────┐
│                        APPLICATION TIER                         │
│   ┌─────────────┐             ┌─────────────────────────────┐   │
│   │   Writes    │             │          Reads              │   │
│   └──────┬──────┘             └──────────────┬──────────────┘   │
└──────────┼───────────────────────────────────┼──────────────────┘
           │                                   │
           ▼                                   ▼
    ┌──────────────┐                 ┌─────────────────┐
    │   PRIMARY    │                 │  LOAD BALANCER  │
    │   (Writer)   │                 │   (for reads)   │
    │              │                 └────────┬────────┘
    │  ┌────────┐  │                          │
    │  │  WAL/  │  │         ┌────────────────┼────────────────┐
    │  │ Binlog │──┼────────►│                ▼                │
    │  └────────┘  │         │    ┌───────────────────────┐    │
    └──────────────┘         │    │      REPLICA 1        │    │
                             │    │  (Async Apply WAL)    │    │
                             │    └───────────────────────┘    │
                             │                                 │
                             │    ┌───────────────────────┐    │
                             │    │      REPLICA 2        │    │
                             │    │  (Async Apply WAL)    │    │
                             │    └───────────────────────┘    │
                             │                                 │
                             │    ┌───────────────────────┐    │
                             │    │      REPLICA N        │    │
                             │    │  (Async Apply WAL)    │    │
                             │    └───────────────────────┘    │
                             │         REPLICA POOL          │
                             └────────────────────────────────┘

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

// Pattern: Context-aware read routing for consistency
 
import { Pool } from 'pg';
 
interface DatabaseCluster {
    primary: Pool;
    replicas: Pool[];
    replicaIndex: number;
}
 
interface RequestContext {
    userId: string;
    lastWriteTimestamp?: number;
    requiresStrongConsistency?: boolean;
}
 
class ConsistencyAwareRouter {
    private cluster: DatabaseCluster;
    
    // Maximum acceptable replication lag in milliseconds
    private maxAcceptableLag = 1000;
    
    constructor(cluster: DatabaseCluster) {
        this.cluster = cluster;
    }
    
    /**
     * Route read queries based on consistency requirements
     */
    async getReadConnection(context: RequestContext): Promise<Pool> {
        // Case 1: Strong consistency explicitly required
        if (context.requiresStrongConsistency) {
            return this.cluster.primary;
        }
        
        // Case 2: User just wrote data - read-your-writes guarantee
        if (context.lastWriteTimestamp) {
            const timeSinceWrite = Date.now() - context.lastWriteTimestamp;
            
            // If write was very recent, use primary
            if (timeSinceWrite < this.maxAcceptableLag) {
                return this.cluster.primary;
            }
        }
        
        // Case 3: Normal read - use replica with round-robin
        return this.getHealthyReplica();
    }
    
    /**
     * Round-robin selection among healthy replicas
     */
    private getHealthyReplica(): Pool {
        const replicas = this.cluster.replicas;
        
        // Simple round-robin
        this.cluster.replicaIndex = 
            (this.cluster.replicaIndex + 1) % replicas.length;
        
        return replicas[this.cluster.replicaIndex];
    }
}
 
// Usage in API handler
async function getUserProfile(
    router: ConsistencyAwareRouter,
    userId: string, 
    context: RequestContext
) {
    // Reading own profile after update? Need consistency
    const isOwnProfile = context.userId === userId;
    
    const conn = await router.getReadConnection({
        ...context,
        requiresStrongConsistency: isOwnProfile && !!context.lastWriteTimestamp,
    });
    
    return conn.query('SELECT * FROM users WHERE id = $1', [userId]);
}

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

"""
Lag-aware read replica load balancer
 
Monitors replication lag and routes queries accordingly:
- Lag < 100ms: Normal round-robin
- Lag 100ms - 1s: Reduced weight
- Lag > 1s: Removed from pool
- Lag > 5s: Alert triggered
"""
 
import asyncio
from dataclasses import dataclass
from typing import Optional
import asyncpg
 
@dataclass
class ReplicaStatus:
    host: str
    port: int
    pool: asyncpg.Pool
    lag_ms: float = 0
    weight: int = 100
    healthy: bool = True
    last_check: float = 0
 
class LagAwareBalancer:
    def __init__(self, replicas: list[ReplicaStatus], max_lag_ms: int = 1000):
        self.replicas = replicas
        self.max_lag_ms = max_lag_ms
        self._current_index = 0
        
    async def start_lag_monitoring(self, interval_seconds: float = 1.0):
        """Background task to monitor replica lag"""
        while True:
            await asyncio.gather(*[
                self._check_replica_lag(replica) 
                for replica in self.replicas
            ])
            await asyncio.sleep(interval_seconds)
    
    async def _check_replica_lag(self, replica: ReplicaStatus):
        """Query replica to determine replication lag"""
        try:
            # PostgreSQL: Check replication lag
            lag_result = await replica.pool.fetchval("""
                SELECT COALESCE(
                    EXTRACT(MILLISECONDS FROM (now() - pg_last_xact_replay_timestamp())),
                    0
                )::bigint AS lag_ms
            """)
            
            replica.lag_ms = lag_result or 0
            replica.healthy = replica.lag_ms < self.max_lag_ms
            
            # Adjust weight based on lag
            if replica.lag_ms < 100:
                replica.weight = 100
            elif replica.lag_ms < 500:
                replica.weight = 50
            elif replica.lag_ms < 1000:
                replica.weight = 25
            else:
                replica.weight = 0  # Effectively removed from pool
                
        except Exception as e:
            replica.healthy = False
            replica.weight = 0
            print(f"Replica {replica.host} health check failed: {e}")
    
    def get_replica(self) -> Optional[ReplicaStatus]:
        """Get next healthy replica using weighted selection"""
        healthy_replicas = [r for r in self.replicas if r.healthy and r.weight > 0]
        
        if not healthy_replicas:
            return None  # Caller should fall back to primary
        
        # Weighted random selection
        total_weight = sum(r.weight for r in healthy_replicas)
        
        # Simple weighted round-robin
        cumulative = 0
        target = self._current_index % total_weight
        self._current_index += 1
        
        for replica in healthy_replicas:
            cumulative += replica.weight
            if cumulative > target:
                return replica
        
        return healthy_replicas[-1]

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

-- PostgreSQL: Comprehensive replication monitoring queries
 
-- 1. View replication status from PRIMARY
SELECT 
    client_addr,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS replay_lag_bytes,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)) AS send_lag_bytes
FROM pg_stat_replication;
 
-- 2. Check replication slots for WAL retention issues
SELECT 
    slot_name,
    slot_type,
    active,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots
WHERE slot_type = 'physical';
 
-- 3. View replication lag from REPLICA
SELECT
    CASE 
        WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() THEN 0
        ELSE EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))
    END AS replica_lag_seconds;
 
-- 4. Create a monitoring/alerting function
CREATE OR REPLACE FUNCTION check_replication_health()
RETURNS TABLE(
    replica_addr inet,
    lag_bytes bigint,
    lag_seconds numeric,
    status text
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        r.client_addr,
        pg_wal_lsn_diff(pg_current_wal_lsn(), r.replay_lsn)::bigint,
        EXTRACT(EPOCH FROM (now() - r.reply_time)),
        CASE 
            WHEN r.state != 'streaming' THEN 'CRITICAL: Not streaming'
            WHEN pg_wal_lsn_diff(pg_current_wal_lsn(), r.replay_lsn) > 100000000 -- >100MB
                THEN 'WARNING: High lag'
            ELSE 'OK'
        END
    FROM pg_stat_replication r;
END;
$$ LANGUAGE plpgsql;

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

-- Safe migration patterns for replicated databases
 
-- Pattern 1: Add nullable column (near-instant, non-blocking)
ALTER TABLE orders ADD COLUMN tracking_number VARCHAR(100);
 
-- Pattern 2: Add column with default (PostgreSQL 11+, non-blocking)
ALTER TABLE orders ADD COLUMN status VARCHAR(20) DEFAULT 'pending';
 
-- Pattern 3: Create index concurrently (non-blocking)
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders(customer_id);
 
-- Pattern 4: Add NOT NULL constraint in stages
-- Step 1: Add check constraint as NOT VALID (fast, no table scan)
ALTER TABLE orders 
ADD CONSTRAINT orders_status_not_null 
CHECK (status IS NOT NULL) NOT VALID;
 
-- Step 2: Validate in background (blocks writes briefly, but can be done off-peak)
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_not_null;
 
-- Step 3: Add NOT NULL using validated constraint (fast)
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;
 
-- Step 4: Drop the now-redundant check constraint
ALTER TABLE orders DROP CONSTRAINT orders_status_not_null;
 
-- Pattern 5: Rename column safely using expand/contract
-- (Rename is instant but breaks queries, so use this pattern instead)
-- Phase 1: Add new column
ALTER TABLE users ADD COLUMN email_address VARCHAR(255);
 
-- Phase 2: Backfill
UPDATE users SET email_address = email WHERE email_address IS NULL;
 
-- Phase 3: Update application to use new column
-- Phase 4: Drop old column
ALTER TABLE users DROP COLUMN email;