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
- count the round trips
- who reads the old value
- two workers, one lost write
- SET col = col + 50
basics
~20 supdate() 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.
code
python · 30 linesfrom 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
Recall that F() refers to the column in the database and that update() writes many rows in one statement without loading them.
Explain the lost-update race in a read-modify-save loop, the round-trip cost, and what update() bypasses: save(), signals and auto_now.
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().
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: 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