Django Interview Questions — Hard

• QuerySet builds a tree of compiler nodes (WhereNode/expressions), deferred until get_compiler().as_sql() runs against the vendor backend.
• Leaks interviewers expect: DISTINCT + order_by on joined models forces sorting columns into SELECT breaking distinctness (fix via .order_by() reset or subqueries); aggregates over joins multiply rows; extra() escapes the model entirely.
• Vendor variance: window functions, ON CONFLICT, index hints vary by backend - portability claim has edges. Depth signal: mention sqlcompiler alias generation and how annotate aliases collide.

---

• Readers see snapshot; writers create row versions - long transactions hold vacuum behind.
• ORM hazards: iterating huge querysets inside atomic() extends transaction lifetime → table bloat; fix via iterator chunks OUTSIDE atomic or keyed batch loops each committing.
• Hot updates churn index entries (HOT updates need no index-touching columns changed) - updating indexed status column frequently defeats HOT; design indexes accordingly.
• Serialization failures under REPEATABLE READ/SERIALIZABLE require retry wrappers around atomic blocks.

---

Skeleton:

def transfer(src, dst, amount, idem_key):
    if Transfer.objects.filter(idem_key=idem_key).exists(): return existing
    try:
        with transaction.atomic():
            t = Transfer.objects.create(..., status="pending", idem_key=idem_key)
            accounts locked via select_for_update; balances adjusted
            transaction.on_commit(lambda: queue.enqueue("settle", t.id))
    except IntegrityError: return existing  # concurrent duplicate

Points: unique idem_key arbitrates concurrency; on_commit guarantees enqueue only post-commit (avoid ghost jobs); worker itself idempotent re-checking state before applying external effects.

---

• select_related: single SQL with LEFT JOINs per relation path; alias explosion on very deep chains; nullable handled automatically.
• prefetch_related: executes IN-query per relation binding ids in Python; chunking via queryset.iterator not applicable - memory proportional to fetched set.
• Nested Prefetch objects compose filtered child sets; cannot reference sibling prefetch results within same round-trip (second-level prefetches run sequentially). Edge worth naming: reverse one-to-one uses select_related path efficiently; reverse FK always prefetch family.

---

• Enable RLS policies on tenant tables (USING (tenant_id = current_setting('app.tenant')::uuid)).
• Middleware SETs SET LOCAL app.tenant inside atomic block per request (connection-scoped, pooling caveats with pgbouncer transaction mode → use SET SESSION on dedicated connections).
• Django layer: manager enforces tenant filter too (defense in depth), migrations disable RLS for superuser-owned migration role. Pitfalls: connection reuse leaking settings; bulk updates crossing tenants silently failing under policy.

---