System Design (HLD)Read Replicas

Read Replicas: Scaling SQL Reads

LevelIntermediate

Duration75 mins

TopicRead Replicas

4 / 5

Consistency Considerations

The Consistency-Availability Trade-off

Read replicas introduce a fundamental tension in system design: the trade-off between consistency and availability. Every read from a replica may return data that is slightly stale—a version of reality that existed moments ago but may no longer reflect the current state of the primary.

For many applications, this staleness is invisible and acceptable. A product catalog that's a few seconds behind poses no problem. But consider a banking application where a user checks their balance immediately after transferring funds, or an e-commerce site where inventory count must be accurate to prevent overselling. In these cases, stale reads create real business problems.

Understanding consistency in replica architectures—what guarantees exist, what guarantees don't, and how to achieve the consistency your application requires—is essential knowledge for building reliable distributed systems.

What You Will Learn

By the end of this page, you will master the spectrum of consistency models relevant to read replicas, understand read-your-writes, monotonic reads, and causal consistency in depth, implement strategies for achieving required consistency levels, and know when each consistency guarantee is necessary versus optional.

Consistency Models Primer

Before diving into implementation, we must establish a precise vocabulary for discussing consistency. Consistency models define what values a read operation may return given a sequence of preceding operations.

The consistency spectrum:

Consistency models range from strong (strict guarantees, higher cost) to weak (fewer guarantees, better performance). Understanding where your application falls on this spectrum guides architectural decisions.

Consistency Models in Read Replica Context
Model	Guarantee	Implementation Cost	Use Case
Strong Consistency (Linearizability)	Reads always return most recent committed write	Highest (synchronous replication required)	Financial transactions, inventory, leader election
Sequential Consistency	All operations appear in some total order consistent with program order	High	Rarely used in practice for databases
Causal Consistency	Operations that are causally related appear in order; concurrent operations may appear in any order	Moderate	Collaborative editing, social feeds
Read-Your-Writes	User sees their own writes immediately	Moderate	User profile updates, form submissions
Monotonic Reads	Once a value is read, subsequent reads never return older values	Low-Moderate	Scrolling through data, session continuity
Eventual Consistency	Reads eventually return most recent write if no further writes occur	Lowest	Analytics, caching, background synchronization

What asynchronous replication provides by default:

With standard asynchronous read replicas, you get eventual consistency—and nothing more. Any stronger guarantee requires explicit implementation. Let's examine why:

Writes commit on primary — At time T₀, a write commits and becomes durable on the primary
Replication lag exists — The replica receives and applies this write at T₀ + Δt
Reads during lag window see stale data — Any read from the replica between T₀ and T₀ + Δt returns the old value

This means:

A user writes, refreshes, and may not see their write (violates read-your-writes)
A user reads value A, reads again, and may see value B, then A again (violates monotonic reads)
Two related operations may appear out of order to an observer (violates causal consistency)

Staleness Is Not a Bug—It's a Feature

Eventual consistency enables the availability and performance benefits of read replicas. Strong consistency requires coordination that adds latency and creates availability constraints. The goal isn't to eliminate staleness but to understand it and ensure your application handles it appropriately.

Read-Your-Writes Consistency

Read-your-writes (RYW) consistency guarantees that after a client writes data, all subsequent reads by that same client will reflect that write. Other clients may still see stale data, but the writing client immediately sees their changes.

This is the most frequently needed consistency guarantee beyond eventual consistency. Users expect to see the effects of their actions immediately—updating a profile, posting a comment, or changing settings.

Why RYW is challenging with replicas:

In a replica architecture, the user's write goes to the primary while subsequent reads may go to a replica. If the replica hasn't received the write yet, the user sees stale data—their update appears to have vanished.

Converting Mermaid diagram...

Implementation strategies for RYW consistency:

Time-based routing tracks when each user last wrote data and routes reads to the primary if within a staleness window.

How it works:

After each write, record the current timestamp for that user
Before each read, check if a recent write occurred
If write was within staleness window (e.g., 5 seconds), read from primary
Otherwise, read from replica

Advantages: Simple to implement, no database-specific features required

Disadvantages: Coarse-grained (routes all reads to primary, not just affected data); relies on clock synchronization; conservative (may route to primary longer than necessary)

time-based-ryw.ts
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
// Time-based read-your-writes implementation
 
interface WriteTracker {
    userId: string;
    lastWriteTimestamp: number;
    affectedTables: Set<string>;
}
 
class TimeBasedRYWRouter {
    private writeTrackers: Map<string, WriteTracker> = new Map();
    private readonly stalenessWindowMs: number;
    
