Database Connection Pooling with Clojure

Table of Contents

Connection pooling is a pattern used by software applications to connect to databases using a pre-created set of reusable connection objects. When a connection is required, an existing one is retrieved from the pool. When the thread using the connection has completed, it is placed back in pool for use by another thread. This pattern reduces the overhead of connecting to a database by decreasing network traffic, limiting the cost of creating new connections, and reducing the load on the garbage collector.

In this article, you’ll learn how to create a database connection pool using the Java Database Connectivity (JDBC) API and the c3p0 library.

Using c3p0

c3p0, much like it’s namesake, is all about protocols. But instead of speaking Ewok, it translates database protocols. c3p0 augments traditional DriverManager-based JDBC drivers with JNDI-bindable DataSources. This includes DataSources that implement Connection and Statement Pooling.

In this example, we’ll use c3p0 with the corresponding clojure/java.jdbc extension. To add these dependencies, open your project’s project.clj file and add these entries to the :dependencies list:

This function makes use of the make-datasource-spec function from the clojure.jdbc library to initialize a connection pool. With this in place, you can use the spec function to retrieve a connection from the pool.

If you already have some code in your application that uses a database, then you can start using the spec function in place of your existing code. In the next section, you’ll see a simple example how to use it.

Using the connection pool

Open your project’s main class (this will be the web.clj file if you used the Getting Started example), and ensure that it has at least these require statements:

You may notice a warning “Bad pool size config”. That’s ok, because the library chooses a nice default. But in a real-world application, you’ll want to configure the minimum pool size as well and many other options.

Configuring the pool

The number of idle connections to keep warm in your pool depends on the size and nature of your application. Many users find one connection per thread handling HTTP requests is sufficient (assuming threads handling HTTP requests are the only threads using connections). Your application may need more if it experiences very high throughput such that it can’t turn connections over to new threads quick enough. Or you may need fewer if not every HTTP request needs to access the database. Ultimately, profiling your application under production loads is the best way to determine the appropriate pool parameters.

In development you can see the number of connections used by your application by checking the database.

$ psql -h localhost
psql (9.3.2)
Type "help" for help.
jkutner=# \q

This will open a connection to your development database. You can then see the number of connections to your postgres by running:

Under simulated production loads, this will give you a good indication of what size pool you need. There are, however, some constraints.

Maximum database connections

Heroku provides managed Postgres databases. Different tiered databases have different connection limits, which you can find listed on the Heroku Postgres add-on documentation. Databases in the lower tiers permit fewer connections than databases in the higher tiers. Once your database has the maximum number of active connections, it will no longer accept new connections. This will result in connection timeouts from your application and will likely cause exceptions.

When scaling out, it is important to keep in mind how many active connections your application needs. If each dyno allows 5 database connections, you can only scale out to four dynos before you need to provision a more robust database.

Now that you know how to configure your connection pool and how to figure out how many connections your database can handle you will need to calculate the right number of connections that each dyno will need.

Limit connections with PgBouncer

You can continue to scale out your applications with additional dynos until you have reached your database connection limits. Before you reach this point it is recommended to limit the number of connections required by each dyno by using the PgBouncer buildpack.

PgBouncer maintains a pool of connections that your database transactions share. This keeps connections to Postgres, which are otherwise open and idle, to a minimum. However, transaction pooling prevents you from using named prepared statements, session advisory locks, listen/notify, or other features that operate on a session level. See the PgBouncer buildpack FAQ for full list of limitations for more information.