Configuring Django Database Routers for Read Replicas

Problem statement: Your Django app runs every query against default, the primary is CPU-bound on SELECTs, and you need DATABASE_ROUTERS to steer reads to replicas and writes to the primary — without sprinkling .using() across the codebase or breaking migrations.

Symptom Identification

Watch for these before and during a router rollout:

  • The primary’s read IOPS and connection count dominate while provisioned replicas idle — Django has no DATABASE_ROUTERS configured, so every QuerySet uses default.
  • After adding a router, migrations fail with relation already exists or attempt to run against a replica — allow_migrate is missing or returns True for replica aliases.
  • Read-your-writes bugs: a form saves a row, the redirect target reads a replica that has not applied the change, and the object appears missing. This is replication lag surfacing through naive routing.
  • With ATOMIC_REQUESTS = True, reads that you expected on a replica still land on default, because the request-wrapping transaction pins the connection.
  • django.db.utils.ProgrammingError: cannot execute ... in a read-only transaction on a replica — a write path leaked into a read alias because a save() ran inside a block you forced onto a replica.

Root Cause Analysis

Django’s ORM decides which database serves a query by consulting each router in DATABASE_ROUTERS, in order, until one returns a non-None alias. A router is a plain class exposing four hooks: db_for_read, db_for_write, allow_relation, and allow_migrate. If no router answers, Django falls back to default. There is no built-in read/write split — you must supply the routing policy.

Three design facts govern a correct router. First, db_for_write must always return the primary alias (default), because replicas reject DML. Second, allow_migrate must return True only for the primary so manage.py migrate never touches a read-only standby — replicas receive schema changes through physical or logical replication, not through Django. Third, db_for_read is called once per query, so any replica-selection strategy (random, weighted, least-lag) runs at query granularity, which is exactly where read-your-writes consistency can break if two reads in one request hit replicas at different lag positions.

Django’s ATOMIC_REQUESTS setting complicates this. When on, Django wraps each request in a transaction on default, so queries in that request run on the primary connection regardless of the router’s db_for_read. This is a frequent source of “my replica gets no traffic” confusion and is addressed in the pitfalls section. The router hooks are Django’s slice of the broader ORM middleware routing model.

Architecture: The Router Decision Path

Django DATABASE_ROUTERS decision path A QuerySet consults the router. db_for_write and allow_migrate return default (primary). db_for_read returns a weighted replica alias unless a sticky-after-write flag is set, in which case it returns default. Replica aliases map to separate replica databases. QuerySet read / write Router db_for_write db_for_read allow_migrate sticky flag? default writes · migrations · sticky WRITE replica_1 (w=2) replica_2 (w=1) READ Replica pool

Step-by-Step Resolution

Step 1: Declare replica aliases in DATABASES

Add one alias per replica. Mark each read-only at the session level so a misrouted write fails on the replica rather than silently succeeding against a read-only backend. Reuse the primary’s connection parameters except host.

python
# settings.py
DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "NAME": "app", "USER": "app", "PASSWORD": "secret",
        "HOST": "primary.db.internal", "PORT": "5432",
        "CONN_MAX_AGE": 60,
    },
    "replica_1": {
        "ENGINE": "django.db.backends.postgresql",
        "NAME": "app", "USER": "app_ro", "PASSWORD": "secret",
        "HOST": "replica-1.db.internal", "PORT": "5432",
        "CONN_MAX_AGE": 60,
        "OPTIONS": {"options": "-c default_transaction_read_only=on"},
        "TEST": {"MIRROR": "default"},
    },
    "replica_2": {
        "ENGINE": "django.db.backends.postgresql",
        "NAME": "app", "USER": "app_ro", "PASSWORD": "secret",
        "HOST": "replica-2.db.internal", "PORT": "5432",
        "CONN_MAX_AGE": 60,
        "OPTIONS": {"options": "-c default_transaction_read_only=on"},
        "TEST": {"MIRROR": "default"},
    },
}

"TEST": {"MIRROR": "default"} tells Django’s test runner to treat each replica as an alias of default so it does not try to create a separate test database per replica.

Inline verification: python manage.py dbshell --database replica_1 -c "SELECT pg_is_in_recovery();" returns t.