    constructor(stalenessWindowMs: number = 5000) {
        this.stalenessWindowMs = stalenessWindowMs;
    }
    
    // Call after any write operation
    recordWrite(userId: string, table: string): void {
        const existing = this.writeTrackers.get(userId);
        const tracker: WriteTracker = existing ?? {
            userId,
            lastWriteTimestamp: 0,
            affectedTables: new Set(),
        };
        
        tracker.lastWriteTimestamp = Date.now();
        tracker.affectedTables.add(table);
        this.writeTrackers.set(userId, tracker);
        
        // Cleanup old trackers to prevent memory leak
        this.scheduleCleanup(userId);
    }
    
    // Determine if read should go to primary
    shouldReadFromPrimary(userId: string, tables: string[]): boolean {
        const tracker = this.writeTrackers.get(userId);
        if (!tracker) return false;
        
        const timeSinceWrite = Date.now() - tracker.lastWriteTimestamp;
        if (timeSinceWrite > this.stalenessWindowMs) {
            return false;
        }
        
        // Check if any queried table was recently written
        return tables.some(table => tracker.affectedTables.has(table));
    }
    
    private scheduleCleanup(userId: string): void {
        setTimeout(() => {
            const tracker = this.writeTrackers.get(userId);
            if (tracker && Date.now() - tracker.lastWriteTimestamp > this.stalenessWindowMs * 2) {
                this.writeTrackers.delete(userId);
            }
        }, this.stalenessWindowMs * 2);
    }
}
 
// Usage example
const rywRouter = new TimeBasedRYWRouter(5000);
 
async function updateUserProfile(userId: string, data: ProfileData): Promise<void> {
    await primaryDb.query('UPDATE users SET ... WHERE id = ?', [userId, ...]);
    rywRouter.recordWrite(userId, 'users');
}
 
async function getUserProfile(userId: string): Promise<Profile> {
    const db = rywRouter.shouldReadFromPrimary(userId, ['users'])
        ? primaryDb
        : replicaDb;
    
    return db.query('SELECT * FROM users WHERE id = ?', [userId]);
}

Monotonic Reads Consistency

Monotonic reads guarantee that once a process reads a value, all subsequent reads by that process return the same or a more recent value—never an older one. Without this guarantee, users can experience "time travel" where data appears to move backward.

The problem without monotonic reads:

Imagine a user scrolling through a paginated list of recent orders:

Page 1 loads from Replica A (lag: 50ms) — Shows orders up to 10:00:00 AM
Page 2 loads from Replica B (lag: 500ms) — Shows orders only up to 9:59:50 AM
User sees an order "disappear" when scrolling, then "reappear" on refresh

This creates a confusing, seemingly buggy experience.

Monotonic Reads Implementation Strategies

•Replica stickiness — Pin a session to a single replica for its duration. Since one replica always makes forward progress, reads are monotonic within that session.
•Position tracking — Track the highest position (LSN/GTID) seen by the client. Only read from replicas that have reached at least that position.
•Replica lag ordering — Always route to the replica with the lowest lag. If the lowest-lag replica changes mid-session, only switch if the new replica is ahead of the previous one.

monotonic-reads.ts
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
// Monotonic reads implementation using high-water mark
 
interface SessionReadPosition {
    sessionId: string;
    highWaterMark: string; // Highest LSN seen
    lastReplicaId: string;
}
 
class MonotonicReadsRouter {
    private sessions: Map<string, SessionReadPosition> = new Map();
    private replicas: Map<string, ReplicaWithPosition>;
    
    async getConnection(sessionId: string): Promise<DatabaseConnection> {
        const session = this.sessions.get(sessionId);
        
        if (!session) {
            // New session - any replica works, but track position after
            return this.selectAndTrack(sessionId, null);
        }
        
        // Find replica that's at or ahead of high water mark
        return this.selectAndTrack(sessionId, session.highWaterMark);
    }
    
    private async selectAndTrack(
        sessionId: string, 
        minPosition: string | null
    ): Promise<DatabaseConnection> {
        // Get replica positions
        const positions = await this.getReplicaPositions();
        
        // Filter replicas that meet minimum position requirement
        let candidates = Array.from(positions.entries());
        if (minPosition) {
            candidates = candidates.filter(([_, pos]) => 
                this.comparePositions(pos, minPosition) >= 0
            );
        }
        
        if (candidates.length === 0) {
            // No replica meets requirements - use primary
            return this.primary;
        }
        
        // Select best candidate (e.g., lowest lag / random)
        const [replicaId, position] = candidates[0];
        const replica = this.replicas.get(replicaId)!;
        
        // Update session high water mark
        const existing = this.sessions.get(sessionId);
        if (!existing || this.comparePositions(position, existing.highWaterMark) > 0) {
            this.sessions.set(sessionId, {
                sessionId,
                highWaterMark: position,
                lastReplicaId: replicaId,
            });
        }
        
        return replica.connection;
    }
    
