Issue: SelectQuery loads all models into memory at once
Author: extesy
State: closed
Created: 2013-07-30 02:27:17

I have a code like this:

```
for book in Book.select().where(Book.merchant == m).order_by(Book.last_checked_at):
```

And there are approximately 100k rows in the table (PostgreSQL). When I don't use any `limit` functions, peewee loads all those 100k rows at the very beginning of the loop and that takes 250mb of memory. When I use `limit(1000)` it only takes 30mb. Is there any way to use cursors to pull models incrementally when they are requested by for loop and not read entire table into memory?


This problem can partially be addressed by using this:

http://peewee.readthedocs.org/en/latest/peewee/cookbook.html#iterating-over-lots-of-rows


There is also http://initd.org/psycopg/docs/usage.html#server-side-cursors but peewee ~~does not have support for this feature currently~~.  Although looking at the docs it should be possible to use it by combining peewee queries with a bit of SQL.


@coleifer I've just tried both `.execute().iterator()` and `.naive().iterator()` but that didn't change anything at all - same 250mb of memory. My for loop looks like this:

```
for book in Book.select().where(Book.merchant == m).order_by(Book.last_checked_at).naive().iterator():
```

Am I doing it wrong?


Try this one:

``` python

books = Book.select().where(Book.merchant == m).order_by(Book.last_checked_at).naive()
books_qr = books.execute()
for book in books_qr.iterator():
    # ... etc ...
```

What I'm curious about is whether this memory usage is related to caching instances on the results wrapper (so that iterating a query multiple times does not cause multiple queries), or is just due to the way psycopg2 handles large result sets.


No changes using your variant either. How can I confirm if the caching happens on peewee side or psycopg2 side?


I'm willing to wager the issue is with psycopg2.  Per the docs (http://initd.org/psycopg/docs/usage.html#server-side-cursors) it sounds like unless you're using server-side cursors all results will be sent to the client:

> When a database query is executed, the Psycopg cursor usually fetches all the records returned by the backend, transferring them to the client process. If the query returned an huge amount of data, a proportionally large amount of memory will be allocated by the client.

Maybe it's time to look into adding support for server-side cursors?


That would be awesome! PostgreSQL is the best open-source database and having pewee support server-side cursors will be great for large datasets.


Added support for named cursors in 9a751322 -- to use them define your db using the "postgres extensions" db:

``` python

from playhouse.postgres_ext import PostgresqlExtDatabase

db = PostgresqlExtDatabase('my_db', server_side_cursors=True)
```

Now any select query will be executed using a server-side cursor.  There's a catch, though... There are slightly different calling semantics when using a named cursor (which I will add to the docs eventually, but can be found more or less on psycopg2's docs).

http://initd.org/psycopg/docs/usage.html#server-side-cursors


Docs: http://peewee.readthedocs.org/en/latest/peewee/playhouse.html#server-side-cursors


@coleifer Thanks for adding server-side cursors support! Could you please also clarify one thing?

Since I use the same db for all models, this `server_side_cursors` flag will affect all of them. Does that mean I'll have to change all `select` queries in my project to also call `commit`? Is there any way to use server-side cursors for one particular `select` and not everywhere else where I don't need it?


I'll see about making it work a query at a time.  The way the code is organized this will be a bit trickier but it should be possible.


Updated:  http://peewee.readthedocs.org/en/latest/peewee/playhouse.html#server-side-cursors

Now you just need to use `PostgresqlExtDatabase` and wrap your query in `ServerSide` -- no need to pass `server_side_cursors=True` unless you want _all_ queries to use them.


Much better, thank you!