Syed Ahmer ShahSyed Ahmer ShahSyed Ahmer ShahSyed Ahmer ShahSyed Ahmer Shah — home
  1. …
  2. Engineering Logs
  3. The Engineering Logs
  4. Inside the Database Stack
Part 14 of 25

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.

Written by

Syed Ahmer Shah

Software Engineer

Oct 7, 2026Updated Oct 9, 20268 min read1,823 words—reading now▼
Developer in a dark server room surrounded by database technology.
Read this articlevoice
Markdown

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.


Also readThe Edge Latency Lie: Solving Global Consistency
Karobar ERP: Books That Balance for the Corner Shop

#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
  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

Find me across the web.

Same person, every platform

Work & code

  • ASPortfolioahmershah.dev
  • LinkedInin/syedahmershah
  • GitHub@ahmershahdev
  • GitLab@ahmershahdev
  • The Orgorg/syed-ahmer-shah
  • AWSAWS Builder Center@syedahmershah
  • Crunchbasesyed-ahmer-shah
  • LinkedIn · Companycompany/syedahmershah

Writing

  • Dev.tosyedahmershah
  • Medium@syedahmershah
  • Hashnode@syedahmershah
  • Substack@syedahmershah
  • HackerNoonu/syedahmershah
  • CLCoderLegionuser/syedahmershah

Reviews & listings

  • Google Knowledge PanelSyed Ahmer Shah
  • Google Business Profileg.page
  • ClClutch
  • TpTrustpilot
  • DRDesignRush
  • TBTechBehemoths

Social

  • YouTube@ahmershahdev
  • Instagram@ahmershahdev
  • TikTok@ahmershahdev
  • Facebookahmershahdev
  • X@ahmershahdev
#softwareengineering#webdevelopment#programmingblogs#coding#django#sql#python#databaseoptimization
ShareXLinkedInRedditWhatsApp

← Previous in series

RAG Retrieval Strategies on a 50k-Document Dataset: BM25 vs Dense vs Hybrid

Next in series →

HolidayOps: Building a Working Days API for 250 Countries

On this page

  • Part I: The Waiter (Python & Django ORM)
  • Part II: Too Many Trips (Query Performance)
  • Part III: The Pantry (Database Fundamentals)
  • Part IV: The Kitchen's Brain (Query Planning)
  • Part V: Many Diners, One Kitchen (Transactions & Concurrency)
  • Part VI: How to Measure Honestly
  • Part VII: Findings
  • References

The Engineering Logs

  1. 01The Edge Latency Lie: Solving Global Consistency
  2. 02Top 10 AI Tools Every Frontend Developer Should Know (2026 Guide)
  3. 03Change One Number. Take Everything.
  4. 04How Databases Work: From Tables to Query Execution
  5. 05The New Era of Software Engineering
  6. 06The Art of Object-Oriented Programming
  7. 07GleanGrid: Building a Pre-Order Marketplace for Farmers Markets
  8. 08The Last Mango Problem: Race Conditions in a Real Marketplace
  9. 09BookMyMovie: Building a Cinema Ticketing Platform
  10. 10One Seat, Two Buyers: Race Conditions, IDOR and Keys in a Cinema Booking System
  11. 11Zovita+: Building an Online Pharmacy That Adapts to Each Shopper
  12. 12The Discount That Knew You Were Hesitating: Rule-Based Personalisation in an Online Pharmacy
  13. 13RAG Retrieval Strategies on a 50k-Document Dataset: BM25 vs Dense vs Hybrid
  14. 14Inside the Database Stack
  15. 15HolidayOps: Building a Working Days API for 250 Countries
  16. 16The Weekend Is Not a Constant
  17. 17MarketClock: A Trading Calendar That Shows Its Work
  18. 17Cybersecurity Trends: Protecting Data in the Digital Age
  19. 18The Day the Money Moves
  20. 19AIT: Building a University Admissions Portal From Scratch
  21. 20I Invented 14 People to Break My University Website
  22. 21OfflineShelf: Choosing Which Wikipedia Goes Offline
  23. 22A Library That Fits on a USB Stick
  24. 23Karobar ERP: Books That Balance for the Corner Shop
  25. 24The Shop That Ran on a Notebook

