What is Gerrit?

I don't consider myself to be any authority on Gerrit, so my description here is based on my working impression of what it does; the details may not be entirely accurate. Gerrit is a "code review" tool, intended to allow collaboration around code changes targeted at a project. At that level, what Gerrit is doing can be compared to a pull request - allows proposal of a change, shows you how it's different from what's there already, allows comments on the change including against specific code, and then provides workflow to allow the change to be merged into the code repository. For those familiar with pull requests, this seems exactly the same, yet while I've always been very unsatisifed with pull requests for a long time, I am completely satisifed with Gerrit's model. So to understand that requires more understanding of Gerrit.

At its core, Gerrit is maintaining a set of git repositories that are basically mirrors of your projects' actual git repositories. Then, it adds some additional, non-mirrored (e.g. only to its local copy) reference paths to these git repositories such that you can push commits to them which represent Code Reviews. These commits resemble feature branches in that they are branched off of "master" or another maintenance branch, but the way we work with them is different. The most immediate difference is that unlike a traditional feature branch, the individual "code review" is always a single commit containing the entire new feature all at once, whereas a feature branch may consist of any number of commits. In order to allow additional changes and refinements to the "Code Review" as it proceeds, you create a brand new changeset that encompasses the previous changeset completely, plus your new changes. Since there is no longer a linear history in Git between these refinements, Gerrit adds an additional "Change-Id" identifier to the commit message which is how Gerrit keeps track of each change in a code review. So instead of a long-lived feature branch that changes by adding new revisions to the end of it, you have a series of discrete all-at-once commits, each one containing the whole feature at once.

In ASCII art parlance, the traditional Git branching model, also used by pull requests, can be seen like this:

rev 1 -> rev 2 -> rev 3 -> rev 4 -> rev 5          -> master
                    |
                    +-> rev 3a -> rev 3b -> rev 3c -> feature branch

Above, the feature branch is forked off from the code at some point, and as development continues, a new revision is added to the feature branch. The multiple commits of the feature branch will eventually be merged into master, either using a traditional merge, or by "rebasing" the feature branch onto the top of master.

In Gerrit, the open-ended nature of Git is taken advantage of in an entirely different way, and the mapping of Gerrit to Git might look more like this:

rev 1 -> rev 2 -> rev 3 -> rev 4 -> rev 5 -> master
                    |
                    +-> rev 3a
                    |     |
                    +-> rev 3a/3b
                    |     |
                    +-> rev 3a/3b/3c
                          |
                          v
                       Change Id: Iabcdef

The above model would be difficult to work with using Git alone; Gerrit maintains the list of changesets conforming to a Change Id in its own database, and provides a full blown GUI and command line API on top so that the history of development of the "Feature" is very clear and easy to work with.

Getting Used to It

The major caveat with this whole approach is that there's a non-trivial conceptual hill to climb in order to use it, particularly if you're not accustomed to "git rebase" (as I wasn't). When we are in our code change that we've pushed as a code review, and we want to revise it and push it up as a new version of that code review, we need to replace the old commit with the new one, not add it on as additional history. The most fundamental thing this means is that instead of saying git commit, we have to say, git commit --amend, meaning, squash our current changes into the most recent commit. The hash of the recent commit is replaced with a brand new one, and the old commit is essentially floating, except for the fact that Gerrit will track it.

When we amend a single commit, we get the same commit message and we basically leave it alone; the commit message represents what the change will be a whole, not the little adjustment we're making here. The commentary about adjustments to our feature occurs in the comment stream on the review itself. With Gerrit's approach, you no longer have commits like, "fix whitespace", "add test", "add documentation" - at the end of the day there will be just one commit message with, "Add Feature XYZ; XYZ does QPR ...". I consider that an advantage, because intermediary commits within a feature branch like "fix whitespace" are really just noise; I don't miss them.

The other thing that has to be dealt with is that Gerrit won't allow you to push up a code review that can't be cleanly merged into the working branch it will be a part of. Again, this is a conceptually odd thing to adjust to but when you start doing it you realize what a great idea it is; when we work with tradtional feature branches and pull requests, there's always that time when we realize we've fallen so far behind master, and oh now we have to merge master into our branch, and fix all the conflicts, so that we can merge back up later without making a huge mess.

With Gerrit's approach, nothing ever gets pushed up that can't be immediately merged, which means if you're targeting a project that has a lot of activity, you'll find yourself having to rebase your code reviews every time - but the key word here is "rebase". Instead of merging master back into our feature branch, we are doing a straight up rebase of our single commit against the state of master; and because there's only one commit to deal with, this is usually a very simple process - we aren't burdened with trying to knit the changesets together in just the right way, or worrying about awkward merge artifacts cluttering up our feature branch forever; we flatten everything into our single changeset, and all the clutter of how we merged things together is discarded. As long as you're comfortable with "git rebase", it's a predictable and consistent process. If you are using Gerrit for any amount of time, you will be very comfortable with rebasing :).