    // PostgreSQL LSN comparison
    private comparePositions(a: string, b: string): number {
        // LSN format: "0/1234ABCD"
        const parsePos = (lsn: string): bigint => BigInt('0x' + lsn.replace('/', ''));
        return Number(parsePos(a) - parsePos(b));
    }
    
    private async getReplicaPositions(): Promise<Map<string, string>> {
        const positions = new Map<string, string>();
        
        for (const [id, replica] of this.replicas) {
            try {
                const result = await replica.connection.query(
                    'SELECT pg_last_wal_replay_lsn()::text AS lsn'
                );
                positions.set(id, result.rows[0].lsn);
            } catch {
                // Skip unavailable replicas
            }
        }
        
        return positions;
    }
}

Replica Stickiness Trade-off

Replica stickiness is the simplest solution for monotonic reads but reduces load balancing effectiveness. Consider using stickiness within a request context (e.g., all reads in a single page load go to one replica) while allowing different requests to use different replicas.

Causal Consistency

Causal consistency ensures that operations that are causally related appear in that order to all observers. If operation A causally precedes operation B (A → B), then any observer who sees B must also see A.

What constitutes causality:

Read-Write dependency: Process reads X, then writes Y. Y causally depends on X.
Write-Write dependency: Process writes X, then writes Y. Y causally depends on X.
Transitivity: If A → B and B → C, then A → C.

Example of causal consistency violation:

Alice posts: "I got the job!"
Bob reads Alice's post, then comments: "Congratulations!"
Charlie reads from a replica that has Bob's comment but not Alice's post
Charlie sees "Congratulations!" with no context—confusing!

Achieving causal consistency:

True causal consistency requires tracking causal dependencies across operations. This is complex to implement from scratch but is supported by some databases natively:

MongoDB: Supports causal consistency sessions that track logical time across operations.

Cosmos DB: Offers session consistency which provides read-your-writes and monotonic reads within a session.

CockroachDB/Spanner: Use hybrid logical clocks for causal ordering.

For systems without native support, you can implement partial causal consistency by:

Including context identifiers in related operations
Waiting for dependent operations to be visible before proceeding
Reading from primary for causally-dependent queries

causal-consistency.ts
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
// Simplified causal consistency using dependency tracking
 
interface CausalToken {
    writerId: string;
    sequence: number;
    dependencies: Map<string, number>; // writer -> last seen sequence
}
 
class CausalConsistencyManager {
    private writerSequences: Map<string, number> = new Map();
    
    // Call after a write to generate a causal token
    generateToken(writerId: string, readDependencies: CausalToken[]): CausalToken {
        const currentSeq = (this.writerSequences.get(writerId) ?? 0) + 1;
        this.writerSequences.set(writerId, currentSeq);
        
        // Merge all dependencies from reads that preceded this write
        const dependencies = new Map<string, number>();
        for (const dep of readDependencies) {
            dependencies.set(dep.writerId, Math.max(
                dependencies.get(dep.writerId) ?? 0,
                dep.sequence
            ));
            // Transitively include dependencies
            for (const [writer, seq] of dep.dependencies) {
                dependencies.set(writer, Math.max(dependencies.get(writer) ?? 0, seq));
            }
        }
        
        return {
            writerId,
            sequence: currentSeq,
            dependencies,
        };
    }
    
    // Check if a replica can satisfy the causal requirements
    async canReplicaSatisfy(
        replica: DatabaseConnection, 
        requiredTokens: CausalToken[]
    ): Promise<boolean> {
        // This is simplified - real implementation would check replica state
        // against all required write sequences
        for (const token of requiredTokens) {
            const replicaHasWrite = await this.checkReplicaHasWrite(
                replica, 
                token.writerId, 
                token.sequence
            );
            if (!replicaHasWrite) return false;
            
            // Also check transitive dependencies
            for (const [writer, seq] of token.dependencies) {
                const hasDep = await this.checkReplicaHasWrite(replica, writer, seq);
                if (!hasDep) return false;
            }
        }
        
        return true;
    }
    
