8 ms·
Show HN: 1M rows/s from Postgres to Python
- 1st1 10y agoasyncpg was developed by @redcrowbar and me. Ask us anything ;)
- Veratyr 10y agoDoes this automatically escape/convert things like datetimes in a query or does that need to be done separately? The docs are a little sparse on details. Looks fantastic anyway!
- RedCrowbar 10y agoYes, query arguments are converted automatically. Results are converted into Python types as expected (including arrays and composite types).
- rdtsc 10y agoGreat work, thanks for sharing. Very impressive speed. I didn't know about uvloop, that's a cool project: http://magic.io/blog/uvloop-blazing-fast-python-networking/ http://magic.io/blog/uvloop-blazing-fast-python-networking/
- 1st1 10y agoYep, it's also much more stable now, 3 months after that article was posted. I plan to make a new blog post about it soon.
- random42 10y agoMay be I am reading the benchmarks wrong, but does it mean I can get a 2x speedup in my end to end query execution from moving from aiopg to asynpg alone without any server side changes or query optimizations?
- RedCrowbar 10y agoWe intentionally benchmarked simple queries to measure the overhead of the driver implementation. So, the 2x speedup really means 50% reduction in overhead.
- welder 10y agoHow can I use it with SQLAlchemy instead of psycopg2?
- RedCrowbar 10y agoSQLAlchemy ORM is not async, so there is no way currently to use any async dialect for it. However, it is possible to write an SQLAlchemy Core adapter for asyncpg.
- infinite8s 10y agoIt might even be easier than writing an adapter if you just use SQLAlchemy Core's DSL as the generative mechanism for a SQL statement that is sent to asyncpg.
- RedCrowbar 10y agoThere appears to be an adapter for Core already: https://github.com/CanopyTax/asyncpgsa https://github.com/CanopyTax/asyncpgsa
- nhumrich 10y agoAuthor of asyncpgsa here, I am in the process of writing that, let me know if you have questions.
- andrioni 10y agoDo you have any numbers on queries that return millions of rows? If it's still fast, asyncpg could be nice for some ETL-type workloads.
- 1st1 10y agoWe haven't benchmarked this specific use case. However, cursors in asyncpg do data prefetching (the exact number of prefetched rows is configurable), so I expect that asynchronous iteration should be pretty fast (and still faster than psycopg2).
- Keats 10y agoI'd love to read the docs for the protocol itself, is it planned? Do you have a link explaining it otherwise? Great work!
- RedCrowbar 10y agoThe protocol is documented in the official PostgreSQL documentation: https://www.postgresql.org/docs/current/static/protocol.html https://www.postgresql.org/docs/current/static/protocol.html
- Keats 10y agoI meant a doc explaining roughly the components at https://github.com/MagicStack/asyncpg/tree/master/asyncpg/protocol https://github.com/MagicStack/asyncpg/tree/master/asyncpg/pr... and how they interact with each other at a high level as it doesn't seem to have comments. Thanks for the link though, I'll have a look
- 1st1 10y agoYeah, documenting internals sounds like a good idea. At least to make it easier for people to contribute and further improve asyncpg. We'll try to find some time to do that.
- glogla 10y agoBit offtopic, but what are the plots made with?
- RedCrowbar 10y agoHandmade with d3.js
- deleted 10y ago[deleted]
- deleted 10y ago[deleted]
- tjpaudio 10y agoThis is really awesome, I'm going to give it a try in my latest project. Has there been any apps/sites with large amounts of i/o running this yet? Curious how reliable it is at this stage.
- 1st1 10y agoWe've open sourced asyncpg just a couple of weeks ago, and right now it has a beta status. Not that we're aware of any bugs in it or plan to add a lot of new features. So I guess you should give it a try, carefully ;)
- merb 10y agowould be cool to also make jmh tests against jdbc and async pg on java. actually what i also found is that python wasn't slow in these cases but it was slow when you do map operations on the lists and reoder the data or convert it to other structures.
- RedCrowbar 10y ago> would be cool to also make jmh tests against jdbc and async pg on java. Pull requests to https://github.com/MagicStack/pgbench https://github.com/MagicStack/pgbench are welcome :-)
- deleted 10y ago[deleted]
- jargino 10y ago> For fairness, all tests were run in a single-thread (GOMAXPROCS=1 for Go code) in async mode. Isn't this just hobbling Go so you can beat it? Doesn't seem like something done "for fairness" at all.
- 1st1 10y agoPython and nodejs applications are usually deployed in a multiprocess configuration (and Go apps without GOMAXPROCS). So in production, applications in all of those languages use all CPU cores. For benchmarks, we didn't want to complicate things with multi-process setup, the idea was to compare the raw performance of all drivers.
- Thaxll 10y ago"usually deployed" where? Every Python app I've seen in Prod don't use multi process to bypass the limitation of GIL/#cores.
- riquito 10y agosupervisord
- malux85 10y agoAre you kidding? I run 80 servers with custom written daemons and it's always multi-process. I just finished said infra at one of the worlds largest online betting sites, their legacy was multi-process and the new systems are multi-process. This is really common
- _qqea 10y agoThey don't use the multiprocess module but most of them are deployed with something like uWSGI or Gunicorn, which spawns the worker processes and forwards requests to them and the responses back.
- nhumrich 10y agoGIL only applies to multi-threaded, not multi-process. Multi-process is a great way to overcome the GIL
- spriggan3 10y agoWhy is node pg so slow relative to go and asyncpg drivers ? I'm sure there is room for improvement here.
- also_on_sunday 10y agoPrimary author of node-postgres (both the JavaScript & libpq versions) here. Very good question. I'd say part of the relative speed is because it's been a mostly 1 person (me!) project for a loooong time & I haven't had a lot of time to do perf analysis to find where exactly to make it faster. I would absolutely love some help! My time for open source is pretty limited, and I try to spend most of it focused on triaging issues. If you'd like to help out please don't hesitate to open an issue, a pull request, or send me a gchat (contact info is in node-postgres readme) and we can discuss!
- bedros 10y agowould this work with django?
- JoelBennett 10y agoI wouldn't mind knowing this as well!
- 1st1 10y agoasyncpg was designed for asyncio (and async/await). So it can be used in any asyncio, Tornado (and soon Twisted) program. Django doesn't support asyncio, it's built for blocking IO model. So, unfortunately, it's not possible to use asyncpg with it.
- machbio 10y agoI know its not related to the Asyncio - for Django; you should follow this - http://channels.readthedocs.io/en/latest/index.html http://channels.readthedocs.io/en/latest/index.html
- davidw 10y agoWonder how this (Erlang driver) would stack up in the benchmarks (devel branch): https://github.com/epgsql/epgsql https://github.com/epgsql/epgsql
- 1st1 10y agoConsider making a PR to https://github.com/MagicStack/pgbench https://github.com/MagicStack/pgbench :)
- Dowwie 10y agoDoes this library address any of the issues/concerns that Mike Bayer discussed in this earlier blog post? http://techspot.zzzeek.org/2015/02/15/asynchronous-python-and-databases/ http://techspot.zzzeek.org/2015/02/15/asynchronous-python-an...
- zzzeek 10y agothe 1M row/sec part is easy to explain: > asyncpg extensively uses PostgreSQL prepared statements. This is an important optimization feature, as it allows to avoid repeated parsing, analysis, and planning of queries. Additionally, asyncpg caches the data I/O pipeline for each prepared statement. it would be great if psycopg2 did this, at least within the context of executemany(). You don't need async to use prepared statements. But even if they did this, it wouldn't be this amazing speed-everywhere kind of thing, just for when you do a bulk INSERT/UPDATE/DELETE. The prepared statement is linked to a cursor and a statement handle so it's not like you can have hundreds of these just queued up in a cache without a great deal of complexity. The TL;DR; for my blog post you mention is that async itself usually has little to do with speed when talking to a database, because the database is typically on the same network as your application, you're using typically no more than a few dozen connections per process (postgresql uses a process per connection, so using thousands of connections isn't feasible anyway), so the overhead of async and especially Python 3's async IO slows things down considerably without offering any opportunity to regain that speed. The GIL doesn't block on IO so threads work just fine, and most applications are CRUD / transactional applications which means you can't spawn off several dozen queries within a transaction at the same time anyway, you need to wait for each one to complete serially regardless. I'd be very wary of a new PG driver especially one that implements the wire protocol itself. pg8000 does this, and also does prepared statements, and while it doesn't have the speed approach here, it also has serious problems supporting all the features and datatypes that psycopg2 does. It's been around for years and reliably breaks on many Postgresql features (and Postgresql introduces new features like a firehose). Writing and maintaining a driver for Postgresql is not an easy task.
- RedCrowbar 10y ago> the 1M row/sec part is easy to explain: Prepared statements were used in all benchmarks where supported. Please don't dismiss the results so easily. The source of performance increases here is not the use of prepared statements, but efficient implementation of the binary type I/O and the protocol itself. > I'd be very wary of a new PG driver especially one that implements the wire protocol itself. > pg8000 does this, and also does prepared statements, and while it doesn't have the speed approach here, it also has serious problems supporting all the features and datatypes that psycopg2 does. asyncpg supports 100% of pg_catalog types (we have a test for that.) Additionally, psycopg2 doesn't _really_ support all the features and datatypes and simply returns unparsed text in many cases. You have to write lots of typecasters to convince it otherwise. > It's been around for years and reliably breaks on many Postgresql features (and Postgresql introduces new features like a firehose). Writing and maintaining a driver for Postgresql is not an easy task. Please. Simply because something has been around is not the reason to stiffle new development and innovation.
- rambos 10y agoFastest setup of a PG client, to date, on OSX for me. No nuances or other package installers like Fink necessary.
- thang 10y agoAwesome work! Can't wait to see more about EdgeDB.
- jamiesonbecker 10y agoWould this work/be binary-compatible with Amazon Red Shift?
- 1st1 10y agoShould be. AFAIK psycopg2 works with Redshift just fine, so should asyncpg. The only thing I'm not sure about is what kind of authentication mode Redshift is using. asyncpg doesn't support all of them, but that's easy to fix.
- RedCrowbar 10y agoHaven't tried, but, AFAIK, redshift speaks the standard PostgreSQL protocol, so asyncpg should work.
- wampler 10y agoCool! Wish there would be a Django wrapper, but then realised it is designed for asyncio
- userbinator 10y agoAt 3.7GHz that's roughly 3700 clock cycles per row. With something that low, I'd be really interested to see a breakdown of how many cycles were spent doing what.
- RedCrowbar 10y agoIn the 1M benchmark roughly half is spent decoding rows (in [1]). The rest is spread over interpreter overhead, GC in particular. [1] https://github.com/MagicStack/asyncpg/blob/master/asyncpg/protocol/prepared_stmt.pyx#L192 https://github.com/MagicStack/asyncpg/blob/master/asyncpg/pr...
- denfromufa 10y agoHow do you develop your Cython code? Do you use PyCharm? Did you start off from pure Python code? How easy is debugging and refactoring?
- 1st1 10y agoI use SublimeText with a Cython highlighter; @redcrowbar uses Atom. I started coding uvloop & asyncpg directly in Cython, basically from scratch. Debugging & refactoring are pretty much as easy as for pure Python code (which is a huge win over C). Sometimes when you're writing low-level code you can get a segfault, in which case lldb (or gdb) help you understand what's going on relatively quickly.
- denfromufa 10y agoSo do you use cygdb to debug Cython code or directly debug the generated C-API code?
- 1st1 10y agoI usually debug the generated C code directly (usually it's quite easy to understand the bug once you see where the program crashes.)
- Fiahil 10y agoWe've been using Python (2.7) and Postgres, where I work, and I must say I really miss the async feature. It's really convenient to be able to parallelize and saturate I/O very quickly. Right now, we're using a combination of threads and processes without proper messaging, which leads to deadlocks and other nasty things. That's one thing I've been quite pleased with, when using Scala.
- landmark3 10y agotime for a rollback uUber
- amelius 10y agoWhat about the complexity of the rows? For instance, how many columns per row?
- RedCrowbar 10y ago12 in the particular case of 1M rows/s. The details are available in the benchmark report: http://magic.io/blog/asyncpg-1m-rows-from-postgres-to-python/report.html#bench1 http://magic.io/blog/asyncpg-1m-rows-from-postgres-to-python...
- pxlfkr 10y agoUsing Cython means you don't/can't use Pypy? Was that something you considered, e.g. with psycopg2cffi or a pure-python driver? It seems to be the defacto performant runtime for Python, and since performance is something you guys obviously care a lot about, I'm surprised it hasn't been mentioned in this thread. Very excited to try out your code - thanks for sharing it.
- RedCrowbar 10y agoA pure-Python implementation would probably never reach this level of performance, even under PyPy. Cython really makes it possible to basically drop down to C and write very efficient code. That said, once PyPy supports 3.5, we will consider making a CFFI binding.
- nhumrich 10y agoBy using python asyncio, you can't use pypy because pypy doesn't support 3.5 yet (or probably for a while), so by going async, you are eliminating pypy right there.
- xgess 10y agoi'd love to see raw, command-line psql added to these benchmarks