Advantages

The advantages to the above Gerrit model are in my opinion huge wins, including:

1. You always have a feature packaged up as a single, clean commit, so that even with "git show", you can see the change represented by the feature all at once in its entirety, with no stream of trivial "work in progress" commits cluttering it up. Because Gerrit maintains its own database of past versions and commentary, the history of how this change was developed is persisted permanently, but outside of your Git repository.
2. You don't clutter up your Git repository with tons of old feature branches. Git of course allows you to delete feature branches, but then you lose all the change history. With Gerrit you get to hold on to a permanent record of how a new feature or change was produced and none of it clutters up your main repository.
3. Any number of developers can collaborate on a single change with no bumps in the process. This is something that is just not practical at all with pull requests; if a user submits a pull request to me, and I'd like to add some changes to it myself, the usual answer is that I need to pull that pull request into my own feature branch somewhere, and change it there - totally separated from where the original pull request is. Or, the developer of that pull request can opt to give my account write access to their repository; however in practice, I've never seen anyone do this, and it also means I'm awkwardly working on my own project via someone else's account. This is the most continuously frustrating thing about pull requests and is a key reason I'm so much happier with Gerrit. With pull requests I often found myself having to painstakingly describe exactly how some part of code should look in order to get the submitter to do it in their branch, so that I wouldn't have to "take it over" and chase them away forever. With a Gerrit code review I can just push up a modification to their change, and the contributor can jump right back on with no bump in continuity. When I've made a lot of changes to someone's change I add "Co-Authored By: " with my name to the commit message so that a viewer can see it was a two-person job.
4. The "change as a single commit" model means we no longer have to deal with pull requests that consist of two dozen changesets, re-merges of master, weird merge artifacts like where you see zillions of merged commits interspersed with the ones you care about, and so on. When I go to "git merge" someone's pull request, I usually need to go through the effort to manually squash it and add my own changelog notes, which means that the Github or Bitbucket UX has no record at all of me "merging" their pull request, and most horribly on Bitbucket I have to explicitly mark the PR as "DECLINED", when meanwhile I just wanted to squash it. Gerrit on the other hand is built for rebasing and squashing from the ground up, and represents an accurate record of exactly what was merged, with no chance that I went off and modified someone's pull request locally after the fact in a short-lived feature branch. Everything on a code contribution from start to final merge to master is visible exactly within the Gerrit UX, with complete accuracy and permanence.
5. The workflow model of Gerrit is far richer than that of a pull request and is also fully customizable. Different roles, such as that of developers, contributors, and automated CI systems, can each get their own workflow flags each with different meanings. Typically, a core developer of the project needs to assign a "+2 Approved" flag, the CI systems in play need to assign "+1 Verified" and in Openstack and on my own system there's additionally a "+1 Ready to Merge" flag; when all those are up, you press "submit" and the code review is pushed to the target branch, and its done. With Bitbucket and Gerrit pull requests, I never pushed the button, as I was always having to add changelog notes, make fixes, squash it, fixing merge conflicts, all of which I can now push straight into that same code review under one consistent interface.
6. Integration with Jenkins is way better for Gerrit than it is with Github, and for Bitbucket I've not even been able to find any Jenkins integration. Once I pull a code change into Gerrit I can easily grant the contributor access to continue working on it, or disallow changes, so that I can on a per-user basis decide who gets to push new changes that automatically kick off builds and not have to worry that someone will submit a malicious pull request when I'm not looking. With the Jenkins Github pull request plugin, it was simply not an option to enable automatic CI unless I were using something like Travis, which currently I'm not.

