How to Route Reads to a PostgreSQL Replication Replica
A read-heavy API pins the primary at 80% CPU while a replica sits idle. The fix is a routing layer that sends reads to the replica and writes to the primary - plus a 5-second session pin so a user always reads back the row they just wrote, even before it replicates. Here is how to build it.
Why one database node is the bottleneck
Most APIs are roughly 90% reads, 10% writes - list endpoints and single-row lookups dominate. When every query hits a single primary, that read traffic is what drives CPU toward saturation. PostgreSQL replication is the cheapest horizontal scale for this: stand up a read replica, send reads there, and keep writes on the primary.
The catch is replication lag. A user inserts a comment, the UI refreshes, the read goes to the replica - and the row has not replicated yet, so it is not there. Classic "I saved it, why isn't it showing?" bug. The routing layer has to handle that case, not just split traffic.
Split the connections in db.py
Two connection URLs, two psycopg2.connect paths. Writes go to
WRITE_DATABASE_URL (primary), reads go to READ_DATABASE_URL (replica):
import os
import time
import psycopg2
WRITE_DATABASE_URL = os.environ["WRITE_DATABASE_URL"] # primary, port 5432
READ_DATABASE_URL = os.environ["READ_DATABASE_URL"] # replica, port 5433
PIN_SECONDS = 5
# session_id -> unix timestamp after which the pin expires
_pins: dict[str, float] = {}
def _is_pinned(session_id: str | None) -> bool:
if not session_id:
return False
expiry = _pins.get(session_id)
return expiry is not None and expiry > time.time()
def get_write_conn():
return psycopg2.connect(WRITE_DATABASE_URL)
def get_read_conn(session_id: str | None = None):
if _is_pinned(session_id):
return psycopg2.connect(WRITE_DATABASE_URL) # read-your-own-writes
return psycopg2.connect(READ_DATABASE_URL)
def mark_wrote(session_id: str | None) -> None:
if not session_id:
return
_pins[session_id] = time.time() + PIN_SECONDS
Wire it into the handlers
Route by HTTP verb. GET handlers call get_read_conn, POST/PUT/DELETE call
get_write_conn, and every write calls mark_wrote to arm the pin. The
session is identified by an X-Session-Id header passed straight through:
@app.get("/api/comments")
def list_comments(x_session_id: str | None = Header(None, alias="X-Session-Id")):
conn = db.get_read_conn(x_session_id) # -> replica
...
@app.post("/api/comments")
def create_comment(body: dict, x_session_id: str | None = Header(None, alias="X-Session-Id")):
conn = db.get_write_conn() # -> primary
cur.execute("INSERT INTO comments (author, body) VALUES (%s, %s) RETURNING id", ...)
conn.commit()
db.mark_wrote(x_session_id) # arm the 5s pin
How the pin dodges replication lag
After a write, that session's next reads go to the primary for 5 seconds - long enough for the replica to catch up. Other sessions keep reading the replica the whole time. You can prove the two data paths are distinct: write a row, query the replica directly, and confirm it is not there yet:
curl -X POST -H 'X-Session-Id: s1' -H 'Content-Type: application/json' \
-d '{"author":"x","body":"y"}' http://localhost:8000/api/comments
# replica does NOT have the row yet (no auto-replication in this scenario):
psql -h localhost -p 5433 -U postgres -d app -c \
"SELECT count(*) FROM comments WHERE author = 'x'" # -> 0
# but the pinned session reads it back from the primary:
curl -H 'X-Session-Id: s1' http://localhost:8000/api/comments/<new_id> # 200
A different, unpinned session hitting the same id gets a 404 - it is still
reading the replica, where the row has not landed. That difference is exactly
the lag the pin protects the user from. Five seconds is the industry-standard
window for read-your-own-writes.
Want to try it hands-on? HeyDevJob gives you this exact setup in a live cloud workspace in your browser - edit it, run it, and see it work. Free, nothing to install.
Try it in a workspace →What you'll practice
- Splitting a psycopg2 DB layer into separate primary (write) and replica (read) connections
- Routing reads to a replica and writes to the primary based on HTTP verb
- Implementing a session pin so read-your-own-writes survives replication lag
FAQ
How do I route reads to a PostgreSQL read replica?
Open two connections - one to the primary for writes, one to the replica for reads - and pick between them per query. In an API, send GET handlers to the replica connection and POST/PUT/DELETE handlers to the primary connection.
What is replication lag and how do I handle it?
Replication lag is the short delay before a write on the primary appears on a replica. It breaks read-your-own-writes: a user saves a row, then reads from the replica and does not see it. The standard fix is to pin that session to the primary for a few seconds (around 5) after any write.
Should reads and writes use the same database connection?
For a read-replica setup, no. Writes must go to the primary because replicas are read-only, and reads should go to a replica to offload the primary. Keep two connection URLs and route each query to the right one.
What is read-your-own-writes consistency?
It is the guarantee that a client always sees its own most recent write, even under replication lag. You achieve it by routing a session's reads to the primary for a short window after it writes, then letting reads fall back to the replica once the data has replicated.
Keep learning
Learn it by doing. Open this in a live cloud workspace, make the change yourself, and keep a record of the work you can share.
Open the workspace →