Database-Side Computation · Django — interview questions

In Django's ORM, why is filter(...).update(loyalty_points=F('loyalty_points') + 50) better than a loop that loads each customer, adds 50 and calls save()?

juniormust knowfreq 63%

answer

count the round trips
who reads the old value
two workers, one lost write
SET col = col + 50

basics

~20 s

update() with F() sends one UPDATE that adds 50 to the stored value inside the database: one round trip, no lost updates. The save() loop runs a SELECT plus an UPDATE per customer and can overwrite concurrent changes.

solid answer

~40 s

F('loyalty_points') refers to the column's value in the database, not in Python, so filter(...).update(loyalty_points=F('loyalty_points') + 50) compiles to a single UPDATE customer SET loyalty_points = loyalty_points + 50 WHERE ... and returns the number of rows matched. The loop does one SELECT, then one UPDATE per customer that writes every field unless you pass update_fields, so ten thousand customers cost ten thousand round trips. It is also racy: if another process changes points between your read and your save(), your write is based on the stale value and theirs is lost. The price of update() is that it bypasses the model: no save() override, no pre_save/post_save signals, no auto_now refresh, and instances already in memory are not changed.

`F('loyalty_points')` refers to the column's value in the database, not in Python, so `filter(...).update(loyalty_points=F('loyalty_points') + 50)` compiles to a single `UPDATE customer SET loyalty_points = loyalty_points + 50 WHERE ...` and returns the number of rows matched. The loop does one `SELECT`, then one `UPDATE` per customer that writes every field unless you pass `update_fields`, so ten thousand customers cost ten thousand round trips. It is also racy: if another process changes points between your read and your `save()`, your write is based on the stale value and theirs is lost. The price of `update()` is that it bypasses the model: no `save()` override, no `pre_save`/`post_save` signals, no `auto_now` refresh, and instances already in memory are not changed.

code

python · 30 lines

python

from django.db import models


class Customer(models.Model):
    email = models.EmailField(unique=True)
    loyalty_points = models.PositiveIntegerField(default=0)
    tier = models.CharField(max_length=10, default="bronze")
    updated_at = models.DateTimeField(auto_now=True)


class Order(models.Model):
    customer = models.ForeignKey(
        Customer, on_delete=models.CASCADE, related_name="orders"
    )
    amount = models.PositiveIntegerField()  # whole currency units
    status = models.CharField(max_length=10)
    placed_at = models.DateTimeField()


# elsewhere, e.g. a service function
from django.db.models import F
from django.db.models.functions import Now


def award_campaign_bonus(week):
    return (
        Customer.objects.filter(orders__placed_at__range=week)
        .distinct()
        .update(loyalty_points=F("loyalty_points") + 50, updated_at=Now())
    )

go deeper

for a junior

Recall that F() refers to the column in the database and that update() writes many rows in one statement without loading them.

for a middle

Explain the lost-update race in a read-modify-save loop, the round-trip cost, and what update() bypasses: save(), signals and auto_now.

for a senior

Show judgment about when bypassing the model is acceptable, how you restore skipped side effects, and the Django 6.0 change to F() assignments on save().

for a principal

Set a team rule for counters and balances: database-side increments by default, with the per-object path reserved for changes that genuinely need model behaviour.

The two versions

Suppose a promotion awards 50 bonus points to every customer who ordered during a campaign week.

python

# Version A: per-row Python
for customer in Customer.objects.filter(orders__placed_at__range=week).distinct():
    customer.loyalty_points += 50
    customer.save()

# Version B: one statement
Customer.objects.filter(orders__placed_at__range=week).distinct().update(
    loyalty_points=F("loyalty_points") + 50
)

Both end with the same numbers on a quiet database. They differ in cost, correctness under concurrency and what they skip.

What `F()` means

An F() expression is a reference to a column's value as the database sees it at the moment the statement runs. F("loyalty_points") + 50 is not evaluated in Python; Django compiles it into SQL arithmetic, so version B becomes one statement of the form UPDATE ... SET loyalty_points = loyalty_points + 50 WHERE ....

update() executes immediately; it is not deferred to a commit or a flush. It returns the number of rows matched.
F() in an update can only reference columns of the model being updated. F("orders__amount") there raises FieldError, because an UPDATE cannot introduce joins.
Filters may still follow relations, as in version B.

Cost: round trips

	Version A (save loop)	Version B (`update()` + `F()`)
Statements	1 `SELECT` + N `UPDATE`s	1 `UPDATE`
Data sent to Python	Every customer row	Nothing but a row count
Columns written	All of them, unless `update_fields` is passed	Only `loyalty_points`
Time for 10,000 rows	Dominated by 10,000 round trips	One statement's execution

