Recomputing loyalty points for two million Django customers in a Python loop takes hours; how would you move it into the database, and why does annotate(Sum(...)).update() fail?

seniorshould knowfreq 40%

answer

the loop is N+1 reads plus N writes
UPDATE cannot join or aggregate
correlate the sum per customer
no orders means NULL, not zero
batch by primary-key ranges

basics

~10 s

Compute each customer's total in a correlated Subquery with OuterRef, wrap it in Coalesce for customers without orders, and pass it to update(); annotate(total=Sum('orders__amount')).update(points=F('total')) raises FieldError because Django's UPDATE cannot reference joins or aggregates.

solid answer

~40 s

The loop reads every customer, sums their orders with a query per customer and saves each one: two million round trips twice over. The database can do it in one statement, but the obvious Customer.objects.annotate(total=Sum("orders__amount")).update(loyalty_points=F("total")) raises FieldError, because an update may only reference the updated table's own columns, not joined or aggregated ones. The working form is a correlated subquery: Order.objects.filter(customer=OuterRef("pk"), status="paid").order_by().values("customer").annotate(total=Sum("amount")).values("total"), passed as update(loyalty_points=Coalesce(Subquery(totals), 0, output_field=...)). Coalesce matters: a customer with no paid orders gets NULL from the subquery. In production I run it in primary-key batches so no single statement locks every row, set updated_at explicitly, and remember that save() and signals are skipped.

The loop reads every customer, sums their orders with a query per customer and saves each one: two million round trips twice over. The database can do it in one statement, but the obvious `Customer.objects.annotate(total=Sum("orders__amount")).update(loyalty_points=F("total"))` raises `FieldError`, because an update may only reference the updated table's own columns, not joined or aggregated ones. The working form is a correlated subquery: `Order.objects.filter(customer=OuterRef("pk"), status="paid").order_by().values("customer").annotate(total=Sum("amount")).values("total")`, passed as `update(loyalty_points=Coalesce(Subquery(totals), 0, output_field=...))`. `Coalesce` matters: a customer with no paid orders gets NULL from the subquery. In production I run it in primary-key batches so no single statement locks every row, set `updated_at` explicitly, and remember that `save()` and signals are skipped.

code

python · 29 lines

python

from datetime import timedelta

from django.db import models
from django.db.models import Max, OuterRef, Subquery, Sum
from django.db.models.functions import Coalesce, Now
from django.utils import timezone

from shop.models import Customer, Order


def recompute_loyalty_points(step=50_000):
    cutoff = timezone.now() - timedelta(days=365)
    totals = (
        Order.objects.filter(
            customer=OuterRef("pk"), status="paid", placed_at__gte=cutoff
        )
        .order_by()
        .values("customer")
        .annotate(total=Sum("amount"))
        .values("total")
    )
    new_points = Coalesce(
        Subquery(totals), 0, output_field=models.PositiveIntegerField()
    )
    last_pk = Customer.objects.aggregate(m=Max("pk"))["m"] or 0
    for start in range(1, last_pk + 1, step):
        Customer.objects.filter(pk__gte=start, pk__lt=start + step).update(
            loyalty_points=new_points, updated_at=Now()
        )

go deeper

for a junior

Recall that per-row loops are slow because of round trips, and that the database can compute sums itself.

for a middle

Explain why update() cannot take a joined aggregate and how Subquery with OuterRef, values() and annotate() builds a per-row scalar.

for a senior

Run the recompute safely: primary-key batches, Coalesce for missing groups, explicit timestamps, cache invalidation and an index that makes the correlated subquery cheap.

for a principal

Decide which derived values the team stores and recomputes in bulk versus computes on read, and who owns the skipped model hooks.

Why the loop is slow

The job: after a rule change, every customer's loyalty_points must equal the sum of their paid orders from the last twelve months. The first version is usually Python:

python

for customer in Customer.objects.all():
    total = customer.orders.filter(status="paid", placed_at__gte=cutoff).aggregate(
        t=Sum("amount")
    )["t"] or 0
    customer.loyalty_points = total
    customer.save()

For two million customers that is one SELECT for the customers, two million aggregate queries and two million UPDATEs. The arithmetic is trivial; the round trips are the cost.

The obvious fix, and why it fails

It is tempting to annotate the sum and feed it straight into update():

python

Customer.objects.annotate(total=Sum("orders__amount")).update(loyalty_points=F("total"))

This raises FieldError. Django compiles update() into a plain UPDATE customer SET ... and resolves the new values with joins disallowed; an aggregate over orders needs a join and a GROUP BY, which an UPDATE of this shape cannot express. Depending on the query, the message complains about joined field references or about aggregate functions not being allowed. Filters may use relations; values may not.

The working version: a correlated subquery

