Asynchronous Python and Databases

So the basic take away seems to be: don't bother using async patterns for single, low latency connections to a server on your local network.

For anything where you're dealing with thousands of connections from random Internet hosts, "just spawn a thread for it" does not cut it. If you take that approach, you're setting yourself up to be accidentally DoS'd at some point in the near future. Async, on the other hand, has more than proven itself to be apt for this kind of scenario.

If you have a Python CRUD app, there are many steps you should take to speed up the app before parallelizing queries.

The first couple that come to mind:

- Caching. The 90-10 rule applies to most CRUD apps. Use varnish to get rid of some requests before they hit your webserver or make a redis/memcache LRU layer to prevent queries from hitting your database.

- Shifting more work away from Python to the database. A surprising number of apps don't do pagination properly.

For example, if you want to show the top 10 X objects for each item Y, you don't need to get X * Y rows. You can use a window function to get the top 10 for each Y can get 10 * Y rows instead.

- Using PyPy if you can. It's a free performance boost, in most cases.

- Smarter indexing. Postgres's partial indexes are really powerful.

- Intermediate caches. If you have common GROUP BY queries, a materialized view could go a long way.

- If you reallllllly need it, you can Cython and rewrite slow parts of your app in C.

My point is - parallelizing queries should be one of your last steps in speeding up your app. It adds a lot of complexity sometimes and there's a lot of cheap easy optimizations out there.

> there are ways of speeding up your app that don't involve touching the Python side (caching using Redis, denormalizing, etc), but they are a lot harder to implement than asynchronous I/O. Why give up on some free performance for many types of applications?

1) I can't say I find migrating blocking code to non-blocking code trivial. The bad thing about it is that you never know what you left behind. Blocking code doesn't tell you when it's blocking your event loop.

2) Async helps apps use less memory by doing away with thread overhead in connections, but is otherwise suboptimal (hinders latency) under higher cpu load scenarios. It never was a way of getting additional "free performance" for your app.

Benchmarks are snake oil when not being given the ability to reproduce them.

Great post.

At this point those who blindly advocate async programming as generally faster just show their level of proficiency (a lack thereof).

The fact that threads can be just as performant (or as we saw, even more performant) for IO code should not be surprising for anyone who knows how stuff works at the lower levels.

BTW this irrational "async is always webscale" crap has been happening in the Java community as well. There is a nice summary of the outcome:

http://www.mailinator.com/tymaPaulMultithreaded.pdf

[Thousands of Threads and Blocking I/O. The old way to write Java Servers is New again (and way better)]

Non-blocking IO based on select (and friends -- epoll, kqueue,...) is working well for very short callback chains. Think a proxy (haproxy, a webserver) or one page demo -- "Look Ma! I got a webscale server running in 3 lines of code!". Large business applications based on callback chains (even disguised as Deferreds, Futures and Promises) easily turn into a speghetti mess.

Going back to asyncio. I am less optimistic about it and I never liked it. It is good that it tried to unify and standardize non-blocking IO. But we already had that, it is called Twisted. Twisted did "async is cool" before it was really cool. It is a fantastic framework (I used for 5 years professionally) but in large code bases you feel its pain. BUT that is not the worst part, the worst part is it fragments the library echosystem. This is really bad especially for Python. Since one can argue the ecosystem of libraries is what makes Python great. With Twisted I had to go find for Twisted versions of drivers for databases. Now for asyncio I would have to look for asyncio version of libraries.

For Python I like either the classic threads for IO or eventlet/gevent threads. BTW eventlet should work with PyPy as well. The latter are great because they do not fragment the library ecosystem but they rely on monkey-patching. I can pick threaded database drivers, monkey patch the socket code and it can work with green threads. Or not work, because monkey-patching breaks sometimes...

Even better for larger concurrent applications I like channels and actors. Pick you eventlet green threads + queues. Or Go's channels. Or Akka. Or Erlang's processes. Clojure's STM is great as well. There are so many better abstraction for serious concurrent applications that if anyone picks callbacks as their default mechanism, they should be able to justify it and rationalize it well (Like say "I only know Javascript so I picked Node.js so I am using callbacks" or "I am building a proxy that maintains hundreds of thousands of TCP socket connections" etc.