Each round trip pays network latency, statement parsing and a Python model instance. That overhead, not the arithmetic, is what makes loops slow.

Correctness: the lost update

Version A reads a value into Python, changes it, and writes it back. Between the read and the write another request can change the same row:

Worker 1 reads 300 points.
Worker 2 reads 300 points and adds a 20-point purchase reward, saving 320.
Worker 1 adds 50 to its stale 300 and saves 350. The purchase reward is gone.

With F() the database computes loyalty_points + 50 from the value it holds when the UPDATE runs, so both increments survive. The same protection works for one instance:

python

customer.loyalty_points = F("loyalty_points") + 50
customer.save(update_fields=["loyalty_points"])

What `update()` skips

Moving work into the database means Django's model layer is not involved:

A custom save() method is not called.
pre_save and post_save signals are not sent.
A field with auto_now=True is not refreshed; set it explicitly, for example updated_at=Now().
Instances already loaded in memory keep their old values until reloaded.
Model validation (full_clean()) does not run; database constraints still apply.

If points changes must trigger side effects (an email, an audit record), either do them explicitly after the update or keep the per-object path for the few rows that need it.

Seeing the difference in the log

Run both versions with query logging on and the contrast is plain:

sql

-- Version B, roughly
UPDATE "shop_customer" SET "loyalty_points" = ("shop_customer"."loyalty_points" + 50)
WHERE "shop_customer"."id" IN (SELECT ... /* customers with a campaign-week order */);

Version A shows the customer SELECT, then one UPDATE "shop_customer" SET "email" = ..., "loyalty_points" = 350, ... per row, each carrying a literal total computed in Python. That literal is the lost-update risk made visible: the database is told a number, not an instruction.

A quick checklist when converting a loop:

Can the new value be expressed from the row's own columns? Use F() arithmetic.
Does it need values from related rows? Use a correlated Subquery, not F() across a relation.
Does anything depend on save() or signals? Handle it explicitly, or keep those rows on the object path.

A version trap: `F()` assigned to an instance

Assigning an F() to an attribute and calling save() changed in Django 6.0. Now the saved value is refreshed from the database on backends that support RETURNING (PostgreSQL, SQLite, Oracle) and the field is marked deferred on MySQL and MariaDB, so the next access loads it. Before 6.0 the attribute kept holding the expression, and a second save() on the same instance applied the increment again, a classic double-count bug that older answers warn about with "always call refresh_from_db()".

## The two versions Suppose a promotion awards 50 bonus points to every customer who ordered during a campaign week. ```python # Version A: per-row Python for customer in Customer.objects.filter(orders__placed_at__range=week).distinct(): customer.loyalty_points += 50 customer.save() # Version B: one statement Customer.objects.filter(orders__placed_at__range=week).distinct().update( loyalty_points=F("loyalty_points") + 50 ) ``` Both end with the same numbers on a quiet database. They differ in **cost**, **correctness under concurrency** and **what they skip**. ## What `F()` means An **`F()` expression** is a reference to a column's value as the database sees it at the moment the statement runs. `F("loyalty_points") + 50` is not evaluated in Python; Django compiles it into SQL arithmetic, so version B becomes one statement of the form `UPDATE ... SET loyalty_points = loyalty_points + 50 WHERE ...`. - `update()` executes immediately; it is not deferred to a commit or a flush. It returns the number of rows **matched**. - `F()` in an update can only reference columns of the model being updated. `F("orders__amount")` there raises `FieldError`, because an `UPDATE` cannot introduce joins. - Filters may still follow relations, as in version B. ## Cost: round trips | | Version A (save loop) | Version B (`update()` + `F()`) | |---|---|---| | Statements | 1 `SELECT` + N `UPDATE`s | 1 `UPDATE` | | Data sent to Python | Every customer row | Nothing but a row count | | Columns written | All of them, unless `update_fields` is passed | Only `loyalty_points` | | Time for 10,000 rows | Dominated by 10,000 round trips | One statement's execution | Each round trip pays network latency, statement parsing and a Python model instance. That overhead, not the arithmetic, is what makes loops slow. ## Correctness: the lost update Version A reads a value into Python, changes it, and writes it back. Between the read and the write another request can change the same row: 1. Worker 1 reads 300 points. 2. Worker 2 reads 300 points and adds a 20-point purchase reward, saving 320. 3. Worker 1 adds 50 to its stale 300 and saves 350. The purchase reward is gone. With `F()` the database computes `loyalty_points + 50` from the value it holds when the `UPDATE` runs, so both increments survive. The same protection works for one instance: ```python customer.loyalty_points = F("loyalty_points") + 50 customer.save(update_fields=["loyalty_points"]) ``` ## What `update()` skips Moving work into the database means Django's model layer is not involved: - A custom `save()` method is not called. - `pre_save` and `post_save` signals are not sent. - A field with `auto_now=True` is not refreshed; set it explicitly, for example `updated_at=Now()`. - Instances already loaded in memory keep their old values until reloaded. - Model validation (`full_clean()`) does not run; database constraints still apply. If points changes must trigger side effects (an email, an audit record), either do them explicitly after the update or keep the per-object path for the few rows that need it. ## Seeing the difference in the log Run both versions with query logging on and the contrast is plain: ```sql -- Version B, roughly UPDATE "shop_customer" SET "loyalty_points" = ("shop_customer"."loyalty_points" + 50) WHERE "shop_customer"."id" IN (SELECT ... /* customers with a campaign-week order */); ``` Version A shows the customer `SELECT`, then one `UPDATE "shop_customer" SET "email" = ..., "loyalty_points" = 350, ...` per row, each carrying a literal total computed in Python. That literal is the lost-update risk made visible: the database is told a number, not an instruction. A quick checklist when converting a loop: 1. Can the new value be expressed from the row's own columns? Use `F()` arithmetic. 2. Does it need values from related rows? Use a correlated `Subquery`, not `F()` across a relation. 3. Does anything depend on `save()` or signals? Handle it explicitly, or keep those rows on the object path. ## A version trap: `F()` assigned to an instance Assigning an `F()` to an attribute and calling `save()` changed in **Django 6.0**. Now the saved value is refreshed from the database on backends that support `RETURNING` (PostgreSQL, SQLite, Oracle) and the field is marked deferred on MySQL and MariaDB, so the next access loads it. Before 6.0 the attribute kept holding the expression, and a second `save()` on the same instance applied the increment again, a classic double-count bug that older answers warn about with "always call `refresh_from_db()`".