    private async checkReplicaHasWrite(
        replica: DatabaseConnection,
        writerId: string,
        sequence: number
    ): Promise<boolean> {
        // Implementation would check replica's causal state
        // This is highly database-specific
        return true; // Placeholder
    }
}
 
// Usage in comment system
async function postComment(context: RequestContext, content: string): Promise<Comment> {
    // This comment depends on having read the parent post
    const token = causalManager.generateToken(context.userId, context.readTokens);
    
    const comment = await primaryDb.query(
        'INSERT INTO comments (post_id, content, causal_token) VALUES (?, ?, ?) RETURNING *',
        [context.postId, content, JSON.stringify(token)]
    );
    
    return { ...comment, causalToken: token };
}
 
async function readComments(postId: string, clientTokens: CausalToken[]): Promise<Comment[]> {
    // Find replica satisfying causal requirements
    for (const replica of replicas) {
        if (await causalManager.canReplicaSatisfy(replica, clientTokens)) {
            return replica.query('SELECT * FROM comments WHERE post_id = ?', [postId]);
        }
    }
    
    // No suitable replica - read from primary
    return primaryDb.query('SELECT * FROM comments WHERE post_id = ?', [postId]);
}

Causal Consistency Complexity

Full causal consistency implementation is complex and typically requires database-level support or significant application complexity. For many applications, read-your-writes + monotonic reads provides sufficient consistency without causal tracking overhead.

Consistency Requirements by Use Case

Different features within an application have different consistency requirements. Understanding these requirements enables targeted optimization—strong consistency only where needed, eventual consistency where acceptable.

Consistency Requirements Analysis
Feature	Required Consistency	Why	Implementation
User authentication	Strong	Cannot serve stale auth state (locked accounts, changed passwords)	Read from primary
Account balance display	Read-your-writes	User must see effect of their transactions	Position-based RYW
Balance for transactions	Strong	Must prevent overdraft, double-spend	Read from primary
User profile editing	Read-your-writes	User expects immediate visibility of changes	Time-based RYW
Viewing another user's profile	Eventual	Brief staleness is invisible	Any replica
Product catalog	Eventual	Slight delays in price/availability acceptable	Any replica
Inventory check (display)	Eventual	Approximate stock is acceptable for browsing	Any replica
Inventory check (checkout)	Strong	Must prevent overselling	Read from primary
Activity feed (own)	Monotonic + RYW	Shouldn't see own posts disappear	Session stickiness
Activity feed (others)	Eventual	Brief delays in new content acceptable	Any replica
Search results	Eventual	Indexing lag is expected	Dedicated replica
Analytics dashboards	Eventual	Data is already aggregated/delayed	Analytics replica

The layered consistency approach:

In practice, applications implement multiple consistency strategies simultaneously:

Default layer: Eventual consistency—reads go to replicas by default
User context layer: Read-your-writes—after user writes, reads route to primary temporarily
Critical path layer: Strong consistency—specific operations always use primary

This layered approach maximizes replica utilization while ensuring correctness for sensitive operations.

layered-consistency.ts
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
// Layered consistency router
 
type ConsistencyLevel = 'eventual' | 'ryw' | 'strong';
 
interface QueryContext {
    userId: string;
    operation: string;
    tables: string[];
}
 
class LayeredConsistencyRouter {
    private rywTracker: TimeBasedRYWRouter;
    private criticalOperations: Set<string>;
    private criticalTables: Set<string>;
    
    constructor() {
        this.rywTracker = new TimeBasedRYWRouter(5000);
        this.criticalOperations = new Set([
            'checkAccountBalance',
            'processPayment',
            'checkInventoryForPurchase',
            'authenticateUser',
        ]);
        this.criticalTables = new Set([
            'account_balances',
            'inventory_reserved',
            'user_credentials',
        ]);
    }
    
    getConsistencyLevel(context: QueryContext): ConsistencyLevel {
        // Layer 3: Critical path - always strong
        if (this.criticalOperations.has(context.operation)) {
            return 'strong';
        }
        
        if (context.tables.some(t => this.criticalTables.has(t))) {
            return 'strong';
        }
        
        // Layer 2: User context - RYW if recent writes
        if (this.rywTracker.shouldReadFromPrimary(context.userId, context.tables)) {
            return 'ryw';
        }
        
        // Layer 1: Default - eventual
        return 'eventual';
    }
    
    getConnection(context: QueryContext): DatabaseConnection {
        const level = this.getConsistencyLevel(context);
        
        switch (level) {
            case 'strong':
            case 'ryw':
                return primaryConnection;
            case 'eventual':
                return loadBalancer.getReplicaConnection();
        }
    }
    
