PostgresBackend: support asyncpg (or accept an injected pool) to avoid psycopg2 + per-call connection churn in asyncpg-native apps

[quantumdark]

Thanks for fastapi-taskflow — really enjoying it. Hitting one rough edge integrating it into an asyncpg-based FastAPI app and wanted to flag it.

PostgresBackend (backends/postgres.py) is built around sync psycopg2 wrapped in asyncio.to_thread. For apps that are already on asyncpg (and don't otherwise pull in psycopg2) this has a few costs:

1. Adds an unwanted DB driver — fastapi-taskflow[postgres] brings in psycopg2-binary, which is otherwise not in our dep tree.
2. No connection reuse — every public method calls self._connect() → conn.close(). At sustained task volume that's a fresh PG handshake per save / load / claim_pending call, which adds latency and connection-slot pressure.
3. Threadpool consumption — every backend call eats an asyncio.to_thread worker. asyncio's default executor maxes out at min(32, cpu+4) threads; under load the snapshot writer competes with anything else the app puts on the default executor.
4. Can't share the app's existing pool — the constructor takes a DSN only, so even apps that already maintain a healthy asyncpg pool can't hand it in.

A few directions that would solve this (any of them would be great):

• Native asyncpg backend alongside PostgresBackend, e.g. AsyncpgBackend(url=..., pool=None, min_size=1, max_size=5).
• Accept an injected pool: PostgresBackend(pool=existing_asyncpg_pool) as an alternative to url=, with the existing psycopg2 path kept for users who want the simple "give me a DSN" story.
• Internal connection pooling for the current psycopg2 path (e.g. psycopg2.pool.ThreadedConnectionPool) — wouldn't fix the dep concern but would address chore: update contributing guide and enable CI on develop branch #2 + Python 3.14 compatibility issue with daemon thread executor #3.

Happy to put up a PR for the asyncpg backend if it's a direction you're open to — would just want to align on the API shape (separate class vs PostgresBackend(driver="asyncpg" | "psycopg2")) before writing it.

[Attakay78]

Hi @quantumdark ,

Thanks for the detailed writeup, this is well observed.

You are right on all counts. The current PostgresBackend opens and closes a connection per operation, uses asyncio.to_thread for every call, and only accepts a DSN. We have internal changes already in progress that address the connection reuse and threadpool concerns for the existing psycopg2 path (ThreadedConnectionPool with a dedicated executor), and we will be raising a PR for that shortly.

On the AsyncpgBackend, we are open to it and the API shape you described makes sense. A separate class (AsyncpgBackend) is the direction we would go rather than a driver kwarg on the existing class, so the two paths stay independent and existing users are not touched.
The install extra would be fastapi-taskflow[asyncpg] alongside the existing fastapi-taskflow[postgres].

We would like to get the sync backend changes merged first so the AsyncpgBackend has a clean base to build on. Once that PR is in we will come back here and let you know it is ready. At that point if you still want to put up the AsyncpgBackend PR we would be glad to review it.
It would be good to align on a couple of things before you write any code: whether the constructor should accept both a url and an injected pool (we think yes, with an owns_pool flag so close() does not tear down a pool the caller manages), and the min_size/max_size defaults. We can work that out in this thread once we notify you.

[Attakay78]

@quantumdark The changes have been merged. You can go ahead and raise the PR. Drop all your questions and concerns here. Also try to tag me for quick responses.

NB: Pull from and commit to develop branch