Introduction
If you’ve worked with Google Cloud SQL for PostgreSQL, you’re likely aware that it sets a maximum number of connections for your instance at creation. This number isn’t static. It depends on the size — or rather, the machine type of the instance. For example, if you choose the db-f1-micro (the smallest available tier), you are capped at 25 connections by default.
And it might happen that you’ve already experienced an early wakeup call when your application suddenly ran out of connection and triggered an error like C: 53300: remaining connection slots are reserved…. That means you have run out of connections.
Let’s discuss connections, database parameters, and the logic behind these limits. My goal is to clear up some of the common questions I hear in the community and in private conversations with developers. I’ve structured this post as a Q&A to address the most frequently asked questions.
Max Connections in Cloud SQL for Postgres
Is the connection number a hard limit?
In chats and conversations with developers, I often hear about how people try to solve the problem when they hit the max number of connections. Quite often, there is a general assumption that 25 connections for a db-f1-micro is a hard limit, and the only ways to tackle it are to change the shape of the instance or implement connection pooling.
I don’t have anything against both approaches — they might be fully justified. But it is not a hard limit, and you can change it by updating the max_connections database flag using either the Google Cloud Console or the gcloud SDK. Keep in mind that the change requires a short downtime to apply. Read more in the documentation.
Will it automatically change if the instance is resized?
If you change your Cloud SQL instance size and haven’t manually set the max_connections database flag, the value will change automatically according to your instance size. For example, if you have a db-f1-micro instance with 25 connections and increase the size to db-g1-small, your max_connections will increase to 50.
What if I set up a custom max_connections flag?
If you define your preferred max_connections as a database flag, it will stay the same even through instance reconfigurations like changing the size. This can play a trick on someone who upgrades the instance size in anticipation of a higher max_connections value, only to find that it hasn’t changed.
What are the factors impacting the max_connections?
The main factor is the instance memory. In PostgreSQL, you have shared memory for the data pages you work with, and each session also has its own individual memory. This is a bit of a simplified description, but it is probably enough to explain the memory impact. Let’s go down to the memory allocations.
You need shared buffers to work with your data — each page of data from a table or index is copied to this area so it can be accessed by your session. The more data you have and the more you need to work with, the more shared buffers you’ll need for better performance. Otherwise, you will be constantly moving data to and from the disk.
When you connect to the instance, your session allocates roughly 2 MB of memory. Then, as you work with data, the memory allocation depends on your operations and the value of the work_mem parameter. By default, it is 4 MB. If you have a sorting or hashing operation in your query (like an ORDER BY), your session will allocate those 4 MB for that operation. However, that is per operation — a single query could potentially run multiple sorting or hashing tasks, multiplying the memory allocation.
Additionally, background processes like vacuuming and logical replication require memory too.
I will live out some other details like temporary buffers and others, operation system processes and caches. You can check all that in the PostgreSQL documentation. But considering all of this, the 25 max_connections limit for a db-f1-micro instance with only 600 MB of memory doesn’t look so small anymore.
And if we look at a graph of default max_connections values relative to memory, we can see it isn’t linear. Different factors have different impacts as the instance grows in size and the number of potential connections increases.
Can I set up max_connections to a higher or lower value?
Yes, you can, but you must consider all factors regarding potential memory allocation. If you run out of available memory, your connection will be terminated by an Out of Memory (OOM) error. Additionally, keep in mind that once you set max_connections as a database flag, it will no longer change dynamically based on the instance size; you will need to update the value manually if you decide to resize the instance.
How can I handle thousands of connections?
If your application requires hundreds or thousands of concurrent connections, the best approach is not to increase the max_connections flag.
Instead, I recommend using connection pooling. Cloud SQL offers Managed Connection Pooling (MCP), which is available in the Enterprise Plus edition. Alternatively, you can set up your own using tools like PgBouncer. Connection pooling solutions act as a proxy between your application and the database. They allow thousands of lightweight client connections- from sources like serverless scripts — to share a small, fixed number of heavy backend server connections. Read more about managed connection pooling in documentation.
Summary
The default max_connections value is tied to the instance memory and adjusts automatically as you resize the instance — unless you have manually defined it as a database flag.
It’s important to remember that the default value is based on best practices and serves as guidance, not a hard limit. However, if you choose to set a custom value, you are responsible for managing it. You’ll need to remember to adjust it manually if you ever resize your instance.
If your application is designed to use a high volume of connections, your best bet might be to implement a connection pooling solution.
Top comments (0)