A correlated subquery is a sub-SELECT evaluated per outer row, referring to that row through OuterRef. Django's recipe for an aggregate inside a Subquery has a fixed shape:

filter(customer=OuterRef("pk"), ...) ties the inner rows to the outer customer.
order_by() clears any ordering on the inner QuerySet. An explicit ordering (for example one a custom manager applies) would add its columns to the GROUP BY and split a customer into several groups; a model's Meta.ordering has not affected GROUP BY since Django 3.1, but the documented recipe clears it anyway.
values("customer") groups by customer.
annotate(total=Sum("amount")) aggregates within that group.
values("total") leaves exactly one column, as a scalar subquery must.

Then pass it to update():

python

totals = (
    Order.objects.filter(customer=OuterRef("pk"), status="paid", placed_at__gte=cutoff)
    .order_by()
    .values("customer")
    .annotate(total=Sum("amount"))
    .values("total")
)
Customer.objects.update(
    loyalty_points=Coalesce(Subquery(totals), 0, output_field=models.PositiveIntegerField()),
    updated_at=Now(),
)

The SQL is roughly UPDATE customer SET loyalty_points = COALESCE((SELECT SUM(amount) FROM order WHERE customer_id = customer.id AND ... GROUP BY customer_id), 0), updated_at = NOW().

Coalesce is not optional. A customer with no qualifying orders has no group, so the subquery returns NULL. Sum(..., default=0) does not help here, because there is no group row for it to fill in.
output_field keeps the expression's type unambiguous when mixing the subquery with a literal.

Running it safely in production

One statement touching two million rows is fast but heavy: it holds row locks on every customer until it finishes, and a long transaction competes with checkout traffic. Common practice:

Batch by primary key: loop over ranges such as filter(pk__gte=start, pk__lt=start + 50_000).update(...), each its own short statement.
Set bypassed fields yourself: update() does not call save(), send pre_save/post_save or apply auto_now, hence updated_at=Now().
Invalidate caches that hold per-customer points, since no signal will tell them.
Check the plan of the subquery once: it runs per customer, so an index on order(customer_id, status, placed_at) decides whether each evaluation is cheap.

Verifying the result

A set-based rewrite should be proven equal to the loop it replaces before it runs on production data:

Run both versions against the same copy of the data and compare Customer.objects.values_list("pk", "loyalty_points") from each; any difference is usually the NULL case or an order status the loop treated differently.
Spot-check edge customers: none of their orders qualify, exactly one order on the cutoff boundary, very large totals near the column's limit.
Read the emitted SQL once (connection.queries under DEBUG) to confirm it is one UPDATE per batch with a correlated sub-SELECT, and not a surprise per-row query from something in the expression.

Choosing between the options

Approach	Statements	Loads rows into Python	Runs model hooks
Loop with `save()`	~2N + 1	Yes	Yes
Annotate for reading, then `bulk_update()`	1 read + 1 per batch	Yes	No
`update()` with `Subquery`	1 (or 1 per batch)	No	No

The subquery update is the default when the rule is expressible in SQL. Keep the Python path only for rules that genuinely need Python, and make it batched.