Step 2: Implement the router class

The router holds all four hooks. Reads pick a replica; writes and relations resolve to default; migrations run only on default.

python
# app/routers.py
import random

REPLICAS = ["replica_1", "replica_2"]

class ReplicaRouter:
    def db_for_read(self, model, **hints):
        return random.choice(REPLICAS)

    def db_for_write(self, model, **hints):
        return "default"

    def allow_relation(self, obj1, obj2, **hints):
        # Primary and replicas hold the same data, so relations are always allowed
        db_set = {"default", *REPLICAS}
        if obj1._state.db in db_set and obj2._state.db in db_set:
            return True
        return None

    def allow_migrate(self, db, app_label, model_name=None, **hints):
        # Schema changes only ever run on the primary
        return db == "default"

Register it:

python
# settings.py
DATABASE_ROUTERS = ["app.routers.ReplicaRouter"]

Inline verification: Model.objects.all().db returns replica_1 or replica_2; Model.objects.create(...)._state.db returns default.

Step 3: Add weighted replica selection

Uniform random.choice sends equal load to every replica. If replicas differ in size, weight the selection so the larger node absorbs proportionally more reads.

python
# app/routers.py
import random

# alias -> weight; a weight-2 replica takes twice the read share of a weight-1
REPLICA_WEIGHTS = {"replica_1": 2, "replica_2": 1}
_ALIASES = list(REPLICA_WEIGHTS)
_WEIGHTS = list(REPLICA_WEIGHTS.values())

class ReplicaRouter:
    def db_for_read(self, model, **hints):
        return random.choices(_ALIASES, weights=_WEIGHTS, k=1)[0]

    def db_for_write(self, model, **hints):
        return "default"
    # allow_relation / allow_migrate unchanged

For lag-aware selection, replace the static weights with a cache-backed lookup that zeroes the weight of any replica whose lag exceeds your budget — the same exclusion logic described in routing queries based on data freshness requirements.

Inline verification: over 3,000 reads, collections.Counter of qs.db shows roughly a 2:1 split between replica_1 and replica_2.

Step 4: Override routing per query with using()

The router is the default policy; using() is the explicit override. Use it for the reads that must be strongly consistent, and for management commands that must target a specific node.

python
# Force this read onto the primary (read-your-writes)
order = Order.objects.using("default").get(pk=order_id)

# Pin a heavy report to a specific replica
rows = Invoice.objects.using("replica_2").filter(status="paid")

# Writes ignore the router entirely when using() names an alias
Account.objects.using("default").create(owner=user)

using() bypasses db_for_read/db_for_write for that QuerySet, so it is the reliable escape hatch when routing policy is not enough.

Inline verification: Order.objects.using("default").get(...).​_state.db == "default" even while the router’s default is a replica.

Step 5: Keep migrations on the primary and handle transactions

allow_migrate already restricts schema changes to default. Run migrations only against the primary:

bash
python manage.py migrate --database default

Never run migrate against a replica alias — the standby is read-only and receives DDL through replication. For the transaction pitfalls, decide on ATOMIC_REQUESTS deliberately:

python
# settings.py — read-heavy service: do NOT wrap every request in a txn on default
DATABASES["default"]["ATOMIC_REQUESTS"] = False

With ATOMIC_REQUESTS = True, each request opens a transaction on default, and reads inside it stay on the primary connection. If you keep it on for write safety, wrap only the mutating views with @transaction.atomic instead, leaving read views free to hit replicas.

Inline verification: with ATOMIC_REQUESTS = False, a GET-only view shows its SELECTs in pg_stat_activity on a replica host, not on the primary.

Step 6: Add sticky-after-write routing for read-your-writes

Pin reads to the primary for the remainder of a request once that request has written, so users see their own changes despite replication lag.

python
# app/routers.py
import threading, random

_state = threading.local()
REPLICAS = ["replica_1", "replica_2"]

class StickyReplicaRouter:
    def db_for_read(self, model, **hints):
        if getattr(_state, "wrote", False):
            return "default"      # read-your-writes: stay on primary this request
        return random.choice(REPLICAS)

    def db_for_write(self, model, **hints):
        _state.wrote = True
        return "default"

    def allow_relation(self, obj1, obj2, **hints):
        return True

    def allow_migrate(self, db, app_label, model_name=None, **hints):
        return db == "default"

