DEV Community: Anas Issath

Database Transactions Are Lying to You: What Isolation Levels Actually Guarantee

Anas Issath — Mon, 09 Nov 2026 06:44:02 +0000

Two requests hit your database at the same time. One overwrites the other's work, and neither one errors. Here's why, and how to stop it.

Here's a scenario I've seen dozens of times.

A "buy now" button works perfectly in every manual test. One person clicks it, stock decrements by one, order gets created. Ships to production.

A few weeks later, a flash sale runs, and support tickets start coming in: customers charged for items that were actually out of stock. The inventory count went negative. Nobody's code has a bug you can point to. Every individual request did exactly what it was told.

The problem wasn't one request. It was two requests, reading the same stock count at the same instant, both deciding there was enough left, both writing "success."

This is what transaction isolation is supposed to prevent, and most developers wrap code in BEGIN/COMMIT assuming it fully does, without knowing which guarantees their isolation level actually makes.

This guide explains what a transaction really promises, the four standard isolation levels, and the concurrency bugs that slip through the cracks depending on which one you're using.

Let's make your writes actually safe under load.

What a Transaction Actually Guarantees

A transaction groups multiple operations so they succeed or fail as one unit. That's the "atomic" in ACID.

What it does not automatically guarantee is that your transaction is isolated from every other transaction running at the same time. That part is configurable, and the default varies by database.

BEGIN;
UPDATE inventory SET stock = stock - 1 WHERE product_id = 42;
INSERT INTO orders (product_id, user_id) VALUES (42, 7);
COMMIT;

This looks safe. It isn't, by itself. It only guarantees that within this transaction, both statements happen together. It says nothing about what a second, concurrent transaction running the exact same code is allowed to see or overwrite while this one is still in flight.

That's what isolation levels control.

The Core Problem: Two Transactions, One Row

Time    Transaction A                Transaction B
----    -------------------------    -------------------------
t1      BEGIN
t2      SELECT stock FROM inventory
        WHERE id=42;  → stock = 1
t3                                   BEGIN
t4                                   SELECT stock FROM inventory
                                      WHERE id=42;  → stock = 1
t5      UPDATE stock = 0
t6      COMMIT
t7                                   UPDATE stock = 0
t8                                   COMMIT

Both transactions read stock = 1. Both decided it was safe to sell. Both committed. The final stock is 0, but two orders were created for one unit of inventory. Nothing errored.

This is a lost update, and it's the textbook example of what isolation levels exist to prevent.

The Four Standard Isolation Levels

The SQL standard defines four levels, each permitting fewer anomalies than the last, at the cost of more locking, more blocking, or more retries.

1. Read Uncommitted

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

The weakest level. A transaction can read data another transaction has written but not yet committed, including data that later gets rolled back. This is called a dirty read.

Transaction A: UPDATE balance SET amount = 1000 WHERE id = 1;  (not committed yet)
Transaction B: SELECT amount FROM balance WHERE id = 1;  → sees 1000
Transaction A: ROLLBACK;
-- Transaction B made a decision based on data that never existed

Almost nobody should use this in production. PostgreSQL doesn't even implement it as distinct from Read Committed. It's there for standards compliance only.

2. Read Committed

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

The default in PostgreSQL, Oracle, and SQL Server. A transaction only ever sees data that has been committed: no dirty reads.

But it doesn't protect you from a non-repeatable read: if you query the same row twice in one transaction, another transaction's commit in between can change what you see the second time.

Transaction A: SELECT stock FROM inventory WHERE id=42;  → 1
Transaction B: UPDATE inventory SET stock=0 WHERE id=42; COMMIT;
Transaction A: SELECT stock FROM inventory WHERE id=42;  → 0
-- Same query, same transaction, different answer

3. Repeatable Read

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

The default in MySQL/InnoDB. Once a transaction reads a row, it will see the same data for that row for the rest of the transaction, even if another transaction commits a change.

This fixes non-repeatable reads, but the standard still permits a phantom read: a query returning a set of rows can see new rows appear on a second run, even though individual rows you already read stay stable.

Transaction A: SELECT * FROM orders WHERE status='pending';  → 5 rows
Transaction B: INSERT INTO orders (status) VALUES ('pending'); COMMIT;
Transaction A: SELECT * FROM orders WHERE status='pending';  → 6 rows
-- A new row "phantom" appeared in the result set

(In practice, PostgreSQL's implementation of Repeatable Read is stricter than the SQL standard requires and also blocks phantom reads. But don't assume that behavior is portable to every database.)

4. Serializable

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

The strongest level. Guarantees that the result of running transactions concurrently is identical to running them one after another in some order. No dirty reads, no non-repeatable reads, no phantoms, no lost updates.

The cost: the database has to detect conflicts and abort one of the transactions when it can't guarantee that ordering.

BEGIN;
-- ... transaction logic ...
COMMIT;
-- Sometimes returns:
-- ERROR: could not serialize access due to concurrent update

Your application must be ready to catch this and retry.

Serializable isn't "safe and free." It's "safe, and sometimes it tells you to try again."

Quick Reference: What Each Level Prevents

The takeaway: your database's default isolation level is Read Committed in most systems, and Read Committed does not prevent the lost-update bug from the flash sale example. If you never explicitly thought about isolation, you're running at the level that permits it.

Common Isolation Problems

Problem 1: Assuming `BEGIN`/`COMMIT` alone prevents race conditions

# Looks safe. Isn't, under Read Committed.
with transaction.atomic():
    product = Product.objects.get(id=42)
    if product.stock > 0:
        product.stock -= 1
        product.save()

Two concurrent requests can both fetch stock = 1, both pass the if, both decrement to 0, and both succeed. Wrapping code in a transaction guarantees atomicity of the write. It does not, by itself, guarantee the read-then-write is protected from another transaction doing the same thing in between.

Fix: use a row-level lock (SELECT ... FOR UPDATE) to force serialization at the row.

with transaction.atomic():
    product = Product.objects.select_for_update().get(id=42)
    if product.stock > 0:
        product.stock -= 1
        product.save()

-- What select_for_update does under the hood:
SELECT * FROM products WHERE id=42 FOR UPDATE;
-- Second transaction's identical query now BLOCKS until this one commits

Problem 2: Using a lock when an atomic update would do

For simple counters, you often don't need a lock at all. You need to stop reading the value into application code in the first place.

# Race-prone: read, modify in Python, write
product.stock -= 1
product.save()

-- Race-free: the decrement happens entirely inside the database
UPDATE products SET stock = stock - 1 WHERE id = 42 AND stock > 0;

The second version pushes the read-modify-write into a single atomic statement the database executes as one operation. There's no window where two transactions can both see the same stale value. This is usually cheaper and simpler than a lock for pure counter updates.

Problem 3: Deadlocks from inconsistent lock ordering

Transaction A: locks row 1, then tries to lock row 2
Transaction B: locks row 2, then tries to lock row 1
-- Both wait for each other forever

-- Transaction A
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Transaction B, running concurrently
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;

If A locks row 1 first and B locks row 2 first, each is now waiting on a row the other holds. The database detects this and kills one transaction with a deadlock error, but only after both have been stalled.

Fix: always acquire locks on multiple rows in a consistent order, e.g. always lock the lower ID first.

-- Both transactions now always touch id=1 before id=2
UPDATE accounts SET balance = balance - :amt WHERE id = LEAST(:a, :b);
UPDATE accounts SET balance = balance + :amt WHERE id = GREATEST(:a, :b);

Problem 4: Long transactions holding locks under load

with transaction.atomic():
    order = Order.objects.select_for_update().get(id=order_id)
    send_confirmation_email(order)   # slow network call, 2–3 seconds
    charge_payment(order)            # slow network call, 2–5 seconds
    order.status = 'confirmed'
    order.save()

The row lock from select_for_update is held for the entire transaction, including both slow external calls. Every other request trying to touch that order queues up behind it for as long as those network calls take.

Fix: keep locks held only as long as the database work itself takes. Do slow, unrelated I/O outside the transaction.

send_confirmation_email_async(order_id)   # queued, not inline
result = charge_payment(order)            # done before opening the lock

with transaction.atomic():
    order = Order.objects.select_for_update().get(id=order_id)
    order.status = 'confirmed' if result.success else 'failed'
    order.save()

Problem 5: Picking Serializable and not handling retries

# Set isolation to Serializable, then treat it like it can't fail
with transaction.atomic(isolation_level='SERIALIZABLE'):
    transfer_funds(from_account, to_account, amount)

Serializable transactions can and will be aborted by the database whenever it can't guarantee a consistent ordering. This is expected, normal behavior, not an edge case.

Fix: wrap serializable transactions in a retry loop.

for attempt in range(3):
    try:
        with transaction.atomic(isolation_level='SERIALIZABLE'):
            transfer_funds(from_account, to_account, amount)
        break
    except SerializationError:
        if attempt == 2:
            raise
        continue

Choosing a Level for Your Use Case

Approach	Use it for
Read Committed (the default, mostly)	The majority of reads, and writes that don't depend on a prior read of the same row within the transaction. Most CRUD operations.
Row locks (`SELECT ... FOR UPDATE`) at Read Committed	The standard fix for read-then-write races on a specific row: inventory, balances, seat reservations. Cheaper and more predictable than jumping to Serializable.
Atomic SQL updates (`SET x = x - 1 WHERE x > 0`)	The simplest, fastest option whenever the operation can be expressed as pure SQL without application logic between the read and the write.
Serializable	Logic too complex for a single atomic statement or a simple row lock: multi-row invariants, complex financial transfers, anything where "occasionally retried" is an acceptable tradeoff for full correctness.

Checking What's Actually Happening

-- PostgreSQL: see current isolation level
SHOW TRANSACTION ISOLATION LEVEL;

-- PostgreSQL: see locks currently held and what's blocked on them
SELECT blocked_locks.pid AS blocked_pid,
       blocking_locks.pid AS blocking_pid,
       blocked_activity.query AS blocked_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_locks blocking_locks
  ON blocking_locks.locktype = blocked_locks.locktype
  AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
  AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
  AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocked_activity
  ON blocked_activity.pid = blocked_locks.pid
WHERE NOT blocked_locks.granted;

If you're seeing mysterious slow requests under concurrent load with no slow query in sight, checking for blocked locks is often more revealing than checking query plans.

The Bottom Line

Wrapping code in a transaction feels like it should make concurrency problems disappear. It doesn't. It only guarantees the specific things ACID promises, and by default, most databases run at a level that still allows two requests to read the same stale value and both act on it.

The flash-sale overselling bug isn't a bug in anyone's code; it's the predictable result of Read Committed doing exactly what it's documented to do.

Remember:

