---
title: "Inside the Database Stack"
description: "A senior-engineer walkthrough of the Django ORM and PostgreSQL: N+1 fixes, indexes, EXPLAIN plans, transactions, and when raw SQL is truly worth it."
author: "Syed Ahmer Shah"
date: 2026-10-07
url: https://ahmershah.dev/blogs/inside-the-database-stack
tags: ["software-engineering", "web-development", "programming-blogs", "coding", "django", "sql", "python", "database-optimization"]
---

# Inside the Database Stack

_Follow one request from Django's QuerySet to PostgreSQL's disk pages, and learn what really slows your app down, why indexes can hurt, and when raw SQL earns its place._

![Developer in a dark server room surrounded by database technology.](https://ahmershah.dev/blog/inside-the-database-stack/cover-855b9f6b.webp)


Think of a restaurant. Your Django code is the **waiter**, PostgreSQL is the **kitchen**, and the disk is the **pantry**. When dinner is slow, people blame the waiter's handwriting (the ORM). Far more often the waiter is making 200 separate trips to the kitchen, or the cook is checking every shelf in the pantry because nobody labeled them.

This article follows one order from the waiter's pad to the pantry shelf and back, and ends with what actually makes databases slow.

* * *

### Part I: The Waiter (Python & Django ORM)

Django open-sourced its ORM in 2005. Its core idea is the **QuerySet**, a _description_ of the data you want rather than the data itself.

```python
orders = Order.objects.filter(status="paid")   # no SQL has run
orders = orders.filter(total__gt=100)          # still nothing
print(orders.query)                            # shows the SQL it *would* run
```

This is **lazy evaluation**. The waiter writes the order on the pad but doesn't walk to the kitchen until you actually need food: when you loop over the results, call `list()`, slice with a step, or ask for `len()`. Each `.filter()` returns a _new_ QuerySet, and Django compiles the final chain into a single SQL statement only at the last moment.

That laziness is a benefit. You can build queries across functions without touching the database. It is also a trap, because a harmless-looking attribute access inside a loop can silently send the waiter running.

**ORM vs raw SQL.** The ORM buys you safety (parameterized queries that block SQL injection), portability, and readable code. It costs you some CPU: building the query objects, compiling SQL, and turning every row into a full Python model instance. For a few hundred rows, this overhead is invisible. For a hundred thousand rows, `.values()`, `.only()`, or `.iterator()` can shrink it considerably.

* * *

### Part II: Too Many Trips (Query Performance)

The classic failure is the **N+1 query problem**:

```python
for order in Order.objects.all():          # 1 query
    print(order.customer.name)             # +1 query per order
```

Show 500 orders and you've sent 501 queries. It is like a waiter walking to the kitchen 500 times for 500 glasses of water. Each trip is fast, but the _trips_ add up, especially when the database sits across a network.

Django has two fixes:

-   **`select_related("customer")`** performs a SQL `JOIN` and fetches the related row in the same query. Use it for single-valued relations (foreign keys, one-to-one).
-   **`prefetch_related("items")`** runs a _second_ query and stitches the results together in Python. Use it for many-valued relations, where a join would duplicate parent rows.

```python
Order.objects.select_related("customer").prefetch_related("items")
```

Two metrics matter here, and people often confuse them. **Query count** is how many trips you made. **Query latency** is how long each one took. A page with 400 fast queries and a page with 3 slow ones need _different_ cures. Tools like `django-debug-toolbar` or `CaptureQueriesContext` show both.

Beyond N+1, the usual bottlenecks are fetching columns you never use, calling `len(qs)` when `qs.count()` or `qs.exists()` would do, saving rows one at a time instead of `bulk_create()`, and filtering in Python what the database could filter itself.

* * *

### Part III: The Pantry (Database Fundamentals)

PostgreSQL is a **process-per-connection** system. Each client gets its own backend process, and all of them share memory buffers and a write-ahead log (**WAL**), which records changes before they hit the main files so a crash can't lose them.

Rows live in a **table** stored as fixed-size **pages**, 8 KB each by default. Imagine a warehouse of unlabeled boxes. Finding "all orders from customer 42" means opening every box. That is a **sequential scan**, and it is the default when nothing smarter is available.

An **index** is the label system. The standard type is the **B-tree**, a balanced, sorted tree dating back to 1970. Like a phone book, it lets Postgres jump to the right place in a handful of steps instead of reading everything.

A **composite index** covers several columns, and _order matters_:

```sql
CREATE INDEX idx_orders_cust_date ON orders (customer_id, created_at);
```

This helps queries filtering on `customer_id`, or on `customer_id` plus `created_at`. It barely helps a query filtering on `created_at` alone, just as a phone book sorted by surname is useless for finding everyone named "Ahmed."

The key idea is **selectivity**: how well a value narrows the search. An index on `customer_id` (many distinct values) is sharp. An index on a boolean `is_active` where 98% of rows are `true` is nearly worthless, because the database would still read most of the table.

**Indexes hurt, too.** Every insert, update, or delete must also update every index. They consume disk and memory, and they slow writes. An index is a trade, not a gift.

* * *

### Part IV: The Kitchen's Brain (Query Planning)

When SQL arrives, PostgreSQL's **optimizer** doesn't just run it. It considers many possible strategies, estimates the **cost** of each using table statistics, and picks the cheapest. Think of a GPS comparing routes.

You can ask to see the route:

```sql
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
```

This shows the _plan_ and the _estimated_ costs without running anything. Add `ANALYZE` and Postgres actually executes the query and reports real times and row counts:

```sql
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42;
```

> **Careful:** `EXPLAIN ANALYZE` really runs the statement. For an `UPDATE` or `DELETE`, wrap it in a transaction and `ROLLBACK`.

The plan names the access method:

-   **Sequential scan:** read the whole table. Fine for small tables, or when you need most rows anyway.
-   **Index scan:** walk the index, then fetch each matching row. Great for a few rows.
-   **Bitmap scan:** collect matching locations from the index, sort them, then read the pages in order. This is the middle path for "more than a few, fewer than most."

For joins, three algorithms compete: **nested loop** (for each row on the left, look up the right, good for small inputs), **hash join** (build a lookup table in memory, good for large unsorted inputs), and **merge join** (zip two sorted lists together).

The most useful habit is comparing the planner's _estimated_ rows with the _actual_ rows. A big gap usually means stale statistics, and `ANALYZE table_name;` often fixes it. The optimizer is only as good as its guesses.

* * *

### Part V: Many Diners, One Kitchen (Transactions & Concurrency)

A **transaction** bundles steps into one all-or-nothing unit. Databases promise **ACID**, a term formalized in 1983:

-   **Atomicity:** all steps happen, or none do.
-   **Consistency:** rules (constraints) always hold.
-   **Isolation:** concurrent transactions don't trample each other.
-   **Durability:** once committed, it survives a crash.

PostgreSQL delivers isolation through **MVCC** (multi-version concurrency control). Instead of making readers wait for writers, it keeps multiple versions of a row. Readers see a consistent snapshot while writers create new versions. Readers don't block writers, and writers don't block readers. The price is cleanup: old versions pile up until `VACUUM` removes them.

Postgres defaults to **Read Committed** isolation, where each statement sees data committed before it began. **Repeatable Read** and **Serializable** are stricter, and Serializable can abort transactions with errors you must retry.

Snapshots don't stop every conflict. Take a classic **race condition**:

```python
acct = Account.objects.get(pk=1)
acct.balance -= 50      # two requests both read 100, both write 50
acct.save()             # one withdrawal vanishes
```

The fix is to lock the row:

```python
from django.db import transaction

with transaction.atomic():
    acct = Account.objects.select_for_update().get(pk=1)
    acct.balance -= 50
    acct.save()
```

`select_for_update()` takes a **row lock**, so the second request waits its turn. For simple arithmetic, an `F("balance") - 50` expression pushes the math into the database itself and is even safer.

Locks bring the risk of **deadlocks**: transaction A holds row 1 and wants row 2, while B holds row 2 and wants row 1. It is two people in a narrow hallway, each waiting for the other to step aside. Postgres detects this (after `deadlock_timeout`, 1 second by default) and kills one transaction. The cure is to **lock rows in a consistent order** everywhere.

* * *

### Part VI: How to Measure Honestly

I'm deliberately not printing benchmark numbers here. Figures from my machine would be meaningless on yours, and invented ones would be worse. What matters is the method:

1.  **Fix the environment.** Same hardware, same Postgres version, same config, warm cache _and_ cold cache runs.
2.  **Scale the dataset.** Test at 1K, 100K, and 10M rows. Many problems only appear at scale, and a missing index looks harmless on a small table.
3.  **Measure several things:** latency (report p50 and p95, not just the average), throughput (queries per second under concurrency), memory (`tracemalloc`), and **query count**.
4.  **Compare like with like:** ORM vs equivalent hand-written SQL, and indexed vs unindexed, changing _one variable at a time_.

```python
import time
from django.db import connection
from django.test.utils import CaptureQueriesContext

with CaptureQueriesContext(connection) as ctx:
    start = time.perf_counter()
    list(Order.objects.select_related("customer")[:1000])
    elapsed = time.perf_counter() - start

print(len(ctx), "queries,", round(elapsed * 1000, 1), "ms")
```

Run it many times, discard the first (cold) run, and keep the distribution.

* * *

### Part VII: Findings

**What actually causes slowness.** In most web applications, the usual suspects are, in rough order of how often they appear: too many queries (N+1), missing or unusable indexes on filtered columns, fetching far more rows or columns than needed, and long-held locks. Rarely is the culprit that Python is "too slow" at building SQL.

**Where the ORM is expensive.** Materializing huge result sets into model instances, hidden per-row queries, and ORM-generated queries nobody has ever inspected.

**Where ORM optimizations matter.** `select_related`, `prefetch_related`, `only()`/`defer()`, `values()`, `bulk_create()`, `exists()` and `iterator()`. These change the _shape_ of the work, and that is where the big wins live.

**Where indexes matter.** On columns used in `WHERE`, `JOIN`, and `ORDER BY`, in large tables, with high selectivity. Always confirm with `EXPLAIN` that the index is actually used.

**When raw SQL is justified.** Complex reporting, window functions or CTEs the ORM expresses awkwardly, or a measured hot path where the ORM's generated query is demonstrably poor. Use parameterized queries, never string formatting.

**The trade-offs.** Readability and safety against fine control. Fast reads against slower writes (indexes). Strict isolation against throughput.

**Myths worth retiring:**

-   _"The ORM is slow."_ Usually the _queries_ are slow, not the ORM.
-   _"Raw SQL is always faster."_ Not if it has the same plan and the same number of round trips.
-   _"More indexes, more speed."_ Each one taxes every write.
-   _"An index will always be used."_ The planner may rightly ignore it.
-   _"`select_related` everywhere."_ Joining tables you never read just makes the kitchen work harder.

> **The one-sentence takeaway:** measure first, count your queries, read your plans, and only then optimize.

* * *

### References

1.  [Django Docs: Database access optimization](https://docs.djangoproject.com/en/stable/topics/db/optimization/)
2.  [Django Docs: `select_for_update()` and QuerySet API](https://docs.djangoproject.com/en/stable/ref/models/querysets/#select-for-update)
3.  [PostgreSQL Docs: Using EXPLAIN](https://www.postgresql.org/docs/current/using-explain.html)
4.  [PostgreSQL Docs: Index Types (B-tree)](https://www.postgresql.org/docs/current/indexes-types.html)
5.  [PostgreSQL Docs: Concurrency Control and MVCC](https://www.postgresql.org/docs/current/mvcc-intro.html)


---

*Originally published at https://ahmershah.dev/blogs/inside-the-database-stack — © Syed Ahmer Shah*