Inefficient use of connection pools that leverage a Connector

[jackwotherspoon]

The Cloud SQL and AlloyDB datastore providers could improve their use of connection pools, leading to more efficient quota and resource consumption.

genai-databases-retrieval-app/retrieval_service/datastore/providers/cloudsql_postgres.py

Lines 57 to 73 in 6987280

	async def getconn() -> asyncpg.Connection:
	async with Connector(loop=loop) as connector:
	conn: asyncpg.Connection = await connector.connect_async(
	# Cloud SQL instance connection name
	f"{config.project}:{config.region}:{config.instance}",
	"asyncpg",
	user=f"{config.user}",
	password=f"{config.password}",
	db=f"{config.database}",
	)
	await register_vector(conn)
	return conn
	pool = create_async_engine(
	"postgresql+asyncpg://",
	async_creator=getconn,
	)

The creator argument (same for async_creator) for SQLAlchemy is called on each new database connection. This means that initializing a Cloud SQL or AlloyDB Connector object within the get_conn is creating a new Connector for each connection pool connection and not sharing a single Connector across all connections.

For heavy usage this would lead to Cloud SQL or AlloyDB quotas being exhausted and for applications to begin to error.

A more efficient use would be to create a single Connector outside of get_conn so that it can be shared across all connections.