asyncpg_vs_psycopg2_pools.py

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):
    return [dict(r.items()) for r in records]

app = Sanic(__name__)

TEST_SQL_QUERY = 'SELECT * FROM data;'
DSN = ''

@app.listener('before_server_start')
async def register_db(app, loop):
    app.pool_1 = await create_pool(
            DSN, loop=loop, min_size=10, max_size=10)
    app.pool_2 = psycopg2.pool.PersistentConnectionPool(
            dsn=DSN, minconn=10, maxconn=10)
    async with app.pool_1.acquire() as connection:
        await connection.execute('DROP TABLE IF EXISTS data')
        await connection.execute("""CREATE TABLE data (
                                id serial primary key,
                                content varchar(50),
                                post_date timestamp
                            );""")
        for i in range(0, 1000):
            await connection.execute(f"""INSERT INTO data
                (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, access_log=False)

200 concurrent requests on the asyncpg pool is able to complete without a problem. The psycopg2 pool will fail every time due to limitations with connections to postgres and the fact that the connections are not being awaited in a coroutine.

Server Software:
Server Hostname:        localhost
Server Port:            8000

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

Concurrency Level:      200
Time taken for tests:   3.777 seconds
Complete requests:      1000
Failed requests:        0
Total transferred:      49884000 bytes
HTML transferred:       49791000 bytes
Requests per second:    264.74 [#/sec] (mean)
Time per request:       755.456 [ms] (mean)
Time per request:       3.777 [ms] (mean, across all concurrent requests)
Transfer rate:          12896.81 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    1   2.2      0       8
Processing:    48  686 340.2    746    2112
Waiting:       44  681 339.5    740    2110
Total:         49  687 340.2    746    2112

Percentage of the requests served within a certain time (ms)
  50%    746
  66%    753
  75%    757
  80%    759
  90%   1111
  95%   1395
  98%   1439
  99%   1468
 100%   2112 (longest request)