asyncpg vs psycopg2

asyncpg_vs_psycopg2.py

"""
a@MacBook-Pro ~ $ ab -c100 -n10000 http://127.0.0.1:8000/asyncpg/select
This is ApacheBench, Version 2.3 <$Revision: 1807734 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/

Benchmarking 127.0.0.1 (be patient)
Completed 1000 requests
Completed 2000 requests
Completed 3000 requests
Completed 4000 requests
Completed 5000 requests
Completed 6000 requests
Completed 7000 requests
Completed 8000 requests
Completed 9000 requests
Completed 10000 requests
Finished 10000 requests


Server Software:
Server Hostname:        127.0.0.1
Server Port:            8000

Document Path:          /asyncpg/select
Document Length:        49791 bytes

Concurrency Level:      100
Time taken for tests:   40.839 seconds
Complete requests:      10000
Failed requests:        0
Total transferred:      498840000 bytes
HTML transferred:       497910000 bytes
Requests per second:    244.86 [#/sec] (mean)
Time per request:       408.393 [ms] (mean)
Time per request:       4.084 [ms] (mean, across all concurrent requests)
Transfer rate:          11928.43 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.6      0      26
Processing:    40  406 158.8    405    1467
Waiting:       37  401 158.5    401    1462
Total:         40  407 158.8    406    1468

Percentage of the requests served within a certain time (ms)
  50%    406
  66%    412
  75%    419
  80%    423
  90%    442
  95%    757
  98%    777
  99%    796
 100%   1468 (longest request)
a@MacBook-Pro ~ $ ab -c100 -n10000 http://127.0.0.1:8000/psycopg2/select
This is ApacheBench, Version 2.3 <$Revision: 1807734 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/

Benchmarking 127.0.0.1 (be patient)
Completed 1000 requests
Completed 2000 requests
Completed 3000 requests
Completed 4000 requests
Completed 5000 requests
Completed 6000 requests
Completed 7000 requests
Completed 8000 requests
Completed 9000 requests


Server Software:
Server Hostname:        127.0.0.1
Server Port:            8000

Document Path:          /psycopg2/select
Document Length:        49791 bytes

Concurrency Level:      100
Time taken for tests:   39.625 seconds
Complete requests:      10000
Failed requests:        0
Total transferred:      498840000 bytes
HTML transferred:       497910000 bytes
Requests per second:    252.36 [#/sec] (mean)
Time per request:       396.253 [ms] (mean)
Time per request:       3.963 [ms] (mean, across all concurrent requests)
Transfer rate:          12293.87 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    2   1.0      2       5
Processing:    12  394  21.3    386     444
Waiting:        9  391  21.2    383     441
Total:         16  396  21.3    389     446

Percentage of the requests served within a certain time (ms)
  50%    389
  66%    395
  75%    415
  80%    418
  90%    425
  95%    431
  98%    434
  99%    440
 100%    446 (longest request)
Completed 10000 requests
Finished 10000 requests
a@MacBook-Pro ~ $
"""

import os
import asyncio

import uvloop
from asyncpg import connect, create_pool

from sanic import Sanic
from sanic.response import json, text
import psycopg2.pool

def jsonify(records):
    """
    Parse asyncpg record response into JSON format
    """
    return [dict(r.items()) for r in records]

app = Sanic(__name__)

TEST_SQL_QUERY = 'SELECT * FROM sanic_post';
DSN = 'postgres://a:@127.0.0.1/testing'

@app.listener('before_server_start')
async def register_db(app, loop):
    app.pool_1 = await create_pool(
            DSN, loop=loop, min_size=8, max_size=8)
    app.pool_2 = psycopg2.pool.PersistentConnectionPool(
            dsn=DSN, minconn=8, maxconn=8)
    async with app.pool_1.acquire() as connection:
        await connection.execute('DROP TABLE IF EXISTS sanic_post')
        await connection.execute("""CREATE TABLE sanic_post (
                                id serial primary key,
                                content varchar(50),
                                post_date timestamp
                            );""")
        for i in range(0, 1000):
            await connection.execute(f"""INSERT INTO sanic_post
                (id, content, post_date) VALUES ({i}, {i}, now())""")

@app.get('/asyncpg/select')
async def root_get(request):
    async with app.pool_1.acquire() as connection:
        results = await connection.fetch(TEST_SQL_QUERY)
        payload = {'posts': jsonify(results)}
        return json(payload)

@app.get('/psycopg2/select')
async def psycopg2_select(request):
    conn = app.pool_2.getconn()
    conn.autocommit = True
    with conn.cursor() as curs:
        curs.execute(TEST_SQL_QUERY)
        columns = [x.name for x in curs.description]
        results = curs.fetchall()
    payload = {'posts': [{x: y for x, y in zip(columns, result)} for result in results]}
    return json(payload)


if __name__ == '__main__':
    app.run(host='127.0.0.1', port=8000, debug=False)