PgBoucner and Prepared statements

If you have been using PgBouncer, or have tried to set it up you might be aware that until recently PgBouncer did not support prepared statements and that might have become a major blocker for you. If that is the case then there is good news as from version 1.21 onwards PgBoucner supports prepared statements and in this article, I will cover What was the issue with prepared statements in older versions of PgBouncer, How prepared statements work in PgBouncer, How to set up prepared statements and what are the limitations with the current implementation of PgBouncer prepared statements that you should be aware of.

How to use Prepared Statements in PostgreSQL

Before starting, I will briefly cover how prepared statements are used in PostgreSQL. There are three steps to using a prepared statement in PostgreSQL

1. Prepare a statement

PREPARE STATEMENT_1 (int) AS SELECT $1

2. Execute statement

EXECUTE STATEMENT_1(1) 
EXECUTE STATEMENT_1(2)

3. Deallocate the statement

DEALLOCATE STATEMENT_1

Here one thing to notice is prepared statements are scoped to a connection i.e prepared statement STATEMENT_1 is only visible to the connection that created it, any other connection trying to access STATEMENT_1 will result in the error “Prepared statement STATEMENT_1 not found”

Why do we need extra effort to make prepared statements work in PgBouncer?

As mentioned above prepared statements are only visible to the connection that created them. This is the part that causes issues with older versions of PgBouncer in transaction mode as for a single client connection each subsequent query might end up using a different server connection from the PgBouncer connection pool. Let me demonstrate this with an example:

Suppose we have one client connection C1 and two server connections S1 and S2 in the PgBouncer connection pool. We try to create a prepared statement using connection C1 from the application, so we execute the query Q1 which will executed as below:

This will be successful and the prepared statement will be created successfully on the database.

Now the issue occurs when we try to execute the prepared statement as the server connection S1 which prepared the statement might not be used by PgBouncer to execute the statement.

Here as S2 does not know about the prepared statement database throws the error “Prepared statement STATEMENT_1 not found”. Thus forwarding queries directly to PostgreSQL won’t work.

How Prepared Statements Work in Newer Versions of PgBouncer

Now to handle the above case PgBouncer needs to store some state/Information about prepared statements and process them before forwarding them to PostgreSQL. The solution for this implemented by PgBouncer works as below:

1. Prepare a statement: Client sends a prepare statement request to PgBouncer for STATEMENT_1 As SELECT $1

PgBouncer intercepts the query sent by the client and checks if it is a PREPARE query, if yes, PgBouncer will do the below steps:

• Map SELECT $1 to a custom-prepared statement name unique to PgBouncer, i.e PGBOUNCER_1, this will serve as a proxy for the actual statement name
• Store STATEMENT_1: SELECT $1 in the list of statements prepared by C1
• Store SELECT $1 in the list of statements prepared by S1
• Forward the query to PostgreSQL for PREPARE with the name PGBOUNCER_1

Why pgBouncer is doing all this will be clear in the next step.

2. Execute a statement: The client sends an execute request to PgBouncer for STATEMENT_1 over connection C1

PgBouncer intercepts the query and checks if it is an EXECUTE query, if yes, PgBouncer will do the below steps:

• PgBouncer fetches a server connection from the connection pool let’s say it gets connection S2
• From C1’s prepared statement map fetch the SQL query(SELECT $1) for prepared statement STATEMENT_1
• Match the list of prepared statements and get the custom statement name for the query(SELECT $1) prepared earlier, i.e PGBOUNCER_1
• Check if server connection S2 has this prepared statement present in its map
• If no, which is the case here PgBouncer will first prepare the statement on PostgreSQL with connection S2
• Execute the prepared statement on server connection S2 with the parameters passed by the client.

By adding the mappings for prepared statements and generating custom names PgBouncer ensures that the previous issue of missing prepared statements is solved.

One thing to note here is that PgBouncer is automatically preparing the statement on the connection S2 which does not have this statement prepared yet which solves the issue.

How to start using prepared statements in PgBouncer

As mentioned on the pgBouncer website here, setting up prepared statements for pgBouncer is a simple task of setting max_prepared_statements to a non-zero value.

max_prepared_statements: 100

This parameter tells PgBouncer how many max prepared statements it will keep in the LRU cache(Map of prepared statements shown in the above example) for each server connection.

So the expectations are once you have this value set to a non-zero value everything should start working and you should not face any issues. But it is not the case this is not where your task of setting prepared statements for PgBouncer ends this is just the start as there are some problems that more or less everyone will run into while using prepared_statements with PgBouncer which I will be covering in the next part.

This brings us to the end of this article which should be a good start for you to understand the workings of PgBouncer-prepared statements and how you can set it up.

Part II of this article has also been published link. Do give it a read to get more insights into prepared statements with pgboucner