Overall, with Gerrit I now have a single, consistent way to do all new code; all of my feature branches, all of my random .patch files sitting in my home directory, pull requests from github or bitbucket; all of it goes straight into Gerrit where I have them all under one interface and where changes, history, code review, workflow, and comments on them are permanent, without creating any branch junk in my main git repo!

Gerrit has a ton more features as well, including an extremely detailed permissioning model which you can map to specific paths in each git repo; a Lucene-powered search feature, a replication engine so that changes to the local git repo can be pushed anywhere else; it is itself a full blown http and ssh server for the git repositories as well as for its command-line and GUI interfaces, and generally has all-around industrial style features.

Where Gerrit is falling short is that on the "self-install" side, it is definitely a little obtuse and unforgiving about things. It was not that easy to set up, and you'll likely have to spend a lot of time reading stack traces in the logs to figure out various issues - documentation, links to packages, and other online help is a little scattered and sometimes incomplete, especially for the plugins. It's not very user friendly on the adminstration side yet. But hosting it yourself is still the way to go, so that when you hit a view like "all", you see just your projects and not a huge list of other people's projects.

Implementation

The move to self-hosting services again is kind of a pendulum swing for me; a couple of years ago I was entirely gleeful to move my issue tracking off of Trac and onto Bitbucket, but i think the reason for that was mostly due to Trac's inability to limit spam accounts as well as a burdensome performance model and very little upstream development. With Gerrit, I'm self-hosting again, however all authorization is pushed up to Github with OAuth, so that I'm not dealing with fake user accounts and so far not any spammers either.

I'm using the Gerrit Oauth plugin to provide Github logins, and this plugin also includes support for Google Oauth which I haven't turned on yet, and Bitbucket OAuth which I couldn't get working.

For integration with community-supplied code submissions, I am still using pull requests as the primary entrypoint into the system. Github and Bitbucket require that you leave the "pull request" button on in any case, so people are naturally going to send you code on both of them no matter what you say, so we might as well use them. The pull request allows me to have a quick preview of what we're dealing with, and I then import it into Gerrit using a semi-manual process. To achieve this consistently, I've created the prtogerrit script which communicates with both the Github and Bitbucket APIs to pull in any target pull request, squash it, push it into Gerrit as a new review, and add comments to the pull request referring the contributor to our Gerrit registration page. Pull requests submitted through Bitbucket still necessarily get that big ugly "DECLINED" status, however it is now consistent for all BB pull requests and the messaging is clear that the work is continued on Gerrit.

Sum Up

Since I've been using Gerrit, I've been surprised at how much more productive I became; being able to see every code change being worked on by anyone all under one interface is very freeing, and the workflow that lets me have the entire change from implementation to tests to changelog and migration notes in one clean, mergable commit, fully run through multiple Jenkins CI jobs before it gets merged has made me totally comfortable pushing a big red "merge" button at the end, which was never the case with pull requests and other ad-hoc patchfiles and feature branches.

What is Asynchronous IO?

