Support psycopg3 RFC

This issue has been migrated from [#14586](https://github.com/matrix-org/synapse/issues/14586).

---

**Description:**

The latest version of psycopg is [psycopg3](https://github.com/psycopg/psycopg/); 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](https://github.com/django/django/pull/15687#issuecomment-1331196632) 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.