sinisaos
commented
6 months ago @chandr-andr Thank you for your interest in Piccolo. Your work with psqlpy and qaspen (I didn't go into details and had a quick look at the source code and I like that it has a lot of similarities with Piccolo :) ) is also great.
I have nothing against the alternative driver, but I have one question regarding the performance that you stated have a significant performance upgrade in comparison to asyncpg.
Apologies in advance if I'm doing something wrong as I'm not a benchmark expert and I'm using the http bencmark test as the ORM or driver will most likely be used for a web application. I've tried the FastAPI example from the docs and built a similar example with asyncpg like this
# users table only have 2 columns (id and name)
import asyncpg
import uvicorn
from contextlib import asynccontextmanager
from typing import AsyncGenerator
from fastapi import FastAPI, Request
from fastapi.responses import JSONResponse
@asynccontextmanager
async def lifespan(app: FastAPI) -> AsyncGenerator[None, None]:
"""Startup database connection pool and close it on shutdown."""
db_pool = await asyncpg.create_pool(
dsn="postgres://postgres:postgres@localhost:5432/psqlpydb",
max_size=10,
)
app.state.db_pool = db_pool
yield
await db_pool.close()
app = FastAPI(lifespan=lifespan)
@app.get("/asyncpg")
async def pg_pool_example(request: Request):
query_result = await request.app.state.db_pool.fetch(
"SELECT * FROM users",
)
return JSONResponse(content=[dict(item) for item in query_result])
if __name__ == "__main__":
uvicorn.run("app:app" )and the http benchmark results are pretty similar and asyncpg does a bit better? Here are the results.
rkl@mint21:~$ bombardier -c 500 -d 10s -l http://localhost:8000/asyncpg
Bombarding http://localhost:8000/asyncpg for 10s using 500 connection(s)
[============================================================================================]10s
Done!
Statistics Avg Stdev Max
Reqs/sec 707.47 109.03 1021.59
Latency 678.21ms 70.37ms 1.54s
Latency Distribution
50% 687.24ms
75% 698.83ms
90% 709.14ms
95% 718.07ms
99% 0.88s
HTTP codes:
1xx - 0, 2xx - 7556, 3xx - 0, 4xx - 0, 5xx - 0
others - 0
Throughput: 218.29KB/s
rkl@mint21:~$ bombardier -c 500 -d 10s -l http://localhost:8000/psqlpy
Bombarding http://localhost:8000/psqlpy for 10s using 500 connection(s)
[============================================================================================] 10s
Done!
Statistics Avg Stdev Max
Reqs/sec 639.18 90.12 975.61
Latency 745.92ms 72.92ms 1.08s
Latency Distribution
50% 755.51ms
75% 762.43ms
90% 784.68ms
95% 797.32ms
99% 0.97s
HTTP codes:
1xx - 0, 2xx - 6860, 3xx - 0, 4xx - 0, 5xx - 0
others - 0
Throughput: 197.62KB/sSorry again if I missed the point about driver performance.
chandr-andr
dantownsend
insani7y
Hello! Thank you for your awesome ORM.
Recently, I've created a new driver for PostgreSQL - https://github.com/qaspen-python/psqlpy. It shows a significant performance upgrade in comparison to asyncpg. It has already been tested in some production services and I can say it is production-ready.
Do you mind if I create a big PR that adds a new engine based on PSQLPy?