Asynchronous IO is an approach used to achieve concurrency by allowing processing to continue while responses from IO operations are still being waited upon. To achieve this, IO function calls are made to be non blocking, so that they return immediately, before the actual IO operation is complete or has even begun. A typically OS-dependent polling system (such as epoll) is used within a loop in order to query a set of file descriptors in search of the next one which has data available; when located, it is acted upon, and when the operation is complete, control goes back to the polling loop in order to act upon the next descriptor with data available.

Non-blocking IO in its classical use case is for those cases where it's not efficient to dedicate a thread of execution towards waiting for a socket to have results. It's an essential technique for when you need to listen to lots of TCP sockets that are arbitrarily "sleepy" or slow - the best example is a chat server, or some similar kind of messaging system, where you have lots of connections connected persistently, only sending data very occasionally; e.g. when a connection actually sends data, we consider it to be an "event" to be responded to.

In recent years, the asynchronous IO approach has also been successfully applied to HTTP related servers and applications. The theory of operation is that a very large number of HTTP connections can be efficiently serviced without the need for the server to dedicate threads to wait on each connection individually; in particular, slow HTTP clients need not get in the way of the server being able to serve lots of other clients at the same time. Combine this with the renewed popularity of so-called long polling approaches, and non-blocking web servers like nginx have proven to work very well.

Asynchronous IO and Scripting

Asynchronous IO programming in scripting languages is heavily centered on the notion of an event loop, which in its most classic form uses callback functions that receive a call once their corresponding IO request has data available. A critical aspect of this type of programming is that, since the event loop has the effect of providing scheduling for a series of functions waiting for IO, a scripting language in particular can replace the need for threads and OS-level scheduling entirely, at least within a single CPU. It can in fact be a little bit awkward to integrate multithreaded, blocking IO code with code that uses non-blocking IO, as they necessarily use different programming approaches when IO-oriented methods are invoked.

The relationship of asynchronous IO to event loops, combined with its growing popularity for use in web-server oriented applications as well as its ability to provide concurrency in an intuitive and obvious way, found itself hitting a perfect storm of factors for it to become popular on one platform in particular, Javascript. Javascript was designed to be a client side scripting language for browsers. Browsers, like any other GUI app, are essentially event machines; all they do is respond to user-initiated events of button pushes, key presses, and mouse moves. As a result, Javascript has a very strong concept of an event loop with callbacks and, until recently, no concept at all of multithreaded programming.

As an army of front-end developers from the 90's through the 2000's mastered the use of these client-side callbacks, and began to use them not just for user-initiated events but for network-initiated events via AJAX connections, the stage was set for a new player to come along, which would transport the ever growing community of Javascript programmers to a new place...

The Server

Node.js is not the first attempt to make Javascript a server side language. However, a key reason for its success was that there were plenty of sophisticated and experienced Javascript programmers around by the time it was released, and that it also fully embraces the event-driven programming paradigm that client-side Javascript programmers are already well-versed in and comfortable with.

In order to sell this, it followed that the "non-blocking IO" approach needed to be established as appropriate not just for the classic case of "tending to lots of usually asleep or arbitrarily slow connections", but as the de facto style in which all web-oriented software should be written. This meant that any network IO of any kind now had to be interacted with in a non-blocking fashion, and this of course includes database connections - connections which are normally relatively few per process, with numbers of 10-50 being common, are usually pooled so that the latency associated with TCP startup is not much of an issue, and for which the response times for a well-architected database, naturally served over the local network behind the firewall and often clustered, are extremely fast and predictable - in every way, the exact opposite of the use case for which non-blocking IO was first intended. The Postgresql database supports an asynchronous command API in libpq, stating a primary rationale for it as - surprise! using it in GUI applications.

node.js already benefits from an extremely performant JIT-enabled engine, so it's likely that despite this repurposing of non-blocking IO for a case in which it was not intended, scheduling among database connections using non-blocking IO works acceptably well. (authors note: the comment here regarding libuv's thread pool is removed, as this only regards file IO.)

The Spectre of Threads

