Million Rows Per Second From Postgres Using Python
asyncpg is the new Python open-source library for working with PostgreSQL. It was written using asyncio and Python 3.5. asyncpg is the fastest driver for working with PostgreSQL among similar implementations in Python, NodeJS and Go.
Why asyncpg?
We are creating EdgeDB , the next generation database, with PostgreSQL on the backend. We need high performance, low latency and additional features of PostgreSQL itself.
The most obvious option is to use psycopg2, the most popular Python driver for working with PostgreSQL. He has an excellent community, he is stable and time-tested. There is also aiopg, which implements an asynchronous interface, on top of psycopg2. Then the obvious question is - why write your bike? Short answer: PostgreSQL performance and features support. Below we will consider this in more detail.
Features
Data Type Support
Our main dissatisfaction with psycopg2 was the mediocre processing of various types of data, including arrays and composite types. Many different data types are one of the distinguishing features of PostgreSQL. And yet, out of the box, psycopg2 only supports simple data types - integers, strings, time and dates. It makes you write your types, for something else.
The main reason for this lies in the fact that psycopg2 communicates with the database server in text format, which is why a lot of data has to be parsed, especially for composite data types.
Unlike psycopg2, asyncpg uses the PostgreSQL binary I / O protocol, which, in addition to performance advantages, also has native support for container data types (arrays, range and compound types).
Prepared Requests
Asyncpg also uses prepared PostgreSQL statements. This is a great opportunity for optimization, as it avoids the repeated parsing, analysis and construction of a query plan. In addition, asyncpg caches I / O data for each prepared statement.
Prepared statements in asyncpg can be created and used directly. They provide an API for receiving and analyzing query results. Most request methods create a connection directly, and asyncpg creates and caches prepared requests.
Easy to deploy
Another important feature of asyncpg is the absence of dependencies. A direct implementation of the PostgreSQL protocol means that you do not need to install libpq. Simple enough to do
pip install asyncpg. In addition, we provide a package for manual assembly on Linux and macOS (Windows is planned in the future).Performance
It soon became clear to us that by implementing the PostgreSQL protocol directly, we can achieve a significant increase in speed. Our past experience with uvloop has shown that with Cython you can create efficient and productive libraries. asyncpg is fully written in Cython with memory management and high optimization. As a result, asyncpg was on average 3 times faster than psycopg2 (or aiopg).
Testing
As with uvloop , we created a separate utility for testing pgbench and reporting for asyncpg and other PostgreSQL driver implementations. We measured the speed of the request (lines per second) and the delay. The main purpose of these tests is to find out the overhead for this driver.
To be honest, all the tests were run in the same thread (GOMAXPROCS = 1 in the case of Golang) in asynchronous mode. Python drivers were launched using uvloop.
This test took place on a clean server with this configuration:
- CPU: Intel Xeon E5-1620 v2 @ 3.70GHz, 64GiB DDR3
- Gentoo Linux, GCC 4.9.3
- Go 1.6.3, Python 3.5.2, NodeJS 6.3.0
- PostgreSQL 9.5.2
Used drivers:
- Python: asyncpg-0.5.2, psycopg2-2.6.2, aiopg-0.10.0, uvloop-0.5.0. aiopg is a tiny wrapper over psycopg2, for asynchronous operation
- NodeJS: pg-6.0.0, pg-native-1.10.0
- Golang: github.com/lib/pg@4dd446efc1, github.com/jackc/pgx@b3eed3cce0
The graphs show the average values for the results obtained by running 4 different types of queries:
- Direct query to select all rows from the pg_type table (about 350 rows). It is pretty close to the total number of application requests. In this test, asyncpg shows the title performance of 1 million lines per second. More details.
- The request generates 1000 lines consisting of a single integer. This test is designed to view performance when creating records and getting results. More details.
- The request returns 100 rows, each of which contains 1 KB of binary data (blob). This is a stressful I / O test. More details.
- The query returns 100 rows, each of which contains an array of 100 integers. This test is designed to check the decoding speed of arrays. Here, asyncpg turned out to be slower than a faster golang implementation. This involves the cost of creating and deleting tuples in Python. More details.
Conclusion
We are confident that with the help of Python it is possible to create high-performance and scalable systems. To do this, we need to make efforts to create fast, high-quality drivers, event loops, and frameworks.
asyncpg is one of the steps in this direction. This is the result of meaningful design, fueled by our experience in creating uvloop, as well as the efficient use of Cython and asyncio.
Article translation 1M rows / s from Postgres to Python
