Consider supporting psycopg 3 as an alternative PostgreSQL DB-API driver

### Feature description

Please consider adding support for psycopg 3 (psycopg) as an alternative PostgreSQL DB-API driver for the PostgreSQL-based destinations, while retaining support for psycopg2.

At the moment the PostgreSQL, Redshift and PostGIS extras depend on psycopg2-binary.

### Are you a dlt user?

Yes, I run dlt in production.

### Use case

psycopg 3 is the actively developed successor to psycopg2, while psycopg2 is now primarily in maintenance mode.

Supporting psycopg 3 alongside psycopg2 would allow dlt's PostgreSQL DB-API layer to become compatible with the actively developed PostgreSQL driver, while retaining backwards compatibility with existing installations. It would also provide a path for dlt to adopt newer driver capabilities without requiring an immediate migration away from psycopg2.

### Proposed solution

Consider making the PostgreSQL DB-API layer work with either:

* psycopg (psycopg 3), preferably the currently supported versions; or
* psycopg2

A possible implementation would be similar (in principle) to [Django's PostgreSQL backend](https://github.com/django/django/blob/main/django/db/backends/postgresql/base.py): detect/use psycopg 3 when available and provide a small compatibility layer for the places where the two drivers differ.

There would also need to be some consideration around dependency management. Since the current relevant extras all install `psycopg2-binary` automatically, there should be a way to opt into or out of psycopg 3.

### Related issues

Not directly in the dlt repository, but here's some relevant issues in the broader ecosystem:

* https://github.com/dbt-labs/dbt-adapters/issues/669
* https://github.com/psycopg/psycopg/issues/122

Start by locating the PostgreSQL, Redshift, and PostGIS extras and the PostgreSQL DB-API layer, then inspect how psycopg2 is currently required and used. Done means the destinations work with a supported psycopg 3 or psycopg2 installation, with dependency choices documented and existing psycopg2 support retained.

Written by the indexing model from the issue text.