Well before node.js was turning masses of client-side Javascript developers into async-only server side programmers, the multithreaded programming model had begun to make academic theorists complain that they produce non-deterministic programs, and asynchronous programming, having the side effect that the event-driven paradigm effectively provides an alternative model of programming concurrency (at least for any program with a sufficient proportion of IO to keep context switches high enough), quickly became one of several hammers used to beat multithreaded programming over the head, centered on the two critiques that threads are expensive to create and maintain in an application, being inappropriate for applications that wish to tend to hundreds or thousands of connections simultaneously, and secondly that multithreaded programming is difficult and non-deterministic. In the Python world, continued confusion over what the GIL does and does not do provided for a natural tilling of land fertile for the async model to take root more strongly than might have occurred in other scenarios.

How do you like your Spaghetti?

The callback style of node.js and other asynchronous paradigms was considered to be problematic; callbacks organized for larger scale logic and operations made for verbose and hard-to-follow code, commonly referred to as callback spaghetti. Whether callbacks were in fact spaghetti or a thing of beauty was one of the great arguments of the 2000's, however I fortunately don't have to get into it because the async community has clearly acknowledged the former and taken many great steps to improve upon the situation.

In the Python world, one approach offered in order to allow for asyncrhonous IO while removing the need for callbacks is the "implicit async IO" approach offered by eventlet and gevent. These take the approach of instrumenting IO functions to be implicitly non-blocking, organized such that a system of green threads may each run concurrently, using a native event library such as libev to schedule work between green threads based on the points at which non-blocking IO is invoked. The effect of implicit async IO systems is that the vast majority of code which performs IO operations need not be changed at all; in most cases, the same code can literally be used in both blocking and non-blocking IO contexts without any changes (though in typical real-world use cases, certainly not without occasional quirks).

In constrast to implicit async IO is the very promising approach offered by Python itself in the form of the previously mentioned asyncio library, now available in Python 3. Asyncio brings to Python fully standardized concepts of "futures" and coroutines, where we can produce non-blocking IO code that flows in a very similar way to traditional blocking code, while still maintaining the explicit nature of when non blocking operations occur.

SQLAlchemy? Asyncio? Yes?

Now that asyncio is part of Python, it's a common integration point for all things async. Because it maintains the concepts of meaningful return values and exception catching semantics, getting an asyncio version of SQLAlchemy to work for real is probably feasible; it will still require at least several external modules that re-implement key methods of SQLAlchemy Core and ORM in terms of async results, but it seems that the majority of code, even within execution-centric parts, can stay much the same. It no longer means a rewrite of all of SQLAlchemy, and the async aspects should be able to remain entirely outside of the central library itself. I've started playing with this. It will be a lot of effort but should be doable, even for the ORM where some of the patterns like "lazy loading" will just have to work in some more verbose way.

However. I don't know that you really would generally want to use an async-enabled form of SQLAlchemy.

Taking Async Web Scale

As anticipated, let's get into where it's all going wrong, especially for database-related code.

Issue One - Async as Magic Performance Fairy Dust

Many (but certainly not all) within both the node.js community as well as the Python community continue to claim that asynchronous programming styles are innately superior for concurrent performance in nearly all cases. In particular, there's the notion that the context switching approaches of explicit async systems such as that of asyncio can be had virtually for free, and as the Python has a GIL, that all adds up in some unspecified/non-illustrated/apples-to-oranges way to establish that asyncio will totally, definitely be faster than using any kind of threaded approach, or at the very least, not any slower. Therefore any web application should as quickly as possible be converted to use a front-to-back async approach for everything, from HTTP request to database calls, and performance enhancements will come for free.

I will address this only in terms of database access. For HTTP / "chat" server styles of communication, either listening as a server or making client calls, asyncio may very well be superior as it can allow lots more sleepy/arbitrarily slow connections to be tended towards in a simple way. But for local database access, this is just not the case.

1. Python is Very , Very Slow compared to your database