    recordWrite(userId: string, tables: string[]): void {
        for (const table of tables) {
            this.rywTracker.recordWrite(userId, table);
        }
    }
}

Summary: Consistency Considerations

Consistency in read replica architectures requires deliberate design. The default eventual consistency is acceptable for many use cases, but specific scenarios demand stronger guarantees.

Key Takeaways

•Asynchronous replication provides eventual consistency only — Stronger guarantees require explicit implementation. Understand what you get by default.
•Read-your-writes is the most commonly needed guarantee — Users expect to see their own changes. Implement via time-based routing, position tracking, or session stickiness.
•Monotonic reads prevent 'time travel' — Ensure users never see older data after seeing newer data. Replica stickiness or position tracking achieves this.
•Causal consistency is powerful but complex — Consider database-level support or simpler RYW + monotonic read combinations for most use cases.
•Different features need different consistency — Analyze each use case. Use strong consistency only where required; eventual consistency maximizes replica value.
•Layered approaches optimize for both — Default to eventual, layer in RYW for user sessions, and use strong consistency for critical paths.

What's next:

The final page in this module covers Replica Promotion—how to handle primary failure by promoting a replica, the considerations for safe promotion, and patterns for maintaining availability during failover.

Page Complete

You now understand the consistency landscape for read replica architectures and have concrete strategies for implementing the consistency guarantees your application requires. This knowledge enables you to make informed trade-offs between consistency, availability, and performance.

4 / 5

Loading learning content...

System Design (HLD)Read Replicas

Read Replicas: Scaling SQL Reads

LevelIntermediate

Duration75 mins

TopicRead Replicas

4 / 5

Consistency Considerations

The Consistency-Availability Trade-off

What You Will Learn

Consistency Models Primer

The consistency spectrum:

Consistency Models in Read Replica Context
Model	Guarantee	Implementation Cost	Use Case
Strong Consistency (Linearizability)	Reads always return most recent committed write	Highest (synchronous replication required)	Financial transactions, inventory, leader election
Sequential Consistency	All operations appear in some total order consistent with program order	High	Rarely used in practice for databases
Causal Consistency	Operations that are causally related appear in order; concurrent operations may appear in any order	Moderate	Collaborative editing, social feeds
Read-Your-Writes	User sees their own writes immediately	Moderate	User profile updates, form submissions
Monotonic Reads	Once a value is read, subsequent reads never return older values	Low-Moderate	Scrolling through data, session continuity
Eventual Consistency	Reads eventually return most recent write if no further writes occur	Lowest	Analytics, caching, background synchronization

What asynchronous replication provides by default:

With standard asynchronous read replicas, you get eventual consistency—and nothing more. Any stronger guarantee requires explicit implementation. Let's examine why:

Writes commit on primary — At time T₀, a write commits and becomes durable on the primary
Replication lag exists — The replica receives and applies this write at T₀ + Δt
Reads during lag window see stale data — Any read from the replica between T₀ and T₀ + Δt returns the old value

This means:

A user writes, refreshes, and may not see their write (violates read-your-writes)
A user reads value A, reads again, and may see value B, then A again (violates monotonic reads)
Two related operations may appear out of order to an observer (violates causal consistency)

Staleness Is Not a Bug—It's a Feature

Read-Your-Writes Consistency

Why RYW is challenging with replicas:

Converting Mermaid diagram...

Implementation strategies for RYW consistency:

Time-based routing tracks when each user last wrote data and routes reads to the primary if within a staleness window.

How it works:

After each write, record the current timestamp for that user
Before each read, check if a recent write occurred
If write was within staleness window (e.g., 5 seconds), read from primary
Otherwise, read from replica

Advantages: Simple to implement, no database-specific features required

Disadvantages: Coarse-grained (routes all reads to primary, not just affected data); relies on clock synchronization; conservative (may route to primary longer than necessary)

time-based-ryw.ts
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
// Time-based read-your-writes implementation
 
interface WriteTracker {
    userId: string;
    lastWriteTimestamp: number;
    affectedTables: Set<string>;
}
 
class TimeBasedRYWRouter {
    private writeTrackers: Map<string, WriteTracker> = new Map();
    private readonly stalenessWindowMs: number;
    
    constructor(stalenessWindowMs: number = 5000) {
        this.stalenessWindowMs = stalenessWindowMs;
    }
    
    // Call after any write operation
    recordWrite(userId: string, table: string): void {
        const existing = this.writeTrackers.get(userId);
        const tracker: WriteTracker = existing ?? {
            userId,
            lastWriteTimestamp: 0,
            affectedTables: new Set(),
        };
        
        tracker.lastWriteTimestamp = Date.now();
        tracker.affectedTables.add(table);
        this.writeTrackers.set(userId, tracker);
        
        // Cleanup old trackers to prevent memory leak
        this.scheduleCleanup(userId);
    }
    
    // Determine if read should go to primary
    shouldReadFromPrimary(userId: string, tables: string[]): boolean {
        const tracker = this.writeTrackers.get(userId);
        if (!tracker) return false;
        
        const timeSinceWrite = Date.now() - tracker.lastWriteTimestamp;
        if (timeSinceWrite > this.stalenessWindowMs) {
            return false;
        }
        
        // Check if any queried table was recently written
        return tables.some(table => tracker.affectedTables.has(table));
    }
    
    private scheduleCleanup(userId: string): void {
        setTimeout(() => {
            const tracker = this.writeTrackers.get(userId);
            if (tracker && Date.now() - tracker.lastWriteTimestamp > this.stalenessWindowMs * 2) {
                this.writeTrackers.delete(userId);
            }
        }, this.stalenessWindowMs * 2);
    }
}
 
// Usage example
const rywRouter = new TimeBasedRYWRouter(5000);
 
async function updateUserProfile(userId: string, data: ProfileData): Promise<void> {
    await primaryDb.query('UPDATE users SET ... WHERE id = ?', [userId, ...]);
    rywRouter.recordWrite(userId, 'users');
}
 
async function getUserProfile(userId: string): Promise<Profile> {
    const db = rywRouter.shouldReadFromPrimary(userId, ['users'])
        ? primaryDb
        : replicaDb;
    
    return db.query('SELECT * FROM users WHERE id = ?', [userId]);
}

Monotonic Reads Consistency

The problem without monotonic reads:

Imagine a user scrolling through a paginated list of recent orders:

Page 1 loads from Replica A (lag: 50ms) — Shows orders up to 10:00:00 AM
Page 2 loads from Replica B (lag: 500ms) — Shows orders only up to 9:59:50 AM
User sees an order "disappear" when scrolling, then "reappear" on refresh

This creates a confusing, seemingly buggy experience.

Monotonic Reads Implementation Strategies

•Replica stickiness — Pin a session to a single replica for its duration. Since one replica always makes forward progress, reads are monotonic within that session.
•Position tracking — Track the highest position (LSN/GTID) seen by the client. Only read from replicas that have reached at least that position.
•Replica lag ordering — Always route to the replica with the lowest lag. If the lowest-lag replica changes mid-session, only switch if the new replica is ahead of the previous one.

monotonic-reads.ts
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
// Monotonic reads implementation using high-water mark
 
interface SessionReadPosition {
    sessionId: string;
    highWaterMark: string; // Highest LSN seen
    lastReplicaId: string;
}
 
class MonotonicReadsRouter {
    private sessions: Map<string, SessionReadPosition> = new Map();
    private replicas: Map<string, ReplicaWithPosition>;
    
    async getConnection(sessionId: string): Promise<DatabaseConnection> {
        const session = this.sessions.get(sessionId);
        
        if (!session) {
            // New session - any replica works, but track position after
            return this.selectAndTrack(sessionId, null);
        }
        
        // Find replica that's at or ahead of high water mark
        return this.selectAndTrack(sessionId, session.highWaterMark);
    }
    
    private async selectAndTrack(
        sessionId: string, 
        minPosition: string | null
    ): Promise<DatabaseConnection> {
        // Get replica positions
        const positions = await this.getReplicaPositions();
        
        // Filter replicas that meet minimum position requirement
        let candidates = Array.from(positions.entries());
        if (minPosition) {
            candidates = candidates.filter(([_, pos]) => 
                this.comparePositions(pos, minPosition) >= 0
            );
        }
        
        if (candidates.length === 0) {
            // No replica meets requirements - use primary
            return this.primary;
        }
        
        // Select best candidate (e.g., lowest lag / random)
        const [replicaId, position] = candidates[0];
        const replica = this.replicas.get(replicaId)!;
        
        // Update session high water mark
        const existing = this.sessions.get(sessionId);
        if (!existing || this.comparePositions(position, existing.highWaterMark) > 0) {
            this.sessions.set(sessionId, {
                sessionId,
                highWaterMark: position,
                lastReplicaId: replicaId,
            });
        }
        
        return replica.connection;
    }
    
