PgBouncer and prepared statements

Table of Contents

In our system, we use connection pooler called PgBouncer as a proxy to PostgreSQL server.

PgBouncer has two main modes

Session pooling mode

This mode is less performant (it’s a default mode). When a client connects, a server connection will be assigned to it for the whole duration it stays connected. So it does not utilize connections properly.

Transaction pooling mode

This mode is more performant as it gives a connection for every transaction started and returns it to the pool when the transaction is being committed or rolled back. So it lets you utilize connections better.

So, we could just turn on transaction pooling and be happy, right?
Not exactly.

Prepared statements issue

Everyone uses prepared statements, I hope. They help you to avoid SQL injections as a class.
And there is a problem with it.
How prepared statements work?
DB client sends a prepare statement request, gets statement ID, and after that, it just makes another request with that statement ID and parameters that should replace placeholders in the request.
The problem is that in transaction mode you always receive a new connection from the pool. And often you run into a situation where you get different connections for prepare request and query request. And statement ID is valid only in the context of one connection. So you could run into problems like unnamed prepared statement does not exist.
You could fix it with always wrapping your prepares into one transaction with its query, but it slows down your application even more because you have to make start and commit/rollback transactions for every query.
But there is a trick, that you can use with PostgreSQL to fix this.
You can use

Solution — using binary parameters query mode

It is almost undocumented in Postgres, but it’s there and used by Twitch, for instance.
The trick is that: when using binary parameters, PostgreSQL client sends only one request per query, but all query parameters are being sent as a binary.
If you use lib/pq for golang, you can turn it on with binary_parameters=yes in your Postgres dsn like in the following example:
postgres://user:pass@host:post/db_name?sslmode=disable&binary_parameters=yes.
If you prefer pgx, you should enable this protocol with a flag PreferSimpleProtocol (https://github.com/jackc/pgx/blob/master/conn.go#L132).

import (
	...
	"github.com/jackc/pgx"
	"github.com/jackc/pgx/stdlib"
	...
)
pgxConfig := stdlib.DriverConfig{
	ConnConfig: pgx.ConnConfig{
		PreferSimpleProtocol: true,
	},
}
stdlib.RegisterDriverConfig(&pgxConfig)

db, err := sql.Open(pgxConfig.ConnectionString(c.DSN))
...

import (

...

"github.com/jackc/pgx"

"github.com/jackc/pgx/stdlib"

...

)

pgxConfig := stdlib.DriverConfig{

ConnConfig: pgx.ConnConfig{

PreferSimpleProtocol: true,

}

stdlib.RegisterDriverConfig(&pgxConfig)

db, err := sql.Open(pgxConfig.ConnectionString(c.DSN))

...

That’s it!
The only problem we’ve run into in a golang application is about saving JSONb parameters.
But it can be easily fixed by wrapping your implementation of valuer with the following one:

// Value returns driver.Value for database driver usage
func (m JSONb) Value() (driver.Value, error) {
	if m.data == nil {
		return []byte("{}"), nil
	}
	j, err := json.Marshal(m.data)
	if err != nil {
		return "", err
	}
	return string(j), nil
}

// Value returns driver.Value for database driver usage

func (m JSONb) Value() (driver.Value, error) {

if m.data == nil {

return []byte("{}"), nil

}

j, err := json.Marshal(m.data)

if err != nil {

return "", err

}

return string(j), nil

}

The deal is to return a string instead of a slice of bytes.

Performance of binary parameters mode

As for me, the main profit of using binary_parameters is not about transaction mode switching, though.
The coolest thing is that, as I said before, using binary parameters makes PostgreSQL use 1 request instead of 2.
And it lets your DB handle more QPS.

There is an interesting video on that (in Russian):

https://youtu.be/Uojy57I-xP0?t=1379

Further reading:
https://wiki.postgresql.org/wiki/PgBouncer#Features
https://blog.twitch.tv/how-twitch-uses-postgresql-c34aa9e56f58
https://medium.com/avitotech/how-to-work-with-postgres-in-go-bad2dabd13e4