Up next

Keep reading.

All 63 articles→
  1. 01

    The New Era of Software Engineering

    AI coding agents are changing software engineering. Here’s what this means for developer, student, coding skills & the future of software development.

    Sep 4, 20269 minThe Engineering LogsFirst on Medium

  2. 02

    How Databases Work: From Tables to Query Execution

    Learn how databases work from fundamentals to optimization. Explore SQL queries, indexing, transactions, & database design with real-world examples.

    Aug 29, 20269 minThe Engineering LogsFirst on Medium

  3. 03

    Top 10 AI Tools Every Frontend Developer Should Know (2026 Guide)

    The 10 AI tools frontend developers are actually using in 2026 — with real pricing, honest tradeoffs, and how they fit into a production workflow.

    Jul 13, 20269 minThe Engineering LogsFirst on DEV

  4. 04

    Cybersecurity Trends: Protecting Data in the Digital Age

    Discover the top cybersecurity trends of 2026, from AI-powered scams to rising breach costs, and simple ways to protect your data.

    Oct 21, 20269 minThe Engineering Logs

  5. 05

    The Art of Object-Oriented Programming

    Learn object-oriented programming through classes, objects, encapsulation, inheritance, polymorphism, composition, and practical design principles.

    Sep 17, 202611 minThe Engineering LogsFirst on Medium

  6. 06

    Change One Number. Take Everything.

    IDOR affects 94% of apps. Learn how changing one URL number exposes millions of records & how to build bulletproof defenses. | Syed Ahmer Shah

    Aug 11, 20269 minThe Engineering LogsFirst on Hashnode

Available for new work · --:--:-- PKT

Have an idea?

sales@ahmershah.dev
Book a call WhatsApp +92 370 4831994

On this site

  • 01About
  • 02Education
  • 03Certificates
  • 04Skills
  • 05Projects
  • 06Experience
  • 07Services
  • 08Testimonials
  • 09Contact

Pages

  • Projects
  • Blog
  • Saved articles
  • RSS
  • Privacy Policy
  • Terms of Service
  • llms.txt

Elsewhere · official profiles

28 links

Professional

Work history, code and credentials.

  • in/syedahmershah
  • company/syedahmershah
  • @ahmershahdev
  • @ahmershahdev
  • The Orgorg/syed-ahmer-shah
  • syed-ahmer-shah
  • syed-ahmer-shah
  • u/syedahmershah
  • @syedahmershah
  • AWS@syedahmershah

Coding & credentials

Problem solving, verified badges.

  • u/syedahmershah
  • syedahmershah
  • users/syedahmershah
  • learner/syedahmershah

Google & business

The official listings.

  • Google Business Profileg.page
  • Syed Ahmer Shah
  • syedahmershah
  • Beaconssyedahmershah
  • syedahmershah

Social (@ahmershahdev)

Build logs, clips, threads.

  • @ahmershahdev
  • @ahmershahdev
  • @ahmershahdev
  • ahmershahdev
  • @ahmershahdev
  • @bluesky.ahmershah.dev
  • ahmershahdev

Design

Interfaces and visuals.

  • syedahmershah
  • syedahmershah

Direct

  • sales@ahmershah.dev

    New projects & quotes

  • support@ahmershah.dev

    Support & help

  • syedahmershahofficial@gmail.com

    Personal

  • +92 370 4831994

    Phone · WhatsApp

  • Hyderabad, Sindh, Pakistan

    Remote-first · Asia/Karachi (UTC+5)

Resume ↗
SYED AHMER SHAH

© 2026 Syed Ahmer Shah. All rights reserved.

PrivacyTermsRSS
ahmershah.dev