Prefect Server - Cloud Postgres Database Hi there, Does anyone have any pointers on setting up prefect server to communicate with a cloud Postgres database? I have been reading this forum post on scaling, and it sounds like it should help improve performance issues I'm facing. I'm trying initially to connect to Google's Cloud Spanner, but getting confused on their documentation with needing PGAdapter and postgres drivers, and how that links with the prefect documentation concerning setting a PREFECT_API_DATABASE_CONNECTION_URL

Hi James, I don't believe we've never tested Prefect against Spanner. While Spanner is largely PostgreSQL protocol compatible, it only supports a subset of PG features. GCP CloudSQL for PostgreSQL will definitely work very well and Prefect has been through a lot of battle testing against this configuration.

Thank you for the pointer in the right direction! that already saves me going down a long route to failure I'm new to setting up postgres in general, am I right in thinking the right approach to linking Cloud SQL and Prefect would be through SQLAlchemy? and the URL would be something like:

PREFECT_API_DATABASE_CONNECTION_URL="postgresql+asyncpg://<db_user>:<db_pass>@<db_host>:<db_port>/<db_name>"

(this is me naively combining this and this, i.e. switching pg8000 for asyncpg and copying the Cloud SQL url format)

Yep that's exactly right, once you've set up a CloudSQL PostgreSQL instance, you'll have created a "superuser" type DB user. You can either use that user directly for

prefect

, or use it to create another user. When you start the prefect server with

prefect server start

, it will create the required database tables, etc. You may want to provide your DB password in a separate variable, like this:

PREFECT_API_DATABASE_PASSWORD='mypassword'
PREFECT_API_DATABASE_CONNECTION_URL='postgresql+asyncpg://<db_user>:${PREFECT_API_DATABASE_PASSWORD}@<db_host>:<db_pass>/<db_name>'

This hides the password a little more and will format your connection string when it is used (just have to make sure to include that

${PREFECT_API_DATABASE_PASSWORD}

part literally

Great advice, thanks @Chris Guidry! Sorry for the delay, but I got it working 🙂 I had some issues since it seems this is not the bottleneck after all, rather the parent flow that orchestrates all my DaskTaskRunner tasks hits 100% on one cpu and then faces PoolTimeout errors. I'd thought this might be helped with an external postgres but apparently not! I think this might be worth a separate github issue though

Oh gosh, sorry James, yes please send us a GH issue with all the details you can spare

Sorry for the delay I had to prioritise getting some other solution in as a stop gap, but here is my github issue in case your still feeling super generous in helping me out! https://github.com/PrefectHQ/prefect/issues/12877