    // PostgreSQL LSN comparison
    private comparePositions(a: string, b: string): number {
        // LSN format: "0/1234ABCD"
        const parsePos = (lsn: string): bigint => BigInt('0x' + lsn.replace('/', ''));
        return Number(parsePos(a) - parsePos(b));
    }
    
    private async getReplicaPositions(): Promise<Map<string, string>> {
        const positions = new Map<string, string>();
        
        for (const [id, replica] of this.replicas) {
            try {
                const result = await replica.connection.query(
                    'SELECT pg_last_wal_replay_lsn()::text AS lsn'
                );
                positions.set(id, result.rows[0].lsn);
            } catch {
                // Skip unavailable replicas
            }
        }
        
        return positions;
    }
}

Replica Stickiness Trade-off

Causal Consistency

What constitutes causality:

Read-Write dependency: Process reads X, then writes Y. Y causally depends on X.
Write-Write dependency: Process writes X, then writes Y. Y causally depends on X.
Transitivity: If A → B and B → C, then A → C.

Example of causal consistency violation:

Alice posts: "I got the job!"
Bob reads Alice's post, then comments: "Congratulations!"
Charlie reads from a replica that has Bob's comment but not Alice's post
Charlie sees "Congratulations!" with no context—confusing!

Achieving causal consistency:

True causal consistency requires tracking causal dependencies across operations. This is complex to implement from scratch but is supported by some databases natively:

MongoDB: Supports causal consistency sessions that track logical time across operations.

Cosmos DB: Offers session consistency which provides read-your-writes and monotonic reads within a session.

CockroachDB/Spanner: Use hybrid logical clocks for causal ordering.

For systems without native support, you can implement partial causal consistency by:

Including context identifiers in related operations
Waiting for dependent operations to be visible before proceeding
Reading from primary for causally-dependent queries

causal-consistency.ts
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
// Simplified causal consistency using dependency tracking
 
interface CausalToken {
    writerId: string;
    sequence: number;
    dependencies: Map<string, number>; // writer -> last seen sequence
}
 
class CausalConsistencyManager {
    private writerSequences: Map<string, number> = new Map();
    
    // Call after a write to generate a causal token
    generateToken(writerId: string, readDependencies: CausalToken[]): CausalToken {
        const currentSeq = (this.writerSequences.get(writerId) ?? 0) + 1;
        this.writerSequences.set(writerId, currentSeq);
        
        // Merge all dependencies from reads that preceded this write
        const dependencies = new Map<string, number>();
        for (const dep of readDependencies) {
            dependencies.set(dep.writerId, Math.max(
                dependencies.get(dep.writerId) ?? 0,
                dep.sequence
            ));
            // Transitively include dependencies
            for (const [writer, seq] of dep.dependencies) {
                dependencies.set(writer, Math.max(dependencies.get(writer) ?? 0, seq));
            }
        }
        
        return {
            writerId,
            sequence: currentSeq,
            dependencies,
        };
    }
    
    // Check if a replica can satisfy the causal requirements
    async canReplicaSatisfy(
        replica: DatabaseConnection, 
        requiredTokens: CausalToken[]
    ): Promise<boolean> {
        // This is simplified - real implementation would check replica state
        // against all required write sequences
        for (const token of requiredTokens) {
            const replicaHasWrite = await this.checkReplicaHasWrite(
                replica, 
                token.writerId, 
                token.sequence
            );
            if (!replicaHasWrite) return false;
            
            // Also check transitive dependencies
            for (const [writer, seq] of token.dependencies) {
                const hasDep = await this.checkReplicaHasWrite(replica, writer, seq);
                if (!hasDep) return false;
            }
        }
        
        return true;
    }
    
    private async checkReplicaHasWrite(
        replica: DatabaseConnection,
        writerId: string,
        sequence: number
    ): Promise<boolean> {
        // Implementation would check replica's causal state
        // This is highly database-specific
        return true; // Placeholder
    }
}
 
// Usage in comment system
async function postComment(context: RequestContext, content: string): Promise<Comment> {
    // This comment depends on having read the parent post
    const token = causalManager.generateToken(context.userId, context.readTokens);
    
    const comment = await primaryDb.query(
        'INSERT INTO comments (post_id, content, causal_token) VALUES (?, ?, ?) RETURNING *',
        [context.postId, content, JSON.stringify(token)]
    );
    
    return { ...comment, causalToken: token };
}
 
