Fast psycopg2 for PyPy

When you need to use Postgres under CPython, you are likely to use psycopg2, a libpq wrapper. It is fast and stable, and is used by Django. However, this module does not work under PyPy, because it is a CPython extension, and not all such modules work under PyPy. And even if they work, they are slow, because PyPy is emulating CPython internals to make them work. The best known option for PyPy is psycopg2ct – a ctypes port (by Michael van Tellingen) of Alex Gaynor’s psycopg2 reimplementation in RPython. It works, but is slower than psycopg2, cause calling C code via ctypes is not that fast under PyPy.

But generally PyPy is very performant: it is 5.7x faster than CPython on a set of real-life benchmarks. Unlike cython or numba this is a full and very compliant implementation of Python language. Except for CPython extension modules, it can be used as a drop-in replacement for CPython. It is very efficient on optimizing numeric code (here it can be close to C), and is also good on typical object-oriented code with a lot of absctactions: it can remove object allocation, inline calls, etc. PyPy uses a just-in-time compilter (JIT), that generates efficient assembler on the fly.

In one of our projects, PyPy is 3x faster than CPython, and mostly uses less memory. But in some cases, database access is the bottleneck. And people are much more sensetive to performance losses than to performance gains – so this slow interface with Postgres was one of the main obstacles preventing us from using PyPy in production.

So I decided to port psycopg2ct to cffi, which is a brand new way of calling C code from Python, that works under CPython 2.x-3.x, and PyPy. cffi was created by core PyPy developers Armin Rigo and Maciej Fijałkowski. It is very fast under PyPy and easy to use. You just have to copy-paste stuff you need from C header files or man pages, and can call C code. Check cffi docs for the details. The speed of calling C functions is essential here, because libpq library (C interface for Postgres) requires you, for example, to make several C calls for each field in each row you fetch.

The porting went smooth (I will talk about it in the last section), and allowed PyPy to outperform CPython on database benchmarks!

Benchmarks

Skipping the usual disclaimers about benchmarks, here is what I did. I wrote a simple django benchmark, that measures some common operations:

  • creating objects in bulk (using model.objects.bulk_create)
  • creating objects one at a time (model.objects.create)
  • updating objects one at a time (instance.save)
  • querying objects one at a time (accessing ForeignKey field)
  • selecting a lot of objects using model.objects.all
  • selecting a lot of objects using cursor.fetchall

The whole benchmark is tiny, you can just read it here bench.py, and the installation instructions are on github https://github.com/chtd/psycopg2-benchmarks. The goal was not to measure the raw speed of Postgres bindings, but to evaluate them in a way similar to the way they are used in real-life web applications.

The benchmark was run in three different settings:

  • CPython with psycopg2 – the most common now
  • PyPy with psycopg2ct – the best known option for PyPy, I think
  • PyPy with psycopg2cffi – brand new bindings under PyPy

I used CPython 2.7.3 for psycopg2, PyPy trunk pypy-c-jit-58548-12f7aa38e6e2-osx64 for psycopg2cffi, and PyPy 1.9 for psycopg2ct (ctypes a a little bit slower in PyPy trunk now). All tests were run on Intel Core i7 with an SSD drive, using Postgres 9.1. In order to use psycopg2ct, I had to add a workaround for this bug. There were 5000 objects in two models. The test was run several times, giving PyPy JIT some time to warm up. The numbers below are from the second to the best result.

Raw results (time in seconds, less is better):

test PyPy, pg2cffi CPython, pg2 PyPy, pg2ct
bulk insert 0.769 1.305 0.848
many inserts 0.548 0.736 0.607
many updates 0.479 1.092 0.565
many selects 0.909 2.576 1.274
select all 0.669 1.119 1.327
fetchall 1.151 0.935 2.488
total 4.527 7.765 7.112

And also as a diagram:

Benchmark results

As we can see, PyPy + psycopg2cffi is the winner here, although CPython + psycopg2 is still a little bit faster doing select using cursor.fetchall (which is almost the same as doing objects.values_list, where django overhead is minimal). So probably psycopg2cffi bindings are still a little bit slower than psycopg2, but they are almost never used in isolation, so fast PyPy can compensate here. Also, psycopg2ct is much slower than psycopg2 when selecting values – this was the blocker for us.