Update - redditor Riddlerforce found valid issues with this section, in that I was not testing over a network connection. Results here are updated. The conclusion is the same, but not as hyperbolically amusing as it was before.

Let's first review asynchronous programming's sweet spot, the I/O Bound application:

I/O Bound refers to a condition in which the time it takes to complete a computation is determined principally by the period spent waiting for input/output operations to be completed. This circumstance arises when the rate at which data is requested is slower than the rate it is consumed or, in other words, more time is spent requesting data than processing it.

A great misconception I seem to encounter often is the notion that communication with the database takes up a majority of the time spent in a database-centric Python application. This perhaps is a common wisdom in compiled languages such as C or maybe even Java, but generally not in Python. Python is very slow, compared to such systems; and while Pypy is certainly a big help, the speed of Python is not nearly as fast as your database, when dealing in terms of standard CRUD-style applications (meaning: not running large OLAP-style queries, and of course assuming relatively low network latencies). As I worked up in my PyMySQL Evaluation for Openstack, whether a database driver (DBAPI) is written in pure Python or in C will incur significant additional Python-level overhead. For just the DBAPI alone, this can be as much as an order of magnitude slower. While network overhead will cause more balanced proportions between CPU and IO, just the CPU time spent by Python driver itself still takes up twice the time as the network IO, and that is without any additional database abstraction libraries, business logic, or presentation logic in place.

This script, adapted from the Openstack entry, illustrates a pretty straightforward set of INSERT and SELECT statements, and virtually no Python code other than the barebones explicit calls into the DBAPI.

MySQL-Python, a pure C DBAPI, runs it like the following over a network:

DBAPI (cProfile):  <module 'MySQLdb'>
     47503 function calls in 14.863 seconds
DBAPI (straight time):  <module 'MySQLdb'>, total seconds 12.962214

With PyMySQL, a pure-Python DBAPI,and a network connection we're about 30% slower:

DBAPI (cProfile):  <module 'pymysql'>
     23807673 function calls in 21.269 seconds
DBAPI (straight time):  <module 'pymysql'>, total seconds 17.699732

Running against a local database, PyMySQL is an order of magnitude slower than MySQLdb:

DBAPI:  <module 'pymysql'>, total seconds 9.121727

DBAPI:  <module 'MySQLdb'>, total seconds 1.025674

To highlight the actual proportion of these runs that's spent in IO, the following two RunSnakeRun displays illustrate how much time is actually for IO within the PyMySQL run, both for local database as well as over a network connection. The proportion is not as dramatic over a network connection, but in that case network calls still only take 1/3rd of the total time; the other 2/3rds is spent in Python crunching the results. Keep in mind this is just the DBAPI alone; a real world application would have database abstraction layers, business and presentation logic surrounding these calls as well:

Local connection - clearly not IO bound.

Network connection - not as dramatic, but still not IO bound (8.7 sec of socket time vs. 24 sec for the overall execute)

Let's be clear here, that when using Python, calls to your database, unless you're trying to make lots of complex analytical calls with enormous result sets that you would normally not be doing in a high performing application, or unless you have a very slow network, do not typically produce an IO bound effect. When we talk to databases, we are almost always using some form of connection pooling, so the overhead of connecting is already mitigated to a large extent; the database itself can select and insert small numbers of rows very fast on a reasonable network. The overhead of Python itself, just to marshal messages over the wire and produce result sets, gives the CPU plenty of work to do which removes any unique throughput advantages to be had with non-blocking IO. With real-world activities based around database operations, the proportion spent in CPU only increases.

2. AsyncIO uses appealing, but relatively inefficient Python paradigms

At the core of asyncio is that we are using the @asyncio.coroutine decorator, which does some generator tricks in order to have your otherwise synchronous looking function defer to other coroutines. Central to this is the yield from technique, which causes the function to stop its execution at that point, while other things go on until the event loop comes back to that point. This is a great idea, and it can also be done using the more common yield statement as well. However, using yield from, we are able to maintain at least the appearance of the presence of return values:

