Proposal to upgrade the psycopg2 dependency to psycopg3 for dbt-postgres

[am-giordano]

Hi all! I've started using the dbt-postgres adapter for a new project and realized that it's using the old psycopg2 Python-PostgreSQL adapter.

I was wondering if there were any plans to upgrade to psycopg3. I would very happily work on a contribution for doing this upgrade, but first I would like to know if perhaps this was something already thought about and discarded for some reason.

I was thinking that maybe the necessity to upgrade was simply not there because the improvement in performance when extracting / loading data between the DB and the Python runtime is irrelevant since dbt-postgres doesn't support Python models and therefore only executes statements directly in the database, where I guess there's not much of a difference between either version of psycopg.

But still, when Python is needed for some very concrete tasks that can't be handled with SQL, e.g. ML tasks, I personally think that it's convenient to just put those scripts in a subfolder of the dbt project and execute them with the same Python environment that has dbt-core and dbt-postgres installed. A shell script for executing the whole flow would look like this:

dbt run --selector before_python
python -m python_scripts.my_ml_task
dbt run --selector after_python

In this case, assuming the Python script uses psycopg for reading from and writing to the database, it would be beneficial for performance to use psycopg3 instead of psycopg2.