Introduction

A few weeks ago I was trying to implement a service that I had on Heroku to use as few connections as possible to the database as they limit the number of connections to 20 on a free dynamo. At that time I was using the lib/pq driver and it doesn’t implement any kind of connection pooling but that would not be a problem as I would close every unused connection after use, so I thought. The implementation of get a connection and do stuff with it is shown in following code.

import (
    ...
)

var (
    instance *db.SQL
)

func getConn() {
    if instance == nil {

    }

    return instance
}

func Query(query string, arguments []interface{}) {
    conn, err := getConn()

    if err != nil {

    }
    defer conn.Close()

    // do important stuff with the acquired connection that will be closed on
    // the end return of the function
}

The problem with this approach is that the connection with the database on the heroku instance isn’t closed as defined in the defer. I really don’t know why and I didn’t checked with the support directly, although I had a written question on SO about it. I was mesmerized. Why it does work on the localhost but it doesn’t on a dyno instance? But I was quite excited to solve the problem and so I turned myself to look after a solution.

PGBouncer

A connection pooler was needed to control how many connections the driver uses and I found an appealing solution. The name of the solution is pgbouncer and it promise to be lightweight and augment database performance.

A tutorial on how to setup a pgbouncer environment can be seen here and any specific details about its configuration can be found here.

The setup alone might be troublesome for some and that is why I think there’s a better option to go with. Apart from that, I used and tested it in my local machine and everything was fine, although the connection pool was not clear to the programmer’s code it was doing just fine.

To use PGBouncer inside a Heroku app instance though you have to configure a few things first and you can follow every instruction to reproduce this environment on this heroku support page.

After I submitted the code to Heroku the application was running fine, until I hit a rock again. I was stunned by it. The connection pool wasn’t doing its job, much less the connections were being closed after usage.

The solution

So I looked for more on the web to see if I could find any better replacement, and I did. After some time I found pgx which featured a connection pool. I was very happy at that point. The code written with it was clear that the use of a connection pool was in place, just as the code below shows it:

package main

import (
    ...

    "github.com/jackc/pgx/v4"
    "github.com/jackc/pgx/v4/pgxpool"
)

var (
    connection *pgxpool.Pool
)

func main() {
    // remember to define the DATABASE_URL env
    pool, err = pgxpool.Connect(context.Background(), os.Getenv("DATABASE_URL"))

    if err != nil {
        log.Fatalf("Couldn't connect to the database. Reason %v", err)
    }
    defer conn.Close(context.Background())

    // A query then could be used with an acquired connection
    conn, err := connection.Acquire(context.Background())

    if err != nil {
        log.Printf("Couldn't get a connection with the database. Reason %v", err)
    } else {
        // release the connection to the pool after using it
        defer conn.Release()

        query := "SELECT SOLUTION FROM TB_GO_PGSQL_DRIVERS";
        arguments = []interface{}{...}

        results, err := conn.Query(context.Background(), query, arguments...)

        if err != nil {
            log.Printf("Couldn't execute query. Reason %v", err)
        } else {
            // show the results boy, you got it.
        }
    }

}

As you can clearly see, a connection pool is initialized and any following queries to the database can be done with little connections from that pool, using the Acquire method.

The problem after that was not anymore. I could enjoy my application running without problems, as long as people using the application weren’t so many to surpass the 20 connections limit.

Conclusion

Although I think I don’t, at the time of writing, understand how pgbouncer and heroku works together and why the heck it didn’t close connections after specifically being told to do so I think that pgx was a much more elegant solution to the problem.

In fact, the pgx driver has a substantial increase in performance than the alternatives on some cases due to specific things (see the performance section) that I won’t mention over here.

All in all the usage of pgx was quite clear to me, contrary to the usage of pgbouncer and . I could just use it without having to worry about two different, or possible more, cogs working together.

So in the end I vote for pgx, for its clear and documented API, has nice features such as database types and connection pool, amongst others.