Querysets, N+1 and transactions

Explain queryset laziness and caching.

A queryset builds SQL but executes nothing until it is evaluated: iteration, len(), list(), bool(), slicing with a step, repr(). The result is then cached on that queryset object.

qs = Order.objects.filter(status="paid")   # no query yet
list(qs)                                   # query runs, results cached
list(qs)                                   # cache hit — no second query
qs.count()                                 # a NEW query (COUNT), even though results are cached
Order.objects.filter(status="paid")[0]     # LIMIT 1 — different queryset, new query

Consequences: if qs: then for o in qs: is one query (good), but qs.count() followed by iteration is two. And re-filtering returns a new queryset, so a loop that re-evaluates a fresh queryset each iteration is an N+1 in disguise. .exists() is the right way to test for presence (it emits SELECT 1 … LIMIT 1), and .count() beats len(qs) only when you do not also need the rows.

select_related vs prefetch_related.

select_related does a SQL JOIN, for forward FK and OneToOne — one query, denormalised rows. prefetch_related runs a second query and joins in Python, which is what you need for reverse FK and ManyToMany, where a join would multiply rows.

# N+1: 1 + N queries
for order in Order.objects.all():
    print(order.customer.name)

# 1 query
Order.objects.select_related("customer", "customer__region")

# 2 queries total, regardless of how many orders
Order.objects.prefetch_related("items")

# combine, and control the inner query
Order.objects.select_related("customer").prefetch_related(
    Prefetch("items",
             queryset=OrderItem.objects.select_related("product").filter(qty__gt=0),
             to_attr="active_items")
)

The Prefetch object is the senior-level detail: without it you cannot filter or optimise the related query, and calling .filter() on the related manager inside the loop silently discards the prefetch and re-queries.

How do you find the N+1 in the first place?

• django-debug-toolbar — the SQL panel shows duplicate queries and their stack traces.
• django.db.connection.queries (with DEBUG=True) or CaptureQueriesContext in a test.
• assertNumQueries(5) in tests around important views — this is what stops regressions.
• nplusone or django-zen-queries to fail loudly on lazy loads.
• APM (Sentry/Datadog) span counts per transaction in production.

only, defer, values, values_list — when does each help?

only("id", "name") fetches those columns (touching another triggers a per-row query — a new N+1 if you get it wrong); defer() is the inverse. values()/values_list() return dicts/tuples and skip model instantiation entirely, which is a large win for big read-only result sets. iterator(chunk_size=2000) streams with a server-side cursor instead of loading everything into memory (but disables the queryset cache and, before Django 4.1, prefetch_related).

emails = User.objects.filter(active=True).values_list("email", flat=True)   # fast, lean
for row in Event.objects.iterator(chunk_size=5000):                          # bounded memory
    process(row)

What are F(), Q() and expressions for?

F() references a column server-side — avoiding a read-modify-write race and a round trip:

# BUG: two concurrent requests both read 10 and write 11
p = Product.objects.get(pk=1); p.stock -= 1; p.save()

# CORRECT: UPDATE product SET stock = stock - 1 WHERE id = 1 — atomic in the database
Product.objects.filter(pk=1, stock__gt=0).update(stock=F("stock") - 1)

Q() builds composable boolean logic: Q(a=1) | (Q(b=2) & ~Q(c=3)). And annotations push computation into SQL:

Author.objects.annotate(
    n=Count("book", filter=Q(book__published=True)),      # conditional aggregate
    latest=Max("book__published_at"),
).filter(n__gte=3).order_by("-n")

Trap: annotating with multiple Counts across different joins multiplies rows and inflates counts — use distinct=True or subqueries (Subquery/OuterRef) instead.

How do bulk operations work, and what do they skip?

Model.objects.bulk_create(objs, batch_size=1000, ignore_conflicts=True)
Model.objects.bulk_update(objs, ["status", "updated_at"], batch_size=1000)
Model.objects.filter(...).update(status="done")      # one UPDATE, no instances loaded

All of these bypass save(), signals, and auto_now. That is the whole point (speed) and the whole danger (your post_save audit log silently stops firing). update() also does not call full_clean(). State this trade-off explicitly — it is exactly what the question is testing.

Explain transactions in Django: atomic, autocommit, and on_commit.

Django runs in autocommit mode; transaction.atomic() opens a transaction (or a savepoint if nested).

with transaction.atomic():
    order = Order.objects.create(...)
    Inventory.objects.filter(sku=sku).update(qty=F("qty") - 1)
    transaction.on_commit(lambda: send_receipt.delay(order.id))   # runs only after COMMIT

Key points:

• An exception escaping the block rolls back. Catching an exception inside atomic after a DB error leaves the transaction broken (TransactionManagementError) — wrap the risky part in its own inner atomic.
• on_commit is the fix for "the Celery task ran before the row existed".
• ATOMIC_REQUESTS = True wraps every request in a transaction — safe but it holds connections and locks for the whole view, including slow third-party calls. Prefer explicit atomic around the smallest write section.
• Keep transactions short: never do network I/O inside one.

How do you avoid lost updates and deadlocks?

Pessimistic: select_for_update() takes row locks (FOR UPDATE), optionally nowait=True or skip_locked=True — the latter is how you build a queue over a table. It requires being inside atomic.

Optimistic: keep a version column and update conditionally, retrying on zero rows affected:

updated = Doc.objects.filter(pk=pk, version=v).update(body=body, version=v + 1)
if not updated:
    raise ConflictError("modified by someone else")

Deadlocks come from inconsistent lock ordering — always lock rows in a deterministic order (e.g. sorted by primary key), keep transactions short, and retry on the database's deadlock error, which is expected under concurrency rather than a bug.

What makes a migration safe on a large table?

Postgres specifics that matter in interviews:

• ADD COLUMN with a nullable default is instant (11+ handles non-null constant defaults too), but adding a column with a volatile default rewrites the table.
• Adding an index locks writes unless you use CREATE INDEX CONCURRENTLY — Django's AddIndexConcurrently (with atomic = False on the migration).
• Adding a NOT NULL constraint scans the table; do it in stages (add nullable → backfill in batches → add a NOT VALID check → VALIDATE CONSTRAINT).
• Renaming or dropping a column breaks the currently-running old code during a rolling deploy. Use expand/contract: add the new column, write to both, migrate reads, then drop in a later release.
• Data migrations should be idempotent, batched, and never import models directly — use apps.get_model() so they run against the historical schema.

How do you debug a slow ORM query?

Print the SQL (str(qs.query) or qs.explain(analyze=True)), then read the plan. Look for sequential scans on large tables (missing index), nested loops with high row counts (bad join order or stale statistics), sorts spilling to disk (work_mem), and row-estimate errors of 100x (run ANALYZE). Common ORM-specific causes: an unindexed filter or ordering column, __icontains (which cannot use a b-tree index — use trigram or full-text search), a huge IN list, OFFSET deep paging, and count() on a large filtered table (consider an approximate count or a maintained counter). If the ORM cannot express the efficient query, raw() or a CTE via .extra/RawSQL is a legitimate answer — say that rather than pretending the ORM is always enough.