Compose Notes - PostgreSQL connection limit control


PostgreSQL 9.5 users can now control the number of incoming connections allowed to their deployments. You'll find the new control in the Compose web console for your PostgreSQL database under Settings.

By default, all PostgreSQL deployments on Compose start with 100 connections allowed. You will need to scale up your deployment to get the ability to increase your connection count. As the database is scaled up, we add 100 connections to the maximum number of connections for every two units of scale. Two units of scale is 2GB of disk storage and 204MB of RAM; on a 1GB PostgreSQL deployment you would add two units by clicking the "3x" option here", taking you to a total of 3GB of storage, 306MB of RAM and potentially a 200 connection limit.

Scale up here from 1GB to 3GB

Just because we could increase the connection limit doesn't mean we do. Each connection consumes RAM, either to manage the connection or associated with the client using the connection. The more connections, the bigger the potential to starve the database of RAM which it could use to run the database. So we don't automatically increase the number of allowed connections and instead we keep it at 100 unless you use the Set Connection Limit option.

A well written app should typically not need a large number of connections. If it does need a large number of connections then consider tools like pg_bouncer which can pool your connections or a PostgreSQL driver which can pool connections for you. As each connection consumes RAM, you should be looking to minimise their use. You may also want to check your own application to ensure you aren't holding connections open unnecessarily.

Once you are sure you need those extra connections, you may need to scale up. If you go to Settings and Set Connection Limit and view the options available, the system will indicate how many connections you are set to and how many are available. If you are already at the maximum and need more connections, you'll have to go back to the Overview and scale up there.

Already at maximum

Otherwise select your new connection limit but be aware, before you click Set Connection Limit, that this operation will trigger a rolling restart on your PostgreSQL deployment.

If you have a busy database, you may want to choose a quiet period to do this operation. Once you have clicked Set Connection Limit, the Compose console will take you to the Jobs view where you can view the progress of that rolling restart.

Remember, you can also roll back the number of connections too so if you've found a particularly greedy client you've been working around and now fixed, you can pull that connection limit down to something more reasonable. You now have the ability to control your PostgreSQL connection limit and match it to what you need; make good choices.

Dj Walker-Morgan
Dj Walker-Morgan is Compose's resident Content Curator, and has been both a developer and writer since Apples came in II flavors and Commodores had Pets. Love this article? Head over to Dj Walker-Morgan’s author page and keep reading.