Django Database Optimization Tips

Django ORM optimization cheat sheet

Goutom Roy
Jun 7, 2019 · 4 min read
Photo by Caspar Camille Rubin on Unsplash

In this piece I will run through a few key optimization tips rather that discussing on every point. Please read the Django official documentation for full details.

Throughout the piece we will use the following models :

class Author(models.Model):
name = models.CharField(max_length=200)
email = models.EmailField()

def __str__(self):
return self.name

class Blog(models.Model):
name = models.CharField(max_length=100)
tagline = models.TextField()

def __str__(self):
return self.name

class Entry(models.Model):
blog = models.ForeignKey(Blog, on_delete=models.CASCADE)
headline = models.CharField(max_length=255)
body_text = models.TextField(blank=True)
likes = models.IntegerField(blank=True, default=0)
authors = models.ManyToManyField(Author, blank=True)

class Meta:
default_related_name = 'entries'

def __str__(self):
return self.headline

Understand QuerySet Evaluation and Caching


Use get() Wisely

try:
one_entry = Entry.objects.get(blog=2000)
except Entry.DoesNotExist:
# query did not match to any item.
pass
except Entry.MultipleObjectsReturned:
# query matched multiple items.
pass
else:
# query matched to just one item
print(one_entry)

Use Available Debug Tools


Use Iterator When Possible

For a QuerySet which returns a large number of objects with lots of memory to cache that you only need to access once, you can use iterator().

In the following code, all entries will be fetched from database and loaded in the memory and then iterate through each one.

q = Entry.objects.all()
for each in q:
do_something(each)

When we use iterator(), Django will hold the SQL connection open and read each row and call do_something() before reading the next row.

q = Entry.objects.all().iterator()
for each in q:
do_something(each)

Use Persistence Database Connection


Use select_related() and prefetch_related()


Use F Expressions

# Don't
for entry in Entry.objects.all():
entry.likes += 1
entry.save()

# Do
Entry.objects.update(likes=F('likes') + 1)

Use Aggregation

# Don't
most_liked = 0
for entry in Entry.objects.all():
if entry.likes > most_liked:
most_liked = entry.likes
# Do
most_liked = Entry.objects.all().aggregate(Max('likes'))['likes__max']

Use Foreign Key Values Directly

# Don't. Needs database hit
blog_id = Entry.objects.get(id=200).blog.id

# Do. The foreign key is already cached, so no database hit
blog_id = Entry.objects.get(id=200).blog_id

# Do. No database hit
blog_id = Entry.objects.select_related('blog').get(id=200).blog.id

Don’t Order Results if You Don’t Care


Use count() and exists()

# Don't
count = len(Entry.objects.all()) # Evaluates the entire queryset

# Do
count = Entry.objects.count() # Executes more efficient SQL to determine count

# Don't
qs = Entry.objects.all()
if qs:
pass

# Do
qs = Entry.objects.exists()
if qs:
pass

Use Bulk add(*objs) to ManyToManyField Fields

author1 = Author(name='author1')
author2 = Author(name='author2')
author3 = Author(name='author3')
entry = Entry.objects.get(id=1)

# Don't
entry.authors.add(author1)
entry.authors.add(author2)
entry.authors.add(author3)

# Do
entry.authors.add(author1, author2, author3)

Use Delete() and Update() for Bulk Operations

# Don't.Delete one by one.
for entry in Entry.objects.all():
entry.delete()

# Do.Delete all at once.
Entry.objects.all().delete()

# Don't
for entry in Entry.objects.all():
entry.likes += 1
entry.save()

# Do
Entry.objects.update(likes=F('likes')+1)

Use bulk_create()

# Don't
for i in range(20):
Blog.objects.create(name="blog"+str(i), headline='tagline'+str(i))

# Do
blogs = []
for i in range(20):
blogs.append(Blog(name="blog"+str(i), headline='tagline'+str(i)))
Blog.objects.bulk_create(blogs)

Use values(), values_list(), defer(), only()

When you need certain fields in QuerySet results, and want model fields in QuerySet instead of list, tuple or dictionaries, use defer() and only().

Better Programming

Advice for programmers.

Goutom Roy

Written by

Engineer, son, brother, friend, archaeology enthusiast and history maniac, it’s me somehow.

Better Programming

Advice for programmers.

More From Medium

More from Better Programming

More from Better Programming

More from Better Programming

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade