DLast updated August 27, 2020

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.

Creating an applicationE

If you already have a Clojure application, you may use it for this example. Otherwise, create a simple application from the Getting Started with Clojure on Heroku article before proceeding. You should also be familiar with Building a Database-Backed Clojure Web Application. You can also start with the source code for this example, and simply follow along.

Using c3p0E

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:

[org.clojure/java.jdbc "0.3.6"]
[clojure.jdbc/clojure.jdbc-c3p0 "0.3.1"]

Save the file, and run Leiningen to ensure the libraries are downloaded:

$ lein install
Retrieving org/clojure/java.jdbc/0.3.6/java.jdbc-0.3.6.pom from central
Retrieving org/clojure/java.jdbc/0.3.6/java.jdbc-0.3.6.jar from central
Retrieving clojure/jdbc/clojure.jdbc-c3p0/0.3.1/clojure.jdbc-c3p0-0.3.1.pom from clojars
Retrieving clojure/jdbc/clojure.jdbc-c3p0/0.3.1/clojure.jdbc-c3p0-0.3.1.jar from clojars
...
Installed jar and pom into local repo.

Next, create a db.clj file in your project, and put the following code at the top (but replace the namespace with the namespace appropriate for your application):

(ns ticks.db
  (:import com.mchange.v2.c3p0.ComboPooledDataSource)
  (require [clojure.java.jdbc :as jdbc]
           [jdbc.pool.c3p0    :as pool]))

Below this, add the following function to retrieve the database URL from the environment:

(def db-uri
  (java.net.URI. (or
    (System/getenv "DATABASE_URL")
    "postgresql://localhost:5432/ticks")))

This function attempts to get the value of the DATABASE_URL environment variable, but defaults to a localhost connection string if it’s not found. This is useful for local development.

Now add the following function, which parses the database URL for a username and password:

