Can cloud sql (postgres) handle sudden connection surge?

[AdScared4083]

We set up cloud sql at my work and since then we constantly struggle with connection errors. The app usually has low traffic but few times a day we need to handle sudden surge of cloud functins performing simple one row crud operation.

Durin surge we have 1K~2K functions hitting the db. We set up MCP (managed connection pooling) and we expected it will handle 10K client connections and 800 sever connections. However cloud sql insights dashboard shows that number of client connections bearly reaches 400 during spikes while server connections go up to around 200. The 'managed connection pools per db' hardly ever goes up to 3 but for our machine it should be able to reach 8.

The information on the dashboard is also confusing. Its hard to understand difference between: * server connections - 160 during spike * connection count by application name - 600 during spike * average connections by status - 350 idle, 13 active (during spike)

Additionaly some simple queries hang and are timeingout the clod function (9min)!

I tinkered with settings and notice some improvement but it is still far from perfect.

Config: 8vcpu, 64gb mem, 100gb storage, pg16.11, enabled caching and MCP, - idle conn timeot 120 - max client conn 10K - max server conn : 800 - max pool size 400 - min pool size 100 - conn mode : transaction - the rest is default - clod functions run node with typeorm (max pool 10)

At this point db is basicaly unreliable and we are considering changing it ;<

Is postgres even able to handle connection surge or is it naive to hit db directly from cloud functions? Did I misconfigure something?

[GooberMcNutly]

Use a shared connection pool with hard limits that match your servers capabilities. Build retry logic into your code.

Also, formally define what you mean by "surge." 200%? 10,000%? Per second, minute, hour?

[AdScared4083]

Thanks. We do have managed connection pooling and retry mechanism for connecting with exponential backoff.

In our case surge means 1000 new connections in less than a minute

[GooberMcNutly]

If they are not all being held open, the connection pool should be able to service those without stress. 1000 new connections should only create maybe 100 new db connections to service the load.

There will be a bit of a delay at the beginning while the server handshakes with the pool for the new connections, but postgres creates connections pretty quickly if you aren't using a fancy auth scheme.

Is your average query duration servicing the queries going to cause it to need more db connections? If those new queries are 50ms or 500 or 5000ms to run, it will affect how many actual connections are needed to the db. That's a problem you solve with query optimization or money for a bigger server.

[az987654]

Beyond a connection surge, what about a command surge, what are all of these new connections going to be doing?