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.
HTTP Request Route
Inspect HTTP Method (GET vs POST/PUT/DELETE)
GET Route -> Query Read Replica Engine Pool
POST Route -> Query Primary Database Engine
Streaming Replication Synchronization
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.
python(22 lines)
1from fastapi import FastAPI, Depends
2from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker, AsyncSession
3from typing import AsyncGenerator
4
5PRIMARY_URL = "postgresql+asyncpg://user:pass@primary-db:5432/app_db"
6REPLICA_URL = "postgresql+asyncpg://user:pass@replica-db:5432/app_db"
7
8primary_engine = create_async_engine(PRIMARY_URL, pool_size=10)
9replica_engine = create_async_engine(REPLICA_URL, pool_size=30)
10
11PrimarySessionFactory = async_sessionmaker(primary_engine, expire_on_commit=False)
12ReplicaSessionFactory = async_sessionmaker(replica_engine, expire_on_commit=False)
13
14async def get_read_db() -> AsyncGenerator[AsyncSession, None]:
15 async with ReplicaSessionFactory() as session:
16 yield session
17
18async def get_write_db() -> AsyncGenerator[AsyncSession, None]:
19 async with PrimarySessionFactory() as session:
20 async with session.begin():
21 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
- 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. - Executing Mutating DDL/DML on Replica: Attempting
INSERTorUPDATEon a read replica raisescannot execute INSERT in a read-only transaction.
Reflect before revealing the guide
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.