(def user-and-password
  (if (nil? (.getUserInfo db-uri))
    nil (clojure.string/split (.getUserInfo db-uri) #":")))

And finally for this module, add a function to define the database connection:

(def spec
  (pool/make-datasource-spec
    {:classname "org.postgresql.Driver"
    :subprotocol "postgresql"
    :user (get user-and-password 0)
    :password (get user-and-password 1)
    :subname (if (= -1 (.getPort db-uri))
                (format "//%s%s" (.getHost db-uri) (.getPath db-uri))
                (format "//%s:%s%s" (.getHost db-uri) (.getPort db-uri) (.getPath db-uri)))}))

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 poolE

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:

(ns ticks.web
  (:require
    [clojure.java.jdbc :as jdbc]
    [ticks.db :as db]
    [compojure.core :refer [defroutes GET]]
    [compojure.handler :refer [site]]
    [compojure.route :as route]
    [ring.adapter.jetty :as jetty]
    [environ.core :refer [env]]))

Now add the following functions, which migrate the database schema:

(defn migrated? []
  (-> (jdbc/query db/spec
    [(str "select count(*) from information_schema.tables "
    "where table_name='ticks'")])
    first :count pos?))

(defn migrate []
  (when (not (migrated?))
  (print "Creating database structure...") (flush)
  (jdbc/db-do-commands db/spec
    (jdbc/create-table-ddl
      :ticks
      [:id :serial "PRIMARY KEY"]
      [:body :varchar "NOT NULL"]
      [:tick :timestamp "NOT NULL" "DEFAULT CURRENT_TIMESTAMP"]))
      (println " done")))

These functions create a simple table named "ticks" if it does not already exist. Notice how they use the db/spec function as an argument to the jdbc functions.

Now add the following function, which queries the new table:

(defn tick []
  (jdbc/insert! db/spec :ticks [:body] ["hello"]))

Next, define a route that calls the tick function and then executes another query to display to count of ticks:

(defroutes app
  (GET "*" []
    (tick)
    {:status 200
      :headers {"Content-Type" "text/plain"}
      :body (str "Ticks: " (first (jdbc/query db/spec ["select count(*) from ticks"])))}))

Finally, ensure that the migrate function is called when you’re application boots. It might look like this:

(defn -main [& [port]]
  (migrate)
  (let [port (Integer. (or port (env :port) 5000))]
    (jetty/run-jetty (site #'app) {:port port :join? false})))

Now you can run the app.

Deploying the app to HerokuE

If you’re Heroku application does not already have a database, you can add one by running this command:

$ heroku addons:create heroku-postgresql:hobby-dev

Then add all of your changes to Git, and deploy your application:

$ git add .
$ git commit -m "adding a database connection pool"
$ git push heroku master

Then view the logs to ensure that the pool is created correctly:

$ heroku logs
2014-12-21T22:12:04.756975+00:00 heroku[web.1]: Starting process with command `java $JVM_OPTS -cp target/ticks-standalone.jar clojure.main -m ticks.web`
2014-12-21T22:12:05.262632+00:00 app[web.1]: Picked up JAVA_TOOL_OPTIONS: -Xmx384m  -Djava.rmi.server.useCodebaseOnly=true
2014-12-21T22:12:07.341079+00:00 app[web.1]: INFO: MLog clients using java 1.4+ standard logging.
2014-12-21T22:12:07.341072+00:00 app[web.1]: Dec 21, 2014 10:12:07 PM com.mchange.v2.log.MLog
2014-12-21T22:12:08.358960+00:00 app[web.1]: Dec 21, 2014 10:12:08 PM com.mchange.v2.c3p0.C3P0Registry
2014-12-21T22:12:08.358965+00:00 app[web.1]: INFO: Initializing c3p0-0.9.5-pre9 [built 08-October-2014 03:06:08 -0700; debug? true; trace: 10]
2014-12-21T22:12:10.579168+00:00 app[web.1]: Dec 21, 2014 10:12:10 PM com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource
2014-12-21T22:12:10.579174+00:00 app[web.1]: INFO: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 1bqs26q96urtp65v8d9bo|3276732, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> org.postgresql.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceUseNamedDriverClass -> false, identityToken -> 1bqs26q96urtp65v8d9bo|3276732, idleConnectionTestPeriod -> 800, initialPoolSize -> 0, jdbcUrl -> jdbc:postgresql://ec2-54-204-42-119.compute-1.amazonaws.com:5432/ddan7chsh1kg3g, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 3600, maxIdleTime -> 1800, maxIdleTimeExcessConnections -> 120, maxPoolSize -> 15, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
2014-12-21T22:12:10.594560+00:00 app[web.1]: Dec 21, 2014 10:12:10 PM com.mchange.v2.resourcepool.BasicResourcePool
2014-12-21T22:12:10.594564+00:00 app[web.1]: WARNING: Bad pool size config, start 0 < min 3. Using 3 as start.
2014-12-21T22:12:10.760360+00:00 app[web.1]: 2014-12-21 22:12:10.759:INFO:oejs.Server:jetty-7.x.y-SNAPSHOT
2014-12-21T22:12:10.801601+00:00 app[web.1]: 2014-12-21 22:12:10.800:INFO:oejs.AbstractConnector:Started [email protected]:34833
2014-12-21T22:12:10.960005+00:00 heroku[web.1]: State changed from starting to up

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 poolE

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:

select count(*) from pg_stat_activity where pid <> pg_backend_pid() and usename = current_user;

Which will return with the number of connections on that database:

 count
-------
   5
(1 row)

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

Maximum database connectionsE

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 PgBouncerE

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.

For many frameworks, you must disable prepared statements in order to use PgBouncer. Then set your app to use the PgBouncer buildpack.

For JDBC, this requires adding prepareThreshold=0 to the connection string. But it may also be necessary to patch your JDBC driver.

$ heroku buildpacks:add heroku/pgbouncer

Next we need to ensure your application can run so you need to add your language specific buildpack. Since you are using Clojure it would be:

$ heroku buildpacks:add heroku/clojure

Now you must modify your Procfile to start PgBouncer. In your Procfile add the command bin/start-pgbouncer-stunnel to the beginning of your web entry. So if your Procfile was

web: java $JVM_OPTS -cp target/ticks-standalone.jar clojure.main -m ticks.web

Will now be:

web: bin/start-pgbouncer-stunnel java $JVM_OPTS -cp target/ticks-standalone.jar clojure.main -m ticks.web

Commit the results to git, test on a staging app, and then deploy to production.

When deploying you should see this in the output:

=====> Detected Framework: pgbouncer-stunnel

For more information on connection pooling with Clojure, JDBC and c3p0, see the java.jdbc section of clojure-doc.org.

You can find the source code for the examples used in this article on GitHub.