Django lazy QuerySets

You have to be extra careful when dealing with big QuerySets in Django.

Suppose we have the CustomUser model and a respective table with a few million rows. Our task is to filter, sort, and then iterate on them:

from myapp.models import CustomUser
large_query_set = CustomUser.objects
    .filter(last_login__gte='2020-10-20 02:00:00')
    .order_by('last_login')

Your first guess may be the following:

for user in large_query_set:
    print(f"user is: {user}")

Yeah, querysets in Django are lazy. It means that .all(), .filter() and .order_by() operations will not be executed until you do not try to perform a read operation on your queryset. You see, in the first lines, we just build our queryset, and assign it to the large_query_set variable.

There will be no SQL queries executed until you try to access the data in the queryset, meaning that all queryset will be persisted in memory once you enter foreach loop. Therefore, simple foreach loop is not suitable for large querysets.

iterator()

One way is to make use of .iterator(). This method allows you to loop through your QuerySet object by object without storing the entire dataset in memory.

This method utilizes database cursors underneath. We already spoke about database cursors:

for user in large_query_set.iterator():
print(f"user is: {user}")

Paginator

Another way is to embrace the power of the Paginator. It adds offset-based logic (we had a discussion about it as well) to the query underneath. Look at this example. Let’s instantiate Paginator:

from django.core.paginator import Paginator
# 1000 is desired amount of items per page
paginator = Paginator(large_query_set, 10000)

And apply it:

for page_num in range(1, paginator.num_pages + 1):
    current_page = paginator.page(page_num)
for item in current_page:
    print(f"user is: {user}")

If you have to iterate 1 mln items in the database, you end up with 100 database queries during this foreach operation. Memory consumption will be 100 times less.

One more trick here is that you can capture necessary columns only with .values():

CustomUser.objects.values('id', 'username')