Django ORM performance tips: changing data

The article explains how to insert, update or delete the database data with Django ORM in efficient way with less or faster SQL queries.

1. QuerySet.update() instead Model.save()

The code below executes two SQL queries: first to load the object and the second to update all the fields:

user = User.objects.get(id=user_id)
user.first_name = 'Alex'
user.save()

QuerySet.update() updates only the specified fields with one query:

User.objects.filter(id=user_id).update(first_name='Alex')

2. How to use field value in QuerySet.update()

Use F() expression for the database field values instead constant or variable value:

User.objects.filter(id=user_id).update(age=F('age') + 1)

that generates SQL like:

UPDATE users SET age = age + 1

Less effecient Model.save() update that does the same:

user = User.objects.get(id=user_id)
user.age += 1
user.save()

There are several benefits comparing to Model.save() update:

• Can update several objects returned by QuerySet.filter()
• Several same queries update correctly if run in parallel
• Don't have to load the current object state with extra SQL query

3. Insert many database objects with one SQL query

Adding object to the database is easy with Django ORM:

  user = User(first_name='Alex', last_name='O')
  user.save()

but it requires one SQL per object so isn't effecient for many objects, use Model.objects.bulk_create(model_list) to add many values with one SQL INSERT query:

  users = [User(first_name='Alex', last_name='O'), User(first_name='John', last_name='Doe')]
  User.objects.bulk_create(users)

4. Delete many objects with one SQL query

Good code:

  User.objects.filter(is_active=False).delete()

Bad code that could trigger a lot of SQL queries:

  for user in User.objects.filter(is_active=False):
      user.delete()

Many transactions per view

The code below executes two transactions to charge customer and prepare shipping:

from django.db import transaction

with transaction.atomic():
    charge_account(order.customer)
    order.paid = True
    order.save()
    
with transaction.atomic():
    make_shipping(order)

Benefits comparing to single transaction:

• Easier handling of failed transaction: if the shipping transaction would fail, the charge transaction isn't cancelled and can process paid order with failed shipping later, if such scenario is possible
• Less database locking: before the transaction 2 starts, the transaction 1 database row and table locks are released so the deadlock chance is reduced