I'm looking for advice on efficient ways to stream data incrementally from a Postgres table into Python. I'm in the process of implementing an online learning algorithm and I want to read batches of training examples from the database table into memory to be processed. Any thoughts on good ways to maximize throughput? Thanks for your suggestions.
2
-
Please elaborate how the date will be structured, "streaming" it somewhere could just mean dumping the table and reading that from stdout (which is fast, and probably mostly limited by your I/O capabilities). But I suspect you want some structure, and what one should do is heavily dependent on that,knitti– knitti2014-02-24 23:06:53 +00:00Commented Feb 24, 2014 at 23:06
-
Nothing fancy here. Each row corresponds to a particular feature vector often with integer or floating point values. I am just scanning through the rows of a single table. Having it in Postgres is a convenience for query when additional attribute data is available.Chris– Chris2014-02-25 00:00:29 +00:00Commented Feb 25, 2014 at 0:00
Add a comment
|
2 Answers
If you are using psycopg2, then you will want to use a named cursor, otherwise it will try to read the entire query data into memory at once.
cursor = conn.cursor("some_unique_name")
cursor.execute("SELECT aid FROM pgbench_accounts")
for record in cursor:
something(record)
This will fetch the records from the server in batches of 2000 (default value of itersize) and then parcel them out to the loop one at a time.
4 Comments
Craig Ringer
Note that you you should set
itersize; see initd.org/psycopg/docs/cursor.html
saccodd
only if you want to customise the size. By default itersize for named cursors is 2000
PirateApp
any ideas what happens in node-postgres library
mountrix
Thanks a lot for this! It have been days I am trying with offset/limit but it is much more slower!
You may want to look into the Postgres LISTEN/NOTIFY functionality https://www.postgresql.org/docs/9.1/static/sql-notify.html