Maybe it is possible to improve performance here, and I will try to do it. But PyPy is already faster than CPython when working with Postgres, which is great.

Experience of porting from ctypes to cffi

Porting went much easies then I feared. I was able to pass most of the tests after about 3 hours, having never worked with cffi and without much C experience. psycopg2ct has excellent tests, which helped a lot. Also cffi is really easy to use, has good docs and examples, and pypy developers are very helpful.

The main steps were:

  • copy-pasted relevant stuff from libpq header file, made it import
  • started running tests and fixing stuff. The main differences between ctypes and cffithat I encountered:
    • when a C function return NULL, ctypes returns None, and cffi returns something equal to ffi.NULL.
    • when a C function returns a char *, ctypes returns a Python string, or None, but cffi returns a object, that might be equal to ffi.NULL. You can extract a Python string (if this is a null-terminated string), or a buffer (if it is binary data).
    • you also have to change how callbacks are passed and how C objects are created, but it is easy, because here interfaces of ctypes and cffi are just incompatible, so it is easy to see errors and fix them.
    • if you declare that the C function returns an enum, then cffi will represent (at least in some cases) return value as a Python string, that is the name of enum value. To make it return an int (as in ctypes) just declare your C function as returning int. It is written in the docs, but I did not notice it and asked on #pypy irc-channel, and got the answer immediately. Thank you!
  • fixed segfaults – there were several segfaults and memory corruptions, that luckily were easy enough to solve and debug in Python – just read how cffi manages memory.
  • tested on 64 bit, found some bugs with C types and fixed them
  • reviewed changes, found some more possible bugs, fixed them and improved tests
  • tested under CPython, found a little bug in cffi
  • packaged it according to cffi docs on distributing
  • wrote some benchmarks, quickly checked profiles with jitviewer, improved getting results from libpq a little bit

Install

Installation requirements are the same as for psycopg2 – you must have libpq headers, pg_config somewhere on your $PATH. Installation was tested on Ubuntu 12.04 and CentOS (RHEL 5.0). It should be possible to make it work on Windows, but I did not even test it.

psycopg2cffi module is only tested with Python 2.6+ and PyPy 2.0 (there beta is already out, and it is already stable enough). Install using pip:

In order to use it instead of psycopg2 module, create somewhere on your python path (for example, site-packages of your virtual env) a file named psycopg2.py with the following:

Feedback

Send comments to twitter @python_chtd and bugs/pull requests to github.

 
  • http://twitter.com/lopuhin Kostia Lopuhin

    Подключили комментрии через disqus – теперь комментировать можно прямо тут)

  • http://twitter.com/lopuhin Kostia Lopuhin

    Подключили комментрии через disqus – теперь комментировать можно прямо тут

  • Alexey Diyan

    Thank you for this review. It was interesting to know how real world project was ported from ctypes to cffi.

    In present time I’m mostly focused on CPython implementation but considering use cffi for writing Kerberos binding by my own which is reqired for pywinrm project – https://github.com/diyan/pywinrm

    The only things worried me is I have to add additional dependency for pywinrm project.

    Do you know any issues with cffi on Windows OS?

    • http://twitter.com/lopuhin Kostia Lopuhin

      Thank you! I have not tried using cffi on Windows, but it is supported, and I’ve heard someone using cffi on windows in a game project. Also, cffi is likely to become part of stdlib in the future. For more specific info – maybe try asking on #pypy irc channel or pypy-dev mailing list?

  • lasi

    what about psycopg2cffi under cpython? can u show the numbers?

    • Kostia Lopuhin

      I ran the benchmarks under CPython too, but don’t remember the numbers off-hand. Should be noticeably slower than psycopg2, at least for reading. You can try to run the benchmarks https://github.com/chtd/psycopg2-benchmarks/



+7 (495) 646-87-45

info@chtd.ru

Bldg.1, #34, Verkhnyaya str., Moscow, Russia

Map