Inside the Database Stack

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.

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:

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.

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:

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:

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:

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:

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:

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.

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
2. Django Docs: select_for_update() and QuerySet API
3. PostgreSQL Docs: Using EXPLAIN
4. PostgreSQL Docs: Index Types (B-tree)
5. PostgreSQL Docs: Concurrency Control and MVCC