2

I have tested queryset.iterator() based on Django document.

Oracle and PostgreSQL use server-side cursors to stream results from the database without loading the entire result set into memory.

With server-side cursors, the chunk_size parameter specifies the number of results to cache at the database driver level. Fetching bigger chunks diminishes the number of round trips between the database driver and the database, at the expense of memory.

On PostgreSQL, server-side cursors will only be used when the DISABLE_SERVER_SIDE_CURSORS setting is False.

print(settings.DATABASES['default']['ENGINE']) # postgresql

class TestModel(Model):
    age = IntegerField(default=1)

# Insert 10 rows
for i in range(10):
    TestModel().save()

settings.DEBUG = True
l = logging.getLogger('django.db.backends')
l.setLevel(logging.DEBUG)
l.addHandler(logging.StreamHandler())   
# From now, every queries emitted by Django will be printed.    

print(settings.DISABLE_SERVER_SIDE_CURSORS) # False

for i in TestModel.objects.all().iterator(chunk_size=2):
    print(i.age)

(0.001) DECLARE "_django_curs_4369655232_3" NO SCROLL CURSOR WITH HOLD FOR SELECT "testmodel"."age" FROM "testmodel"; args=()

I expected the above code will hit database 5 times for every 2 rows because of chunk_size=2(and the total number of rows are 10).

However, it seems to emit just one query(above printed query).

Do I misunderstand on queryset.iterator()?

Community
  • 1
  • 1
SangminKim
  • 8,358
  • 14
  • 69
  • 125

1 Answers1

5

You have correctly understood the purpose of queryset.iterator().

In this case (PostgreSQL) Django declared an cursor (using DECLARE statement) which should be used inside iterator.

To fetch data from a cursor, cursor should be first open (using OPEN statement) and then data should be fetched (using FETCH statement).

It does seem that your logging did not catch any of these statements happening inside of iterator, to confirm this you can set logging on PostgreSQL side :).

iklinac
  • 14,944
  • 4
  • 28
  • 30