Scaling Django Databases: Read Replicas, PgBouncer Connection Pooling, and Database Routers

A single PostgreSQL primary handles a surprising amount of load — until it doesn't. Before you reach for sharding (hard) or a different database (risky), you can usually multiply your read capacity with replicas and fix connection exhaustion with PgBouncer. This is the production playbook for scaling Django's database tier without rewriting your app: replicas, routing, pooling, and the subtle bugs each one introduces.

Diagnose before you scale

Scaling the wrong thing wastes weeks. First find out why the database is the bottleneck. Is it read-bound (most web apps), write-bound, connection-bound, or just running unindexed queries? Check pg_stat_statements for the queries consuming the most total time, watch pg_stat_activity for connection counts and lock waits, and confirm your hot queries actually use indexes. Half the time "the database is slow" is a missing index or an N+1 in the ORM, and no amount of replicas will fix that — it will just spread the same bad query across more machines.

Streaming read replicas

PostgreSQL streams the primary's write-ahead log (WAL) to one or more read-only replicas in near real time. Writes still go to the primary; reads spread across replicas. For read-heavy workloads — which is almost every web app — this is the highest-leverage scaling move available, and it requires no application rewrite, only routing. You can add replicas behind a load balancer and scale reads roughly linearly until the primary's write throughput becomes the ceiling.

A Django database router

Declare the connections, then route reads to a replica and writes to the primary with a router:

DATABASES = {
    "default": {...},                      # primary, read-write
    "replica": {..., "TEST": {"MIRROR": "default"}},
}

class PrimaryReplicaRouter:
    def db_for_read(self, model, **hints):  return "replica"
    def db_for_write(self, model, **hints): return "default"
    def allow_relation(self, a, b, **hints): return True
    def allow_migrate(self, db, app_label, **hints):
        return db == "default"             # migrate only the primary

DATABASE_ROUTERS = ["app.routers.PrimaryReplicaRouter"]

With several replicas, randomize the read target inside db_for_read or, better, put the replicas behind a single load-balanced endpoint and let it spread the load. Keep allow_migrate pinned to the primary so schema changes never run against a read-only node.

A production-grade router

The minimal router above always sends reads to the replica, which is correct for most queries and wrong for a predictable handful: reads inside a transaction, reads that must see a write from a moment ago, reads of tables that are written on nearly every request (sessions), and related-object lookups on an instance that was itself loaded from the primary. A router that handles all four looks like this:

# myproject/db_routing.py
import random
from contextlib import contextmanager
from contextvars import ContextVar

from django.db import connections

REPLICAS = ["replica1", "replica2"]
PRIMARY_ONLY_APPS = {"sessions"}          # written constantly, read immediately

_force_primary = ContextVar("force_primary", default=False)


@contextmanager
def primary():
    """Route every read inside the block to the primary."""
    token = _force_primary.set(True)
    try:
        yield
    finally:
        _force_primary.reset(token)


class PrimaryReplicaRouter:
    def db_for_read(self, model, **hints):
        if _force_primary.get():
            return "default"
        if model._meta.app_label in PRIMARY_ONLY_APPS:
            return "default"
        if connections["default"].in_atomic_block:
            return "default"               # reads inside atomic() see our own writes
        instance = hints.get("instance")
        if instance is not None and instance._state.db == "default":
            return "default"               # keep related lookups consistent
        return random.choice(REPLICAS)

    def db_for_write(self, model, **hints):
        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"

The in_atomic_block check matters: routing is decided entirely by db_for_read, and opening transaction.atomic() starts a transaction on the default connection but does not, by itself, change where a subsequent .get() is sent. Without this check, a read inside an atomic block would go to a replica that cannot see the transaction's uncommitted writes, and select_for_update() — which the router treats as a read — would fail on the replica with a read-only error. With the check, "everything inside atomic() hits the primary" becomes a guarantee of your router rather than an assumption. Checking in_atomic_block does not open a database connection.

The instance hint is supplied when you traverse a relation from an object you already hold, such as order.items.all(). If the order came from the primary, a lagging replica could return a parent without its children; honouring instance._state.db keeps an object graph on one node.

Replication lag — the read-your-writes trap

Replicas are milliseconds behind, but milliseconds are enough to break a user's mental model: they save their profile (primary), the next page reads from a replica that has not caught up, and their change appears to have vanished. This "read-your-writes" violation is the number-one bug introduced by replicas. The fix is to pin reads to the primary immediately after a write:

from django.db import transaction

with transaction.atomic():        # only reads the primary if your router checks in_atomic_block
    profile.save()
    return render_profile(Profile.objects.get(pk=profile.pk))

Or force a single query onto the primary explicitly when you know you just wrote:

Profile.objects.using("default").get(pk=pk)

A common pattern is "sticky primary": for a few seconds after a user writes, route all their reads to the primary, then relax back to replicas. Store a short per-user flag in Redis and check it in your router. Always monitor lag with pg_stat_replication and alert if it grows — a replica that falls minutes behind is serving stale data to everyone.

Background jobs and the lag race

The subtlest read-your-writes bug is not in a view at all. A view creates an order and enqueues a task with the order's id; a Celery worker picks it up within milliseconds, reads from a replica, and gets Order.DoesNotExist. Two independent fixes apply. First, enqueue only after the transaction commits, otherwise the worker can win the race even against the primary. Second, have the task read what it was told about from the primary:

# views.py
from django.db import transaction

with transaction.atomic():
    order = Order.objects.create(customer=request.user, total=total)
    transaction.on_commit(lambda: send_receipt.delay(order.pk))

# tasks.py
from myproject.db_routing import primary

@app.task
def send_receipt(order_id):
    with primary():
        order = Order.objects.select_related("customer").get(pk=order_id)
    render_and_email(order)

Keep the primary() block narrow: load the triggering object, then let reference-data reads go to replicas as usual.

PgBouncer: connection pooling

Each gunicorn worker holds its own database connections, and PostgreSQL forks a backend process per connection. At a few hundred connections the server spends more time context-switching between processes than doing work, and memory balloons. PgBouncer sits between Django and PostgreSQL and multiplexes thousands of client connections onto a small pool of real backend connections:

[databases]
myproject = host=127.0.0.1 port=5432 dbname=myproject

[pgbouncer]
pool_mode = transaction       # the sweet spot for web apps
max_client_conn = 2000
default_pool_size = 20        # real backend connections per database

Twenty real connections can now serve two thousand clients, because web requests hold a connection only for the brief duration of a transaction.

The transaction-mode caveat

Transaction pooling is what makes PgBouncer so effective, but it changes the rules: a connection is handed back to the pool after each transaction, so anything that relies on session state across transactions breaks. Session-level SET, advisory locks held across statements, server-side prepared statements (unless you run PgBouncer 1.21+ with max_prepared_statements set), and LISTEN/NOTIFY will not behave as expected. The Django-specific consequence: the simplest setup disables persistent connections, because PgBouncer is doing the pooling now (keeping short-lived persistent connections to PgBouncer also works; it is a choice, not a requirement):

DATABASES = {"default": {..., "CONN_MAX_AGE": 0}}

If you genuinely need session features, route those specific operations to a second connection in session pool mode, and keep the bulk of your traffic on the transaction-mode pool.

The complete Django settings for transaction pooling

CONN_MAX_AGE is not the only setting that interacts with transaction pooling. Django's PostgreSQL backend uses named server-side cursors for QuerySet.iterator() when you are not inside a transaction, and a named cursor is session state: the next fetch may be sent to a different backend, which fails with an error saying the cursor does not exist. DISABLE_SERVER_SIDE_CURSORS exists precisely for this case:

import os

def pooled(name):
    return {
        "ENGINE": "django.db.backends.postgresql",
        "NAME": name,                       # the PgBouncer alias, not the real dbname
        "USER": "deploy",
        "PASSWORD": os.environ["DB_PASSWORD"],
        "HOST": "127.0.0.1",
        "PORT": "6432",                     # PgBouncer, not PostgreSQL
        "CONN_MAX_AGE": 0,
        "DISABLE_SERVER_SIDE_CURSORS": True,
    }

DATABASES = {
    "default": pooled("myproject"),
    "replica1": {**pooled("myproject_replica"), "TEST": {"MIRROR": "default"}},
}
DATABASE_ROUTERS = ["myproject.db_routing.PrimaryReplicaRouter"]

With server-side cursors disabled, the driver receives the full result set before .iterator() yields anything, so process very large tables in primary-key ranges. Prepared statements are mostly a non-issue: with psycopg 3, Django uses client-side parameter binding by default, so leave the "server_side_binding" option off behind a transaction pooler.