@asyncio.coroutine
def some_coroutine():
    conn = yield from db.connect()
    return conn

That syntax is fantastic, I like it a lot, but unfortunately, the mechanism of that return conn statement is necessarily that it raises a StopIteration exception. This, combined with the fact that each yield from call more or less adds up to the overhead of an individual function call separately. I tweeted a simple demonstration of this, which I include here in abbreviated form:

def return_with_normal():
    """One function calls another normal function, which returns a value."""

    def foo():
        return 5

    def bar():
        f1 = foo()
        return f1

    return bar

def return_with_generator():
    """One function calls another coroutine-like function,
    which returns a value."""

    def decorate_to_return(fn):
        def decorate():
            it = fn()
            try:
                x = next(it)
            except StopIteration as y:
                return y.args[0]
        return decorate

    @decorate_to_return
    def foo():
        yield from range(0)
        return 5

    def bar():
        f1 = foo()
        return f1

    return bar

return_with_normal = return_with_normal()
return_with_generator = return_with_generator()

import timeit

print(timeit.timeit("return_with_generator()",
    "from __main__ import return_with_generator", number=10000000))
print(timeit.timeit("return_with_normal()",
    "from __main__ import return_with_normal", number=10000000))

The results we get are that the do-nothing yield from + StopIteration take about six times longer:

yield from: 12.52761328802444
normal: 2.110536064952612

To which many people said to me, "so what? Your database call is much more of the time spent". Never minding that we're not talking here about an approach to optimize existing code, but to prevent making perfectly fine code more slow than it already is. The PyMySQL example should illustrate that Python overhead adds up very fast, even just within a pure Python driver, and in the overall profile dwarfs the time spent within the database itself. However, this argument still may not be convincing enough.

So, I will here present a comprehensive test suite which illustrates traditional threads in Python against asyncio, as well as gevent style nonblocking IO. We will use psycopg2 which is currently the only production DBAPI that even supports async, in conjunction with aiopg which adapts psycopg2's async support to asyncio and psycogreen which adapts it to gevent.

The purpose of the test suite is to load a few million rows into a Postgresql database as fast as possible, while using the same general set of SQL instructions, such that we can see if in fact the GIL slows us down so much that asyncio blows right past us with ease. The suite can use any number of connections simultaneously; at the highest I boosted it up to using 350 concurrent connections, which trust me, will not make your DBA happy at all.

The results of several runs on different machines under different conditions are summarized at the bottom of the README. The best performance I could get was running the Python code on one laptop interfacing to the Postgresql database on another, but in virtually every test I ran, whether I ran just 15 threads/coroutines on my Mac, or 350 (!) threads/coroutines on my Linux laptop, threaded code got the job done much faster than asyncio in every case (including the 350 threads case, to my surprise), and usually faster than gevent as well. Below are the results from running 120 threads/processes/connections on the Linux laptop networked to the Postgresql database on a Mac laptop:

Python2.7.8 threads (22k r/sec, 22k r/sec)
Python3.4.1 threads (10k r/sec, 21k r/sec)
Python2.7.8 gevent (18k r/sec, 19k r/sec)
Python3.4.1 asyncio (8k r/sec, 10k r/sec)

Above, we see asyncio significantly slower for the first part of the run (Python 3.4 seemed to have some issue here in both threaded and asyncio), and for the second part, fully twice as slow compared to both Python2.7 and Python3.4 interpreters using threads. Even running 350 concurrent connections, which is way more than you'd usually ever want a single process to run, asyncio could hardly approach the efficiency of threads. Even with the very fast and pure C-code psycopg2 driver, just the overhead of the aiopg library on top combined with the need for in-Python receipt of polling results with psycopg2's asynchronous library added more than enough Python overhead to slow the script right down.

