Primary / Replica Architecture

Primary/replica architecture separates writes (primary) from reads (replicas).

Architecture Overview

                    ┌─────────────┐
                    │Application  │
                    └──────┬──────
                           │
               ┌───────────┼───────────┐
               │ Write     │ Read      │
               ▼           ▼           │
        ┌──────────┐ ┌──────────┐      │
        │ Primary  │ │ Replicas │      │
        │ (write)  │ │ (read)   │      │
        └────┬─────┘ └──────────┘      │
             │                         │
        Replication                    │
             │                         │
        ┌────▼────┐                    │
        │ Replicas│                    │
        └─────────┘                    │

Read/Write Splitting

Writes:
- INSERT, UPDATE, DELETE → Primary
- All mutations go to primary

Reads:
- SELECT → Replicas
- Distribute across replicas

Benefits:
- Primary handles writes efficiently
- Replicas distribute read load
- Better overall throughput

Implementation

# Application-level splitting
class DatabaseRouter:
    def __init__(self, primary, replicas):
        self.primary = primary
        self.replicas = replicas
    
    def get_connection(self, operation):
        if operation in ['INSERT', 'UPDATE', 'DELETE']:
            return self.primary
        else:  # SELECT
            return self.get_read_replica()
    
    def get_read_replica(self):
        # Round robin or least connections
        return random.choice(self.replicas)

Connection Pooling

# Separate pools for read and write
[databases]
primary = host=primary port=5432 pool_size=20
replica1 = host=replica1 port=5432 pool_size=30
replica2 = host=replica2 port=5432 pool_size=30

When to Use Primary/Replica

Use when:
- Read-heavy workload (>80% reads)
- Can tolerate some replication lag
- Need high read availability
- Want to scale reads independently

Avoid when:
- Write-heavy workload
- Strong consistency required for all reads
- Low latency reads critical (replication lag)

Failover handles primary database failure by promoting a replica.

Failover Process

1. Detect primary failure
   - Health checks fail
   - Replication stops

2. Select new primary
   - Choose best replica
   - Usually least lag

3. Promote replica
   - Stop replication
   - Make read-write

4. Update configuration
   - Point application to new primary
   - Update other replicas

5. Verify
   - Test new primary
   - Resume operations

Failover Architecture

Before Failover:
Primary (write) → Replicas (read)

During Failover:
Primary (DOWN) → Replicas (read)
                  ↓
              Promote best replica
                  ↓
New Primary (write) → Remaining Replicas (read)

Failover Methods

Method	Description	Speed
Manual	Operator promotes replica	Slow
Semi-automatic	Detect + manual promote	Medium
Automatic	Full auto-promotion	Fast

Automatic Failover

Tools:
- Patroni (PostgreSQL)
- Orchestrator (MySQL)
- RDS Multi-AZ (AWS)

Process:
1. Health check fails
2. Consensus on failure
3. Promote replica
4. Update DNS/endpoints
5. Notify operators

Failover Challenges

1. Data Loss
   - Async replication: last writes may be lost
   - Semi-sync: reduces but doesn't eliminate

2. Split Brain
   - Two primaries possible
   - Use fencing/stonith

3. Replication Lag
   - Promoted replica may be behind
   - Accept data loss or wait

4. Application Updates
   - Connection strings change
   - DNS propagation delay

Failover Best Practices

1. Test failover regularly: Don't wait for real failures
2. Use semi-sync: Reduce data loss risk
3. Monitor replication lag: Know lag before failover
4. Automate where possible: Reduce human error
5. Document runbooks: Know how to handle each scenario

Different consistency levels balance data accuracy with performance.

Consistency Levels

1. Strong Consistency
   - Read always returns latest write
   - Read from primary
   - Higher latency

2. Eventual Consistency
   - Read may return stale data
   - Can read from replica
   - Lower latency

3. Read-Your-Writes
   - User always sees their own writes
   - Read from primary after write
   - Medium latency

4. Monotonic Reads
   - Once you see data, you won't see older data
   - Stick to same replica

Consistency Level Implementation

# Consistency levels
class ConsistentReads:
    def __init__(self, primary, replicas):
        self.primary = primary
        self.replicas = replicas
    
    def strong_read(self, key):
        # Always read from primary
        return self.primary.get(key)
    
    def eventual_read(self, key):
        # Read from any replica
        return random.choice(self.replicas).get(key)
    
    def read_your_writes(self, key, user_id):
        # Read from primary if user recently wrote
        if self.recent_write(user_id):
            return self.primary.get(key)
        return random.choice(self.replicas).get(key)

Consistency Level Tradeoffs

Level	Consistency	Performance	Use Case
Strong	High	Lower	Financial, inventory
Eventual	Low	Higher	Social media, analytics
Read-your-writes	Medium	Medium	User profiles
Monotonic	Medium	Medium	Feed, timeline

Tuning Consistency

-- PostgreSQL: Read from primary
BEGIN;
SET TRANSACTION READ ONLY;
SET LOCAL search_path TO public;
SELECT * FROM users WHERE id = 1;
COMMIT;

-- Or use synchronous replication
ALTER SYSTEM SET synchronous_standby_names = 'replica1';

Consistency Best Practices

1. Choose based on use case: Not all data needs strong consistency
2. Default to eventual: Use strong only when needed
3. Implement read-your-writes: For user-facing features
4. Monitor consistency: Track stale read incidents
5. Document consistency model: Team must understand tradeoffs