BEGIN/COMMIT guarantees atomicity, not isolation from concurrent reads. Those are separate guarantees.
Read Committed (most databases' default) still allows lost updates. Know your default, don't assume.
SELECT ... FOR UPDATE is usually the right fix for read-then-write races on a single row.
Atomic SQL updates (SET x = x - 1 WHERE x > 0) beat locks when the logic is simple enough to express in one statement.
Serializable transactions can fail on purpose. Your code needs a retry loop, not just a try/except that gives up.

The bug that only shows up during your highest-traffic moment (the flash sale, the ticket drop, the Black Friday spike) is usually a concurrency bug, not a capacity bug. Understanding isolation levels is how you catch it before that moment finds it for you.

Your Turn

Have you ever shipped a race condition that only showed up under real traffic? What gave it away? Tell me in the comments 👇

If this was useful, follow me here for more stories from the backend trenches, and subscribe to The Backend Tech Lead on Substack for weekly blueprints.

asyncpg vs SQLAlchemy ORM - The 9x Slowdown Nobody Warns You About

Anas Issath — Sun, 08 Nov 2026 14:19:10 +0000

Async doesn't mean fast. Your ORM might be quietly throwing away everything asyncpg gives you.

We migrated a FastAPI service from sync SQLAlchemy to async SQLAlchemy, expecting a performance win. Async is supposed to be faster, right? Non-blocking I/O, more concurrent requests per worker, better resource utilization.

Our load test showed the opposite. The async version was slower per-request than the sync version had been. Not by a little: a specific endpoint that fetched 500 rows and mapped them to ORM objects went from 340ms to 890ms.

I assumed we'd misconfigured something. Wrong connection pool size, maybe, or a missing index. I profiled the endpoint with cProfile and found the answer: the async ORM layer itself was the bottleneck.

Object instantiation, identity map tracking, and attribute loading (the same overhead that exists in sync SQLAlchemy) was still there in async SQLAlchemy. We'd added the complexity of async without eliminating the cost of the ORM.

This isn't a reason to avoid async or avoid SQLAlchemy. It's a reason to understand exactly what you're paying for at each layer, so you can make an informed choice instead of assuming "async" automatically means "fast."

The Three Layers and What Each One Costs

When you fetch data from PostgreSQL in a modern async Python app, there are three layers between your code and the database:

Your code
  → SQLAlchemy ORM (optional)     ← object mapping, identity map, relationships
  → SQLAlchemy Core / Driver       ← query compilation, connection management
  → asyncpg or psycopg              ← the actual wire protocol to PostgreSQL

Each layer adds capability and each layer adds overhead. Understanding where the overhead comes from tells you when it's worth paying and when it isn't.

Layer 1: The Driver (asyncpg vs psycopg 3)

At the bottom, you have the actual PostgreSQL wire protocol implementation. Two real choices in 2026:

asyncpg: built from scratch for asyncio. Speaks PostgreSQL's binary protocol directly. No general-purpose abstraction layer. This is the fastest way to talk to PostgreSQL from Python, full stop.
psycopg 3 (async mode): the modern rewrite of the classic psycopg2, with native async support. Slightly slower than asyncpg in raw benchmarks, but shares an API with sync psycopg, which matters if you're maintaining both sync and async code paths.

# Raw asyncpg - as fast as Python gets talking to PostgreSQL
import asyncpg

async def get_orders(user_id: int):
    conn = await asyncpg.connect(DATABASE_URL)
    try:
        rows = await conn.fetch(
            "SELECT id, total, status FROM orders WHERE user_id = $1", user_id
        )
        return [dict(row) for row in rows]
    finally:
        await conn.close()

# psycopg 3 async - nearly as fast, more familiar API
import psycopg

async def get_orders(user_id: int):
    async with await psycopg.AsyncConnection.connect(DATABASE_URL) as conn:
        async with conn.cursor() as cur:
            await cur.execute(
                "SELECT id, total, status FROM orders WHERE user_id = %s", (user_id,)
            )
            rows = await cur.fetchall()
            return rows

Both are dramatically faster than going through an ORM for simple queries. The difference between them is small enough that it rarely decides an architecture. asyncpg wins benchmarks by roughly 2x for raw throughput, but in a real application with network latency and business logic around the query, that gap shrinks to something you won't notice.

Layer 2: SQLAlchemy Core (No ORM)

SQLAlchemy Core gives you a query builder and connection management without object mapping. You write something close to SQL, but Python-native and composable:

from sqlalchemy import select, table, column
from sqlalchemy.ext.asyncio import create_async_engine

engine = create_async_engine("postgresql+asyncpg://user:pass@localhost/mydb")

orders = table("orders", column("id"), column("total"), column("status"), column("user_id"))

async def get_orders(user_id: int):
    async with engine.connect() as conn:
        result = await conn.execute(
            select(orders).where(orders.c.user_id == user_id)
        )
        return result.fetchall()

This adds a small amount of overhead over raw asyncpg (query compilation, parameter binding, connection pool management), but it's a fraction of what the full ORM costs.

Layer 3: The ORM (SQLAlchemy's Declarative Models)

The ORM adds object identity tracking, relationship loading, change tracking (dirty attribute detection), and Python object instantiation for every row:

from sqlalchemy import select
from sqlalchemy.orm import declarative_base, Mapped, mapped_column
from sqlalchemy.ext.asyncio import AsyncSession

Base = declarative_base()

class Order(Base):
    __tablename__ = "orders"
    id: Mapped[int] = mapped_column(primary_key=True)
    total: Mapped[float]
    status: Mapped[str]
    user_id: Mapped[int]

async def get_orders(session: AsyncSession, user_id: int):
    result = await session.execute(select(Order).where(Order.user_id == user_id))
    return result.scalars().all()

Same query. Same data. But now every row becomes a full Python object with SQLAlchemy's instrumentation attached:

Tracking whether each attribute has changed
Registering the object in the session's identity map, so session.get(Order, id) returns the same instance
Setting up lazy-loading machinery for any relationships

The Benchmark: What Each Layer Actually Costs

I ran this on a table with 100,000 rows, fetching 500 rows matching a filter, on a local PostgreSQL instance to eliminate network variance:

The jump from Core to ORM is the one that matters. Going from raw asyncpg to SQLAlchemy Core costs you a small, reasonable overhead for a massive gain in composability and safety. Going from Core to full ORM costs you almost 3x more on top of that, for object mapping you may not even need.

This matches what other benchmarks in the Python community have found: SQLAlchemy's ORM layer with async drivers can be an order of magnitude slower than raw driver access for simple read-heavy queries, primarily because of Python-level object construction overhead, not the database round trip itself.

Why the ORM Overhead Is So High With Async

Here's the part that surprised me most. You'd expect async to at least match sync ORM performance, since the actual database call is non-blocking either way. But in some benchmarks, async ORM queries are actually slower than sync ORM queries for CPU-bound work like object instantiation.

The reason: async adds its own overhead (event loop scheduling, coroutine creation, awaiting each step of the driver protocol) on top of the ORM's existing object-mapping cost.

For I/O-bound work (waiting on the network), async wins because the event loop can do other things while waiting.
But object instantiation isn't I/O-bound. It's pure CPU work happening on the same thread, and async adds bookkeeping around it without adding parallelism, because Python's GIL means you're not getting extra CPU time from asyncio. You're just avoiding blocking waits.

So the ORM's object-mapping tax is paid in full, and the async machinery adds a smaller additional tax on top, for operations that were never going to benefit from concurrency in the first place.

When Each Layer Is the Right Choice

Use raw asyncpg when...

You're building a narrow, performance-critical service: a data pipeline, an analytics endpoint, a service that streams large result sets. You control the SQL directly and don't need object mapping, relationships, or migrations tied to this code path.

# High-throughput analytics endpoint
@app.get("/api/analytics/daily-revenue")
async def daily_revenue(pool: asyncpg.Pool = Depends(get_pool)):
    async with pool.acquire() as conn:
        rows = await conn.fetch("""
            SELECT date_trunc('day', created_at) as day, SUM(total) as revenue
            FROM orders
            WHERE created_at >= NOW() - INTERVAL '30 days'
            GROUP BY day
            ORDER BY day
        """)
        return [dict(r) for r in rows]

Use SQLAlchemy Core when...

You want composable, injection-safe query building without the ORM's object overhead. This is the sweet spot for most read-heavy API endpoints (list views, search, reporting) where you need dynamic filters but don't need to mutate and save objects back.

async def list_orders(db: AsyncSession, filters: OrderFilters):
    stmt = select(
        orders_table.c.id,
        orders_table.c.total,
        orders_table.c.status,
    ).where(orders_table.c.is_active == True)

    if filters.status:
        stmt = stmt.where(orders_table.c.status == filters.status)
    if filters.min_total:
        stmt = stmt.where(orders_table.c.total >= filters.min_total)

    result = await db.execute(stmt)
    return result.mappings().all()  # Returns dicts, not ORM objects

result.mappings().all() is the key detail. It returns plain dictionaries instead of ORM entity objects, skipping the identity map and change-tracking machinery entirely while still using SQLAlchemy's query builder and connection pooling.

Use the full ORM when...

You're doing writes with relationships (creating an order with items in one transaction)
You need the identity map to prevent duplicate object instances across a request
You're using SQLAlchemy's relationship loading (selectinload, joinedload) to manage complex object graphs
Your team's productivity gain from the ORM's ergonomics outweighs the performance cost for that specific endpoint

async def create_order_with_items(db: AsyncSession, user_id: int, items: list[dict]):
    order = Order(user_id=user_id, status="pending")
    for item in items:
        order.items.append(OrderItem(
            product_id=item["product_id"],
            quantity=item["quantity"],
        ))
    db.add(order)
    await db.commit()
    await db.refresh(order)
    return order

This is exactly where the ORM earns its overhead: cascading inserts, relationship management, and object lifecycle tracking that would take significantly more raw SQL and manual bookkeeping to replicate correctly.

Layer	Best for	Returns
Raw asyncpg	Narrow, performance-critical paths, analytics, streaming	Records / dicts
SQLAlchemy Core	Read-heavy list, search and reporting endpoints	Dicts via `.mappings()`
Full ORM	Writes with relationships, complex object graphs	Tracked ORM objects

The Hybrid Pattern I Actually Use

Most production FastAPI services benefit from using different layers for different endpoints, all sharing the same async engine and connection pool:

# app/database.py
from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker

engine = create_async_engine(
    "postgresql+asyncpg://user:pass@localhost/mydb",
    pool_size=10,
    max_overflow=20,
    pool_pre_ping=True,
)

AsyncSessionLocal = async_sessionmaker(engine, expire_on_commit=False)

async def get_db():
    async with AsyncSessionLocal() as session:
        yield session

from sqlalchemy import text

# Writes and relationship-heavy reads: full ORM
@app.post("/api/orders")
async def create_order(data: OrderCreate, db: AsyncSession = Depends(get_db)):
    order = await order_service.create_order(db, data)  # Uses ORM
    return order

# High-volume list endpoints: SQLAlchemy Core, dicts not objects
@app.get("/api/orders")
async def list_orders(
    filters: OrderFilters = Depends(),
    db: AsyncSession = Depends(get_db),
):
    stmt = build_order_query(filters)  # Core query, no ORM mapping
    result = await db.execute(stmt)
    return result.mappings().all()

# Reporting and analytics: raw SQL via the same session
@app.get("/api/reports/summary")
async def report_summary(db: AsyncSession = Depends(get_db)):
    result = await db.execute(text("""
        SELECT status, COUNT(*) as count, SUM(total) as revenue
        FROM orders GROUP BY status
    """))
    return result.mappings().all()

All three share the same AsyncSession and connection pool. You're not managing separate drivers or separate pools. You're choosing, per endpoint, how much of the ORM's machinery you actually need.

The One Setting That Changes Everything: `expire_on_commit`

If you do use the ORM, this default setting quietly causes extra queries you didn't ask for:

# Default behavior - expires all objects after commit
async_session = async_sessionmaker(engine)

order = await create_order(db, data)
await db.commit()
print(order.status)  # Triggers a NEW query to refresh the expired object!

By default, SQLAlchemy marks every object's attributes as "expired" after a commit, so the next time you access them, it silently re-fetches from the database to guarantee freshness.

In a request-response API, where the session ends right after the commit, this is almost always wasted work. You're about to serialize the object and return it, not keep using it across a long-lived session.

# Disable expiration - trust the in-memory data after commit
async_session = async_sessionmaker(engine, expire_on_commit=False)

expire_on_commit=False is the single highest-leverage SQLAlchemy async setting for API workloads.

I set it on every FastAPI project now. Without it, a simple "create and return" endpoint fires a COMMIT and then a silent SELECT to re-fetch data you already had in memory.

Bottom Line

Async doesn't erase the cost of the layers above the database driver. It just changes what kind of cost you're paying.

Raw asyncpg is fast because it does almost nothing beyond speaking PostgreSQL's protocol.
SQLAlchemy Core adds a thin, worthwhile layer of safety and composability.
The full ORM adds real, measurable overhead in exchange for object mapping, relationship management, and identity tracking: features that are essential for some endpoints and pure cost for others.

The mistake isn't choosing SQLAlchemy's ORM. It's using it uniformly across every endpoint without asking whether that endpoint needs what it provides. A list endpoint returning 500 rows of read-only data doesn't need an identity map. A checkout endpoint creating an order with five related line items absolutely does.

That endpoint that went from 340ms to 890ms after our sync-to-async migration? We didn't revert to sync. We kept async, dropped the ORM for that specific read path, and switched to result.mappings().all() with SQLAlchemy Core.

It came back down to 95ms: faster than the original sync version, using the same connection pool, the same database, and about fifteen lines of changed code.

Over to You

What's your database access pattern in async Python? Full ORM everywhere, raw SQL everywhere, or a hybrid like this? And has anyone measured the async-vs-sync ORM overhead in their own benchmarks? I'd love to see numbers from a different workload than mine. Share in the comments 👇

If this was useful, follow me here for more stories from the backend trenches, and subscribe to The Backend Tech Lead on Substack for weekly blueprints.---
title: asyncpg vs SQLAlchemy ORM - The 9x Slowdown Nobody Warns You About
description: Async doesn't mean fast. Your ORM might be quietly throwing away everything asyncpg gives you.

tags: python, fastapi, postgres, performance

Async doesn't mean fast. Your ORM might be quietly throwing away everything asyncpg gives you.

The Three Layers and What Each One Costs

When you fetch data from PostgreSQL in a modern async Python app, there are three layers between your code and the database:

Your code
  → SQLAlchemy ORM (optional)     ← object mapping, identity map, relationships
  → SQLAlchemy Core / Driver       ← query compilation, connection management
  → asyncpg or psycopg              ← the actual wire protocol to PostgreSQL

Each layer adds capability and each layer adds overhead. Understanding where the overhead comes from tells you when it's worth paying and when it isn't.

Layer 1: The Driver (asyncpg vs psycopg 3)

At the bottom, you have the actual PostgreSQL wire protocol implementation. Two real choices in 2026:

asyncpg: built from scratch for asyncio. Speaks PostgreSQL's binary protocol directly. No general-purpose abstraction layer. This is the fastest way to talk to PostgreSQL from Python, full stop.
psycopg 3 (async mode): the modern rewrite of the classic psycopg2, with native async support. Slightly slower than asyncpg in raw benchmarks, but shares an API with sync psycopg, which matters if you're maintaining both sync and async code paths.

# Raw asyncpg - as fast as Python gets talking to PostgreSQL
import asyncpg

async def get_orders(user_id: int):
    conn = await asyncpg.connect(DATABASE_URL)
    try:
        rows = await conn.fetch(
            "SELECT id, total, status FROM orders WHERE user_id = $1", user_id
        )
        return [dict(row) for row in rows]
    finally:
        await conn.close()

# psycopg 3 async - nearly as fast, more familiar API
import psycopg

async def get_orders(user_id: int):
    async with await psycopg.AsyncConnection.connect(DATABASE_URL) as conn:
        async with conn.cursor() as cur:
            await cur.execute(
                "SELECT id, total, status FROM orders WHERE user_id = %s", (user_id,)
            )
            rows = await cur.fetchall()
            return rows

Layer 2: SQLAlchemy Core (No ORM)

SQLAlchemy Core gives you a query builder and connection management without object mapping. You write something close to SQL, but Python-native and composable:

from sqlalchemy import select, table, column
from sqlalchemy.ext.asyncio import create_async_engine

engine = create_async_engine("postgresql+asyncpg://user:pass@localhost/mydb")

orders = table("orders", column("id"), column("total"), column("status"), column("user_id"))

async def get_orders(user_id: int):
    async with engine.connect() as conn:
        result = await conn.execute(
            select(orders).where(orders.c.user_id == user_id)
        )
        return result.fetchall()

This adds a small amount of overhead over raw asyncpg (query compilation, parameter binding, connection pool management), but it's a fraction of what the full ORM costs.

Layer 3: The ORM (SQLAlchemy's Declarative Models)

The ORM adds object identity tracking, relationship loading, change tracking (dirty attribute detection), and Python object instantiation for every row:

from sqlalchemy import select
from sqlalchemy.orm import declarative_base, Mapped, mapped_column
from sqlalchemy.ext.asyncio import AsyncSession

Base = declarative_base()

class Order(Base):
    __tablename__ = "orders"
    id: Mapped[int] = mapped_column(primary_key=True)
    total: Mapped[float]
    status: Mapped[str]
    user_id: Mapped[int]

async def get_orders(session: AsyncSession, user_id: int):
    result = await session.execute(select(Order).where(Order.user_id == user_id))
    return result.scalars().all()

Same query. Same data. But now every row becomes a full Python object with SQLAlchemy's instrumentation attached:

Tracking whether each attribute has changed
Registering the object in the session's identity map, so session.get(Order, id) returns the same instance
Setting up lazy-loading machinery for any relationships

The Benchmark: What Each Layer Actually Costs

I ran this on a table with 100,000 rows, fetching 500 rows matching a filter, on a local PostgreSQL instance to eliminate network variance:

Why the ORM Overhead Is So High With Async

The reason: async adds its own overhead (event loop scheduling, coroutine creation, awaiting each step of the driver protocol) on top of the ORM's existing object-mapping cost.

For I/O-bound work (waiting on the network), async wins because the event loop can do other things while waiting.
But object instantiation isn't I/O-bound. It's pure CPU work happening on the same thread, and async adds bookkeeping around it without adding parallelism, because Python's GIL means you're not getting extra CPU time from asyncio. You're just avoiding blocking waits.

So the ORM's object-mapping tax is paid in full, and the async machinery adds a smaller additional tax on top, for operations that were never going to benefit from concurrency in the first place.

When Each Layer Is the Right Choice

Use raw asyncpg when...

# High-throughput analytics endpoint
@app.get("/api/analytics/daily-revenue")
async def daily_revenue(pool: asyncpg.Pool = Depends(get_pool)):
    async with pool.acquire() as conn:
        rows = await conn.fetch("""
            SELECT date_trunc('day', created_at) as day, SUM(total) as revenue
            FROM orders
            WHERE created_at >= NOW() - INTERVAL '30 days'
            GROUP BY day
            ORDER BY day
        """)
        return [dict(r) for r in rows]

Use SQLAlchemy Core when...

async def list_orders(db: AsyncSession, filters: OrderFilters):
    stmt = select(
        orders_table.c.id,
        orders_table.c.total,
        orders_table.c.status,
    ).where(orders_table.c.is_active == True)

    if filters.status:
        stmt = stmt.where(orders_table.c.status == filters.status)
    if filters.min_total:
        stmt = stmt.where(orders_table.c.total >= filters.min_total)

    result = await db.execute(stmt)
    return result.mappings().all()  # Returns dicts, not ORM objects

result.mappings().all() is the key detail. It returns plain dictionaries instead of ORM entity objects, skipping the identity map and change-tracking machinery entirely while still using SQLAlchemy's query builder and connection pooling.

Use the full ORM when...

You're doing writes with relationships (creating an order with items in one transaction)
You need the identity map to prevent duplicate object instances across a request
You're using SQLAlchemy's relationship loading (selectinload, joinedload) to manage complex object graphs
Your team's productivity gain from the ORM's ergonomics outweighs the performance cost for that specific endpoint

async def create_order_with_items(db: AsyncSession, user_id: int, items: list[dict]):
    order = Order(user_id=user_id, status="pending")
    for item in items:
        order.items.append(OrderItem(
            product_id=item["product_id"],
            quantity=item["quantity"],
        ))
    db.add(order)
    await db.commit()
    await db.refresh(order)
    return order

Layer	Best for	Returns
Raw asyncpg	Narrow, performance-critical paths, analytics, streaming	Records / dicts
SQLAlchemy Core	Read-heavy list, search and reporting endpoints	Dicts via `.mappings()`
Full ORM	Writes with relationships, complex object graphs	Tracked ORM objects

The Hybrid Pattern I Actually Use

Most production FastAPI services benefit from using different layers for different endpoints, all sharing the same async engine and connection pool:

# app/database.py
from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker

engine = create_async_engine(
    "postgresql+asyncpg://user:pass@localhost/mydb",
    pool_size=10,
    max_overflow=20,
    pool_pre_ping=True,
)

AsyncSessionLocal = async_sessionmaker(engine, expire_on_commit=False)

async def get_db():
    async with AsyncSessionLocal() as session:
        yield session

from sqlalchemy import text

# Writes and relationship-heavy reads: full ORM
@app.post("/api/orders")
async def create_order(data: OrderCreate, db: AsyncSession = Depends(get_db)):
    order = await order_service.create_order(db, data)  # Uses ORM
    return order

# High-volume list endpoints: SQLAlchemy Core, dicts not objects
@app.get("/api/orders")
async def list_orders(
    filters: OrderFilters = Depends(),
    db: AsyncSession = Depends(get_db),
):
    stmt = build_order_query(filters)  # Core query, no ORM mapping
    result = await db.execute(stmt)
    return result.mappings().all()

# Reporting and analytics: raw SQL via the same session
@app.get("/api/reports/summary")
async def report_summary(db: AsyncSession = Depends(get_db)):
    result = await db.execute(text("""
        SELECT status, COUNT(*) as count, SUM(total) as revenue
        FROM orders GROUP BY status
    """))
    return result.mappings().all()

The One Setting That Changes Everything: `expire_on_commit`

If you do use the ORM, this default setting quietly causes extra queries you didn't ask for:

# Default behavior - expires all objects after commit
async_session = async_sessionmaker(engine)

order = await create_order(db, data)
await db.commit()
print(order.status)  # Triggers a NEW query to refresh the expired object!

By default, SQLAlchemy marks every object's attributes as "expired" after a commit, so the next time you access them, it silently re-fetches from the database to guarantee freshness.

# Disable expiration - trust the in-memory data after commit
async_session = async_sessionmaker(engine, expire_on_commit=False)

expire_on_commit=False is the single highest-leverage SQLAlchemy async setting for API workloads.

I set it on every FastAPI project now. Without it, a simple "create and return" endpoint fires a COMMIT and then a silent SELECT to re-fetch data you already had in memory.

Bottom Line

Async doesn't erase the cost of the layers above the database driver. It just changes what kind of cost you're paying.

Raw asyncpg is fast because it does almost nothing beyond speaking PostgreSQL's protocol.
SQLAlchemy Core adds a thin, worthwhile layer of safety and composability.
The full ORM adds real, measurable overhead in exchange for object mapping, relationship management, and identity tracking: features that are essential for some endpoints and pure cost for others.

It came back down to 95ms: faster than the original sync version, using the same connection pool, the same database, and about fifteen lines of changed code.

Over to You

If this was useful, follow me here for more stories from the backend trenches, and subscribe to The Backend Tech Lead on Substack for weekly blueprints.

The Life of a Django Request

Anas Issath — Sat, 07 Nov 2026 06:08:11 +0000

A guided tour of everything that happens between someone clicking a link and your view function running. No traps, no gotchas, just how it actually works.

Most articles about Django are about what goes wrong. This one isn't. This is a tour.

Every time someone loads a page in your Django app, a small, elegant machine springs into motion. It unwraps the request, threads it through a stack of layers, finds the right view, runs your code, and packages the answer back up, usually in well under a tenth of a second.

Most of us write views for years without ever watching that machine run. We know our view function gets called with a request object, and we know we return a response, and the space between is a comfortable fog.

Let's clear the fog. We're going to follow a single request from the instant it leaves a browser to the instant the response arrives back, and at each stop I'll show you what Django is actually doing and why.

By the end, request and response won't be magic. They'll be things you understand from the inside. That understanding is quietly one of the most useful things you can have as a Django developer, because once you can see the machine, you know exactly where to reach when something needs explaining, debugging, or extending.

Here's the whole journey at a glance, and then we'll walk it step by step:

Browser → Nginx → Gunicorn (WSGI) → Django handler
      → Middleware (in) → URL resolver → Your view (ORM + templates)
      → Middleware (out) → Gunicorn → Nginx → Browser

Stop 1: The Browser and the Web Server

It starts with a browser making an HTTP request (GET /articles/42/) and sending it across the internet to your server. The first thing it hits isn't Django at all. It's a web server, typically Nginx, sitting at the front door.

Nginx handles the things Django shouldn't be bothered with:

Serving static files (CSS, images) directly
Terminating SSL
Load-balancing across multiple app processes

For anything dynamic (anything that needs your code), Nginx hands the request off to an application server like Gunicorn or uWSGI.

Think of Nginx as the receptionist who deals with routine visitors themselves and only forwards the real business inward.

So already there are two programs involved before Django wakes up. This is worth knowing, because when people say "Django is slow to serve static files" or debug a deployment, the answer usually lives right here in the handoff between the web server and the app server.

Stop 2: WSGI, the Universal Handshake

Gunicorn now has the request, but Gunicorn and Django are separate projects written by different people. How do they talk? Through a contract called WSGI (Web Server Gateway Interface), and understanding this contract demystifies a huge amount.

WSGI is dead simple. The server calls your application as a plain function with two arguments:

environ: a dictionary holding everything about the request (the path, headers, method, all of it)
start_response: a callback for setting the status and headers

Your application does its work and returns the response body as bytes.

That's the entire contract. And it's why you can run Django under Gunicorn or uWSGI or others interchangeably. They all speak WSGI, so any of them can talk to any WSGI framework.

Django's end of this handshake lives in a file you've seen and probably never opened: wsgi.py. That file exposes a WSGIHandler, the object Gunicorn calls. This is the true front door of Django itself.

(There's a newer async version of this contract called ASGI, in asgi.py, which is what makes websockets and async views possible. Same idea, built for concurrency. But the classic synchronous path is WSGI, and that's the one we're walking.)

Stop 3: Django Wakes Up and Builds the Request Object

Now Django's WSGIHandler takes over, and the first meaningful thing it does is fire a signal: request_started. This is genuinely your earliest possible hook into the lifecycle. Code can subscribe to that signal to run the moment any request begins, before anything else happens.

Then Django takes that raw environ dictionary and wraps it into the object you know and love: the HttpRequest. This is the request that gets passed to every view you've ever written: request.method, request.path, request.GET, request.user. It's not magic; it's a friendly Python object built from the raw WSGI environment.

Here's a lovely detail most people never learn: parts of the request are lazy. request.body, request.POST, and request.FILES don't actually read the incoming data from the network socket until you access them.

Django creates the request object instantly without reading the (potentially huge) body, and only pulls it in when your code asks. It's an elegant efficiency: why read a 10MB upload into memory if this particular view never looks at it?

(One consequence: once you've read request.body, you generally can't read it again. The stream's been consumed.)

Stop 4: The Middleware Onion (Going In)

Now the request begins its journey inward through the middleware stack, and the onion is the perfect mental image. The request travels from the outside layer toward the center, and later the response travels back out through the same layers in reverse.

Middleware is just a list in your settings.py, and Django processes it top to bottom on the way in. Each layer is a chance to inspect, modify, or even stop the request before it reaches your view. Walk the default stack and you'll recognize old friends, each doing one job:

Middleware	What it does on the way in
`SecurityMiddleware`	Enforces HTTPS and security-related rules
`SessionMiddleware`	Loads the user's session from the session store and attaches it
`CommonMiddleware`	Handles URL normalization and a few housekeeping rules
`CsrfViewMiddleware`	Checks the CSRF token on form submissions
`AuthenticationMiddleware`	Gives you `request.user`, by reading the session and looking up who's logged in

That last one explains something you've used a thousand times: request.user isn't conjured from nowhere. A specific middleware put it there, on the way in, before your view ran. (And it's lazy too: request.user is only actually fetched from the database the first time you touch it.)

The power of the onion is that any layer can short-circuit the whole thing. If CsrfViewMiddleware sees a bad token, it returns a 403 right there. Your view never runs, and the response turns around and heads back out.

Middleware is where cross-cutting concerns (security, sessions, auth) live so your views don't have to repeat them.

Stop 5: The URL Resolver Finds Your View

The request has made it through middleware. Now Django needs to answer: which view handles /articles/42/? That's the job of the URL resolver (the URL dispatcher), driven by your urls.py.

Django takes the request path and tests it against your URL patterns, in order, top to bottom, until one matches. When path("articles/<int:article_id>/", views.article_detail) matches, two things come out of it:

The view function to call (article_detail)
The captured arguments from the URL (article_id=42, already converted to an int by the <int:> converter)

A great under-the-hood detail: the resolver is cached. Django builds the URL-pattern structure once and keeps it in memory rather than rebuilding it on every request. That's an important efficiency, since every single request needs it.

This is also the quiet reason that changing your urls.py requires a server restart in production: the resolved URL configuration is cached at startup, so new patterns aren't picked up until the process reloads. If you've ever wondered why a URL change "didn't take" until you restarted, now you know exactly why.

Stop 6: Your View Runs (the Part You Already Know)

Here we are at the center of the onion: your code. Django calls your view with the request object and the captured URL arguments:

def article_detail(request, article_id):
    article = Article.objects.get(id=article_id)   # ORM → SQL → database
    return render(request, "article.html", {"article": article})

This is the part you write every day, so I'll just point out the two systems it usually reaches for:

The ORM. Article.objects.get(...) doesn't talk to the database directly. It builds a description of a query that the ORM translates into SQL, sends to the database, and turns the result rows back into Python Article objects. (Lazy, as ever: the query runs when the data is actually needed.)
The template engine. render(...) takes your template and the context dictionary, and the template engine produces the final HTML string, which gets wrapped in an HttpResponse object.

One detail worth carrying with you, especially for class-based views: Django creates a new view instance for every request. That's deliberate. It keeps requests isolated from each other.

(It's also why a mutable class attribute on a view is shared across all requests while instance attributes aren't: a direct consequence of this one-instance-per-request design.)

Your view's job ends the moment it returns a response object. Now that response begins its journey back out.

Stop 7: Back Out Through the Middleware (in Reverse)

The response now travels back out through the same middleware stack, but in reverse order, bottom to top. This symmetry is the elegant heart of the onion: each middleware that got a look at the request on the way in gets a look at the response on the way out.

And they use it. On the way out:

SessionMiddleware saves any changes you made to the session back to the session store.
SecurityMiddleware and others add security headers to the outgoing response (things like X-Content-Type-Options).
Other middleware can compress the body, set cookies, or add their own headers.

So the response that leaves is richer than the one your view returned. Middleware decorated it on the way out with sessions, headers, and cookies.

The same layers, in mirror order: request going in, response coming out. That mirror is the whole design.

Stop 8: The Response Goes Home

The finished HttpResponse (status code, headers, and body) is handed back to the WSGI server (Gunicorn), which converts it into raw HTTP bytes and passes it to Nginx, which sends it across the internet to the browser, which renders the page. The machine goes quiet, waiting for the next request.

The whole round trip happened in a fraction of a second:

Browser to Nginx to Gunicorn to Django's handler
In through middleware
Resolve the URL
Run the view, query the database, render the template
Back out through middleware
And home again

And now you can see every stop.

Why Knowing This Makes You Better at Django

Here's the payoff, and it's real. Almost every Django concept that feels like "magic" is actually just one specific stop on this tour, and once you can place it, it stops being mysterious:

The question	Where it lives
Where does `request.user` come from?	`AuthenticationMiddleware`, on the way in (Stop 4)
Why do my security headers appear even though my view didn't add them?	Middleware, on the way out (Stop 7)
Why did my URL change not work until I restarted?	The cached URL resolver (Stop 5)
Where should I put code that runs on every request?	Middleware, or the `request_started` signal (Stops 3–4)
Why is a class attribute on my view shared between users?	One view instance per request (Stop 6)
How can I run Django under Gunicorn or uWSGI?	They both speak WSGI (Stop 2)

None of these need memorizing once you hold the map. They all fall out of understanding the flow. That's the difference between using a framework and understanding it, and it's the difference between guessing where a problem lives and knowing.

Django's request/response cycle is, honestly, a beautiful piece of design. A raw network request enters, gets progressively wrapped and enriched into a friendly Python object, passes through orderly layers that each do one job, reaches your code at the calm center, and the answer flows back out through the same layers in mirror image.

It's an onion, and now you've seen every ring of it. The next time you write def my_view(request):, you'll know exactly what that request went through to reach you, and exactly where it's going next.

The 30-Second Takeaway

A request hits a web server (Nginx) first. It serves static files and forwards dynamic requests to an app server (Gunicorn/uWSGI).
Gunicorn and Django talk through WSGI, a tiny contract (environ dict in, response bytes out), which is why any WSGI server can run any WSGI framework. Django's entry point is wsgi.py (async apps use asgi.py).
Django fires the request_started signal, then wraps the raw environ into the HttpRequest you know, with body/POST/FILES read lazily, only when accessed.
The request travels inward through middleware (top→bottom): SecurityMiddleware, SessionMiddleware, AuthenticationMiddleware (this is what sets request.user), etc. Any layer can short-circuit and return early.
The URL resolver matches the path against urls.py (top→bottom) and extracts view + arguments. It's cached, which is why URL changes need a restart.
Your view runs (a new instance per request for CBVs), uses the ORM and template engine, and returns an HttpResponse.
The response travels back out through middleware in reverse (bottom→top), saving the session and adding security headers, then back through WSGI → Nginx → browser.
Nearly every "magic" Django behavior is just one stop on this tour. Knowing the map turns mysteries into locations.

Your Turn

Enjoyed the tour? The best way to make it stick is to open your own project's settings.py, read your MIDDLEWARE list top to bottom, and trace one real URL from urls.py to its view. You'll never see def my_view(request): the same way again.

Which stop on the tour surprised you most? Tell me in the comments 👇

If this was useful, follow me here for more stories from the backend trenches, and subscribe to The Backend Tech Lead on Substack for weekly blueprints.

I Built a Tiny Django QuerySet to Finally Understand How .filter() Works

Anas Issath — Fri, 06 Nov 2026 17:30:06 +0000

Django's QuerySet is "lazy" and "chainable" and everyone repeats those words, so let's build a working one in about 50 lines and watch the magic disappear.

You've written Book.objects.filter(published=True).order_by("-created_at")[:10] a thousand times. You know it's "lazy." You know it's "chainable." You may have read that "nothing hits the database until you iterate."

But if someone asked you to explain how that actually works (how chaining .filter() five times still produces one query, how the thing knows to wait), could you?

Most of us couldn't, and that's fine, because you don't need to in order to use it. But there's a special kind of confidence that comes from understanding a tool from the inside. The fastest way to get there isn't to read the Django source (it's thousands of lines of edge cases). It's to build a tiny version yourself.

So that's what we're going to do: a working, chainable, lazy QuerySet in about 50 lines of plain Python. No Django, no database, just the ideas. By the end, the "magic" will just be code you wrote.

We'll build it in four small steps, each solving the problem the last one revealed.

Step 1: The Naive Version (and Why It's Wrong)

Let's start with the most obvious way to write a "filter my data" object, so we can feel exactly what's missing. Imagine our "database" is just a list of dicts:

BOOKS = [
    {"title": "Dune", "published": True, "year": 1965},
    {"title": "Drafty", "published": False, "year": 2020},
    {"title": "Foundation", "published": True, "year": 1951},
]

class NaiveQuery:
    def __init__(self, data):
        self.data = data

    def filter(self, **conditions):
        # immediately loop and keep matching rows
        results = [
            row for row in self.data
            if all(row.get(k) == v for k, v in conditions.items())
        ]
        return results

NaiveQuery(BOOKS).filter(published=True)
# → [{'title': 'Dune', ...}, {'title': 'Foundation', ...}]

It works! But look at what it forces.

It can't chain. Because filter() immediately returns a plain list, NaiveQuery(BOOKS).filter(published=True).filter(year=1965) blows up, because a list has no .filter().
It's eager. It does the full scan the instant you call filter, even if you only wanted to know "are there any?" or "how many?". If this were a real database, every .filter() would be a separate expensive query.

Those two problems, can't chain and runs immediately, are exactly the two things Django's design solves. Let's solve them too.

Step 2: Make It Chainable (Return a New Object, Don't Execute)

Here's the key insight that unlocks chaining, and it's the same one Django uses:

filter() shouldn't return results. It should return a new query object that remembers the filter and hasn't run anything yet.

So instead of storing the data and filtering it, our query object stores the list of conditions to eventually apply:

class Query:
    def __init__(self, data, filters=None):
        self.data = data
        self.filters = filters or []      # a list of conditions, not results

    def filter(self, **conditions):
        # DON'T run anything. Return a NEW Query with this filter added on.
        return Query(self.data, self.filters + [conditions])

Look at what changed. filter() now returns Query(...) (another Query object) with the new condition appended to the list. It touches no data. And because it returns a Query, that returned object also has a .filter() method, so you can call it again:

q = Query(BOOKS).filter(published=True).filter(year=1965)
# q is a Query holding filters=[{'published': True}, {'year': 1965}]
# ...and still NOTHING has been computed.

This is exactly Django's real behavior, and the real source looks almost identical. Every .filter() calls _clone() to make a new QuerySet, adds the condition to it, and returns it.

Django calls this being immutable through cloning: your original query is never modified; each method hands back a fresh copy with one more instruction. That's why you can safely build a base queryset and branch off it in different directions without them interfering.

We can chain now. But we still haven't solved "when does it actually run?"

Step 3: Make It Lazy (Run Only When Something Asks for Results)

Here's the heart of it. Our Query is now a recipe: a stored list of instructions that hasn't been executed. We want it to execute only when someone actually tries to read the results: loops over it, lists it, counts it.

In Python, "someone loops over this object" has a specific hook: the __iter__ method. That's our trigger.

class Query:
    def __init__(self, data, filters=None):
        self.data = data
        self.filters = filters or []
        self._cache = None          # results live here AFTER we run

    def filter(self, **conditions):
        return Query(self.data, self.filters + [conditions])

    def _execute(self):
        # apply every stored filter, in order - this is the "expensive" part
        results = self.data
        for condition in self.filters:
            results = [
                row for row in results
                if all(row.get(k) == v for k, v in condition.items())
            ]
        return results

    def __iter__(self):
        # THE TRIGGER: only now do we actually run the query
        if self._cache is None:          # ...and only once
            self._cache = self._execute()
        return iter(self._cache)

Now watch the behavior fall into place:

q = Query(BOOKS).filter(published=True)   # nothing runs - just a recipe
print("built the query, DB untouched")    # prints; still nothing ran

for book in q:                            # NOW __iter__ fires → _execute() runs
    print(book["title"])                  # Dune, Foundation

Building the query ran nothing. The work happened only when we looped. That is lazy evaluation, and it's the entire reason chaining five .filter() calls doesn't mean five database trips. None of them execute; they just stack instructions onto the recipe, and the one execution happens at the end when you consume it.

And notice self._cache. The first time you iterate, we run _execute() and store the results. The second time you iterate the same object, self._cache is already filled, so we skip execution and reuse it.

This is Django's result cache, the exact reason that iterating the same queryset variable twice only queries once. We just built it, in three lines.

Django's real __iter__ is remarkably close to ours. It calls _fetch_all(), which checks "is _result_cache None? If so, run the query and fill it," then hands back the cached list. Same idea, same shape.

Step 4: Add the Terminal Methods (`count`, `exists`, `first`)

The last piece: the convenience methods that consume the query. Now that iteration is our trigger, these are almost trivial to write. They just use the results:

    # inside class Query:
    def count(self):
        return len(list(self))       # list(self) triggers __iter__

    def exists(self):
        return bool(list(self))

    def first(self):
        results = list(self)
        return results[0] if results else None

Each of these forces evaluation by consuming the query, then answers its narrow question.

And here's a subtlety worth pausing on that mirrors real Django. In our simple version, list(self) fills the cache, so calling .count() and then looping is efficient. In real Django, .count() and .exists() are actually smarter: they send SELECT COUNT(*) and SELECT 1 to the database instead of loading every row.

That's why you should prefer .exists() over if queryset: when you only need a yes/no.

But the shape is the same: terminal methods are the things that break the laziness and make the query finally run.

The Whole Thing, ~50 Lines

Here's our complete mini-QuerySet. Read it top to bottom. There's no magic left in it, because you built every line:

class Query:
    def __init__(self, data, filters=None):
        self.data = data
        self.filters = filters or []
        self._cache = None

    def filter(self, **conditions):
        # chainable + lazy: return a NEW query, run nothing
        return Query(self.data, self.filters + [conditions])

    def _execute(self):
        results = self.data
        for condition in self.filters:
            results = [
                row for row in results
                if all(row.get(k) == v for k, v in condition.items())
            ]
        return results

    def __iter__(self):
        if self._cache is None:        # run once...
            self._cache = self._execute()
        return iter(self._cache)       # ...then reuse the cache

    def count(self):
        return len(list(self))

    def exists(self):
        return bool(list(self))

    def first(self):
        results = list(self)
        return results[0] if results else None

# our "manager":
class Manager:
    def __init__(self, data):
        self.data = data
    def all(self):
        return Query(self.data)
    def filter(self, **conditions):
        return Query(self.data).filter(**conditions)

books = Manager(BOOKS)

And it behaves like the real thing:

q = books.filter(published=True)   # lazy - nothing ran
print("nothing executed yet")

for b in q:                        # runs once, caches
    print(b["title"])              # Dune, Foundation

print(q.count())                   # reuses the cache - no re-run
print(books.filter(year=1965).first()["title"])   # Dune

That Manager at the bottom is the last piece of the puzzle. It's why you write Book.objects.filter(...) and not Query(Book).filter(...). In Django, .objects is a Manager, and its job is exactly what ours does: hand you a fresh QuerySet to start chaining from. Every Book.objects.filter(...) starts a brand-new recipe.

What You Can Now See That You Couldn't Before

You just built, in miniature, the real architecture of Django's ORM. And a bunch of things that were "just how it works" now have obvious mechanical explanations:

Question	Answer
Why doesn't `Book.objects.filter(...)` hit the database?	Like our `filter()`, it returns a new object holding instructions and runs nothing.
Why can I chain `.filter().exclude().order_by()` endlessly with one query?	Each one just appends to the recipe and returns a new query; execution happens once, at the end.
Why does iterating the same queryset twice only query once?	The result cache: our `self._cache`, Django's `_result_cache`.
Why is a fresh `Model.objects.filter(...)` a separate query from another one?	Different Query objects, different caches, exactly like calling our `Manager.filter()` twice.
Why does `.exists()` exist when I could just do `if qs:`?	A purpose-built terminal method can ask the database a cheaper question than "load everything."

None of this is memorization anymore. It falls out of the shape:

A query object stores instructions, stays lazy by returning new copies of itself, and only runs (once, then cached) when something consumes it.

That one sentence is the whole ORM, and now you've felt it from the inside.

The real Django QuerySet is, of course, vastly more. It compiles these instructions into actual SQL across different database backends, handles joins and annotations and prefetching, and manages a hundred edge cases we happily ignored. But the soul of it (lazy, chainable, cloned-not-mutated, cached-after-execution) is exactly the fifty lines you just wrote.

The next time you chain a queryset, you won't see magic. You'll see the recipe being built, waiting patiently for you to ask for the meal.

The 30-Second Takeaway

Django's QuerySet feels magical because it's lazy (doesn't run until consumed) and chainable (.filter().exclude()...), but both are simple mechanics you can build yourself.
Chainable: filter() returns a new query object holding the accumulated conditions and runs nothing. Django does the same via _clone(). QuerySets are "immutable through cloning," which is why you can branch a base query safely.
Lazy: execution is triggered by consumption, Python's __iter__ hook. Building the query runs nothing; looping/listing/counting is what finally executes it. That's why five chained .filter()s = one query, not five.
Result cache: the first execution stores its results (self._cache in ours, _result_cache in Django), so iterating the same queryset again reuses them instead of re-querying.
Terminal methods (count, exists, first) are the things that consume the query and break the laziness; real Django's .count()/.exists() send cheaper SQL than loading every row.
.objects is a Manager. Its only job is to hand you a fresh QuerySet to start chaining from.
Build the ~50-line version once and the real ORM's behavior stops being magic. It's just "store instructions, clone to stay lazy, execute once when consumed."

Your Turn

The best way to make this stick: paste the 50-line version into a file, add an order_by method yourself (hint: it also just appends to the recipe), and watch it slot right in. Understanding beats memorizing, and building beats reading.

Got your order_by working? Share your version in the comments 👇

If this was useful, follow me here for more stories from the backend trenches, and subscribe to The Backend Tech Lead on Substack for weekly blueprints.

Six Things Django Developers Say That Aren't True in 2026

Anas Issath — Thu, 05 Nov 2026 11:40:53 +0000

The recycled criticisms that sound smart, get repeated everywhere, and quietly stopped being accurate years ago.

Every few months, someone declares Django dead.

"It's too slow." "It's not async." "It doesn't scale." "Everyone's on FastAPI now."

These takes get repeated in Hacker News threads, Discord servers, and hallway conversations until they feel like common knowledge: the kind of thing you nod along to without checking.

Here's the problem: most of them stopped being true years ago, and a few were never true to begin with. Meanwhile, in 2026, Django quietly powers Instagram, Spotify, and Pinterest and serves millions of users for companies that never got the memo that it "doesn't scale."

This isn't a "Django is perfect" article. It has real, honest limitations, and I'll point to them. It's a "let's check the claims against reality" article. Because repeating an outdated criticism doesn't make you sound experienced; it makes you sound like you learned Django in 2015 and stopped paying attention.

Let's go through the six most common ones, concede what's true in each, and correct what isn't.

Myth 1: "Django Doesn't Scale"

This is the big one, and it's the easiest to debunk because the counterexamples are enormous and public. Instagram runs on Django at hundreds of millions of users. Disqus scaled Django to serve billions of comments. Spotify and Pinterest are on it. If Django "didn't scale," these companies would have been the first to find out, at the worst possible time.

The grain of truth: a naive Django app does fall over under load, but not because of Django. It falls over because of N+1 queries, missing database indexes, blocking calls in the request cycle, and no caching. Those are architecture and code problems, and they'd sink an app in any framework.

As one 2026 scaling write-up put it bluntly: scaling without measurement is superstition, and the real bottleneck is almost never the framework. It's poor query design, missing indexes, and unclear ownership.

The reality: Django is stateless by design, which is exactly what makes it scale horizontally:

Add more Gunicorn workers or more instances behind a load balancer
Cache expensive queries in Redis
Push slow work to Celery

Do that, and it scales to the moon. The "doesn't scale" argument is from 2015, and it was wrong then too. The framework was never the ceiling.

Myth 2: "Django Isn't Async, So It's Slow"

This one sounds technical, which is why it spreads. And it contains a real fact wrapped around a wrong conclusion.

The grain of truth: Django came to async late (async views in 3.1, ASGI support, and async ORM operations since 4.1), and its async story is genuinely still maturing. The ORM has sync roots, and there are sharp edges. If you're building something truly async-first with heavy concurrent I/O, that friction is real. (I've written about it. It's a legitimate pain point.)

Where the logic breaks: "not async" does not mean "slow." Here's the fact the benchmarks don't advertise: most web applications are I/O-bound, not CPU-bound, and the bottleneck isn't the framework. It's your database queries, third-party API calls, and unoptimized code.

Async helps when you're I/O-bound and juggling many concurrent slow operations (external APIs, websockets, streaming). It does not magically make a normal CRUD endpoint faster. A well-architected Django app with proper caching, indexing, and query optimization will outperform a poorly written async app every single time.

Async is a tool for a specific shape of problem, not a global speed setting, and most apps don't have that shape.

Myth 3: "Django Can't Do APIs. Use FastAPI."

Django "can't do APIs" would be news to the thousands of production APIs running on Django REST Framework, which has been a mature, capable API toolkit for over a decade.

The grain of truth: for a pure, async-first, high-concurrency API microservice, FastAPI is often the better fit in 2026. It's async from the ground up, its Pydantic-based validation is lovely, and it benchmarks faster than DRF on async workloads. That's a real and fair distinction, and if that's your exact use case, reach for it.

The reality: "Django can't do APIs" and "FastAPI is better for some API workloads" are very different claims. Django does APIs extremely well, and in 2026 you're not even limited to DRF. Django Ninja brings FastAPI's exact ergonomics (type hints, Pydantic v2, auto-generated docs, native async) into Django, so you can have the modern API feel without giving up the ORM, admin, auth, and migrations.

The honest framing the community has settled on:

Django for full products and admin-heavy SaaS, FastAPI for API-first microservices. Both are correct choices, not a winner and a loser.

"Can't" is just wrong.

Myth 4: "Django Can't Do Microservices. It's a Monolith."

The grain of truth: Django encourages a monolith, and its batteries-included design is optimized for building one cohesive application. That's a real default.

Why the conclusion is wrong: "encourages a monolith" is not "can't do microservices." You can absolutely build decoupled Django services that talk over APIs and message queues, with DRF, Docker, and Celery. Django is not the obstacle; the architecture is.

And here's the part that actually matters in 2026: most applications don't need microservices in the first place. Splitting prematurely buys you network complexity, debugging pain, and operational overhead in exchange for benefits you may never use.

The guidance from people running Django at scale is to start as a modular monolith and stay one until you have a concrete reason to split:

Teams blocked by codebase coupling
Deployment bottlenecks
A domain that needs radically different scaling

A well-designed modular monolith survives astonishing scale. "Django is a monolith" is a description, not an insult, and often it's describing the right choice.

Myth 5: "Batteries-Included Makes Django Bloated and Slow"

The grain of truth: Django ships a lot, and it does carry two decades of history, including some dated modules you'll never use. If your project is genuinely tiny (a single-endpoint service), Django is more than you need, and a microframework fits better.

Why it's mostly wrong: people call Django "too heavy" until they spend three days wiring up authentication, permissions, migrations, an admin interface, and CSRF protection in a lighter framework. Django gives you all of that on day one, tested and integrated.

"Batteries included is a feature, not a liability" is the take that holds up. And bloat doesn't equal slow: the modules you don't use don't run.

Django's security posture alone earns its weight:

PBKDF2/bcrypt password hashing
CSRF and XSS protections
SQL-injection-safe ORM queries
A framework that actively refuses to start with a dangerously weak config

That's not bloat; that's a mountain of correct decisions you didn't have to make or maintain yourself.

Myth 6: "Real Developers Hand-Write SQL. The ORM Is for Beginners."

The grain of truth: the ORM can generate inefficient SQL, it hides the database in ways that cause N+1 problems, and for genuinely complex analytical queries, raw SQL is sometimes the right and clearer tool. A good Django developer can read the SQL the ORM produces and drops to raw SQL when it's warranted.

Why the "for beginners" framing is backwards: the ORM isn't training wheels you outgrow. It's a productivity and safety tool that experts use deliberately. It gives you:

Parameterized queries that prevent SQL injection by default
Database-agnostic code
Migrations
Readable query composition

The mark of an experienced Django developer isn't refusing the ORM. It's using it fluently for the 95% of queries where it's clearer and safer, and knowing exactly when to reach past it for the other 5%.

Treating "I hand-write all my SQL" as a badge of seniority usually signals the opposite: someone who hasn't learned to use the ORM well, or who's reintroducing injection risks and database lock-in for no benefit. Use the right tool; the ORM is right far more often than the myth admits.

The Pattern Underneath All Six

Step back and notice something: nearly every one of these myths collapses into the same underlying truth.

The bottleneck is almost never Django. It's the architecture, the queries, and the code.

The myth says...	What's actually happening
"Doesn't scale"	Your N+1 queries don't scale
"Too slow"	Your missing indexes are slow
"Can't do APIs/microservices"	Your design choices, not the framework

Over and over, people blame the tool for what is, honestly, a design or discipline problem, because blaming the framework is easier than auditing your own queries. Django is a mirror here: a well-architected Django app is fast and scales enormously; a poorly-architected one falls over, exactly as a poorly-architected app in any framework would.

None of this means Django is the right choice for everything. It genuinely isn't. If you're building a high-concurrency, async-first, real-time API service, FastAPI or an async-native stack is often the better call, and Django's async maturity is a real, honest limitation. That's a legitimate reason to pick something else.

"Django is slow / dead / doesn't scale / can't do APIs" is not a legitimate reason. It's a 2015 opinion that reality stopped supporting years ago.

So the useful move in 2026 isn't to defend Django or bury it. It's to stop repeating criticisms you haven't checked, evaluate the actual workload in front of you, and pick the tool that fits it, knowing that for a huge range of real products, the boring, batteries-included, "doesn't scale" framework is quietly running some of the largest applications on the internet.

Repeating the myth is easy. Checking it is what separates an engineer from a parrot.

The 30-Second Takeaway

"Doesn't scale": Instagram, Disqus, Pinterest, Spotify run on Django at massive scale. It's stateless and scales horizontally; what fails is N+1 queries and missing indexes, not the framework.
"Not async, so slow": most apps are I/O-bound; async only helps with many concurrent slow I/O operations, and a well-optimized sync app beats a badly-written async one. (Django's async maturity is a real limitation for async-first workloads, though.)
"Can't do APIs": DRF has done APIs well for a decade, and Django Ninja now offers FastAPI-style typed, async, auto-documented APIs inside Django. FastAPI is better for pure async microservices; that's a fit question, not a "can't."
"Can't do microservices / it's a monolith": you can build Django services, but more importantly most apps shouldn't. A modular monolith survives huge scale, and premature splitting adds cost for no benefit.
"Batteries-included = bloated/slow": unused modules don't run; the built-in auth, admin, migrations, and security (PBKDF2, CSRF, injection-safe ORM) are a feature that saves you weeks.
"The ORM is for beginners": experts use it deliberately for safety (parameterized queries) and speed of development, and drop to raw SQL only for the rare query that needs it.

The through-line: the bottleneck is almost never Django. It's your architecture and your queries.

Your Turn

Pick your tools by fit, not by folklore. Django isn't right for everything, but "it doesn't scale" hasn't been a real reason since before half the people repeating it started coding.

Which Django myth do you hear most often? Or is there one on this list you think is actually still true? Tell me in the comments 👇

If this was useful, follow me here for more stories from the backend trenches, and subscribe to The Backend Tech Lead on Substack for weekly blueprints.

Adding a Database Server Doubled Capacity. One Index Multiplied It by 2,600.

Anas Issath — Wed, 04 Nov 2026 19:20:38 +0000

Measure first, then climb the ladder.

The API is slow.

The dashboard takes two seconds to load. Someone opens the database graph and sees CPU at 100%.

Then the meeting starts.

"We need a read replica."
"Should we add Redis?"
"Maybe it's time to shard."

All three sound like serious engineering. All three cost money, add moving parts, and stay with you for years.

But nobody in that meeting has asked the first question: what is the database actually doing?

Nikki Siapno's recent Level Up Coding piece on scaling databases lays out a sensible order: measure the bottleneck, reduce the work, optimize queries, then add replicas, and shard last.

So this article puts that order to the test. One table, one slow query, one load test, and real measurements at each step.

The Test

The setup is a small PostgreSQL lab that you can run yourself. The full script is at the end of this article.

PostgreSQL 16.15 on a 2-core Linux cloud machine
An orders table with 5 million rows and 100,000 customers
One common query: the 20 latest orders for a customer
8 clients sending that query as fast as they can for 30 seconds
Each step repeated 3 times, reporting the median

Here is the query:

SELECT id, status, amount, created_at
FROM orders
WHERE customer_id = 4242
ORDER BY created_at DESC
LIMIT 20;

It looks harmless. You probably have one like it in your code right now.

To keep the comparison fair, the primary database was pinned to one CPU core. The second core ran the load generator, or the replica in the replica test.

The Results

Read that table twice.

The replica worked. Capacity went up about 2.2 times, which is close to what a second core should give.

But one CREATE INDEX on the original server handled about 2,600 times the traffic. It beat the two-server setup by about 1,180 times.

The index took 4.8 seconds to build and used 150 MB of disk.

Step 1: Measure the Bottleneck

Before touching anything, ask PostgreSQL where the time goes.

The pg_stat_statements extension records every query shape, how often it runs, and how long it takes. It must be added to shared_preload_libraries, which needs a restart.

SELECT calls,
       round(mean_exec_time::numeric, 1) AS avg_ms,
       round(total_exec_time::numeric / 1000, 1) AS total_seconds,
       left(query, 70) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Sort by total time, not average time. A 5 ms query that runs a million times hurts more than a 2 second report that runs once a day.

Next, look at the plan of your worst query:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, amount, created_at
FROM orders
WHERE customer_id = 4242
ORDER BY created_at DESC
LIMIT 20;

This is the real output from the test, shortened:

Limit (actual time=224.516..224.520 rows=20)
  Buffers: shared hit=66670
  -> Sort
       -> Seq Scan on orders
            Filter: (customer_id = 4242)
            Rows Removed by Filter: 4999944
Execution Time: 245.630 ms

Here is the whole problem in one line: Rows Removed by Filter: 4999944.

To find 56 rows for one customer, PostgreSQL read all 5 million. Think of a librarian who walks past every book in the building each time someone asks for one author.

No amount of extra hardware changes what this query is doing.

Different symptoms point to different problems:

What you see	Look at first
High CPU, slow queries	Query plans, missing indexes
Many rows read, few returned	Indexes
Same query running repeatedly	N+1 queries, caching
Too many connections	Connection pooling
Slow only at peak, plans are ok	Hardware or replicas
Fast database, slow API	The app or another service
Slow writes, heavy disk use	Write volume, too many indexes

These two queries help with the first pass:

-- Tables being read the hard way
SELECT relname, seq_scan, seq_tup_read, idx_scan,
       seq_tup_read / NULLIF(seq_scan, 0) AS rows_per_scan
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC
LIMIT 10;

-- Connection pressure
SELECT count(*) FILTER (WHERE state = 'active') AS active,
       count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_txn,
       count(*) AS total,
       current_setting('max_connections') AS max
FROM pg_stat_activity
WHERE backend_type = 'client backend';

Step 2: The Replica Added Capacity, Not Speed

This is the step many teams take first, so it deserves a fair look.

With a replica, the same 8 clients were split between two servers. Throughput doubled and average latency fell from 2,020 ms to 926 ms.

That looks like a fix. It isn't.

With no load, the query took 246 ms on the primary and 304 ms on the replica. The plan on the replica was identical: same sequential scan, same 4,999,944 rows thrown away.

So where did the latency gain come from?

Queueing. With 8 clients on one core, each request spent most of its time waiting behind other requests. A second server shortened the line. The checkout was just as slow.

A replica shortens the queue. It does not make the work smaller.

The replica also brings bills of its own: another server to pay for and monitor, and one more problem that the next section measures.

Step 3: The Replica Problem Nobody Sees in Testing

A read replica copies changes from the primary a moment after they commit. That moment is called replication lag.

Here is the test. Insert a row on the primary, wait for the commit, then read it from the replica straight away. Do that 2,000 times.

This was the best possible case: both servers on the same machine, no network between them, and almost no load.

	Idle	Under write load
Writes tested	2,000	2,000
Row missing on first read	1,973	1,473
Median catch-up	0.33 ms	0.53 ms
Worst catch-up	18.44 ms	41.12 ms

98.65% of immediate reads missed the row on an idle system.

The replica was only 0.33 ms behind. That was still slower than the next read.

Don't read too much into the idle and load numbers being in that order. The point is that "very low lag" and "no stale reads" are different things. Across a real network, lag will be higher.

Your users meet this as a strange bug:

A user updates their profile.
The app redirects to the profile page.
The page shows the old name.
The user refreshes, and now it's correct.

The fix is to read from the primary right after a write. Here is a simple Django router that does this for the rest of a request:

import threading

_state = threading.local()

class PrimaryAfterWriteRouter:
    def db_for_read(self, model, **hints):
        return "default" if getattr(_state, "wrote", False) else "replica"

    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, **hints):
        return db == "default"

def primary_after_write_middleware(get_response):
    def middleware(request):
        _state.wrote = False
        try:
            return get_response(request)
        finally:
            _state.wrote = False
    return middleware

Add the class to DATABASE_ROUTERS and the middleware to MIDDLEWARE.

This covers one request. In the profile example, the redirect is a second request, so you also need something that carries over, such as a short-lived cookie or session flag that keeps that user on the primary for a few seconds.

Also keep money reads on the primary: balances, stock counts, permissions, and anything a decision depends on.

The Failure This Lab Hit by Accident

The first run of this test never finished.

During the write-heavy stage, the replica fell behind. PostgreSQL's primary keeps only a limited amount of old WAL (its change log), and it recycled the segment the replica still needed. The replica could never catch up, and the primary's log filled with this line every few seconds:

ERROR: requested WAL segment 00000001000000000000004C has already been removed

Nothing crashed. The replica stayed up and kept answering queries, while its data froze in the past. The test sat waiting for a row that would never arrive until it was stopped.

This is the scariest kind of failure: a healthy-looking server serving old data.

The fix was a replication slot, which makes the primary keep WAL until the replica has received it:

pg_basebackup -h primary -D /data/replica -R -X stream -c fast -C -S lab_slot

Slots have their own danger. If a replica dies and the slot stays, the primary keeps WAL forever and can fill its disk. Set max_slot_wal_keep_size as a limit, and watch both of these:

SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS wal_held
FROM pg_replication_slots;

SELECT application_name, state,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS behind,
       replay_lag
FROM pg_stat_replication;

If a replica is missing from the second query, it has stopped replicating. Alert on that.

Step 4: Cache Only What Repeats

Redis is the next suggestion in most of these meetings, so the lab tested it on the slow, unindexed query.

The code is the standard cache-aside pattern: check Redis, and on a miss, query PostgreSQL and store the result for 5 minutes. One client ran for 60 seconds with three traffic patterns:

Traffic pattern	Hit rate	Average	Median
All customers equally	0.4%	218 ms	210 ms
90% from 1,000 customers	9.8%	197 ms	209 ms
90% from 100 customers	84.7%	33 ms	0.08 ms

The third row is what people imagine when they say "add a cache." The first two rows are what can happen instead.

The cache did not fail. The traffic did not repeat enough. With 100,000 customers and about 5 slow queries a second, most keys were requested once and never seen again before the test ended.

Even in the best case, the misses stayed painful. The 95th percentile was still 219 ms, because every miss ran the same full table scan.

Compare that with the plain index: under 1 ms on average, even with 8 clients at full speed, with no hit rate to worry about and no invalidation to get wrong.

A cache makes a good query cheaper. It is a poor way to hide a bad one.

So before you add one, ask these questions:

Do the same keys get read again and again?
How stale can this data be?
Who invalidates it when the data changes?
What happens to the database if Redis restarts and every request misses?

That last one is a real risk. OpenAI's write-up, covered below, describes using cache locking and leasing so that only one reader fetches a missed key from PostgreSQL.

Step 5: The Index, and Its Bill

Here is the plan after one index:

CREATE INDEX CONCURRENTLY orders_customer_created
ON orders (customer_id, created_at DESC);

Limit (actual time=0.034..0.205 rows=20)
  Buffers: shared hit=23
  -> Index Scan using orders_customer_created on orders
       Index Cond: (customer_id = 4242)
Execution Time: 0.228 ms

From 66,670 buffers to 23. From 245.6 ms to 0.23 ms.

The column order matters. customer_id narrows down to one customer, and created_at DESC means the rows are already sorted, so PostgreSQL can stop after 20. The sort step disappears.

Use CONCURRENTLY on a live table. A plain CREATE INDEX blocks writes while it builds. In Django, the safe version looks like this:

from django.contrib.postgres.operations import AddIndexConcurrently
from django.db import migrations, models

class Migration(migrations.Migration):
    atomic = False

    dependencies = [("orders", "0041_previous")]

    operations = [
        AddIndexConcurrently(
            model_name="order",
            index=models.Index(
                fields=["customer_id", "-created_at"],
                name="orders_customer_created",
            ),
        ),
    ]

If a concurrent build fails, it leaves an invalid index behind. Drop it and try again.

Indexes are not free, though. The lab measured insert speed on a 1 million row table with no indexes, 1 index, and 5 indexes:

Indexes	Inserts/sec	Index size
0	6,524	0 MB
1	5,879	17 MB
5	5,178	131 MB

Five indexes cost about 21% of write speed on this test. These runs were noisy, and the ranges for 0 and 1 index overlapped, so treat the trend as the finding, not the exact percentage.

The cost grows with every index you never use. This query finds the candidates:

SELECT s.relname, s.indexrelname, s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique
  AND NOT i.indisprimary
ORDER BY pg_relation_size(s.indexrelid) DESC;

Before you drop one, remember these counts are per server. An index with no scans on the primary may be busy on a replica.

Step 6: Shard Last, and Mean It

This lab did not test sharding. It would need several machines to say anything honest. So here are two real teams that lived at the top of the ladder.

Case study: Figma

Figma's database team describes a stack that grew almost 100x since 2020.

They climbed in order. In 2020 they ran a single Postgres database on AWS's largest physical instance. By the end of 2022 they had caching, read replicas, and about a dozen vertically partitioned databases, with related tables split into their own groups.

Only then did they hit real limits. Some tables reached several terabytes and billions of rows, and the highest-write tables neared the IOPS limit on RDS.

Horizontal sharding started in late 2022. The first table took roughly nine months and shipped in September 2023. Figma called the work "an order of magnitude more complex" than their earlier scaling, and listed what it gave up: cross-shard transactions, foreign keys, and globally unique indexes.

Case study: OpenAI

OpenAI's January 2026 post describes serving 800 million ChatGPT users on PostgreSQL, with one primary handling writes and nearly 50 read replicas.

They did not shard their PostgreSQL deployment. Their reasons: sharding would mean changing hundreds of endpoints and could take months or years, and the workload is mainly reads.

But they were not doing nothing. They optimized queries, reviewed ORM-generated SQL, used PgBouncer, put a cache in front, and moved shardable, write-heavy workloads to systems such as Azure Cosmos DB.

Note what both stories share. Neither team reached for the biggest tool first, and both moved based on a specific limit they had measured.

Signs you might really need sharding

A write bottleneck on the primary that remains after query and index work
A single table so large that maintenance like vacuum causes reliability problems
Storage or IOPS limits on the largest machine you can buy
Isolating noisy tables into their own database has already been done

Replicas will not help with any of these writes, because every replica has to apply every write too.

And there is a smaller step that people skip. PostgreSQL partitioning splits a big table into pieces on the same server, which helps with time-based data and deleting old rows, with no routing layer at all.

The Ladder

Here is the order, with the question that earns each step:

Step	Question that earns it
1. Measure	What exactly is slow?
2. Do less	Do we need this query at all?
3. Fix queries	Is it reading more rows than it returns?
4. Pool	Are connections the limit?
5. Cache	Do the same reads repeat, and can they be stale?
6. Scale up	Is a healthy query simply short of hardware?
7. Replicas	Are reads the limit, and can they lag?
8. Partition	Is one table too big to manage?
9. Shard	Are writes or data size beyond one primary?

You can stop at any step. Most systems should.

What This Test Does Not Prove

2,600x is not a promise. This query was chosen because it had no index on a big table. It is a best case for indexing. If your query already uses a good index, you will see nothing like this.
The replica test was a clean case. One extra core, no network, and only reads. Real replicas also have to replay writes.
One query is not a workload. Real systems have many query shapes competing for the same resources.
The numbers depend on the machine. This was a small shared cloud machine, and baseline runs ranged from 3.7 to 5.5 requests per second. Trust the size of the gaps, not the decimals.
Cold caches matter. The replica's 304 ms included reading pages into memory for the first time, while the primary's data was already cached.

What To Do on Monday

Turn on pg_stat_statements if it's off, and find your top 10 queries by total time.
Run EXPLAIN (ANALYZE, BUFFERS) on each one. Look for a high Rows Removed by Filter.
Check for N+1 queries. In Django, select_related and prefetch_related can remove whole groups of queries.
Add indexes from real query patterns, using CONCURRENTLY.
Drop indexes nobody uses, after checking every replica.
Before any new infrastructure, write down the number it should change. "Replica will take p95 from X to Y." If nobody can fill in that sentence, the measuring isn't finished.
If you already have replicas, alert on lag and on a replica going missing, and list which reads must stay on the primary.

Common Questions

Should I add an index for every slow query?
No. Start with queries that run often. An index for a report that runs once a month may cost more in write overhead than it saves.

Is high CPU always a reason to scale up?
No. High CPU tells you the database is working hard. The query plan tells you whether that work is necessary. In this test, a missing index was creating almost all of it.

Can a replica ever beat an index?
Yes, when queries are already efficient and you simply have too many of them. That is exactly when replicas earn their cost.

When should I cache instead of optimizing?
When a query is already good but runs constantly on data that rarely changes. A homepage product list is a classic example.

Do managed databases solve this?
They help with backups, replicas and failover. They will not fix a sequential scan on 5 million rows.

Are ORMs the problem?
Not by themselves. But they make it easy to run queries you never see. OpenAI's post specifically calls out reviewing ORM-generated SQL.

Run It Yourself

Run this on a throwaway machine or container with at least 2 cores, and allow about 15 minutes. It creates and deletes its own data in /tmp/scaling-lab.

docker run -it --rm --cpus=2 ubuntu:24.04 bash

# inside the container
apt-get update
apt-get install -y postgresql redis-server python3-psycopg2 python3-redis util-linux

Save the script below as /tmp/scaling_lab.py, then run it as the postgres user:

chmod a+r /tmp/scaling_lab.py
su postgres -s /bin/bash -c \
  'cd /tmp && PATH=/usr/lib/postgresql/16/bin:$PATH python3 /tmp/scaling_lab.py'

The results are saved to lab_results.json. Change one thing at a time: try 20 million rows, a hotter or colder cache pattern, or a real replica on a second machine.

Click to expand the full lab script (scaling_lab.py)

#!/usr/bin/env python3
"""Database scaling lab: test each step of the scaling ladder on one workload.

Stages: measure, read replica, cache, index, index write cost, stale reads.
Needs PostgreSQL 16 with contrib, Redis, psycopg2, redis-py and 2 CPU cores.
Run as a non-root user with the PostgreSQL bin directory on PATH.
Use a throwaway machine. It creates and deletes its own data in BASE.
"""
import json, os, random, re, shutil, statistics as st, subprocess as sp, time
import psycopg2, redis

BASE = "/tmp/scaling-lab"
P, R, RP = 55432, 55433, 56379
ROWS, CUSTOMERS, RUN_S, REPEATS = 5_000_000, 100_000, 30, 3
READ = ("SELECT id, status, amount, created_at FROM orders "
        "WHERE customer_id = %s ORDER BY created_at DESC LIMIT 20")
out = {}

def sh(cmd, core=None, check=True):
    if core is not None:
        cmd = f"taskset -c {core} {cmd}"
    r = sp.run(cmd, shell=True, capture_output=True, text=True)
    if check and r.returncode:
        raise RuntimeError(cmd + "\n" + r.stdout + r.stderr)
    return r.stdout

def conn(port=P):
    c = psycopg2.connect(host=BASE, port=port, dbname="postgres")
    c.autocommit = True
    return c

def q(sql, port=P):
    c = conn(port); cur = c.cursor(); cur.execute(sql)
    rows = cur.fetchall() if cur.description else []
    c.close()
    return rows

def plan(port=P):
    rows = q("EXPLAIN (ANALYZE, BUFFERS) " + READ.replace("%s", "4242"), port)
    return [r[0] for r in rows]

def pgbench(port, script, clients, core, seconds=RUN_S):
    cmd = (f"pgbench -n -h {BASE} -p {port} -f {script} -c {clients} -j 1 "
           f"-T {seconds} postgres")
    return sp.Popen(f"taskset -c {core} {cmd}", shell=True, stdout=sp.PIPE,
                    stderr=sp.STDOUT, text=True)

def parse(text):
    tps = float(re.search(r"tps = ([\d.]+)", text).group(1))
    lat = float(re.search(r"latency average = ([\d.]+) ms", text).group(1))
    return tps, lat

def setup():
    shutil.rmtree(BASE, ignore_errors=True); os.makedirs(BASE)
    sh(f"initdb -D {BASE}/p -A trust")
    with open(f"{BASE}/p/postgresql.conf", "a") as f:
        f.write(f"port={P}\nunix_socket_directories='{BASE}'\nlisten_addresses=''\n"
                "shared_buffers=1GB\nmax_parallel_workers_per_gather=0\n"
                "shared_preload_libraries='pg_stat_statements'\nwal_level=replica\n"
                "max_wal_senders=5\nhot_standby=on\nmax_connections=50\n")
    with open(f"{BASE}/p/pg_hba.conf", "a") as f:
        f.write("local replication all trust\n")
    sh(f"pg_ctl -D {BASE}/p -l {BASE}/p.log -w start", core=0)
    q("CREATE EXTENSION pg_stat_statements")
    q(f"""CREATE TABLE orders(id bigserial PRIMARY KEY, customer_id int NOT NULL,
          status text NOT NULL, amount int NOT NULL,
          created_at timestamptz NOT NULL, note text NOT NULL)""")
    q(f"""INSERT INTO orders(customer_id, status, amount, created_at, note)
          SELECT (random()*{CUSTOMERS-1})::int + 1,
                 (ARRAY['new','paid','shipped','done'])[1+(random()*3)::int],
                 (random()*50000)::int,
                 now() - random() * interval '365 days', repeat('x', 40)
          FROM generate_series(1, {ROWS})""")
    q("ANALYZE orders")
    with open(f"{BASE}/read.sql", "w") as f:
        f.write(f"\\set cid random(1, {CUSTOMERS})\n" + READ.replace("%s", ":cid") + ";\n")
    with open(f"{BASE}/write.sql", "w") as f:
        f.write(f"\\set cid random(1, {CUSTOMERS})\n\\set amt random(1, 50000)\n"
                "INSERT INTO orders_w(customer_id,status,amount,created_at,note) "
                "VALUES (:cid,'new',:amt,now(),repeat('x',40));\n")
    with open(f"{BASE}/load.sql", "w") as f:
        f.write(f"\\set cid random(1, {CUSTOMERS})\n\\set amt random(1, 50000)\n"
                "INSERT INTO orders(customer_id,status,amount,created_at,note) "
                "VALUES (:cid,'new',:amt,now(),repeat('x',40));\n")
    q("SELECT count(*) FROM orders")
    out["table_mb"] = q("SELECT pg_total_relation_size('orders')/1048576")[0][0]

def start_replica():
    sh(f"pg_basebackup -h {BASE} -p {P} -D {BASE}/r -R -X stream -c fast -C -S lab_slot")
    with open(f"{BASE}/r/postgresql.conf", "a") as f:
        f.write(f"port={R}\nmax_connections=50\n")
    sh(f"pg_ctl -D {BASE}/r -l {BASE}/r.log -w start", core=1)
    for _ in range(100):
        if q("SELECT count(*) FROM pg_stat_replication")[0][0]:
            break
        time.sleep(0.2)
    q("SELECT count(*) FROM orders", R)

def stage_baseline():
    results = []
    q("SELECT pg_stat_statements_reset()")
    for _ in range(REPEATS):
        p = pgbench(P, f"{BASE}/read.sql", 8, 1)
        results.append(parse(p.communicate()[0]))
    out["baseline"] = results
    out["top_query"] = q("""SELECT calls, round(mean_exec_time::numeric,1),
        round(total_exec_time::numeric/1000,1), shared_blks_hit+shared_blks_read,
        left(query,70) FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 1""")
    q(READ.replace("%s", "4242"))
    out["plan_noindex"] = plan()

def stage_replica():
    results = []
    for _ in range(REPEATS):
        a = pgbench(P, f"{BASE}/read.sql", 4, 1)
        b = pgbench(R, f"{BASE}/read.sql", 4, 0)
        ra, rb = parse(a.communicate()[0]), parse(b.communicate()[0])
        results.append((ra[0] + rb[0], (ra[1] + rb[1]) / 2))
    out["replica"] = results
    out["plan_replica"] = plan(R)

def stage_cache():
    os.sched_setaffinity(0, {1})
    r = redis.Redis(unix_socket_path=f"{BASE}/redis.sock")
    pg = conn(); cur = pg.cursor()
    def get_orders(cid):
        key = f"orders:{cid}"
        hit = r.get(key)
        if hit is not None:
            return True
        cur.execute(READ, (cid,))
        r.set(key, json.dumps(cur.fetchall(), default=str), ex=300)
        return False
    def run(pick, seconds=60):
        r.flushall()
        times, hits, end = [], 0, time.monotonic() + seconds
        while time.monotonic() &lt; end:
            t = time.perf_counter(); hits += get_orders(pick())
            times.append((time.perf_counter() - t) * 1000)
        times.sort()
        return dict(requests=len(times), hit_rate=round(hits / len(times), 3),
                    mean_ms=round(st.mean(times), 2), p50_ms=round(times[len(times)//2], 2),
                    p95_ms=round(times[int(len(times)*.95)], 2))
    out["cache_uniform"] = run(lambda: random.randint(1, CUSTOMERS))
    def hot(size):
        return lambda: (random.randint(1, size) if random.random() &lt; .9
                        else random.randint(1, CUSTOMERS))
    out["cache_hot_1000"] = run(hot(1000))
    out["cache_hot_100"] = run(hot(100))
    pg.close(); os.sched_setaffinity(0, {0, 1})

def stage_index():
    t = time.monotonic()
    q("CREATE INDEX orders_customer_created ON orders (customer_id, created_at DESC)")
    out["index_build_s"] = round(time.monotonic() - t, 1)
    out["index_mb"] = q("SELECT pg_relation_size('orders_customer_created')/1048576")[0][0]
    q(READ.replace("%s", "4242"))
    out["plan_index"] = plan()
    results = []
    for _ in range(REPEATS):
        p = pgbench(P, f"{BASE}/read.sql", 8, 1)
        results.append(parse(p.communicate()[0]))
    out["indexed"] = results

def stage_writes():
    extra = ["customer_id", "created_at", "status", "amount", "(customer_id, created_at DESC)"]
    res = {}
    for n in (0, 1, 5):
        q("DROP TABLE IF EXISTS orders_w")
        q("CREATE TABLE orders_w (LIKE orders INCLUDING DEFAULTS)")
        q("ALTER TABLE orders_w ALTER id SET DEFAULT nextval('orders_id_seq')")
        q("INSERT INTO orders_w SELECT * FROM orders LIMIT 1000000")
        for i, col in enumerate(extra[:n]):
            q(f"CREATE INDEX w{i} ON orders_w ({col.strip('()')})")
        q("CHECKPOINT")
        tps = []
        for _ in range(REPEATS):
            p = pgbench(P, f"{BASE}/write.sql", 4, 1, 20)
            tps.append(parse(p.communicate()[0])[0])
        res[n] = dict(tps=tps, index_mb=q("SELECT pg_indexes_size('orders_w')/1048576")[0][0])
    out["writes"] = res
    q("DROP TABLE orders_w")

def stale(n=2000, limit_s=35, give_up_s=5):
    wr, rd = conn(P), conn(R)
    w, c = wr.cursor(), rd.cursor()
    miss, lag, gave_up, end = 0, [], 0, time.monotonic() + limit_s
    for _ in range(n):
        if time.monotonic() &gt; end:
            break
        w.execute("INSERT INTO orders(customer_id,status,amount,created_at,note) "
                  "VALUES (1,'new',1,now(),'x') RETURNING id")
        i = w.fetchone()[0]; t = time.perf_counter(); first = True
        while True:
            c.execute("SELECT 1 FROM orders WHERE id = %s", (i,))
            if c.fetchone():
                break
            if first:
                miss += 1; first = False
            if time.perf_counter() - t &gt; give_up_s:
                gave_up += 1
                break
        lag.append((time.perf_counter() - t) * 1000)
    wr.close(); rd.close(); lag.sort()
    return dict(reads=len(lag), stale_first_read=miss, gave_up=gave_up,
                p50_ms=round(lag[len(lag)//2], 3), p99_ms=round(lag[int(len(lag)*.99)], 2),
                max_ms=round(lag[-1], 2))

def stage_stale():
    out["stale_idle"] = stale()
    p = pgbench(P, f"{BASE}/load.sql", 8, 1, 45)
    time.sleep(3)
    out["stale_load"] = stale()
    out["lag_under_load"] = q("""SELECT pg_size_pretty(pg_wal_lsn_diff(
        pg_current_wal_lsn(), replay_lsn)), replay_lag::text
        FROM pg_stat_replication""")
    p.wait()

if __name__ == "__main__":
    print(sh("postgres --version").strip(), flush=True)
    setup()
    sh(f"redis-server --port 0 --unixsocket {BASE}/redis.sock --save '' --daemonize yes --dir {BASE}")
    for name, fn in [("baseline", stage_baseline), ("replica_start", start_replica),
                     ("replica", stage_replica), ("cache", stage_cache),
                     ("index", stage_index), ("writes", stage_writes), ("stale", stage_stale)]:
        t = time.monotonic(); fn()
        print(name, "done in", round(time.monotonic() - t), "s", flush=True)
        json.dump(out, open("lab_results.json", "w"), indent=1, default=str)
    sh(f"pg_ctl -D {BASE}/r -m fast -w stop", check=False)
    sh(f"pg_ctl -D {BASE}/p -m fast -w stop", check=False)
    sh(f"redis-cli -s {BASE}/redis.sock shutdown nosave", check=False)

Back to That Meeting

"We need a read replica."

Maybe you do. In this test, the replica did what it promised: it doubled capacity.

But it also cost a second server, introduced stale reads, and left a 5-million-row scan behind every request. One line of SQL did far more.

Here is the short version:

Measure first. One EXPLAIN showed 4,999,944 wasted rows.
Replicas add capacity. They don't make a bad query good.
Replicas lag. Even at 0.33 ms, 98.65% of immediate reads missed the row.
Caches need repetition. Hit rate ranged from 0.4% to 84.7% on the same code.
Indexes have a bill. Five of them cost about 21% of insert speed here.
Sharding is real work. Teams like Figma did it only after hitting measured limits.

So the next time someone says "we need to scale," ask one question first:

Which number is the bottleneck, and how do we know?

Over to You

What was the most expensive scaling step your team took that one index could have avoided? Tell me in the comments 👇

Test details: PostgreSQL 16.15 and Redis on a 2-core Linux cloud machine. 5 million rows, 8 clients, 3 repeats per step with medians reported. Numbers will vary on other machines. The scaling order in this piece follows Nikki Siapno's article on database scaling in the Level Up Coding System Design Newsletter (October 2026).

If this was useful, follow me here for more stories from the backend trenches, and subscribe to The Backend Tech Lead on Substack for weekly blueprints.

x is 257 Was True on Monday and False on Tuesday

Anas Issath — Tue, 03 Nov 2026 12:06:50 +0000

Why Python's is and == are not the same thing, how one of them lies about numbers, and the single rule that keeps you safe.

I had a check that compared two numbers. Something close to this, simplified:

if parsed_id is expected_id:
    grant_access()

It worked. Every test passed. Then one day, with slightly different data, the same comparison started returning False for two numbers that were obviously equal. Both were 257. I printed them. They were identical. And parsed_id is expected_id was False.

So I changed the test data, and it went back to True. Smaller numbers worked. Bigger numbers did not. The comparison's correctness depended on how large the number was, which is not a sentence that should ever make sense.

The culprit was a single two-letter word I had used where I meant something else: is. In Python, is and == look interchangeable and are not. One asks "are these equal?" The other asks a completely different question, and the answer to that question happens to be unpredictable for numbers.

Here is what is actually checks, why it lied to me, and the one rule that makes this disappear.

The Two Questions Are Not the Same Question

== and is ask different things, and the whole bug lives in the gap between them.

== asks: are these two values equal? Do they represent the same thing? 5 == 5 is True because five equals five. This is almost always the question you actually want.
is asks: are these two the exact same object in memory? Not "equal," but "literally the same object, the same identity."

Two different lists that happen to hold the same items are equal but are not the same object:

a = [1, 2, 3]
b = [1, 2, 3]

a == b      # True  -> same contents
a is b      # False -> two different list objects

a and b are equal. They are not identical. They are two separate lists that look alike. == sees them as equal; is sees them as distinct, because they are distinct objects sitting at different places in memory.

That is the core confusion: is is not a stricter ==. It is a different operator answering a different question.

And for most values you compare, you care about equality, not identity. You want ==.

Why `is` Lied About My Numbers

So why did parsed_id is expected_id ever return True for 257, let alone sometimes?

Because of an optimization most people never learn about. For performance, CPython keeps a cache of small integer objects, roughly -5 through 256, created once at startup and reused everywhere. Every time you use the number 5, you get back the same cached 5 object. So for small numbers, two equal values really are the same object, and is returns True by luck.

Step outside that cached range and the luck runs out. Numbers above 256 are not cached, so two equal large numbers can be two different objects, and is returns False.

Here is the trap in its purest form, which I ran on Python 3.13:

int('256') is int('256')      # True
int('257') is int('257')      # False

Read that again. The exact same code. The only difference is 256 versus 257. One is True, the other is False, purely because 256 is in the small-integer cache and 257 is not.

That is why my check worked for small IDs and broke for large ones. is was never checking equality. It was checking identity, and identity for numbers depends on an internal caching optimization that you are not supposed to rely on and that can change between Python versions and even between how the values were produced. I had built a correctness check on top of an implementation detail.

A note, because it makes the danger worse: with plain literals written in your source (257 is 257), Python's compiler often folds them into one shared constant, so it returns True and looks fine. The failure only appears when the numbers are computed at runtime, as mine were, from parsing. So it can look correct in a quick REPL test with literals and then fail on real, computed data. Exactly the data you did not test with.

Why Python Itself Now Warns You

This mistake is common enough that recent versions of Python will warn you about one form of it. Compare a value to a literal with is and the interpreter says:

SyntaxWarning: "is" with 'str' literal. Did you mean "=="?

The language is telling you, out loud, that is is almost certainly not what you meant when comparing to a literal string or number. If you see that warning, the fix is virtually always to change is to ==. Python is trying to save you from the exact bug I shipped.

The One Place `is` Is Not Only Fine but Correct: `None`

Here is where it flips. There is a specific, important case where is is the right operator and == is subtly wrong: comparing to None.

The official Python style guide, PEP 8, states it directly:

Comparisons to singletons like None should always be done with is or is not, never the equality operators.

Always x is None, never x == None. Why? Two reasons.

First, None is a singleton. There is exactly one None object in the entire program, so "is this the same object as None" is precisely the right question, and is answers it directly and fast.

Second, and more important, == can be overridden. Any object can define its own __eq__ method and decide what it considers equal to. A badly behaved or cleverly designed object can make x == None return True when x is not actually None. I confirmed exactly this:

class Weird:
    def __eq__(self, other):
        return True

w = Weird()

w == None      # True   <- __eq__ hijacked the comparison
w is None      # False  <- correct: w is genuinely not None

w == None is True, which is a lie. w is None is False, which is the truth. is cannot be fooled this way, because identity is not something an object can redefine. That is why checking for None is the one case where you reach for is on purpose.

So the operators split cleanly by use case:

Comparing to...	Use	Why
`None` (and other singletons like `True`/`False` when you truly mean the object)	`is` / `is not`	One object exists; `==` can be overridden and lie
Numbers, strings, lists, any value	`==` / `!=`	You care about equality, not identity

The Rule I Take Away

Compressed to one line:

== asks "are these equal?" is asks "are these the exact same object?" Use == for comparing values. Use is only for None and other singletons. Never use is to compare numbers or strings, because its answer depends on caching and interning you must not rely on.

The deeper lesson, the one that outlasts this specific trap, is that identity and equality are different concepts, and the two-letter word is means identity, not "equals."

English makes is feel like a synonym for equality, which is exactly why people reach for it when they mean ==. But Python's is is a precise, narrow operator about object identity, and for values like numbers that identity is governed by internal optimizations that are not part of the language's promises. Build a comparison on is for a value, and you have built it on sand that shifts between numbers, between runs, and between Python versions.

And the usual sting applies. This hides in testing because small numbers and literal values make is accidentally return True, so it looks correct right up until real, computed, larger data flows through and the identity you were never entitled to quietly stops holding.

How to Catch It Before It Catches You

Keep these four habits:

Use == to compare values, always. Numbers, strings, anything where you care about the value being equal. If the question is "do these represent the same thing," that is ==, every time. Reaching for is on a value is the bug.
Use is only for None and singletons. if x is None, if x is not None. This is both correct and the officially recommended style (PEP 8). The same applies if you genuinely mean the one True or False object, but for None it is essential, because == can be overridden and lie.
Heed the SyntaxWarning. If Python warns "is" with a literal. Did you mean "=="?, the answer is yes, you did. Change it to ==.
Never test identity-based comparisons only with small numbers or literals. is gives false confidence with cached small ints and folded literals. If you must reason about is, test with large, runtime-computed values, where the caching no longer rescues you.

I changed one is to == in that access check, and the comparison started telling the truth for every number, large or small. The two values had been equal the whole time. I had just been asking Python the wrong question, and for small numbers it had been politely giving me the right answer anyway, right up until it stopped.

The 30-Second Takeaway

== and is ask different questions. == asks "are these values equal?" is asks "are these the exact same object in memory?" They are not interchangeable.
Two equal objects can fail an is check: [1,2,3] == [1,2,3] is True, but [1,2,3] is [1,2,3] is False, because they are two distinct objects.
is lies about numbers because of small-integer caching. On Python 3.13: int('256') is int('256') is True (256 is cached), but int('257') is int('257') is False (257 is not). Same code, different answer by size. This is an implementation detail you must never rely on.
It hides in testing: small numbers and source literals make is accidentally return True (caching and compiler constant-folding). It breaks only on large, runtime-computed values.
The one correct use of is: None. PEP 8 says comparisons to singletons like None should always use is or is not. None is a singleton, and == can be overridden to lie (w == None can be True while w is None is False).
The rule: == for comparing values; is only for None and singletons; never is for numbers or strings. And if Python warns "is" with a literal, change it to ==.

Your Turn

Search your code for is and is not and check each one: is the right-hand side None (good) or a number, string, or other value (bug)? Every is comparing a value should be ==. The ones comparing to None are exactly right.

Did is vs == ever bite you in production? Tell me in the comments 👇

If this was useful, follow me here for more stories from the backend trenches, and subscribe to The Backend Tech Lead on Substack for weekly blueprints.

My Page Ran One Query in Development and 5,000 in Production

Anas Issath — Mon, 02 Nov 2026 08:45:48 +0000

The N+1 problem: how a harmless-looking loop quietly fires one database query per row, and the two methods that collapse it back to one.

The page was a simple list. Show every book, with its author's name next to it.

books = Book.objects.all()

{% for book in books %}
  <li>{{ book.title }} by {{ book.author.name }}</li>
{% endfor %}

On my laptop, with twelve books in the database, it loaded instantly. It stayed fast through code review. It shipped.

Then production, with a real catalogue, got slow. Not broken. Slow. The page that rendered in 30 milliseconds locally was taking four seconds live, and the database was lighting up while it did.

I turned on query logging and felt my stomach drop. That one page, the simple list, was running over five thousand database queries to render. One to get the books. And then one more for every single book, to go fetch its author.

I had written what looked like one query. I had actually written one, plus one per row. This is the N+1 problem, and it is probably the single most common performance bug in Django. Here is exactly how the loop betrays you, and the two methods that fix it.

Where the Thousands of Queries Come From

The trap hides in a line that does not look like a query at all.

books = Book.objects.all()      # looks like the only database call

That line fetches the books. One query. So far so good.

The damage happens later, in the template, on this piece:

{{ book.author.name }}

book.author is a foreign key. When you first access it, the author is not in memory yet, so Django quietly runs a query to go get that author from the database. One query, right there, triggered just by reading the attribute.

Now put that inside a loop over every book. For each book, the template reads book.author, and each read fires its own query. One hundred books means one hundred author queries. Five thousand books means five thousand author queries.

So the real cost is:

1 query to fetch the books, plus N queries, one per book, to fetch each author.

That is the "N+1." One query for the parents, N queries for the related objects, and the N is however many rows you have.

The line that triggers it, book.author, does not look like database access. It looks like reading a field on an object you already have. That is exactly why it is so easy to write and so hard to spot.

Why It Is Invisible Until It Is Catastrophic

This bug has the signature of every dangerous one. It scales with your data, and your development database has almost none.

On my laptop I had twelve books. Twelve books means thirteen queries, which run in a couple of milliseconds. Nothing to notice. The page was fast, the tests passed, nobody saw a problem.

Production had thousands of rows. The exact same code now meant thousands of queries, each a separate round trip to the database, and the page crawled.

That is the cruel part: the code did not change between fast and slow. The row count did. N+1 is invisible precisely when you are testing, because you test with a handful of rows, and it only becomes catastrophic when real data arrives. The bug was always there. It just needed enough rows to show its teeth, and you never have enough rows on your laptop.

This is also why it survives review. A reviewer reads book.author.name and sees a field access, not a query. There is nothing visibly wrong with the code. The wrongness is in how it multiplies, and multiplication does not show up in a diff.

The Fix for Foreign Keys: `select_related`

When the related object is reached through a forward foreign key or a one-to-one (each book has exactly one author), the fix is select_related.

books = Book.objects.select_related("author")

This tells Django to fetch the books and their authors together, in a single query, using a SQL JOIN. Instead of fetching books and then running off to get each author one at a time, the database returns each book with its author's columns already attached.

The result:

N+1 queries collapse into one.

Now the template's book.author.name touches data that is already in memory, loaded by that single joined query. No per-row database trip. The page that ran five thousand queries runs one.

The way to read it: select_related does the joining in the database, in one query, and it is for single-valued relationships, where each row points to exactly one related object (ForeignKey, OneToOne). That "one related object per row" is what lets it ride along in a single JOIN.

The Fix for the Many Side: `prefetch_related`

A JOIN works when each book has one author. But what about the other direction, where one object has many related rows? An author with many books, a post with many tags, an order with many line items. You cannot pull "many" into a single joined row cleanly, so Django gives you a different tool: prefetch_related.

authors = Author.objects.prefetch_related("books")

This runs a separate second query to fetch all the related objects at once, then joins them to their parents in Python, in memory. So fetching a list of authors and all their books costs:

2 queries total. One for the authors, one for all their books.

Not one query per author. Two queries for the whole thing, regardless of how many authors you have.

It nests too: prefetch_related("books", "books__publisher") adds one more query for the publishers, and so on: one additional query per relationship level, not per row.

The way to read it: prefetch_related does the joining in Python across a small number of queries, and it is for multi-valued relationships: the many-to-many and reverse-foreign-key cases where a single JOIN would not work.

Which One to Reach For

The choice comes down to one question: how many related objects does each row point to?

Each row points to...	Relationship types	Use	How it works
One related object	Forward `ForeignKey`, `OneToOne`	`select_related`	One query, a SQL JOIN
Many related objects	Many-to-many, reverse `ForeignKey`	`prefetch_related`	A separate query per relationship, joined in Python

A useful shortcut:

If you access the relationship as a single object (book.author), you almost certainly want select_related.
If you access it as a collection you loop over (author.books.all()), you want prefetch_related.

And you can combine them freely on one queryset when a page touches both kinds:

Book.objects.select_related("author").prefetch_related("tags")

One JOIN for the single author, one extra query for the many tags. A page that might have run thousands of queries now runs a small, fixed handful, no matter how many books, authors, or tags exist.

The Rule I Take Away

Compressed to one line:

Accessing a related object inside a loop fires one query per row, turning one intended query into N+1. Fetch the relationships up front: select_related for single-valued relationships (one JOIN), prefetch_related for multi-valued ones (a second query joined in Python).

The deeper lesson, the one that outlasts this specific framework, is that in an ORM, reading an attribute can be a database call, and the ones that fire inside loops are the ones that hurt.

The ORM is wonderful because it hides the database behind ordinary-looking object access. That same hiding is what makes N+1 so easy to write: book.author looks free, costs a query, and you put it in a loop without a second thought.

The fix is always the same shape: load what the loop will need before the loop, in a bounded number of queries, instead of letting the loop pull it one row at a time.

And the usual sting applies. This never shows up when you test, because you test with a handful of rows where N+1 is indistinguishable from one query. It only bites once real data arrives, which is to say in production, at the worst possible time, on the page you were sure was simple.

How to Catch It Before It Catches You

Keep these four habits:

Count your queries, do not eyeball your code. Install the Django Debug Toolbar (or log queries) and watch the query count for each page. N+1 is invisible in the code and glaringly obvious in a query count that scales with your rows. This single tool catches almost every instance.
Suspect every related access inside a loop. book.author, order.customer, item.product inside a for loop, in a template or a serializer, is a query per iteration. The moment you loop and touch a relationship, ask whether you prefetched it.
Match the method to the relationship. Single object per row (book.author) → select_related. Collection per row (author.books.all()) → prefetch_related. Combine both on one queryset when a page needs both.
Test with realistic data volumes. Seed your development or test database with thousands of rows, not twelve. N+1 hides at small scale by definition; the only way to feel it before production does is to give it enough rows to multiply.

I added .select_related("author") to that one queryset, reloaded, and watched the query count fall from over five thousand to one. The page dropped back under a tenth of a second. The code in the template never changed. I had simply loaded the authors before the loop, instead of letting the loop fetch them five thousand times.

The 30-Second Takeaway

Accessing a related object (book.author) inside a loop triggers a separate query per row, because the ORM lazily fetches the relationship on first access. One list query plus N per-row queries is the N+1 problem.
It is invisible in development (a handful of rows means a handful of queries) and catastrophic in production (thousands of rows means thousands of queries). The code never changes; the row count does.
select_related fixes it for single-valued relationships (forward ForeignKey, OneToOne): it does a SQL JOIN and fetches everything in one query. Use it when you access the relation as one object.
prefetch_related fixes it for multi-valued relationships (many-to-many, reverse ForeignKey): it runs a separate query and joins in Python, costing a small fixed number of queries (e.g. 2), not one per row. Use it when you loop over a collection.
Choosing: one related object per row → select_related; many per row → prefetch_related. Combine both on one queryset when needed.
Catch it with a query counter (Django Debug Toolbar), suspect every related access inside a loop, and test with realistic data volumes, because N+1 only reveals itself at scale.

Your Turn

Install the Django Debug Toolbar and open your busiest list page. If the query count climbs with the number of rows on the page, you have an N+1. Add select_related or prefetch_related to the queryset and watch it collapse to a handful.

What's the worst query count you've ever found on a single page? Tell me in the comments 👇

If this was useful, follow me here for more stories from the backend trenches, and subscribe to The Backend Tech Lead on Substack for weekly blueprints.

My API Was Returning Every User's Password Hash. I Didn't Notice for Weeks.

Anas Issath — Sat, 31 Oct 2026 07:33:51 +0000

Why a FastAPI endpoint happily sends back fields you never meant to expose, and the one line that filters them out.

The endpoint looked harmless. Fetch a user, return the user.

@app.get("/users/{user_id}")
async def get_user(user_id: int):
    user = await db.get_user(user_id)
    return user

It worked. It returned the user's name, email, everything the frontend needed. Nobody looked twice.

Weeks later, someone opened the browser network tab while debugging an unrelated issue and went quiet. The JSON response for that endpoint had a field none of us wanted anyone to see.

{
  "id": 42,
  "username": "anna",
  "email": "anna@example.com",
  "password_hash": "$2b$12$Q9....",
  "is_admin": true,
  "internal_notes": "flagged for review"
}

The password hash. The admin flag. An internal notes field. Every column on the user row, shipped straight to the client, on every single request, for weeks.

Nothing had errored. The endpoint did exactly what I told it to. The problem was that I had told it to return "the user," and "the user" turned out to mean all of it.

Here is why FastAPI did this, why it is the default, and the one line that would have stopped it.

What "Return the User" Actually Sent

The mistake is assuming FastAPI only sends back the fields you have in mind. It does not. By default, it serializes whatever object you return, in full.

My db.get_user() returned a database object with every column on it, including password_hash, is_admin, and internal_notes. When I returned that object, FastAPI turned the whole thing into JSON. Every field. It had no way to know some of those fields were secret, because I never told it which fields were allowed out.

The endpoint was not leaking by accident. It was doing precisely what "return this object" means: send the object. All of it.

This is the trap. "Return the user" sounds like it means "return the public view of the user." To FastAPI, it means "serialize this entire object and send it," and the entire object includes the things you would never put in a response on purpose.

The Feature I Had Skipped: `response_model`

FastAPI has a built-in answer to exactly this, and I had simply never used it. You declare a response model: a separate schema describing what the response is allowed to contain. FastAPI then filters the output down to only those fields.

The official docs state the behavior directly:

FastAPI will take care of filtering out all the data that is not declared in the output model.

The docs even use this exact password scenario as the motivating example. They show the danger of reusing one model for input and output:

Now, whenever a browser is creating a user with a password, the API will return the same password in the response.

And then the fix: one model for input (with the password), a different model for output (without it).

from pydantic import BaseModel, EmailStr

class UserIn(BaseModel):
    username: str
    password: str
    email: EmailStr
    full_name: str | None = None

class UserOut(BaseModel):     # note: no password, no is_admin, no internal_notes
    username: str
    email: EmailStr
    full_name: str | None = None

@app.post("/user/", response_model=UserOut)
async def create_user(user: UserIn):
    return user

The docs explain why this is safe even though the function returns the full input object:

even though our path operation function is returning the same input user that contains the password … we declared the response_model to be our model UserOut, that doesn't include the password.

That is the whole mechanism. You return whatever you have. FastAPI validates it against UserOut, keeps only the fields UserOut declares, and drops everything else before it reaches the client. The password hash never leaves the server, because UserOut does not mention it.

Applied to my leaking endpoint, the fix was one decorator argument:

@app.get("/users/{user_id}", response_model=UserOut)
async def get_user(user_id: int):
    user = await db.get_user(user_id)
    return user        # still the full object; FastAPI filters it to UserOut

Same code, same return value. But now the response contained only username, email, and full_name. The hash, the admin flag, and the internal notes were filtered out, every time, automatically.

Why Filtering Is Opt-In, Not Automatic

Here is the part worth sitting with, because it is the root of the danger.

FastAPI does not filter by default. Filtering only happens when you declare a response model (or a response return type). No declaration means no filter, which means the full object goes out.

That design makes sense once you see it: FastAPI cannot guess which of your fields are public. Only you know that email is fine and password_hash is not. So the safe output is something you have to describe, and until you describe it, FastAPI does the literal thing and sends what you returned.

The consequence is that forgetting the response model is not a loud error. It is a silent over-share. The endpoint works, the tests pass, the frontend gets its data plus a pile of extra fields it quietly ignores. Nobody notices the extra fields until someone reads the raw response, because nothing is broken from the client's point of view. It is just handing out more than it should.

That is the signature of this whole class of bug. The failure is not a crash. It is the absence of a restriction you never added.

Two Ways to Declare It, and the One That Bit Me

You can specify the allowed output in two places, and knowing both saves confusion.

On the decorator with response_model=UserOut. This is a parameter of the route decorator (@app.get, @app.post), not of your function.
As the function's return type with -> UserOut. FastAPI reads the annotation and filters to it, and your editor and type checker get the benefit too.

If you use both, the docs are explicit about precedence:

If you declare both a return type and a response_model, the response_model will take priority and be used by FastAPI.

The gotcha that got me: I had typed my endpoint as -> User, where User was my database model, thinking I was being careful with type hints. But User had every field on it, so annotating with it filtered nothing.

The type hint looked responsible and did nothing for safety, because the model I pointed at was the very model with the secrets in it. A response model only protects you if the model you name actually omits the sensitive fields.

A Bonus the Response Model Unlocks: Trimming Unset Fields

Once you are declaring response models, one related option is worth knowing, because it cleans up responses nicely. response_model_exclude_unset=True tells FastAPI to leave out fields that were never actually set, so defaults do not clutter the output:

@app.get("/items/{item_id}", response_model=Item, response_model_exclude_unset=True)
async def read_item(item_id: str):
    return items[item_id]

The docs describe the effect:

those default values won't be included in the response, only the values actually set.

So an item stored as just {"name": "Foo", "price": 50.2} returns exactly those two fields, instead of padding the response with every optional field at its default.

There are siblings for this too, response_model_exclude_defaults and response_model_exclude_none, for when you want to strip defaults or nulls specifically. Minor features, but they make your responses lean once the response model is in place.

The Rule I Take Away

Compressed to one line:

FastAPI serializes whatever you return, in full, unless you declare a response model. Returning a database object with no response model sends every field, secrets included. Declare a separate output schema with response_model= (or a return type) that lists only the fields allowed out, and FastAPI filters the rest away.

The deeper lesson, the one that outlasts this specific framework, is to treat your output shape as something you declare on purpose, never something you let fall out of your internal objects.

Your database model and your API response are two different things that happen to overlap:

The database model is for storage and has everything.
The response is a public contract and should have only what the public may see.

Collapsing the two, returning the storage object directly as the response, is how private fields escape. Keep an explicit output schema and the leak becomes impossible, because the secret fields are simply not in the schema.

And the usual sting applies. This never shows up as a failure in testing, because the client gets its data and works fine. The extra fields ride along silently, and the only way to notice is to read the raw response and ask "why is that in here?" Usually long after it has been going out to everyone.

How to Catch It Before It Catches You

Keep these four habits:

Never return a raw database object from an endpoint without a response model. If you return an ORM or database object and have not declared what is allowed out, you are shipping every column. Declare a response_model that lists only public fields.
Make output models separate from input and database models. One schema for what comes in, a different schema for what goes out, and neither is your storage model. The output schema should physically not contain the secret fields, so there is nothing to leak.
Do not trust a return type that points at your database model. -> User filters to User, and if User has the password on it, you have filtered nothing. The protection comes from the named model omitting the secrets, not from having an annotation.
Read your own raw JSON responses. Open the network tab or curl the endpoint and look at every field in the response. The leak is invisible from the frontend, which ignores extra fields. Reading the raw response is the only reliable way to spot an over-share.

I added one response_model=UserOut to the leaking endpoint, reloaded, and watched the password hash, the admin flag, and the internal notes vanish from the response. The code still returned the full user object. FastAPI had simply, finally, been told what was allowed to leave.

The 30-Second Takeaway

By default, FastAPI serializes whatever you return, in full. Return a database object and every field goes to the client, including password_hash, is_admin, or anything else on the row.
Filtering is opt-in. FastAPI only limits the output when you declare a response model. No response model means no filter, so the whole object is sent. This is a silent over-share, not an error.
The fix: declare a separate output schema and pass it as response_model=UserOut on the decorator (or as a -> UserOut return type). FastAPI filters out everything not declared in the output model.
Keep input, output, and database models separate. The output model should not contain the secret fields at all, so there is nothing to leak. The docs use this exact password example.
A return type pointing at your DB model protects nothing. -> User filters to User; if User holds the password, you have filtered nothing. Safety comes from the model omitting the secrets.
Bonus: response_model_exclude_unset=True (and _exclude_defaults, _exclude_none) trims unset or default fields from responses once a response model is declared.
It is invisible in testing: the frontend ignores extra fields and works fine. Read the raw JSON response to spot what is actually being sent.

Your Turn

Open your API and find every endpoint that returns a database object. For each one, check: is there a response model, and does it omit every sensitive field? Any endpoint returning a raw ORM object with no response model is shipping every column to the client right now.

Have you ever found something in a raw API response that shouldn't have been there? Tell me in the comments 👇

If this was useful, follow me here for more stories from the backend trenches, and subscribe to The Backend Tech Lead on Substack for weekly blueprints.

select_related vs prefetch_related: Stop Memorizing, Start Understanding

Anas Issath — Thu, 29 Oct 2026 07:59:58 +0000

One question tells you which to use every single time, and it's not "which method do I remember?"

These two methods are how Django developers fix the N+1 query problem, the single most common ORM performance bug. They're also the pair people most often memorize wrong, apply backwards, and never quite feel sure about. You look it up, you copy the one that seems right, the error goes away, and you move on still not really knowing why it was that one.

Let's end that. There's a single question that tells you which to reach for, every time, with zero memorization. The answer isn't arbitrary: it falls directly out of how relational databases physically work. Once you see why there are two methods, you'll never confuse them again.

We'll start with the problem they both solve, then the one question, then the two traps that catch even experienced developers.

The Problem They Both Solve (a 60-Second Refresher)

You list 100 books and print each author's name:

for book in Book.objects.all():        # 1 query for the books
    print(book.author.name)            # 1 query PER book to get the author

That's 101 queries: one for the books, then one more every time you touch book.author, because Django didn't load the authors up front and has to run out to the database for each one.

This is the N+1 problem, and both select_related and prefetch_related exist to collapse those hundred extra queries down to one or two. They're the same goal ("load the related data efficiently, up front") achieved two different ways.

The only question is which way. And that's where people flail. So here's the question that settles it.

The One Question: Does This Relationship Point to ONE Thing, or MANY Things?

That's it. That's the whole decision. Forget the method names for a second and just look at the shape of the relationship you're crossing:

Does each object point to exactly ONE related object? → select_related
Does each object point to MANY related objects? → prefetch_related

Map it to Django's field types and it's completely mechanical:

Relationship	Field type	Shape	Use
A book has one author	`ForeignKey`	One	`select_related`
A user has one profile	`OneToOneField`	One	`select_related`
An author has many books	Reverse `ForeignKey`	Many	`prefetch_related`
A post has many tags	`ManyToManyField`	Many	`prefetch_related`

You don't memorize "select_related is for ForeignKey." You ask "one or many?" and the field type answers it for you. ForeignKey and OneToOne point at one thing; reverse-ForeignKey and ManyToMany point at many. One → select. Many → prefetch. Done.

But why? Why does "one vs many" force different methods? Because of how a database actually combines tables, and understanding that is what makes this permanent instead of memorized.

Why "One" Gets a JOIN (`select_related`)

select_related works by adding a SQL JOIN. It fetches the object and its related object together, side by side, in a single query:

books = Book.objects.select_related("author")   # ONE query, with a JOIN
for book in books:
    print(book.author.name)                     # already loaded - no extra query

The SQL is essentially "get each book and glue its author's columns onto the same row." That works beautifully when each book has one author, because gluing one author onto one book row keeps things tidy: 100 books → 100 rows, each carrying its author. One clean query, no duplication.

This is why select_related is restricted to single-valued relationships (ForeignKey, OneToOne). Django's own docs are explicit that it's limited to single-valued relationships, and the reason is the next section. A JOIN is the right tool precisely because there's one thing on the other side to glue on.

Why "Many" Can't Use a JOIN, and Gets a Second Query Instead (`prefetch_related`)

Now picture joining across a many relationship. An author has 50 books. If you JOIN author-to-books, the database can't put 50 books on one author row, so it duplicates the author across 50 rows, one per book.

Do that for 100 authors with 50 books each and your result set explodes into 5,000 rows, most of it the same author data repeated over and over. That's wasteful and slow, and it's exactly why Django refuses to use a JOIN here. The docs describe it as avoiding "the much larger result set" that joining across a many-relationship produces.

So prefetch_related takes a completely different strategy: two separate queries, joined in Python.

authors = Author.objects.prefetch_related("books")   # TWO queries, not a JOIN
for author in authors:
    for book in author.books.all():                  # already loaded - no extra query
        print(book.title)

Here's the mechanism, and it's clever:

Django runs query #1 to get all the authors.
Then it runs query #2: a single query that grabs all the books belonging to all those authors at once (WHERE author_id IN (...)).
Then, in Python memory, Django matches each book to its author.

Two queries total, no matter how many authors or books, and no exploded result set, because there's no JOIN. Each object's data is fetched exactly once.

That's the whole "why":

One relationship → a JOIN is efficient → select_related.
Many relationship → a JOIN explodes → separate query matched in Python → prefetch_related.

The two methods aren't arbitrary API choices you memorize. They're the only two sensible ways to load related data, each matched to the relationship shape that makes it efficient.

The reframing to keep: you're not choosing a method. You're identifying a shape (one or many), and the correct method is forced by how databases handle that shape.

Following Chains, and Combining Both

Real queries cross several relationships, and both methods follow chains with the double-underscore syntax:

# Follow FK chain: book → author → publisher (all "one" hops)
Book.objects.select_related("author__publisher")

# Combine them: each book has ONE author (select) and MANY tags (prefetch)
Book.objects.select_related("author").prefetch_related("tags")

That last line is the real-world pattern. A single query is usually a mix of "one" hops and "many" hops, so you select_related the single-valued ones and prefetch_related the multi-valued ones in the same queryset. Apply the "one or many?" question to each relationship you touch, and chain the right tool for each.

Trap 1: Filtering a Prefetch Silently Re-Queries (the One That Catches Everyone)

This is the trap that bites experienced developers, and it's worth internalizing.

prefetch_related caches the related objects and attaches them to the parents, so author.books.all() reads from the cache, no extra query. But the instant you filter the prefetched relationship, Django ignores the cache and runs a fresh query per parent:

authors = Author.objects.prefetch_related("books")

for author in authors:
    author.books.all()                    # ✅ uses the prefetch cache - no query
    author.books.filter(published=True)   # ❌ IGNORES the cache - new query PER author

Why? Because Django prefetched all the books, but it can't know in advance which filter you'll apply, so a new .filter() can't be served from the "all books" cache. It goes back to the database. You've silently reintroduced the N+1 you were trying to kill.

The fix is the Prefetch object, which lets you customize the query used for prefetching (including filtering it) and store the result under a new attribute name with to_attr:

from django.db.models import Prefetch

authors = Author.objects.prefetch_related(
    Prefetch(
        "books",
        queryset=Book.objects.filter(published=True),
        to_attr="published_books",        # store the filtered result here
    )
)

for author in authors:
    for book in author.published_books:   # ✅ pre-filtered, cached - no extra query
        print(book.title)

Now the filtered set is fetched efficiently up front and cached under published_books.

Bonus: because to_attr stores it in its own attribute, you can prefetch the same relationship multiple times with different filters (e.g. published_books and draft_books) without them clobbering each other.

Any time you catch yourself writing .filter() on a prefetched relationship inside a loop, reach for Prefetch.

Trap 2: `prefetch_related` Can Do ForeignKey, but You Usually Shouldn't

A subtle point: prefetch_related technically works on single-valued relationships too (ForeignKey, OneToOne). But for those, select_related is more efficient, because one JOIN beats two separate queries when the JOIN doesn't cause duplication.

So "it works" isn't "it's right." Keep the rule clean: single-valued → select_related (unless you have a specific reason not to JOIN, like joining would produce an enormous row set for other reasons). The "one or many?" question still gives you the right default every time.

Why Understanding This Beats Memorizing It

Almost every developer eventually memorizes "select_related for ForeignKey, prefetch_related for ManyToMany." That surface rule works, right up until it doesn't.

A reverse ForeignKey (which is "many" but still a ForeignKey) trips the memorizers up, because their rule was about field names, not relationship shapes. The person who memorized sees "ForeignKey," reaches for select_related, and gets an error or a bad query. The person who understands asks "does an author point to one book or many?", sees "many," and reaches for prefetch_related without hesitation.

Same field type, opposite answer, and only the mental model gets it right.

That's the whole reason this article exists. The two methods look like trivia to memorize, but they're actually a direct, physical consequence of how relational databases combine data. A JOIN is efficient for one-to-one gluing and disastrous for one-to-many multiplication, so Django gives you a JOIN-based tool for the first shape and a separate-query tool for the second.

Hold that (one gets a JOIN, many gets a second query, because that's what the database can do efficiently) and you never have to look it up again. You'll just glance at a relationship, see its shape, and know. That's the difference between using the ORM and understanding it.

The 30-Second Takeaway

Both select_related and prefetch_related fix the N+1 query problem: loading related data up front instead of one query per object. The only question is which to use.
The one question: does the relationship point to ONE thing or MANY? One → select_related. Many → prefetch_related. The field type answers it: ForeignKey/OneToOne point to one; reverse-ForeignKey/ManyToMany point to many.
select_related uses a SQL JOIN (single query), efficient for "one" relationships because gluing one related row onto each object stays tidy. It's limited to single-valued relationships for exactly this reason.
prefetch_related uses a separate query matched in Python, because JOINing a "many" relationship would explode the result set (an author repeated across 50 book rows). It runs 2 queries total and matches in memory.
Chain and combine: follow relations with author__publisher, and mix select_related("author").prefetch_related("tags") when a query has both shapes.
Trap 1: calling .filter() on a prefetched relationship ignores the cache and re-queries per object. Use a Prefetch object (with to_attr) to filter efficiently up front.
Trap 2: prefetch_related works on ForeignKeys too, but select_related is more efficient there. Keep single-valued → select_related.
Don't memorize field-name rules (they break on reverse ForeignKeys). Understand the shape: one gets a JOIN, many gets a second query, because that's what the database does efficiently.

Your Turn

Next time you write a queryset, don't reach for a remembered method. Glance at each relationship and ask "one or many?" The right tool falls out on its own, and the N+1 problem stops being something you fix and starts being something you never create.

Has the prefetch-filter trap ever bitten you? Tell me in the comments 👇

If this was useful, follow me here for more stories from the backend trenches, and subscribe to The Backend Tech Lead on Substack for weekly blueprints.

Transaction Isolation Levels, Explained Without the Jargon

Anas Issath — Wed, 28 Oct 2026 09:49:15 +0000

Dirty reads, phantom reads, and the setting that quietly decides how safe your Django data really is.

Every Django developer uses database transactions. Far fewer know that transactions come with a dial: a setting called the isolation level that silently decides how much two simultaneous transactions are allowed to see of each other's half-finished work.

Turn the dial one way and your app is fast but can read data that's wrong or half-baked. Turn it the other way and your data is rock-solid but slower and prone to conflicts. Most people never touch this dial, never learn it exists, and inherit its default without knowing what it protects them from, or doesn't.

This article is the plain-English tour of that dial. We'll cover:

The three main things that can go wrong when transactions overlap (the "anomalies")
The four isolation levels that prevent progressively more of them
What Django defaults to and why
When you'd actually change it

No SQL-standard jargon dumps, just the mental model.

The Core Trade-Off in One Sentence

Here's the whole topic compressed:

Isolation level is the trade-off between concurrency (speed) and consistency (safety) when multiple transactions run at the same time. Higher isolation prevents more data anomalies but reduces performance.

That's it. Everything else is detail.

A database could run every transaction one-at-a-time, perfectly isolated, never interfering, but that would be catastrophically slow. So instead databases let transactions overlap for speed, and the isolation level is how you choose how much overlap-weirdness you're willing to tolerate in exchange for that speed.

Lower isolation = faster, but you might see anomalies.
Higher isolation = safer, but slower and more conflicts.

To choose intelligently, you need to know what the "anomalies" actually are. There are three main ones, and they're easier to understand than their names suggest.

The Three Things That Can Go Wrong

Picture two transactions, A and B, running at the same time. Here's what can go wrong, from worst to subtlest.

1. Dirty read: reading someone's uncommitted work

Transaction A deducts $100 from an account but hasn't committed yet. It might still fail and roll back. Transaction B reads the balance right now and sees the lowered amount. Then A crashes and rolls back: the $100 was never actually deducted. But B already read it and acted on it.

B read data that never truly existed. That's a dirty read: seeing another transaction's uncommitted, possibly-about-to-vanish changes. It's the most dangerous anomaly, because you're acting on phantom data.

2. Non-repeatable read: the same row changes under you

Transaction B reads Maria's balance: $0. A moment later, within the same transaction, B reads it again, and now it's $100, because transaction A committed a change in between.

B asked the exact same question twice and got two different answers. A row B already read got modified by someone else mid-transaction. For logic that reads a value, does some work, then re-reads expecting it to be stable, this is a real hazard.

3. Phantom read: new rows appear

Transaction B runs "count all active orders" and gets 10. It does some work, runs the same count again, and now it's 11, because transaction A inserted a new matching row in between.

No row B read changed. Instead, a new row appeared that matches B's query. The set of rows matching a condition changed mid-transaction. It's the "many rows" cousin of the non-repeatable read.

(There's a fourth, dirtiest-of-all case, dirty write, that all levels prevent, so we'll set it aside.)

Notice these get progressively subtler: reading uncommitted garbage → a value shifting → the row set shifting. The isolation levels are simply a ladder that blocks more of these as you climb.

The Four Isolation Levels: A Ladder

Each level blocks everything the levels below it block, plus one more anomaly. That's the whole structure.

Read Uncommitted (the bottom rung)

Blocks almost nothing. It even permits dirty reads: a transaction can see other transactions' uncommitted changes. It's the fastest and the least safe, and in practice it's rarely used for anything that matters. (PostgreSQL doesn't even truly implement it; it treats it as Read Committed.)

Read Committed (the practical baseline)

You can only read data that has been committed: successfully saved. This entirely prevents dirty reads. You'll never read someone's half-finished, about-to-be-rolled-back work.

But it still permits non-repeatable reads and phantom reads. Read the same row twice in one transaction and it can change, because another transaction committed in between.

This is the default in PostgreSQL, and Django's default, because it's the sweet spot for the vast majority of web apps: no reading of garbage data, minimal locking, high concurrency.

Repeatable Read

Guarantees that if you read a row, reading it again in the same transaction gives the same value. The row is effectively frozen for your transaction's view. This blocks dirty and non-repeatable reads.

In classic theory it still allows phantom reads, though PostgreSQL's implementation is stricter and prevents most of those too. The cost: more locking/versioning overhead and more chance of your transaction hitting a conflict and having to retry.

Serializable (the top rung)

The strongest: transactions behave as if they ran one after another, completely alone, in some serial order. It blocks all three anomalies, including phantoms.

It's the safest and the slowest, and it produces the most serialization failures. The database detects that two transactions can't both commit without violating the "as if serial" guarantee, and it aborts one, which your code must catch and retry. You use this when correctness is non-negotiable and you're prepared to retry.

The ladder in one glance

Isolation level	Dirty read	Non-repeatable read	Phantom read
Read Uncommitted	Possible	Possible	Possible
Read Committed	✅ Blocked	Possible	Possible
Repeatable Read	✅ Blocked	✅ Blocked	Possible*
Serializable	✅ Blocked	✅ Blocked	✅ Blocked

*In classic theory. PostgreSQL's Repeatable Read prevents most phantoms too.

Higher = safer + slower + more retries.

What Django Actually Does

Two things every Django developer should know about how this plays out in practice.

Django defaults to READ COMMITTED, matching PostgreSQL

From the official docs: like PostgreSQL itself, Django defaults to the READ COMMITTED isolation level. So unless you've changed it, every transaction in your app reads only committed data (no dirty reads) but can see non-repeatable and phantom reads. For most apps, that's exactly right.

You change it in the `DATABASES` `OPTIONS`

Per the current docs, use the IsolationLevel enum rather than a raw string:

# settings.py
from django.db.backends.postgresql.psycopg_any import IsolationLevel

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        # ... name, user, etc.
        "OPTIONS": {
            "isolation_level": IsolationLevel.READ_COMMITTED,   # the default
            # or REPEATABLE_READ / SERIALIZABLE for stronger guarantees
        },
    }
}

That sets the default for all transactions on that connection.

If you only need a stronger level for a specific operation (the common case), the clean approach is a second database alias pointed at the same database but with a higher isolation level, and route the sensitive transaction through it. Or use a library like django-pgtransaction, which extends transaction.atomic() with a per-block isolation_level argument and built-in retry handling for the serialization failures that stronger levels cause.

⚠️ MySQL warning (it bites people): MySQL's own default is REPEATABLE READ, but Django works best with and defaults to READ COMMITTED. The docs explicitly warn that with MySQL's repeatable read, data loss is possible: you can see cases where get_or_create() raises an IntegrityError but the object won't appear in a subsequent get(). If you're on MySQL, let Django set READ COMMITTED rather than leaving MySQL's default in place.

When You'd Actually Change the Dial

The honest answer for most apps: leave it at Read Committed. It's the default for good reason. But there are real cases to climb the ladder:

Financial/consistency-critical operations where you read several values, make a decision, and write, and a value shifting underneath you (non-repeatable read) or new rows appearing (phantom) would produce a wrong result. Bumping that transaction to SERIALIZABLE (with retry logic) buys you correctness.
Reports/analytics that must be a consistent snapshot: reading many tables that must all reflect the same instant in time. REPEATABLE READ gives you a stable view for the duration.

And two crucial caveats before you reach for a higher level:

Higher isolation is not free, and it's not a substitute for locking or atomic updates

For a lot of the concurrency bugs people try to solve with isolation levels (like a counter losing increments, or a limit check letting one too many through), the better tools are:

An atomic F() update
A select_for_update() row lock
A database constraint

These are targeted, cheaper, and clearer than cranking the whole transaction to serializable. Isolation level is the blunt, global dial; those are the precise instruments.

Higher levels mean retries are mandatory

Serializable will abort transactions with serialization failures under contention. If you raise the level without wrapping the operation in retry logic, you've traded silent anomalies for loud crashes. That's often the right trade, but only if you actually handle the retries.

Why Understanding This Makes You Better at Django

Isolation levels sit underneath a whole family of "how is this data wrong?" bugs, and most developers never connect the symptom to this cause because the dial is invisible and defaulted.

The developer who understands it can look at a flaky, concurrency-related data bug and reason precisely:

"I'm reading this value twice in one transaction and it's changing. That's a non-repeatable read, which Read Committed allows. I either need Repeatable Read here, or better, a row lock."

The developer who doesn't just sees "sometimes the number is wrong" and starts adding random time.sleeps and prayer.

And the mental model is genuinely simple once the jargon is stripped away:

Databases let transactions overlap for speed.
Overlapping causes three flavors of weirdness: reading uncommitted data, a row changing under you, new rows appearing.
The isolation level is a four-rung ladder where each rung costs performance to block one more flavor.

Django hands you Read Committed by default (no dirty reads, high concurrency) and lets you climb higher for the specific transactions that need it, ideally alongside the sharper tools (F(), select_for_update, constraints) for the specific races those solve better.

Know the ladder, and "the data is mysteriously wrong under load" turns into a diagnosis with a name and a fix.

The 30-Second Takeaway

Isolation level is the dial trading concurrency (speed) against consistency (safety) when transactions overlap. Higher = safer but slower and more retries.
Three anomalies, subtlest-increasing: dirty read (reading uncommitted, maybe-rolled-back data) → non-repeatable read (a row you read changes mid-transaction) → phantom read (new rows matching your query appear mid-transaction).
Four levels, a ladder: Read Uncommitted (blocks nothing) → Read Committed (blocks dirty reads) → Repeatable Read (also blocks non-repeatable reads) → Serializable (also blocks phantoms, "as if run one at a time").
Django defaults to READ COMMITTED (like PostgreSQL): no dirty reads, but non-repeatable and phantom reads are possible. Great for most web apps.
Change it in DATABASES["OPTIONS"]["isolation_level"] using the IsolationLevel enum. For one-off stronger transactions, use a second DB alias or django-pgtransaction (which adds per-block level + retries).
MySQL warning: its default is Repeatable Read, but Django prefers Read Committed. The docs warn Repeatable Read can cause data loss (e.g. get_or_create raising IntegrityError then the object not appearing in get()).
Serializable requires retry logic (it aborts conflicting transactions). And for specific races (lost updates, limit checks), prefer targeted tools (F(), select_for_update(), constraints) over cranking the global isolation dial.

Your Turn

Next time a number is "sometimes wrong" under load, don't reach for a random sleep. Name the anomaly. Is a value changing mid-transaction, or are new rows appearing? The anomaly tells you whether you need a higher isolation level or a sharper tool like a row lock.

Have you ever changed the isolation level in production? Tell me in the comments 👇

If this was useful, follow me here for more stories from the backend trenches, and subscribe to The Backend Tech Lead on Substack for weekly blueprints.

How Django Actually Manages Database Connections

Anas Issath — Mon, 26 Oct 2026 01:46:05 +0000

Per-request, persistent, or pooled: the three modes, the setting everyone misconfigures, and the 2026 feature that changes the default advice.

Here's a question most Django developers can't answer confidently: when your view runs a query, where does the database connection come from, and what happens to it when the request ends?

We write Model.objects.get(...) thousands of times without ever thinking about the connection underneath. And that's fine, until the day your app is under load, connections become the bottleneck, and you're staring at settings named CONN_MAX_AGE and pool with no mental model for which one you need or why they conflict.

Let's build that model. By the end you'll understand:

Exactly how Django opens, holds, and closes database connections
The three modes you can run in
The one setting people constantly misconfigure
The 2026 change (native connection pooling) that shifts the standard advice

This is straight from the official docs, current for Django 6.x.

The Foundation: A Connection Is Expensive, and Django Opens One Lazily

First, why any of this matters. A database connection isn't free to create. Opening one means a network handshake, authentication, and session setup on the database server. On PostgreSQL, each connection is backed by a real server-side process holding memory.

Establishing a connection can take meaningful milliseconds before a single query runs. So how often Django opens connections directly affects your latency and your database's load.

Now, how Django gets one. Per the docs: Django opens a connection to the database when it first makes a database query. Not when the request starts, but lazily, the moment a query actually needs it. A view that never touches the database never opens a connection. Once open, Django keeps that connection and uses it for the rest of the work that needs it.

The real question, the one that defines the three modes, is:

What happens to that connection when the request finishes?

Mode 1: Per-Request Connections (the Historical Default)

By default, Django's answer is blunt: close the connection at the end of every request. Open it when the first query runs, use it for the duration of that request, throw it away when the response is sent. Next request? Open a brand-new one.

This is controlled by a setting called CONN_MAX_AGE, and its default value is 0, which per the docs preserves the historical behavior of closing the database connection at the end of each request.

# The default - a fresh connection per request, closed at the end
DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        # CONN_MAX_AGE defaults to 0 → open, use, close, every request
    }
}

It's simple and safe: no stale connections, no leaks across requests. But it's also wasteful under load. If you're serving hundreds of requests per second, you're paying that connection-setup cost (handshake, auth, session) hundreds of times per second, for connections you use for a few milliseconds and discard.

That repeated open/close is pure overhead. Which leads to the first optimization.

Mode 2: Persistent Connections (`CONN_MAX_AGE`)

The idea: instead of throwing the connection away at the end of each request, keep it open and reuse it for the next request. That's a persistent connection, and per the docs it exists precisely to avoid the overhead of re-establishing a connection to the database in each HTTP request.

You enable it by setting CONN_MAX_AGE to a positive number of seconds: the maximum lifetime of a connection before Django closes and replaces it.

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "CONN_MAX_AGE": 60,              # reuse each connection for up to 60s
        "CONN_HEALTH_CHECKS": True,      # revalidate a reused connection per request
    }
}

Now Django opens a connection, and at the start of each request it checks: has this connection exceeded its max age? If not, reuse it. If yes (or if it's broken), close it and open a fresh one.

You've turned "open a connection per request" into "open a connection per minute," eliminating the vast majority of that setup overhead.

Two things the docs are firm about, and both bite people:

Each thread keeps its own connection. A persistent connection belongs to one worker thread. So your database must allow at least as many simultaneous connections as you have worker threads across all your app processes. Twenty Gunicorn workers with CONN_MAX_AGE set means twenty held-open connections sitting on your database even when idle. Do the math against your database's connection limit (PostgreSQL defaults to 100).
Set CONN_HEALTH_CHECKS = True. A held-open connection can go dead: the database restarted, a proxy killed an idle connection. Health checks revalidate a reused connection once per request, so you don't hand a stale connection to your query and get an error. And if your database kills idle connections after some time, keep CONN_MAX_AGE below that timeout so Django doesn't try to use a connection the server already dropped.

Persistent connections were the standard performance win for years. But they have a limitation that native pooling now addresses, and this is the 2026 part.

Mode 3: Native Connection Pooling (the 2026 Change)

Here's the limitation of persistent connections: they reduce how often you open connections, but they don't share connections intelligently.

Each thread still monopolizes its own connection for the entire request, including the parts of the request that aren't touching the database at all. A request that runs one quick SELECT and then spends 200ms calling an external API keeps its database connection pinned that whole 200ms, doing nothing, unavailable to anyone else.

A connection pool fixes that. A small, shared set of connections is opened once and kept warm. A request borrows one only for the moment it needs it and returns it immediately, so a handful of connections can serve far more concurrent requests than persistent connections could.

Since Django 5.1, this is built in for PostgreSQL (via psycopg 3). No third-party library needed. You enable it in OPTIONS:

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "CONN_MAX_AGE": 0,               # REQUIRED - see the trap below
        "OPTIONS": {
            "pool": {
                "min_size": 4,           # keep this many connections warm
                "max_size": 16,          # cap under load
            },
        },
    }
}

("pool": True accepts sensible defaults if you don't want to tune it.)

Reported latency improvements are real. Teams cite cutting tens of milliseconds off database-touching requests by not paying setup cost per request and not pinning connections during non-database work.

The Trap Everyone Hits: Pooling and Persistent Connections Are Mutually Exclusive

This is the single most important configuration fact in the article, because it crashes apps on startup.

You cannot use native pooling and persistent connections at the same time. If you set "pool" in OPTIONS without also setting CONN_MAX_AGE = 0, Django refuses to start and throws:

ImproperlyConfigured: Pooling doesn't support persistent connections

The reason is clean once you see it. The pool manages connection lifetime itself (via its own max_lifetime / max_idle settings), so Django's persistent-connection lifetime management (CONN_MAX_AGE) would be a second, conflicting manager fighting over the same connections. They're two different strategies for the same job, so you pick exactly one:

Strategy	`CONN_MAX_AGE`	`pool` in `OPTIONS`
Persistent connections	Positive number	Not set
Native pooling	`0`	Set

Set pool and forget to zero out CONN_MAX_AGE, and your app won't boot. It's a five-second fix once you know the rule, and a baffling crash if you don't.

The Other Trap: ASGI Changes the Answer

One more current caveat that's easy to miss. Everything above about persistent connections assumes the classic synchronous (WSGI) model, where one thread handles one request at a time.

Under ASGI (async Django), the docs are explicit: persistent connections should be disabled. The async model handles many requests concurrently in ways that don't map cleanly onto "one thread holds one connection," so CONN_MAX_AGE-style persistence is the wrong tool.

Instead, per the docs, use your database backend's built-in connection pooling (the pool option) or a third-party pooler. So if you're running async, pooling isn't just an option. It's the recommended path, and persistent connections are off the table.

Choosing, in Practice

Putting it together, here's the honest 2026 guidance:

Small app / low traffic / development: leave the default (CONN_MAX_AGE = 0). The per-request overhead is negligible at low volume, and it's the simplest, safest behavior. (Don't bother enabling persistence on the dev server. It spawns a thread per request and gains nothing.)
Moderate traffic, synchronous (WSGI): the classic win is persistent connections: CONN_MAX_AGE to something like 60, plus CONN_HEALTH_CHECKS = True. Easy, effective, well-proven. Just make sure your database's connection limit covers your total worker-thread count.
Higher concurrency, or you want the best latency, on Postgres + psycopg 3 / Django 5.1+: native pooling ("pool" in OPTIONS, CONN_MAX_AGE = 0). It reuses connections and avoids pinning them during non-database work. The strongest option, and now built in.
Async (ASGI): pooling, not persistent connections. The docs say so directly.
Many app servers against a small connection budget (e.g. serverless, or a database with a tight limit): consider an external pooler like PgBouncer in front of everything, because even native pools multiply across processes and can overwhelm a small connection limit.

Why Understanding This Makes You Better at Django

Database connection management is one of those areas that stays completely invisible until it suddenly becomes the thing standing between your app and scaling. Then, if you don't have the model, you're copy-pasting settings from blog posts and hoping.

The developer who understands it reads their traffic and their deployment (WSGI vs ASGI, how many workers, how tight the connection budget) and picks the right mode deliberately:

Per-request for simplicity
Persistent for a quick synchronous win
Pooling for real concurrency

The developer who doesn't either leaves free performance on the table (paying connection setup on every request forever) or pastes a pool config next to a nonzero CONN_MAX_AGE and can't figure out why the app won't boot.

The model itself is simple once the fog clears. A connection is expensive to open. Django opens one lazily on first query. And the only real question is what happens at request's end: throw it away (per-request), keep it for reuse (persistent, CONN_MAX_AGE), or return it to a shared warm pool (pooling, pool in OPTIONS). The hard rules: persistence and pooling are mutually exclusive, and ASGI wants pooling.

Hold that, and connection settings stop being mysterious incantations and become a deliberate choice you make based on how your app actually runs.

The 30-Second Takeaway

A DB connection is expensive to open (handshake, auth, a server-side process on Postgres). Django opens one lazily on the first query of a request. The key question is what happens to it at the request's end.
Mode 1, per-request (default, CONN_MAX_AGE = 0): open, use, close every request. Simple and safe, but pays setup cost on every request. Wasteful under load.
Mode 2, persistent (CONN_MAX_AGE = positive seconds): keep the connection open and reuse it across requests. Add CONN_HEALTH_CHECKS = True to avoid stale connections. Caveat: each thread holds its own connection, so your DB's connection limit must cover your total worker threads.
Mode 3, native pooling (Django 5.1+, "OPTIONS": {"pool": {...}}): a shared warm pool. Requests borrow a connection only while needed, so they aren't pinned during non-DB work (e.g. slow external API calls). Built in for Postgres/psycopg 3, with real latency wins.
The crash-on-startup trap: pooling and persistent connections are mutually exclusive. Set pool and leave CONN_MAX_AGE nonzero and Django raises ImproperlyConfigured: Pooling doesn't support persistent connections. Pooling requires CONN_MAX_AGE = 0.
ASGI (async): disable persistent connections. The docs say use pooling instead.
Rule of thumb:

Your situation	Use
Low traffic / development	Default (`CONN_MAX_AGE = 0`)
Moderate traffic, WSGI	Persistent + health checks
High concurrency / best latency	Native pooling
Async (ASGI)	Native pooling
Tiny connection budget across many servers	PgBouncer in front

Your Turn

Open your settings.py and look at your DATABASES block. If there's no CONN_MAX_AGE and no pool, you're opening a fresh connection on every single request. Depending on your traffic, one of the two upgrades above might be the easiest performance win you'll find all week.

Which mode is your app running in right now? Tell me in the comments 👇

If this was useful, follow me here for more stories from the backend trenches, and subscribe to The Backend Tech Lead on Substack for weekly blueprints.

DEV Community: Anas Issath

Database Transactions Are Lying to You: What Isolation Levels Actually Guarantee

What a Transaction Actually Guarantees

The Core Problem: Two Transactions, One Row

The Four Standard Isolation Levels

1. Read Uncommitted

2. Read Committed

3. Repeatable Read

4. Serializable

Quick Reference: What Each Level Prevents

Common Isolation Problems

Problem 1: Assuming BEGIN/COMMIT alone prevents race conditions

Problem 2: Using a lock when an atomic update would do

Problem 3: Deadlocks from inconsistent lock ordering

Problem 4: Long transactions holding locks under load

Problem 5: Picking Serializable and not handling retries

Choosing a Level for Your Use Case

Checking What's Actually Happening

The Bottom Line

Your Turn

asyncpg vs SQLAlchemy ORM - The 9x Slowdown Nobody Warns You About

The Three Layers and What Each One Costs

Layer 1: The Driver (asyncpg vs psycopg 3)

Layer 2: SQLAlchemy Core (No ORM)

Layer 3: The ORM (SQLAlchemy's Declarative Models)

The Benchmark: What Each Layer Actually Costs

Why the ORM Overhead Is So High With Async

When Each Layer Is the Right Choice

Use raw asyncpg when...

Use SQLAlchemy Core when...

Use the full ORM when...

The Hybrid Pattern I Actually Use

The One Setting That Changes Everything: expire_on_commit

Bottom Line

Over to You

tags: python, fastapi, postgres, performance

The Three Layers and What Each One Costs

Layer 1: The Driver (asyncpg vs psycopg 3)

Layer 2: SQLAlchemy Core (No ORM)

Layer 3: The ORM (SQLAlchemy's Declarative Models)

The Benchmark: What Each Layer Actually Costs

Why the ORM Overhead Is So High With Async

When Each Layer Is the Right Choice

Use raw asyncpg when...

Use SQLAlchemy Core when...

Use the full ORM when...

The Hybrid Pattern I Actually Use

The One Setting That Changes Everything: expire_on_commit

Bottom Line

Over to You

The Life of a Django Request

Stop 1: The Browser and the Web Server

Stop 2: WSGI, the Universal Handshake

Stop 3: Django Wakes Up and Builds the Request Object

Stop 4: The Middleware Onion (Going In)

Stop 5: The URL Resolver Finds Your View

Stop 6: Your View Runs (the Part You Already Know)

Stop 7: Back Out Through the Middleware (in Reverse)

Stop 8: The Response Goes Home

Why Knowing This Makes You Better at Django

The 30-Second Takeaway

Your Turn

I Built a Tiny Django QuerySet to Finally Understand How .filter() Works

Step 1: The Naive Version (and Why It's Wrong)

Step 2: Make It Chainable (Return a New Object, Don't Execute)

Step 3: Make It Lazy (Run Only When Something Asks for Results)

Step 4: Add the Terminal Methods (count, exists, first)

The Whole Thing, ~50 Lines

What You Can Now See That You Couldn't Before

The 30-Second Takeaway

Your Turn

Six Things Django Developers Say That Aren't True in 2026

Myth 1: "Django Doesn't Scale"

Myth 2: "Django Isn't Async, So It's Slow"

Myth 3: "Django Can't Do APIs. Use FastAPI."

Myth 4: "Django Can't Do Microservices. It's a Monolith."

Myth 5: "Batteries-Included Makes Django Bloated and Slow"

Myth 6: "Real Developers Hand-Write SQL. The ORM Is for Beginners."

The Pattern Underneath All Six

The 30-Second Takeaway

Problem 1: Assuming `BEGIN`/`COMMIT` alone prevents race conditions

The One Setting That Changes Everything: `expire_on_commit`

The One Setting That Changes Everything: `expire_on_commit`

Step 4: Add the Terminal Methods (`count`, `exists`, `first`)

Why `is` Lied About My Numbers

The One Place `is` Is Not Only Fine but Correct: `None`

The Fix for Foreign Keys: `select_related`

The Fix for the Many Side: `prefetch_related`

The Feature I Had Skipped: `response_model`

Why "One" Gets a JOIN (`select_related`)

Why "Many" Can't Use a JOIN, and Gets a Second Query Instead (`prefetch_related`)

Trap 2: `prefetch_related` Can Do ForeignKey, but You Usually Shouldn't