Remember, I wasn't even trying to prove that asyncio is significantly slower than threads; only that it wasn't any faster. The results I got were more dramatic than I expected. We see also that an extremely low-latency async approach, e.g. that of gevent, is also slower than threads, but not by much, which confirms first that async IO is definitely not faster in this scenario, but also because asyncio is so much slower than gevent, that it is in fact the in-Python overhead of asyncio's coroutines and other Python constructs that are likely adding up to very significant additional latency on top of the latency of less efficient IO-based context switching.

def transfer(amount, payer, payee, server):
    if not payer.sufficient_funds_for_withdrawal(amount):
        raise InsufficientFunds()
    log("{payer} has sufficient funds.", payer=payer)
    payee.deposit(amount)
    log("{payee} received payment", payee=payee)
    payer.withdraw(amount)
    log("{payer} made payment", payer=payer)
    server.update_balances([payer, payee])

@coroutine
def transfer(amount, payer, payee, server):
    if not payer.sufficient_funds_for_withdrawal(amount):
        raise InsufficientFunds()
    log("{payer} has sufficient funds.", payer=payer)
    payee.deposit(amount)
    log("{payee} received payment", payee=payee)
    payer.withdraw(amount)
    log("{payer} made payment", payer=payer)
    yield from server.update_balances([payer, payee])

cursor.execute(
    "select id from geo_record where fileid=%s and logrecno=%s",
    (item['fileid'], item['logrecno'])
)
row = cursor.fetchone()
geo_record_id = row[0]

cursor.execute(
    "select d.id, d.index from dictionary_item as d "
    "join matrix as m on d.matrix_id=m.id where m.segment_id=%s "
    "order by m.sortkey, d.index",
    (item['cifsn'],)
)
dictionary_ids = [
    row[0] for row in cursor
]
assert len(dictionary_ids) == len(item['items'])

for dictionary_id, element in zip(dictionary_ids, item['items']):
    cursor.execute(
        "insert into data_element "
        "(geo_record_id, dictionary_item_id, value) "
        "values (%s, %s, %s)",
        (geo_record_id, dictionary_id, element)
    )

yield from cursor.execute(
    "select id from geo_record where fileid=%s and logrecno=%s",
    (item['fileid'], item['logrecno'])
)
row = yield from cursor.fetchone()
geo_record_id = row[0]

yield from cursor.execute(
    "select d.id, d.index from dictionary_item as d "
    "join matrix as m on d.matrix_id=m.id where m.segment_id=%s "
    "order by m.sortkey, d.index",
    (item['cifsn'],)
)
rows = yield from cursor.fetchall()
dictionary_ids = [row[0] for row in rows]

assert len(dictionary_ids) == len(item['items'])

for dictionary_id, element in zip(dictionary_ids, item['items']):
    yield from cursor.execute(
        "insert into data_element "
        "(geo_record_id, dictionary_item_id, value) "
        "values (%s, %s, %s)",
        (geo_record_id, dictionary_id, element)
    )

def transfer(amount, payer, payee, server):
    with transaction.begin():
        if not payer.sufficient_funds_for_withdrawal(amount, lock=True):
            raise InsufficientFunds()
        log("{payer} has sufficient funds.", payer=payer)
        payee.deposit(amount)
        log("{payee} received payment", payee=payee)
        payer.withdraw(amount)
        log("{payer} made payment", payer=payer)
        server.update_balances([payer, payee])

Sum up!

Please note I am not trying to make the point that you shouldn't use asyncio. I think it's really well done, it's fun to use, and I still am interested in having more of a SQLAlchemy story for it, because I'm sure folks will still want this no matter what anyone says.

My point is that when it comes to stereotypical database logic, there are no advantages to using it versus a traditional threaded approach, and you can likely expect a small to moderate decrease in performance, not an increase. This is well known to many of my colleagues, but recently I've had to argue this point nonetheless.

An ideal integration situation if one wants to have the advantages of non-blocking IO for receiving web requests without needing to turn their business logic into explicit async is a simple combination of nginx with uWsgi, for example.