Why is iterating through a large Django QuerySet consuming massive amounts of memory?

by

Nate C was close, but not quite.

From the docs:

You can evaluate a QuerySet in the following ways:

  • Iteration. A QuerySet is iterable, and it executes its database query the first time you iterate over it. For example, this will print the headline of all entries in the database:

    for e in Entry.objects.all():
    print e.headline

So your ten million rows are retrieved, all at once, when you first enter that loop and get the iterating form of the queryset. The wait you experience is Django loading the database rows and creating objects for each one, before returning something you can actually iterate over. Then you have everything in memory, and the results come spilling out.

From my reading of the docs, iterator() does nothing more than bypass QuerySet’s internal caching mechanisms. I think it might make sense for it to a do a one-by-one thing, but that would conversely require ten-million individual hits on your database. Maybe not all that desirable.

Iterating over large datasets efficiently is something we still haven’t gotten quite right, but there are some snippets out there you might find useful for your purposes:

More Related Contents:

  1. Django select only rows with duplicate field values
  2. How to include excluded rows in RETURNING from INSERT … ON CONFLICT
  3. Select rows which are not present in other table
  4. Optimize GROUP BY query to retrieve latest row per user
  5. How to implement a many-to-many relationship in PostgreSQL?
  6. Check if value exists in Postgres array
  7. How to use ANY instead of IN in a WHERE clause?
  8. Is there a shortcut for SELECT * FROM?
  9. sql statement error: “column .. does not exist”
  10. What is the difference between single quotes and double quotes in PostgreSQL?
  11. Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?
  12. Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
  13. Best way to count rows by arbitrary time intervals
  14. PostgreSQL sequence based on another column
  15. Sorting null values after all others, except special
  16. Reference alias in WHERE clause
  17. Sort NULL values to the end of a table
  18. How to compare the current row with next and previous row in PostgreSQL?
  19. ERROR: functions in index expression must be marked IMMUTABLE in Postgres
  20. Why postgres returns unordered data in select query, after updation of row?
  21. Continuing a transaction after primary key violation error
  22. Select (retrieve) all records from multiple schemas using Postgres
  23. What is the difference between a LATERAL JOIN and a subquery in PostgreSQL?
  24. PostgreSQL – fetch the rows which have the Max value for a column in each GROUP BY group
  25. postgres – where in (list) – column does not exist
  26. incomplete information from query on pg_views [duplicate]
  27. How to perform OR condition in django queryset?
  28. PostgreSQL: Give all permissions to a user on a PostgreSQL database
  29. How to use SQL LIKE condition with multiple values in PostgreSQL?
  30. Is there a postgres CLOSEST operator?

Leave a Comment