Scaling Connections in Postgres

DZone 's Guide to

Scaling Connections in Postgres

A safe level for connections is 300-500. This may seem low if you’re running with 1000s of connections, but it’s likely fine with pgBouncer doing the heavy lifting.

· Database Zone ·
Free Resource

There are a number of applications out there that have a high number of connections to Postgres. What’s high? That all depends on your application, but generally, when you get to the few hundred connections area in Postgres, you’re on the higher end. Anything in the thousands is definitely in the high territory, and even several hundred can put a strain on your application. Generally, a safe level for connections should be somewhere around 300-500 connections. This may seem low if you’re already running with thousands of connections, but it’s likely perfectly fine with pgBouncer taking care of the heavy lifting for you. Let’s drill into why a bit further.

Connection Basics in Postgres

Each new connection to Postgres is a forked process. This process comes with its own memory allocation of roughly 10 MB with at least some load for the query. In this sense, every new connection is already consuming some of those precious resources you want processing queries. For 300 database connections, this is 3 GB of memory going just to managing those connections — memory which could be better used for caching your data.

When a Connection Isn’t Just a Connection

Many application frameworks such as Rails like to grab a pool of connections when they start up. This reduces the time needed to get a connection when they run a query, instead they pull one from the pool. This is good for your app’s performance as the time to get a connection isn’t always trivial. However, this does result in a bunch of connections to your database sitting idle. Recently I saw an application with 300 open connections, but only 17 active queries. How do you check active queries? You can run this to give you some insight to currently active queries:

FROM pg_stat_activity
WHERE state <> 'idle'

The issue here is that by default, every new request is going to grab a new connection, so if you have 300 requests happening at a time, you have 300 connections to your database. For something like sidekiq, which processes background jobs for Ruby, you can easily use as many as 25 connections per process. In reality, most of the time, the job is just waiting on some other process like an upload or sending an email. Sure, you can manually release the connection when you’re not using it, but that can become quite error prone. And further, why do that work when you don’t have to?

The short of it is, even if you see “300” open connections to your database, you may not actively have 300 concurrent transactions going on.

Enter Connection Pooling for Postgres

One could easily blame Rails or Django, but in reality, transactional connection pooling is a lot of extra logic to build into a framework. Instead, a simpler option exists in the form of Postgres-specific connection pooling. A connection pooler will do the hard work of maintaining a pool of connections and then give them out as your application needs them, which is when a transaction or query happens. A connection pooler can have a number of settings two very key ones are:

  1. A max amount of active connections.
  2. A max on idle connections.

For Citus Cloud, the limit is 300 active connections and 2,000 idle connections. There are a few options when it comes to your connection pooler, including PgBouncer and PgPool. At Citus, we leverage PgBouncer.

Setting Up PgBouncer (Our Preferred Connection Pooler)

If you’re running Postgres and haven’t setup your connection pooler yet, it’s worth giving a watch to some of the recent talks at PGConf SV on PgBouncer here and here. For PgBouncer, there are a few modes it can be run in: session pooling and transaction pooling. What you want to more efficiently manage your connections is transaction pooling. Transaction pooling will grant a connection when you run BEGIN; and return the transaction when you COMMIT;.

Note: with transaction pooling, you do need to make sure you turn off prepared statements which Rails turns on by default.

If you’re running on something like Heroku, they have a custom buildpack for you to set up PgBouncer in front of your app. If you’re running on Amazon RDS and looking to setup PgBouncer, this guide from @andrewkane gives you the steps.

PgBouncer Connection Pooling and Citus Cloud

On Citus Cloud, PgBouncer is already running and configured for you. To connect to PgBouncer, you can simply swap your port from 5432 to 6432 and you’ll be connected to PgBouncer. This should make it easier for you when managing connections on Citus to get back to building your app and give you one less thing about your database to have to think or worry about.

Thanks to Kelly Reynolds and Brandur Leach for reviews on this post.

connections, database, postgres, scaling, tutorial

Published at DZone with permission of Craig Kerstiens , DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}