PgBouncer and prepared statements

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

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

Everyone uses prepared statements, right? 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.
You could fix it with always wrapping your prepares with one transaction with query request, but it slows down your application even more because you have to make start and commit/rollback requests.
But there is a trick, that you can use with PostgreSQL to fix this.
You can use

Binary parameters

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.
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.

That’s it!
The only thing you can run into (at least, in golang application) is with saving JSONb parameters.
But it can be easily fixed by wrapping your implementation of valuer with the following one:

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

Performance of binary parameters mode

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 leads to more RPS to DB.

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

LEAVE A COMMENT