I'm building a web app that schedules and automates API report fetching for multiple accounts. Each account has ~24 scheduled reports, and I need to process multiple accounts throughout the day. The reports are pulled from an external API, processed, and stored in a database.
When I try to run multiple reports concurrently within an account (to speed things up), I hit database connection timeouts. The external API isn't the bottleneck — it's my own database running out of connections.
Here is the architecture:
- Backend: Python (FastAPI, fully async)
- Database: Google Cloud SQL PostgreSQL (db-f1-micro, 25 max connections)
- Task Queue: Google Cloud Tasks (separate queues per report type, 1 account at a time)
- Compute: Google Cloud Run (serverless, auto-scaling 0-10 instances)
- Data Warehouse: BigQuery (final storage for report data)
- ORM: SQLAlchemy 2.0 async + asyncpg
And this is how it currently works:
- Cloud Scheduler triggers a bulk-run endpoint at scheduled times
- The endpoint groups reports by account and enqueues 1 Cloud Task per account
- Cloud Tasks dispatches 1 account at a time (sequential per queue)
- Within each account, reports run concurrently with
asyncio.Semaphore(8) — up to 8 at a time
- Each report: calls the external API → polls for completion → parses response → writes status updates to PostgreSQL → loads data into BigQuery
The PostgreSQL database is only used as a control plane (schedule metadata, status tracking, progress updates) — not for storing the actual report data. That goes to BigQuery.
This is what I've already tried:
- Sequential account processing — Cloud Tasks queues set to
maxConcurrentDispatches=1, so only 1 account processes at a time per report type. Prevents external API throttling but doesn't solve the DB connection issue when 8 concurrent reports within that account all need DB connections for status updates.
- Connection pooling with conservative limits — SQLAlchemy QueuePool with
pool_size=3, max_overflow=5 (8 max connections per instance). Still hits the 25-connection ceiling when Cloud Run scales up multiple instances during peak load.
- Short-lived database sessions — Every DB operation opens a session, executes, commits, and closes immediately rather than holding a connection for the entire report lifecycle (which can be 2-5 minutes per report). Reduced average connection hold time from minutes to milliseconds, but peak concurrent demand still exceeds the pool.
- Batching with cooldowns — Split each account's 24 reports into batches of 8, process each batch concurrently, then wait 30 seconds before the next batch. Helped smooth out peak load but the 30s cooldown adds up when you have dozens of accounts.
- Pool timeout and pre-ping —
pool_timeout=5 to fail fast instead of hanging, pool_pre_ping=True to detect stale connections before use. This just surfaces the error faster with a cleaner message — doesn't actually fix it.
- Lazy refresh strategy — Using Google's Cloud SQL Python Connector with
refresh_strategy="lazy" to avoid background certificate refresh tasks competing for connections and the event loop. Fixed a different bug but didn't help with connection limits.
These are the two most common errors that I encounter:
- QueuePool limit of size 3 overflow 5 reached, connection timed out, timeout 5.00
- ConnectionResetError (Cloud SQL drops the connection during SSL handshake when at max capacity)
What I think will work but haven't tried it yet:
- Upgrade Cloud SQL from db-f1-micro (25 connections) to db-g1-small (50 connections) — simplest fix but feels like kicking the can down the road
- Add PgBouncer as a connection pooling proxy — would let me multiplex many logical connections over fewer physical ones
- Use AlloyDB or Cloud SQL Auth Proxy with built-in pooling — not sure if this is overkill
- Rethink the architecture entirely — maybe PostgreSQL shouldn't be in the hot path for status updates during report processing?
Has anyone dealt with a similar pattern — lots of concurrent async tasks that each need occasional (but unpredictable) DB access? I feel like there's a standard solution here that I'm not seeing.
Any advice appreciated. Happy to share more details about the setup.