pq or pgx - Which Driver Should I Go With?

Here is a question I was asked by my students recently:

When using PostgreSQL, which driver should we go for?

As we all know, Go does not provide drivers as part of its standard library, and rightfully so. This would have been an overkill to maintain for all the different database vendors and versions. Instead, the job has been left to the community.

If we check the list for PostgreSQL drivers, we will see two main contenders:

• pq: https://github.com/lib/pq
• pgx: https://github.com/jackc/pgx

pq #

pq implements the standard sql.DB specification, and as such, may be the first choice that most people new to the language go for. If you are looking for a driver, which simply gives you access to a standard relational database, pq is just fine. All you need to do is import it, and you are good to go:

import (
	_ "github.com/lib/pq" // Don't forget the driver!
)

dbPath := "postgres://path_to_the_db"
db, err := sql.Open("postgres", dbPath)

Unfortunately, pq has not been in active feature development as of a couple of years. It is still under active maintenance, so anything critical will still make it into the repo, but it may eventually diverge from the development of future PostgreSQL versions:

This package is effectively in maintenance mode and is not actively developed. Small patches and features are only rarely reviewed and merged. We recommend using pgx which is actively maintained.

pgx #

That said, I would advise people to seriously look at pgx as an alternative implementation:

pgx aims to be low-level, fast, and performant, while also enabling PostgreSQL-specific features that the standard database/sql package does not allow for. The driver component of pgx can be used alongside the standard database/sql package.

Let’s try to decode the above message. Because pgx offers a compatibility layer with database/sql , it can be used in exactly the same way as we used pq before. If you are in a hurry, and don’t have time to explore all the cool features of pgx, you can simply do the following, and go on with your life:

import (
	_ "github.com/jackc/pgx/v4/stdlib"
)

But pgx can do a lot more #

While it is perfectly fine to use pgx as a standard database driver replacement of pq, it can do a whole lot more when used on its own. See, pgx wasn’t developed to comply with the standard library’s design specification. While it mimics the functions, methods, and types from the database/sql package, under the hood, it provides a completely different implementation that directly uses the best out of PostgreSQL, because it was designed for it.

// source: https://github.com/jackc/pgx/wiki/Getting-started-with-pgx#using-a-connection-pool

dbpool, err := pgxpool.Connect(context.Background(), os.Getenv("DATABASE_URL"))
// handle the error
defer dbpool.Close()

err = dbpool.QueryRow(context.Background(), "select 'Hello, world!'").Scan(&greeting)
// pgx mimics the standard library for the most part

pgx was designed for performance right out of the gate, and the fact that it forces the developer to think about connection pools right from the start pays off later on.

In addition, when not tied to the standard library, pgx can make use of a faster binary protocol for marshalling and unmarshalling of JSON data from Postgres. It also understands and supports most of the data types specific to Postgres.

The verdict #

If your code makes use of pq already, or if you are not sure, whether you will keep using PostgreSQL in the near future, it is OK to keep it as it is. My advice is to try and give pgx’s compatibility layer a try. It won’t give you any performance advantage over pq, but it will move you a step further in the direction, without requiring any changes to the rest of your code. Once you have settled in on using pgx, you might as well replace the compatibility layer with a direct pgx pool. This may require a few more changes, but for the most part, your code should be easy to adjust.

Recommended Reading #