3 ms·
One could argue that pumping huge amounts of data with psycopg2 is slow in any case due to the blocking nature of the network calls. I recently copied some big
by aeyes 7y ago
One could argue that pumping huge amounts of data with psycopg2 is slow in any case due to the blocking nature of the network calls.
I recently copied some big tables (100M+ rows) into a different system, when I switched the simple script to asyncpg I gained 5x over psycopg2. Experimenting with the amount of rows cursor.fetchmany returns changed absolutely nothing.
I wonder how much gain there is from using a cursor, for CRUD applications you most likely need the full result set before being able to do anything with it. Having a cursor could be made optional for when you actually want to iterate over the resultset.
The cursor interface could also be hidden, I just want to iterate over the resultset - I don't care what happens in the background.
- ghostwriter 7y ago> when I switched the simple script to asyncpg I gained 5x over psycopg2. This should really be a comparison between asyncpg and psycopg2+gevent+psycogreen to be technically aligned, as otherwise it's a comparison between two scripts with non-blocking and blocking IO.
- 1st1 7y agoHere's a benchmark [1] against aiopg, which is psycopg2+asyncio. [1] http://magic.io/blog/asyncpg-1m-rows-from-postgres-to-python/report.html http://magic.io/blog/asyncpg-1m-rows-from-postgres-to-python...
- ddorian43 7y agoYou can't scale that. Adding clients will have a lot of overhead (ex: 100K clients to 1 db server). Only way is autocommit with proxy (pgbouncer etc).
- ghostwriter 7y agoUnless aiopg patches psycopg2 around its C extension with available callback hooks (see [1] and [2] - psycogreen uses it to register gevent callbacks), this comparison is still between blocking and non-blocking IO scripts. Quick GitHub search for `set_wait_callback` didn't return any result in the aiopg repository - https://github.com/aio-libs/aiopg/search?q=set_wait_callback&unscoped_q=set_wait_callback https://github.com/aio-libs/aiopg/search?q=set_wait_callback... [1] https://www.psycopg.org/docs/extensions.html#coroutines-support-functions https://www.psycopg.org/docs/extensions.html#coroutines-supp... [2] https://www.psycopg.org/docs/advanced.html#green-support https://www.psycopg.org/docs/advanced.html#green-support
- wnoise 7y agoThe point is to compare the blocking vs non-blocking though.
- ghostwriter 7y agoI was addressing OP's impression that psycopg2 was slow "in any case" and that it cannot be used in non-blocking fashion. > psycopg2 is slow in any case due to the blocking nature of the network calls.