Connection Pool with Deno Postgres

So far, we have implemented our applications so that whenever we wish to make a query to the database, we open up a connection to the database, execute the query, and close the connection. This works, but it could be more efficient -- let's look into this through measuring the time that it takes to execute a single request -- in the examples below, we have used an ElephantSQL instance as the database.

Measuring time can be done using the time command. The time command effectively times the execution of the command given after the time command. Below, we measure the time that it takes to do a POST request to an address (including the time it takes to receive a response); this is done five times to allow us to have some sort of an estimate of the average time it takes to complete such a request.

time curl -X POST http://localhost:7777/delete/doesnotexist
curl -X POST http://localhost:7777/delete/doesnotexist  0,00s user 0,01s system 3% cpu 0,282 total
time curl -X POST http://localhost:7777/delete/doesnotexist
curl -X POST http://localhost:7777/delete/doesnotexist  0,01s user 0,01s system 7% cpu 0,224 total
time curl -X POST http://localhost:7777/delete/doesnotexist
curl -X POST http://localhost:7777/delete/doesnotexist  0,01s user 0,00s system 6% cpu 0,264 total
time curl -X POST http://localhost:7777/delete/doesnotexist
curl -X POST http://localhost:7777/delete/doesnotexist  0,00s user 0,01s system 5% cpu 0,236 total
time curl -X POST http://localhost:7777/delete/doesnotexist
curl -X POST http://localhost:7777/delete/doesnotexist  0,00s user 0,01s system 5% cpu 0,252 total

The output of the time command tells the amount of time and computer resources that the specific command given after the time command took to execute. To us, the most relevant part is the last number in the outputs, i.e. 0,282, 0,224, 0,264, 0,236, and 0,252. These correspond to the time that it took to process the request in seconds.

In practice, when calculated over the five requests shown above, a single request takes approximately 250 milliseconds on average.

Cost of a simple database query

Note that we are simplifying this quite a bit -- details on establishing connections are discussed in networking courses.

In the current version of the application, whenever we make a query, we open a database connection, we make the database query, and we close the connection.

If the web server and the database server are geologically far from each others, sending a single message may already take tens of milliseconds. When we study the steps outlined above, we may observe that the amount of time that is spent making the actual query is actually only a small part of the overall process.

Could we somehow skip opening and closing the database connection for each request? Connection pools provide one solution to this problem.

Connection pools

As discussed above, quite a bit of time is spent on opening and closing the connections, whereas the most important part -- that is, executing the actual query -- is only a small part of the whole process. A common way to reduce the overhead of creating connections is the use of connection pools.

A connection pool is effectively a set of connections to the database server from the web server, which are opened when the application is started. These connections can be retrieved or "borrowed" from the pool for a moment to execute a query, after which they are returned to the pool.

Using a connection pool

In the deno postgres database driver, there exists a class called Pool, which can be used for creating a connection pool. The pool takes two parameters, the database configuration and the number of database connections that can be concurrently opened. The number of concurrent connections is limited by the database in use -- for example, the free tier of ElephantSQL provides 5 concurrent connections.

In the example below, we create a connection pool with two concurrent connections. We use the number two so that, if ElephantSQL is used, we can use the ElephantSQL online browser and possibly also another database connection even when the web server is running. The empty object is the database configuration -- it is empty as the configuration is typically entered through the environment.

import { Pool } from "https://deno.land/x/postgres@v0.17.0/mod.ts";

const CONCURRENT_CONNECTIONS = 2;
const connectionPool = new Pool({}, CONCURRENT_CONNECTIONS);

In practice, a single PostgreSQL instance can handle hundreds of concurrent connections, and there are a multitude of approaches for scaling PostgreSQL.

When we use a connection pool, instead of opening and closing a connection, we borrow a connection using the method connect, which we then release using the method release once we are ready. Other than that, the functionality remains the same. The following example outlines our new database.js file which now uses a connection pool instead of opening a new connection for each database query.

import { Pool } from "https://deno.land/x/postgres@v0.17.0/mod.ts";

const CONCURRENT_CONNECTIONS = 2;
const connectionPool = new Pool({}, CONCURRENT_CONNECTIONS);

const executeQuery = async (query, params) => {
  const response = {};
  let client;

  try {
    client = await connectionPool.connect();
    const result = await client.queryObject(query, params);
    if (result.rows) {
      response.rows = result.rows;
    }
  } catch (e) {
    response.error = e;
  } finally {
    try {
      await client.release();
    } catch (e) {
      console.log(e);
    }
  }

  return response;
};

export { executeQuery };

Other than changing the database.js to use the connection pool as shown above, we do not have to do any changes to the code. This is due to the database functionality being used through the executeQuery function exported from the database.js.

Now, let's restart the server and try making a few requests to the server. Similarly to the previous requests, we time these requests as well.

time curl -X POST http://localhost:7777/delete/doesnotexist
curl -X POST http://localhost:7777/delete/doesnotexist  0,01s user 0,01s system 70% cpu 0,022 total
time curl -X POST http://localhost:7777/delete/doesnotexist
curl -X POST http://localhost:7777/delete/doesnotexist  0,02s user 0,00s system 78% cpu 0,029 total
time curl -X POST http://localhost:7777/delete/doesnotexist
curl -X POST http://localhost:7777/delete/doesnotexist  0,01s user 0,01s system 70% cpu 0,018 total
time curl -X POST http://localhost:7777/delete/doesnotexist
curl -X POST http://localhost:7777/delete/doesnotexist  0,01s user 0,00s system 76% cpu 0,023 total
time curl -X POST http://localhost:7777/delete/doesnotexist
curl -X POST http://localhost:7777/delete/doesnotexist  0,01s user 0,00s system 76% cpu 0,028 total

Now, when we look at the above output, we notice that the requests are quite a bit faster. The most relevant parts of the above output are again the last numbers, i.e. 0,022, 0,029, 0,018, 0,023, and 0,028. While when not using a connection pool, a single request took on average 250 milliseconds, the duration of a request when using a connection pool is now on average 24 ms. This is a ten-fold improvement, which is significant.