Session-level settings need a new home too. A SET statement_timeout issued by the application leaks into whichever client next borrows that backend, and PgBouncer rejects an options startup parameter unless told to ignore it — in which case it silently never applies. Attach such settings to the database role instead, where every backend PgBouncer opens for that role picks them up automatically:

ALTER ROLE deploy    SET statement_timeout = '5s';
ALTER ROLE deploy    SET idle_in_transaction_session_timeout = '30s';
ALTER ROLE reporting SET statement_timeout = '10min';

Separate roles also give PgBouncer separate pools, so dashboards cannot starve the request path.

When the primary's writes are the ceiling

Replicas scale reads, not writes — every write still lands on the single primary. When write throughput is the limit, your options escalate in cost: batch writes to reduce transaction overhead, move high-volume append-only data (events, logs) to a separate store, introduce a write-behind queue so spikes are absorbed asynchronously, and only as a last resort shard by tenant or key across multiple primaries. Sharding is a large project that touches every query, so exhaust the cheaper options first — most apps never need it.

Indexing is still the cheapest win

No amount of hardware beats a missing index. Before and during any scaling work, keep auditing: every column you filter or join on should be indexed; composite indexes should lead with the most selective column; and partial indexes (WHERE active) keep hot subsets small. EXPLAIN (ANALYZE, BUFFERS) on your slowest queries tells you whether you are scanning or seeking. A correctly indexed single primary often outperforms a poorly indexed cluster.

Monitoring the database tier

You cannot scale what you cannot see. Track replication lag per replica, connection counts at both PgBouncer and PostgreSQL, cache-hit ratio, the slowest queries from pg_stat_statements, and lock-wait events. Alert on lag crossing a threshold and on the connection pool saturating — both are early warnings of a tier about to fall over. Put these on the same dashboard as your app latency so you can correlate a slow endpoint with a database event.

Rollout checklist

• Confirm the bottleneck is reads (and not a missing index) with pg_stat_statements.
• Add a replica; verify pg_stat_replication shows low, stable lag.
• Ship the router; hunt for read-your-writes bugs on every post-write page.
• Add sticky-primary for recently-writing users if those bugs appear.
• Front everything with PgBouncer in transaction mode; set CONN_MAX_AGE = 0.
• Load-test, then add replicas behind a load balancer as traffic grows.
• Wire lag and connection-saturation alerts before you rely on the setup.

Caching: the scaling layer before replicas

The fastest query is the one you never run. Before adding replicas, make sure you are not asking the database the same question thousands of times a second. A Redis cache in front of expensive, slow-changing reads — a homepage's product list, a user's permission set, a dashboard's aggregates — can remove the majority of read load that would otherwise push you toward replicas in the first place. Cache at the right granularity: per-view for whole pages, fragment caching for expensive template pieces, and low-level cache.get_or_set for individual querysets. The hard part is invalidation; key entries by the data they depend on and bust them on write, or use short TTLs where slightly-stale is acceptable. Often a day of caching work buys more headroom than a month of database scaling — and it is cheaper to operate.

Materialized views for expensive reads

Some reads are inherently heavy: multi-table aggregations for reporting, leaderboards, analytics rollups. Running them on every request is wasteful when the underlying data changes slowly. A PostgreSQL materialized view computes the result once and stores it like a table, and you refresh it on a schedule. Reads against it are fast and predictable, and the refresh runs off the request path. Use REFRESH MATERIALIZED VIEW CONCURRENTLY so reads are not blocked during the refresh. This pattern moves the cost of an expensive query from "every read" to "once per refresh interval," which for reporting data is exactly the trade you want.

Partitioning large tables

When a single table grows into the hundreds of millions of rows, even well-indexed queries slow down and maintenance operations crawl. PostgreSQL table partitioning splits one logical table into many physical partitions — commonly by time range (one partition per month) for append-heavy data like events, orders, or logs. Queries that filter on the partition key touch only the relevant partitions (partition pruning), so a query for last week reads one small partition instead of scanning everything. Old partitions can be detached and archived cheaply, which keeps the hot set small. Partitioning is more operational work than indexing, so reserve it for genuinely huge, naturally time-ordered tables — but for those, it is transformative.

Vacuum, bloat, and autovacuum tuning

PostgreSQL's MVCC model means updates and deletes leave behind dead row versions that VACUUM reclaims. On a busy database, default autovacuum settings often cannot keep up, and tables bloat — growing on disk, slowing scans, and degrading index efficiency — until performance mysteriously declines. Monitor dead-tuple counts and table bloat, and tune autovacuum to run more aggressively on your hottest tables (lower the scale factor so it triggers sooner). This is invisible maintenance that quietly determines whether a write-heavy database stays fast over months. A surprising number of "we need to scale the database" situations are really "autovacuum has been losing the race for weeks."