Why does Customer.objects.update(loyalty_points=F('orders__amount')) fail with FieldError?
An SQL `UPDATE` in Django can only reference columns of the table being updated; the update compiler resolves expressions with joins disallowed, so an `F()` that crosses a relation raises `FieldError` ("Joined field references are not permitted in this query"). Filtering on a relation is fine. To pull a value from related rows, use a correlated `Subquery` with `OuterRef`.
When is the save() loop still the right choice?
When each object's change must run model behaviour: an overridden `save()`, `pre_save`/`post_save` receivers, per-object validation or logic that needs Python (an external call per customer). Even then, pass `update_fields` so each `save()` writes only the changed columns, and consider whether the side effect can be done in bulk afterwards instead.

saying these in an interview costs you the question

update() is deferred until the transaction commits
update() calls each model's save() and sends post_save
F() computes the new value in Python before sending it
A save loop is safe because each save() is atomic
update() refreshes auto_now timestamps automatically

open as a page

In Django, how does update() with Case/When differ from bulk_update() and a save() loop when assigning loyalty tiers from each customer's points?

middleshould knowfreq 37%

basics

~20 s

update(tier=Case(When(...), default=...)) computes every tier inside one UPDATE from the stored points; bulk_update() needs the instances loaded and sends Python-computed values as CASE WHEN pk=... per batch; a save() loop sends one UPDATE per customer but runs save() and signals.

open as a page

In Django's ORM, when is QuerySet.exists() the right check, and when is it worse than if queryset: or count()?

middleshould knowfreq 50%

basics

~20 s

QuerySet.exists() sends a SELECT 1 ... LIMIT 1 and is right when you only need a yes/no; if you will iterate the rows anyway, if queryset: is cheaper because it fetches once and caches, and count() is for when you need the number.

open as a page

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%

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.

open as a page

§explore

▾roadmaps that cover this2

§questions

In Django's ORM, why is filter(...).update(loyalty_points=F('loyalty_points') + 50) better than a loop that loads each customer, adds 50 and calls save()?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

The two versions

What F() means

Cost: round trips

Correctness: the lost update

What update() skips

Seeing the difference in the log

A version trap: F() assigned to an instance

▾common follow-ups2 questions

▾red flags5 flags

In Django, how does update() with Case/When differ from bulk_update() and a save() loop when assigning loyalty tiers from each customer's points?

basics

In Django's ORM, when is QuerySet.exists() the right check, and when is it worse than if queryset: or count()?

basics

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?

basics

The two versions

What F() means

Cost: round trips

Correctness: the lost update

What update() skips

Seeing the difference in the log

A version trap: F() assigned to an instance

explore

questions

answer

go deeper

What `F()` means

What `update()` skips

A version trap: `F()` assigned to an instance

What `F()` means

What `update()` skips

A version trap: `F()` assigned to an instance