Reset the flag at the end of each request with middleware:

python
# app/middleware.py
from app.routers import _state

class ResetStickyMiddleware:
    def __init__(self, get_response):
        self.get_response = get_response

    def __call__(self, request):
        _state.wrote = False
        return self.get_response(request)

Inline verification: a POST that writes, followed by a read in the same request, resolves the read to default; a fresh GET-only request resolves to a replica.

Configuration Snippet

The complete, production-shaped router plus middleware registration:

python
# settings.py
DATABASE_ROUTERS = ["app.routers.StickyReplicaRouter"]
MIDDLEWARE = [
    # ... security, session, etc. ...
    "app.middleware.ResetStickyMiddleware",
]
DATABASES["default"]["ATOMIC_REQUESTS"] = False

This pairs weighted-or-random replica reads with sticky-after-write consistency and keeps migrations and DML on the primary. Threaded servers (Gunicorn sync workers, uWSGI) get correct isolation because threading.local() scopes the sticky flag per worker thread. Under ASGI/async views, replace threading.local() with contextvars.ContextVar so the flag follows await boundaries.

Verification and Rollback

Confirm the split is live:

python
from django.db import connections

# Which alias served the last read?
Article.objects.all().db          # -> 'replica_1' / 'replica_2'
Article.objects.create(...)._state.db   # -> 'default'

# Confirm each replica is a standby
with connections["replica_1"].cursor() as c:
    c.execute("SELECT pg_is_in_recovery()")
    assert c.fetchone()[0] is True

Roll back instantly by emptying the router list — Django reverts to default for everything with no code change:

python
# settings.py — kill switch during a replica incident
DATABASE_ROUTERS = []

Redeploy, and all traffic collapses onto the primary. Restore the router list once replicas are healthy. Because using() calls still name explicit aliases, audit that no view hardcodes a replica before disabling the router during an incident.

Edge Cases and Gotchas

ATOMIC_REQUESTS silently pins reads to the primary

ATOMIC_REQUESTS = True opens a transaction on default around every request. Django keeps that request’s work on the atomic connection, so db_for_read is effectively ignored for those queries and your replicas stay cold. For a read-scaling deployment, set ATOMIC_REQUESTS = False globally and apply @transaction.atomic only to views that mutate data. This is the single most common reason “the router did nothing.”

Two reads in one request hit different replicas

Because db_for_read runs per query, a view that reads twice can land on replica_1 then replica_2, each at a different lag position. If the second read depends on the first, you can observe non-monotonic data. For sequences of related reads, either wrap them in the sticky flag or pin them with using() to one alias. This is the Django face of the read-your-writes consistency problem.

allow_migrate governs more than migrate

allow_migrate also affects dumpdata, loaddata, and contenttypes/permissions creation. Returning True for a replica can make Django attempt to write permission rows to a read-only node during migrate. Always constrain allow_migrate to db == "default", and pass --database default explicitly in deploy scripts to avoid ambiguity.

FAQ

Why do all my queries hit the primary when ATOMIC_REQUESTS is on?

ATOMIC_REQUESTS wraps each request in a transaction on default. Django keeps the request’s reads on that atomic connection, so db_for_read is bypassed and most queries stay on the primary. Disable ATOMIC_REQUESTS for read-heavy services and instead wrap only mutating views in @transaction.atomic, which frees read views to reach replicas.

How does Django pick a replica when db_for_read returns random?

Django calls db_for_read once per query and uses whatever alias you return. random.choice(REPLICAS) gives uniform spread; random.choices(..., weights=...) lets a larger replica take more load. Because the choice is per query, two reads in one view may land on different replicas with different lag, which can break read-your-writes unless you pin them.

How do I force a read onto the primary after a write in Django?

Use Model.objects.using('default').get(...) for the specific read, or set a thread-local (or ContextVar) sticky flag right after the write so db_for_read returns 'default' for the rest of the request. The router reads that flag and pins subsequent reads to the primary until the replica has caught up, then middleware resets it at request end.

← Back to ORM Middleware for Automatic Query Routing