Node.js Postgres Pooling Revisited (with transactions!)

This is a follow up to the last connection pooling blog entry. If it seems like I am spending an undue amount of time on this topic then you feel the same way as I do.

I’ve grown wary of node-postgres‘ built-in pooling and have decided to implement my own connection pooling on top of it. In production, I was seeing that connections would “leak” from the pool, causing the pool to fill and time out all further requests. The only fix I had was to have a periodic health check that rebooted the process: obviously this wouldn’t work for launch. The issue is documented here: https://github.com/brianc/node-postgres/issues/137 but most of the discussed solutions won’t work with my pooled decorator pattern.

Luckily, new versions of node-pool has the pattern built in and baking your own pool is a couple of lines. Let’s create a pool:

pg = require('pg').native # Or non-native, if you prefer
poolModule = require 'generic-pool'

connectionString = "tcp://user:pass@localhost/your_database"
pgPool = poolModule.Pool
    name: 'postgres'
    create: (cb) ->
        client = new pg.Client connectionString
        client.connect (err) ->
          return cb(err) if err?
          client.on "error", (err) ->
            console.log "Error in postgres client, removing from pool"
            pgPool.destroy(client)
          client.pauseDrain() #Make sure internal pooling is disabled
          cb null, client

    destroy: (client) -> client.end()
    max: 10
    idleTimeoutMillis : 30 * 1000
    log: true #remove me if you aren't debugging

pgPool is a pool of up to ten postgres clients. Calling pgPool.acquire will give you a native postgres client to work with, while calling pgPool.release will return it back to the pool. Lastly, calling pgPool.pooled and passing in a function will “decorate” your function so that it auto-acquires on call, and auto-releases on callback. See the pooled function decoration documentation.

Note that in create we call the pg.Client constructor directly instead of using pg.connect because pg.connect returns a proxy object that does internal connection pooling.

Okay! That was pretty basic. To spice this post up a bit, here’s a little class that helps out with transactions:

class Transaction
    #Unmanaged client (not auto-released)
    @startInClient: (pgClient, releaseOnCompletion, callback) ->
      [callback, releaseOnCompletion] = [releaseOnCompletion, false] if typeof releaseOnCompletion == 'function'
      (new Transaction(pgClient, releaseOnCompletion)).startTransaction callback

    #Managed client (auto-released on commit / rollback)
    @start: (callback) ->
      pgPool.acquire (err, pgClient) ->
        return callback(err) if err?
        Transaction.startInClient pgClient, true, callback

    constructor: (@pgClient, @releaseOnComplete) ->

    startTransaction: (cb) ->
      @pgClient.query "BEGIN", (err) => cb(err, @)

    rollback: (cb) ->
      @pgClient.query "ROLLBACK", (err) =>
        pgPool.release @pgClient if @releaseOnComplete
        cb(err, @) if cb?

    commit: (cb) ->
      @pgClient.query "COMMIT", (err) =>
        pgPool.release @pgClient if @releaseOnComplete
        cb(err, @) if cb?

    wrapCallback: (cb) -> (err) =>
      callerArguments = arguments
      if err?
        @rollback()
        return cb callerArguments...
      else
        @commit (commitErr) ->
          return cb(commitErr) if commitErr?
          cb callerArguments...

Transaction.start acquires a new connection from the pool, begins a transaction and returns an object that can be used to manage the transaction. You can either rollback or commit the transaction. One more sophisticated function is the wrapCallback function. This is best understood with an example:

# Start a transaction and do something with it
Transaction.startTransaction (err, t) =>
      return console.log "Couldn't start transaction" if err?
      callback = t.wrapCallback(callback)
      pgClient = t.pgClient
      pgClient.query "INSERT INTO fun(id) VALUES(1)", (err) ->
        return callback(err) if err?
        pgClient.query "INSERT INTO people(id,fun_id) VALUES (1,1)", callback

The above code transactionally inserts an item into the fun table and the people table. Beyond Transaction.startTransaction it never mentions the transaction again. The magic here is the t.wrapCallback function: it produces a wrapped callback that rollbacks when called with an error (the first parameter isn’t undefined/null) and commits otherwise.

In the languages I know, the “gold standard” for transactions is when they are as transparent as possible. Sometimes (i.e. Spring with JDBC in Java), this means resorting to thread-local contexts. I think the above transaction class matches this standard, even if the wrapCallback is a little tricky to understand.

And with that, we’re back to coding. If you like postgres and silly faces, try out the Silly Face Society on the iPhone.

  • Pingback: Node.js Postgres Pooling Revisited (with transactions!) | Arg! Team Blog | javascript.js | Scoop.it

  • treasonx

    Great article! Glad I run into this when I did :)

    I was just starting to investigate connection pooling with node-postgres. generic-pool is a neat little module!

    I am handling transactions using promises. I am using meld.js to wrap a function around my data access functions. It introduces a deferred, executes the data access function, and returns a promise to the caller. The data access function needs to resolve or reject the deferred. If the deferred is rejected, I rollback the transaction, else commit. The transaction is transparent to the author of the data access function where the queries and results processing are handled. Also when the deferred is resolved with success or error I always release the connection.

  • http://twitter.com/briancarlson Brian Carlson

    Original author of node-postgres here. I also have grown weary of the built-in pool. :) It doesn’t belong within node-postgres itself, and has some real bad usability problems with retaining/releasing clients and what happens when you request a client but don’t use it.

    I initially opened this issue https://github.com/brianc/node-postgres/issues/227 and through further discussions we think it best to use node-any-db-pool for pooling purposes rather than me re-invent the wheel specifically for node-postgres.

    I need to do a better job of documenting the shortcomings of the built in pool on the node-postgres github page and point people in the right direction towards a more full-featured, easier to use implementation.

    In the interest of backwards compatibility I will likely not remove the built-in pool, but will some day mark it as deprecated.

    • http://twitter.com/cosbynator Thomas Dimson

      Hi Brian,

      I totally forgot to reply to this. I really appreciate the work you are putting into this – it looks like I’ll have to update my code slightly to work with the latest version. For what it is worth, I would vote for moving connection pooling completely out of node-postgres and just put some documentation on how to use node-pool.

      I’ve been running on production with it for the last 6 months or so and it has been meeting my needs (I contributed a patch to do “pooled decoration” that makes things easier – see https://github.com/coopernurse/node-pool)

      -Thomas

  • Brian Takita

    @twitter-16961083:disqus Can you keep the same pooling api but have it delegate to node-any-db-pool then?

  • johaness vix

    are u using coffescript? why?