async function readComments(postId: string, clientTokens: CausalToken[]): Promise<Comment[]> {
    // Find replica satisfying causal requirements
    for (const replica of replicas) {
        if (await causalManager.canReplicaSatisfy(replica, clientTokens)) {
            return replica.query('SELECT * FROM comments WHERE post_id = ?', [postId]);
        }
    }
    
    // No suitable replica - read from primary
    return primaryDb.query('SELECT * FROM comments WHERE post_id = ?', [postId]);
}

Causal Consistency Complexity

Consistency Requirements by Use Case

Consistency Requirements Analysis
Feature	Required Consistency	Why	Implementation
User authentication	Strong	Cannot serve stale auth state (locked accounts, changed passwords)	Read from primary
Account balance display	Read-your-writes	User must see effect of their transactions	Position-based RYW
Balance for transactions	Strong	Must prevent overdraft, double-spend	Read from primary
User profile editing	Read-your-writes	User expects immediate visibility of changes	Time-based RYW
Viewing another user's profile	Eventual	Brief staleness is invisible	Any replica
Product catalog	Eventual	Slight delays in price/availability acceptable	Any replica
Inventory check (display)	Eventual	Approximate stock is acceptable for browsing	Any replica
Inventory check (checkout)	Strong	Must prevent overselling	Read from primary
Activity feed (own)	Monotonic + RYW	Shouldn't see own posts disappear	Session stickiness
Activity feed (others)	Eventual	Brief delays in new content acceptable	Any replica
Search results	Eventual	Indexing lag is expected	Dedicated replica
Analytics dashboards	Eventual	Data is already aggregated/delayed	Analytics replica

The layered consistency approach:

In practice, applications implement multiple consistency strategies simultaneously:

Default layer: Eventual consistency—reads go to replicas by default
User context layer: Read-your-writes—after user writes, reads route to primary temporarily
Critical path layer: Strong consistency—specific operations always use primary

This layered approach maximizes replica utilization while ensuring correctness for sensitive operations.

layered-consistency.ts
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
// Layered consistency router
 
type ConsistencyLevel = 'eventual' | 'ryw' | 'strong';
 
interface QueryContext {
    userId: string;
    operation: string;
    tables: string[];
}
 
class LayeredConsistencyRouter {
    private rywTracker: TimeBasedRYWRouter;
    private criticalOperations: Set<string>;
    private criticalTables: Set<string>;
    
    constructor() {
        this.rywTracker = new TimeBasedRYWRouter(5000);
        this.criticalOperations = new Set([
            'checkAccountBalance',
            'processPayment',
            'checkInventoryForPurchase',
            'authenticateUser',
        ]);
        this.criticalTables = new Set([
            'account_balances',
            'inventory_reserved',
            'user_credentials',
        ]);
    }
    
    getConsistencyLevel(context: QueryContext): ConsistencyLevel {
        // Layer 3: Critical path - always strong
        if (this.criticalOperations.has(context.operation)) {
            return 'strong';
        }
        
        if (context.tables.some(t => this.criticalTables.has(t))) {
            return 'strong';
        }
        
        // Layer 2: User context - RYW if recent writes
        if (this.rywTracker.shouldReadFromPrimary(context.userId, context.tables)) {
            return 'ryw';
        }
        
        // Layer 1: Default - eventual
        return 'eventual';
    }
    
    getConnection(context: QueryContext): DatabaseConnection {
        const level = this.getConsistencyLevel(context);
        
        switch (level) {
            case 'strong':
            case 'ryw':
                return primaryConnection;
            case 'eventual':
                return loadBalancer.getReplicaConnection();
        }
    }
    
    recordWrite(userId: string, tables: string[]): void {
        for (const table of tables) {
            this.rywTracker.recordWrite(userId, table);
        }
    }
}

Summary: Consistency Considerations

Consistency in read replica architectures requires deliberate design. The default eventual consistency is acceptable for many use cases, but specific scenarios demand stronger guarantees.

Key Takeaways

•Asynchronous replication provides eventual consistency only — Stronger guarantees require explicit implementation. Understand what you get by default.
•Read-your-writes is the most commonly needed guarantee — Users expect to see their own changes. Implement via time-based routing, position tracking, or session stickiness.
•Monotonic reads prevent 'time travel' — Ensure users never see older data after seeing newer data. Replica stickiness or position tracking achieves this.
•Causal consistency is powerful but complex — Consider database-level support or simpler RYW + monotonic read combinations for most use cases.
•Different features need different consistency — Analyze each use case. Use strong consistency only where required; eventual consistency maximizes replica value.
•Layered approaches optimize for both — Default to eventual, layer in RYW for user sessions, and use strong consistency for critical paths.

What's next:

Page Complete

4 / 5