Read/Write Split Engine & Routing

Learning outcomes

• Route read traffic dynamically to replica instances
• Ensure write consistency across multi-node databases

Mental model

In high-traffic systems, 80-90% of database queries are read-only (SELECT). Splitting queries between a single Primary database (handling INSERT/UPDATE/DELETE) and multiple Read Replicas prevents primary CPU starvation.

Theory

FastAPI applications configure two distinct SQLAlchemy engines: primary_engine connected to the primary write host, and replica_engine connected to a load-balanced endpoint across read-only standbys.

from fastapi import FastAPI, Depends
from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker, AsyncSession
from typing import AsyncGenerator

PRIMARY_URL = "postgresql+asyncpg://user:pass@primary-db:5432/app_db"
REPLICA_URL = "postgresql+asyncpg://user:pass@replica-db:5432/app_db"

primary_engine = create_async_engine(PRIMARY_URL, pool_size=10)
replica_engine = create_async_engine(REPLICA_URL, pool_size=30)

PrimarySessionFactory = async_sessionmaker(primary_engine, expire_on_commit=False)
ReplicaSessionFactory = async_sessionmaker(replica_engine, expire_on_commit=False)

async def get_read_db() -> AsyncGenerator[AsyncSession, None]:
    async with ReplicaSessionFactory() as session:
        yield session

async def get_write_db() -> AsyncGenerator[AsyncSession, None]:
    async with PrimarySessionFactory() as session:
        async with session.begin():
            yield session

Alternatives and trade-offs

• Single Database Host: Simple; limited by single-server CPU/RAM limits.
• Application-Level Read/Write Split: Maximizes read scale; risks Read-Your-Own-Writes lag anomalies if reads occur immediately after a write.

Failure modes and misconceptions

1. Read-Your-Own-Writes Anomaly: User updates profile (POST), gets redirected (GET), but the read replica has 20ms replication lag, displaying stale data. Remedy: Route reads following a write to the Primary engine for a short window.
2. Executing Mutating DDL/DML on Replica: Attempting INSERT or UPDATE on a read replica raises cannot execute INSERT in a read-only transaction.

Decision scenario

Inject get_read_db dependencies into analytical GET list endpoints and get_write_db dependencies into state-mutating POST/PUT/DELETE endpoints to scale read throughput independently.

Learning outcomes

• Architect dual-engine read/write split database routing in FastAPI.
• Mitigate replication lag anomalies (Read-Your-Own-Writes).
• Scale read throughput across load-balanced database replica pools.

Trade-offs

Read/write splitting offloads read heavy workloads from the primary database, but requires managing replication lag consistency windows.

Primary sources

• FastAPI Framework Architecture & Dependency Injection Specification — Tiangolo, verified 2026-07-22