Introduction to pgbouncer

What is pgbouncer

pgbouncer is connection pooling middleware software between the application servers & Postgres DB instance. The purpose of connection pooling is to make it efficient for applications to connect to Postgres instances. How does pgbouncer do this? By reusing the connections to the database.

In Postgres (and generally most databases), it’s really costly to establish a connection, taking around 1.5–14.5 MB per connection. If you have 100 open connections to the database, that will take a maximum of ~1.45GB of RAM just to maintain the connections, which is a waste. Remember, any wasted resources translate to money loss. Moreover, most DBaaS offerings today limit the number of connections per database instance. For example, Heroku Postgres Standard 0 instance priced at $50 only allows up to 120 connections. When you need to scale out your backend servers beyond that limit, that’s when you need connection pooling between application servers and Postgres instances.

Comparison between systems with and without connection pooling

Comparison between systems with and without connection pooling

From the application server’s point of view, it thinks it’s connected to a regular Postgres server. It doesn’t have that connection pool notion. Application server here refers to your backend software, whether they’re written in Laravel PHP, Python Django, Ruby on Rails, etc.

Advantages

Disadvantages

Everything comes at a cost.

Don’t get confused with application-level connection pooling

You may have noticed that many ORMs, database adapters, and web frameworks today have built-in connection pooling features. You can see in their documentations: Ruby on Rails, Django, Sequelize.js, Prisma, Elixir Ecto, Go Database, etc.

Application-level connection pooling

Application-level connection pooling

The application-level connection pooling only manages the threads among the server-level threads. If you spin up hundreds of servers, each of them will establish connections to the database servers, resulting in a high number of connections. This does not solve the high connection number issue when you’re adding servers to your system.

When to use pgbouncer

In my opinion, you should only use pgbouncer when you are scaling out application servers, and your database connection is exhausting. For example, your database allows up to 100 connections, but you have 200 application servers (and each runs multiple threads). Theoretically, your database instances won’t be able to handle that 200+ database connections from the application servers, and this is the ideal use case for pgbouncer.

If you do not need many servers to support your users, you better NOT use connection pooling as the benefit is minimal.

When NOT to use pgbouncer

You should NOT use pgbouncer to speed up your queries. Consider doing the low-hanging fruit optimizations first, like adding relevant table indexing, building materialized views, scaling up your database server, etc., before actually installing pgbouncer in your system.

Installing pgbouncer won’t help much if your queries are slow.

Which companies are using pgbouncer

They are pretty popular among large scale internet companies

How Twitch uses PostgreSQL

Scaling the GitLab database

GitHub - PostHog/posthog-cloud: Internal scripts for PostHog multi-tenancy environment

Optimizing Database Connection Loads With PgBouncer and Testwick

Let’s start

#1 Launch Postgres container (Optional)

$ docker run \
	-e POSTGRES_PASSWORD=password --network=host --name postgres postgres

You can skip this step if you already have a database running on your laptop or in the cloud.

Note that we’re using the host network when launching this Docker container so that the pgbouncer container in step #2 can reach it.

#2 Launch pgbouncer container

To launch a pgbouncer container, you’ll need the following environment variables:

$ docker run \
    -e "POSTGRESQL_HOST=localhost" \
    -e "POSTGRESQL_USERNAME=postgres" \
$ docker run \
	-e "POSTGRESQL_HOST=localhost" \
	-e "POSTGRESQL_USERNAME=postgres" \
	-e "POSTGRESQL_PASSWORD=password" \
	-e "POSTGRESQL_DATABASE=postgres" \
	-e "PGBOUNCER_POOL_MODE=transaction" \
	-e "PGBOUNCER_PORT=6432" \
	--network=host \
	--name=pgbouncer \
	bitnami/pgbouncer

There are multiple pgbouncer Docker images available. Personally, I prefer the [Bitnami pgbouncer Docker image](https://github.com/bitnami/bitnami-docker-pgbouncer) because they are more up-to-date than the others.

### #3 Connect to the database

```shell
$ psql -h localhost -U postgres -d postgres -p 6432 -W\
$ <PASSWORD>

Remember to set the database to postgres as that’s the default database name specified in the Docker container.

#4 Teardown

To remove the containers created, run the following command

$ docker rm -f pgbouncer postgres

What’s next

If you just want to use pgbouncer for a single database instance, then you’re good to go. However, the pgbouncer has many more things to offer. Please consider learning how to manually configure pgbouncer.ini file. From there, you can change its logging configuration, admin user credentials, upstream database user credentials, etc. It’s also essential for you to understand which pool mode suits your workload.

Bear in mind that there are several other connection pooling software alternatives out there. For Postgres, you can check out pgpool and Yandex Odessey. If you’re using MySQL, you can check out ProxySQL and Vitess.

Bonus: Connecting pgbouncer to Heroku

To connect to Heroku Postgres, we’ll need to use an SSL connection from the pgbouncer to the Postgres server.

$ docker run \
    -e "POSTGRESQL_HOST=<HEROKU PG HOST>" \
    -e "POSTGRESQL_USERNAME=<HEROKU PG USERNAME>" \
$ docker run \
	-e "POSTGRESQL_HOST=<HEROKU PG HOST>" \
	-e "POSTGRESQL_USERNAME=<HEROKU PG USERNAME>" \
	-e "POSTGRESQL_PASSWORD=<HEROKU PG PASSWORD>" \
	-e "POSTGRESQL_DATABASE=<HEROKU PG DBNAME>" \
	-e "PGBOUNCER_POOL_MODE=transaction" \
	-e "PGBOUNCER_SERVER_TLS_SSLMODE=require" \
	-e "PGBOUNCER_SERVER_TLS_PROTOCOLS=secure" \
	-p 6432:6432 \
	--name=pgbouncer \
	bitnami/pgbouncer

*This part is more about ranting about my first experience with the pgbouncer container. You can skip this part if you're not interested.*

Recently, I hit a scalability issue where I needed to scale my Sidekiq workers into a specific number, but that's huge enough to hurt my Heroku Postgres instance. For testing, I wanted to launch pgbouncer from my laptop and connect to the remote Heroku Postgres instance.

After hours of searching, I discovered that Heroku does actually offer a pgbouncer buildpack. However, I don't want to launch that pgbouncer in my Heroku Dyno but instead on my local laptop. I've always wanted to use connection pooling software before, but it wasn't always the time.

After hours of debugging, I wondered why I couldn't establish a connection between my pgbouncer and the Heroku. The good thing is source code is available here on Github.

[GitHub - heroku/heroku-buildpack-pgbouncer: Run pgbouncer in a dyno along with your application](https://github.com/heroku/heroku-buildpack-pgbouncer)

In the [gen-pgbouncer-conf.sh](https://github.com/heroku/heroku-buildpack-pgbouncer/blob/main/bin/gen-pgbouncer-conf.sh) file, we can see that it is using the secure server connection.

```shell
server_tls_sslmode = prefer\
server_tls_protocols = secure\
server_tls_ciphers = HIGH:!ADH:!AECDH:!LOW:!EXP:!MD5:!3DES:!SRP:!PSK:@STRENGTH
server_tls_sslmode = preferserver_tls_protocols = secureserver_tls_ciphers = HIGH:!ADH:!AECDH:!LOW:!EXP:!MD5:!3DES:!SRP:!PSK:@STRENGTH

## Learn more about pgbouncer