## Why the loop is slow The job: after a rule change, every customer's `loyalty_points` must equal the sum of their paid orders from the last twelve months. The first version is usually Python: ```python for customer in Customer.objects.all(): total = customer.orders.filter(status="paid", placed_at__gte=cutoff).aggregate( t=Sum("amount") )["t"] or 0 customer.loyalty_points = total customer.save() ``` For two million customers that is one `SELECT` for the customers, two million aggregate queries and two million `UPDATE`s. The arithmetic is trivial; the **round trips** are the cost. ## The obvious fix, and why it fails It is tempting to annotate the sum and feed it straight into `update()`: ```python Customer.objects.annotate(total=Sum("orders__amount")).update(loyalty_points=F("total")) ``` This raises **`FieldError`**. Django compiles `update()` into a plain `UPDATE customer SET ...` and resolves the new values with **joins disallowed**; an aggregate over `orders` needs a join and a `GROUP BY`, which an `UPDATE` of this shape cannot express. Depending on the query, the message complains about joined field references or about aggregate functions not being allowed. Filters may use relations; values may not. ## The working version: a correlated subquery A **correlated subquery** is a sub-`SELECT` evaluated per outer row, referring to that row through `OuterRef`. Django's recipe for an aggregate inside a `Subquery` has a fixed shape: 1. `filter(customer=OuterRef("pk"), ...)` ties the inner rows to the outer customer. 2. `order_by()` clears any ordering on the inner QuerySet. An explicit ordering (for example one a custom manager applies) would add its columns to the `GROUP BY` and split a customer into several groups; a model's `Meta.ordering` has not affected `GROUP BY` since Django 3.1, but the documented recipe clears it anyway. 3. `values("customer")` groups by customer. 4. `annotate(total=Sum("amount"))` aggregates within that group. 5. `values("total")` leaves exactly one column, as a scalar subquery must. Then pass it to `update()`: ```python totals = ( Order.objects.filter(customer=OuterRef("pk"), status="paid", placed_at__gte=cutoff) .order_by() .values("customer") .annotate(total=Sum("amount")) .values("total") ) Customer.objects.update( loyalty_points=Coalesce(Subquery(totals), 0, output_field=models.PositiveIntegerField()), updated_at=Now(), ) ``` The SQL is roughly `UPDATE customer SET loyalty_points = COALESCE((SELECT SUM(amount) FROM order WHERE customer_id = customer.id AND ... GROUP BY customer_id), 0), updated_at = NOW()`. - **`Coalesce` is not optional.** A customer with no qualifying orders has no group, so the subquery returns NULL. `Sum(..., default=0)` does not help here, because there is no group row for it to fill in. - **`output_field`** keeps the expression's type unambiguous when mixing the subquery with a literal. ## Running it safely in production One statement touching two million rows is fast but heavy: it holds row locks on every customer until it finishes, and a long transaction competes with checkout traffic. Common practice: - **Batch by primary key**: loop over ranges such as `filter(pk__gte=start, pk__lt=start + 50_000).update(...)`, each its own short statement. - **Set bypassed fields yourself**: `update()` does not call `save()`, send `pre_save`/`post_save` or apply `auto_now`, hence `updated_at=Now()`. - **Invalidate caches** that hold per-customer points, since no signal will tell them. - **Check the plan** of the subquery once: it runs per customer, so an index on `order(customer_id, status, placed_at)` decides whether each evaluation is cheap. ## Verifying the result A set-based rewrite should be proven equal to the loop it replaces before it runs on production data: - Run both versions against the same copy of the data and compare `Customer.objects.values_list("pk", "loyalty_points")` from each; any difference is usually the NULL case or an order status the loop treated differently. - Spot-check edge customers: none of their orders qualify, exactly one order on the cutoff boundary, very large totals near the column's limit. - Read the emitted SQL once (`connection.queries` under `DEBUG`) to confirm it is one `UPDATE` per batch with a correlated sub-`SELECT`, and not a surprise per-row query from something in the expression. ## Choosing between the options | Approach | Statements | Loads rows into Python | Runs model hooks | |---|---|---|---| | Loop with `save()` | ~2N + 1 | Yes | Yes | | Annotate for reading, then `bulk_update()` | 1 read + 1 per batch | Yes | No | | `update()` with `Subquery` | 1 (or 1 per batch) | No | No | The subquery update is the default when the rule is expressible in SQL. Keep the Python path only for rules that genuinely need Python, and make it batched.

Why does Django's recipe call order_by() with no arguments before values('customer') in the subquery?
Explicit ordering on the inner QuerySet, such as an `order_by()` applied by a custom manager, adds its columns to the `GROUP BY`. One customer's orders then split into several groups and the scalar subquery returns more than one row. A model's `Meta.ordering` stopped affecting `GROUP BY` in Django 3.1, but an empty `order_by()` clears explicit and default ordering alike, so the grouping is by customer only.
Why does Coalesce(Subquery(totals), 0) handle customers without orders when Sum(..., default=0) does not?
The subquery groups orders by customer; a customer with no qualifying orders produces no group at all, so the scalar subquery yields NULL. `Sum`'s `default` only replaces a NULL aggregate within a returned row. `Coalesce` wraps the whole subquery and turns its NULL into 0.
What would you do if points also needed an audit record per customer?
Keep the set-based update for the numbers, then write the audit rows in bulk too, for example an `INSERT ... SELECT`-style path or `bulk_create()` from a values query of changed customers. Per-customer `post_save` receivers will not fire, so the audit logic must be called explicitly, ideally inside the same transaction as each batch.

saying these in an interview costs you the question

annotate(Sum(...)).update(field=F('alias')) works in Django
Sum(default=0) inside the subquery removes NULL for customerless groups
update() will fire post_save so caches refresh themselves
One UPDATE over two million rows is free because it is one statement
OuterRef can be used without a surrounding Subquery or Exists

Recomputing loyalty points for two million Django customers in a Python loop takes hours; how would you move it into the database, and why does annotate(Sum(...)).update() fail?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

Why the loop is slow

The obvious fix, and why it fails

The working version: a correlated subquery

Running it safely in production

Verifying the result

Choosing between the options

▾common follow-ups3 questions

▾red flags5 flags

Why the loop is slow

The obvious fix, and why it fails

The working version: a correlated subquery

Running it safely in production

Verifying the result

Choosing between the options

answer

go deeper