Mike Bayer, author of SQLAlchemy, writes about Python, databases, ORMs, and software patterns.
Commenters repeatedly cite his 2015 post on asynchronous Python and databases as a key reference showing async often isn't faster for database-backed web apps.
> I want to scream every time somebody suggests threading as a solution to non-blocking database calls and HTTP requests.
Rather than screaming, perhaps read this (from the developer of the Python world's most popular ORM) to see why they might be suggesting it:
http://techspot.zzzeek.org/2015/02/15/asynchronous-python-and-databases/
tl;dr:
If your database is properly setup and provisioned it shouldn't be a source of blocking I/O (and if it is, throwing thousands of concurrent async connections at it isn't going to help you). For standard CRUD applications, your program will spend much more time in CPU processing the database response than it will waiting on the database to deliver it.
Furthermore, when querying a traditional RDBMS like postgres or mysql, safe concurrency is ultimately enforced by the ACID capabilities built in to the database itself i.e. transactions. Whether you're using async, threads, multiple processes or something else entirely doesn't matter. Given the database itself controls how much concurrency it can support, real world tests (at least for Python) show that threads give you better performance than event driven callbacks.
Of course, threads are completely wrong for the HTTP side of things and this is where async (e.g. nginx or node) offers all of its advantages. That doesn't mean it's wrong to put your DBMS behind a thread pool.
I am SUPER happy someone else is finally looking at this. It is long past time that the reflexive use of asycnio or systems like gevent/eventlet for no other reason than "hand-wavy SPEED" come to an end. That web applications that literally serve just one user at at time are built in Tornado for "speed". (my example for this is the otherwise excellent SnakeViz: https://jiffyclub.github.io/snakeviz/ which IMO should have just used wsgiref).
As the blog post apparently cites as well (woo!), I've written about the myth of "async == speed" some years ago here and my conclusions were identical.
https://techspot.zzzeek.org/2015/02/15/asynchronous-python-and-databases/
> The main thing one needs to think about when coding an app in a load balanced environment is that there’s no guarantee that request ‘n+1′ is going to land on the same server as request ‘n’, so you need to handle sessions in a centralized/db manner so that the cookies in the browser link you up to the proper session regardless of what server you hit.
Instead of doing it in a centralized/db manner, Beaker allows sessions to be distributed client side:
http://techspot.zzzeek.org/2011/10/01/thoughts-on-beaker/
It was one of the best things I learned last year.
> In order for async to be popular it needs to be the default way of writing python. Right now its an after thought for writing “high performance” python.
Might this have to do with the fact that the (lack of) performance of asyncio is exactly the issue here?
I remember reading a blog post by zzzeek (the SQLAlchemy author who's also participating in our discussion here) about precisely this issue a while ago. Not sure but it might have been this one:
https://techspot.zzzeek.org/2015/02/15/asynchronous-python-and-databases/
yes, not just the memory... for models with lots of fields, in Python+Django, the time overhead of instantiating these complex heavyweight objects can really add up if you have a whole 'page' of them
I always figured the main overhead was doing I/O with the db, but in these cases it's not always so
interesting blog post about this from the creator of SQLAlchemy:
http://techspot.zzzeek.org/2015/02/15/asynchronous-python-and-databases/
I'm just waiting for zzzeek to publish a blog post on how to implement this on top of sqlalchemy core, like this post [1]. :) Seriously though, sqlalchemy ORM layer already does this. Why not leverage all the existing features and support it has? Also, it's an easier sell for a $100 piece of software: "If you're using sqlalchemy, you can migrate to pony immediately."
[1] http://techspot.zzzeek.org/2011/05/17/magic-a-new-orm/