Support psycopg3 RFC

[thinkwelltwd]

Description:

The latest version of psycopg is psycopg3; it supports asyncio and also works in pure python for better pypy compatibility.

In a django app that I develop, it's almost 5% faster with more optimizations coming. I'm also a synapse user and would be interested in trying to port synapse to psycopg3 for the improved performance.

• Would such a PR be of interest to the synapse project?
• If so, could this be a complete migration to psycopg3, or would you want the codebase to support both 2 & 3?
  • If it's critical to retain the support if psycopgcffi, then both would need to be supported.
  • (Given the pure-python mode, perhaps pypy performance on psycopg3 would be equivalent to psycopgcffi?)

Any thoughts on this proposal? It might be biting off more than I can chew to do a migration, but I'd like to try if the synapse maintainers are open to it.

[clokep]

Would such a PR be of interest to the synapse project?

I think so! I started some work on this at https://github.com/matrix-org/synapse/pull/new/clokep/psycopg3. I believe I was testing it with:

$ USE_POSTGRES_FOR_TESTS=psycopg python -m twisted.trial tests

I don't fully remember the state of this branch (and don't see having time in the near term to pick it up again, so would appreciate some help), but some notes are below:

• It does not use the asyncio nature of psycopg3, it uses it in a sync fashion (via Twisted's thread pool, as psycopg2 is done). I see full async support as a follow-on?
• (If I'm remembering correctly) it does not do server-side binding.
• It duplicates a lot of code from the pscyopg2 adapter, which is unfortunate. (Maybe some abstraction could help this.)

If so, could this be a complete migration to psycopg3, or would you want the codebase to support both 2 & 3?

I think we'd want to support both until we're sure psycopg3 doesn't have any regressions.

If it's critical to retain the support if psycopgcffi, then both would need to be supported.

I don't think this is critical if it is pypy only.

---

A rough to do list of how I was thinking of doing this:

• Abstract out places where we check isinstance of PsycopgEngine (or Sqlite3Engine) to check for features instead. (This isn't required, but should make supporting psycopg3 much easier.) (This is partially done in the branch above, but should really be separate PRs.)
• Add a new database engine for psycopg3 (I forget why I called it psycopg in the branch above, there might be some magic with the Python package name?)
• Figure out a way to drive this via async instead of via the Twisted thread pool.

[DMRobertson]

Would such a PR be of interest to the synapse project?

Yes, absolutely.

If so, could this be a complete migration to psycopg3, or would you want the codebase to support both 2 & 3?

Probably both, until psycopg3 has proliferated enough so that requiring it isn't a burden on downstream packagers. (Compare https://pkgs.org/search/?q=psycopg3 versus https://pkgs.org/search/?q=psycopg)

With that said, we'd still be interested in a proof-of-concept which only supports psycopg3, particularly if we can use it to see what sort of performance improvements we might get. We just won't be able to commit to shipping such a POC.

it supports asyncio

Heads-up that Synapse is using Twisted's reactor machinery to drive its async execution; this might present obstacles.

also works in pure python for better pypy compatibility.
psycopgcffi

I wouldn't worry too much on this front. The Synapse team chiefly supports CPython; we don't even run CI against pypy.