Isolating analytics on a dedicated replica

Analysts and dashboards run heavy, long-running queries that can starve your transactional workload of resources. Rather than let a sprawling report lock up the database serving customers, point all reporting and BI traffic at a dedicated replica tuned for it. The transactional primary and its low-latency replicas stay responsive for the app, while the reporting replica absorbs the analytical load. This separation of workloads is one of the cleanest uses of replication — it is not about read capacity at all, but about preventing two very different query patterns from interfering with each other.

Failover and high availability

Scaling and availability overlap: once you run replicas, you are most of the way to a high-availability setup. With a tool like Patroni or your cloud provider's managed failover, a replica can be promoted to primary automatically when the primary fails, turning a multi-hour outage into a brief blip. The application side matters too — your connection layer (often PgBouncer plus a virtual IP or service endpoint) must repoint to the new primary, and your app must tolerate the short window of failed writes gracefully with retries. Design for the primary disappearing, because eventually it will, and the difference between a planned failover and an unplanned one is whether you tested it.

Capacity planning

Scaling is not a one-time event; it is a continuous read of headroom against growth. Watch the trend lines — connection saturation, replication lag under peak, CPU and IO on the primary, cache hit ratio — and act before they hit the ceiling, not after. Knowing your growth rate tells you whether you have months or weeks of runway, and which lever to pull next: another replica, a bigger primary, more caching, partitioning, or finally sharding. The teams that scale calmly are the ones watching these numbers continuously; the ones firefighting are the ones who looked only when the database was already on fire.

Testing the routing layer

Routing bugs are invisible in development, where there is usually one database, and in the test suite, where the replica is configured with "TEST": {"MIRROR": "default"} and therefore sees every write instantly. Mirroring is the right default, but it means no test will ever reproduce lag. Test the routing decisions directly instead, as plain unit tests that do not touch the database:

# tests/test_routing.py
from django.contrib.sessions.models import Session
from django.test import SimpleTestCase

from accounts.models import Profile
from myproject.db_routing import REPLICAS, PrimaryReplicaRouter, primary


class RouterTests(SimpleTestCase):
    def setUp(self):
        self.router = PrimaryReplicaRouter()

    def test_plain_reads_go_to_a_replica(self):
        self.assertIn(self.router.db_for_read(Profile), REPLICAS)

    def test_forced_primary(self):
        with primary():
            self.assertEqual(self.router.db_for_read(Profile), "default")

    def test_sessions_always_primary(self):
        self.assertEqual(self.router.db_for_read(Session), "default")

    def test_writes_and_migrations_only_on_primary(self):
        self.assertEqual(self.router.db_for_write(Profile), "default")
        self.assertFalse(self.router.allow_migrate("replica1", "accounts"))
        self.assertTrue(self.router.allow_migrate("default", "accounts"))

Tests that do hit the database through several aliases must declare them, for example databases = {"default", "replica1"} on a TestCase; Django refuses queries against aliases a test did not declare, which is a useful guard against accidental routing. For the lag itself, the most realistic test is a staging environment with a real replica and artificially induced delay: setting recovery_min_apply_delay on a staging replica makes it deliberately apply WAL late, and clicking through every "save and redirect" flow against it flushes out read-your-writes bugs. Never set that parameter on a production replica that serves the application.

Troubleshooting common errors

Most replica and pooling problems announce themselves with a handful of error messages:

The last row deserves a step in your deploy pipeline: schema changes reach replicas through WAL like any other write, so new code can briefly query a column a lagging replica has not created yet. Gate the traffic switch on replicas catching up, or better, follow the expand/contract pattern — add columns in one release, read them in the next, drop old ones last.

Summary

Read replicas plus a Django database router give you horizontal read scaling almost for free, and PgBouncer in transaction mode stops connection storms from killing PostgreSQL. The two bugs to watch are replication lag — pin post-write reads to the primary, optionally with sticky-primary, and your users never notice the replicas exist — and transaction-mode's loss of session state, which means setting CONN_MAX_AGE = 0 and keeping session-dependent features off the pooled path. Diagnose before you scale, keep indexing relentlessly, monitor lag and connections, and save sharding for the rare day replicas and pooling genuinely run out — most apps never reach it.