Boosting Postgres Performance With Prepared Statements and PgBouncer's Transaction Mode

Adopting prepared statements in your application is an easy performance gain. Using prepared statements lets your application skip query parsing and analyzing, eliminating a substantial amount of overhead. Pairing this with a connection pooler and transaction mode can dramatically boost your Postgres database performance. Needless to say, we were excited to learn that support for prepared statements in transaction mode was introduced in the 1.21 release of PgBouncer! 

In this post we’ll cover, at a high level, why you should enable prepared statements in your application. If you’re looking for the gory details of the implication, our peers over at Crunchy wrote a great post on the 1.21 release here!

Connection poolers on our mature cloud platform, Timescale, use PgBouncer under the hood, supporting prepared statements and transaction pooling. Start a free trial today to try it out—no credit card required!

What is a prepared statement, and why does it boost Postgres’ performance?

A prepared statement is a query that can accept parameters that you PREPARE on the server side, which does the query parsing, analysis, and any rewriting. You can then call this query by EXECUTEing it with the corresponding arguments. 

I like to think of prepared statements as similar to functions. You create a template of what will happen in the database and can call that template with parameters to have it happen inside the database. To over-extend the analogy, this “template” (prepared statements) lets you pre-compile the query, potentially greatly improving the overall execution time.

In an application setting, many of your queries are probably already templated rather than executing arbitrary queries. These are ideal candidates to use as prepared statements since they repeat the same underlying query but with different values. 

How to make it work with my app?

One limitation of the implementation of prepared statement support in PgBouncer is that “PgBouncer tracks protocol-level named prepared statements.” Basically, rather than writing raw SQL, we should use the libpq implementation of prepared statements instead.

Fortunately for you, whatever language your application is likely using already relies on this as part of the object-relational mapping (ORM) implementation! We just need to use the corresponding API rather than writing PREPARE … EXECUTE … in raw SQL ourselves.

ActiveRecord, the default ORM of Rails, makes this even simpler for us by using prepared statements by default. In your config/database.yml file, ensure you have not altered your production environment to turn off prepared_statements. They will allow up to 1,000 prepared statements by default.

Since Timescale allows for 100, we recommend reducing the config to equal our max_prepared_statements (see the next section for more detail). Thus, your config might look something like this:

production:
  adapter: postgresql
  statement_limit: 100

Note that prepared_statements: false is absent, as we want them on (which they are by default).

For an example of what is happening under the hood or to use as a template for other ORMs that may not handle this automatically, in the Ruby pg gem, we have the prepare() function. Creating a prepared statement would look something like:

conn = PG.connect(:dbname => 'tsdb')
conn.prepare('statement1', 'insert into metrics (created, type_id, value) values ($1::timestamptz, $2::int, $3::float)')

This uses the same table structure as our Energy tutorial. Note that the official gem documentation recommends casting the values to the desired types to avoid type conflicts. These are SQL types, not Ruby types, though, since they are a part of the query. 

To execute the query, you’d use something like:

conn.exec_prepared('statement1', [ 2023-05-31 23:59:59.043264+00,13, 1.78 ])

As a quirk of the PgBouncer implementation, we do not need to DEALLOCATE prepared statements. PgBouncer handles this automatically for us. 

All you need to do is, for each connection, try to prepare the statement you want to use once and then call exec_prepared()as often as the connection stays open.

What’s happening behind the scenes?

Behind the scenes, PgBouncer intercepts the creation of the prepared statement and creates a cache of prepared statements for the connection pool. It looks at what the client called it ( statement1 in our example) and sees if it has a prepared statement already on that connection, which looks something like PGBOUNCER_1234. If it does, then it will just reuse the already created statement. If not, it will automatically handle this and create it on behalf of the client in the database connection.

This implementation lets you effectively cache prepared statements across connections —which is insanely cool—giving you the full benefit of prepared statements even when using a transaction pool. For example, if your pool size is 20, and you have 100 client connections, each running the same query, this means that, at most, the query will be planned 20 times instead of the standard 100 in previous transaction mode usage. That’s pretty awesome!

One thing to be mindful of is the max_prepared_statements set in your connection pooler. On Timescale, the default is 100. For ideal performance, it’s recommended to keep the number of different prepared statements your application uses to be less than this value.

This lets you get maximum efficiency of PgBouncer’s cache. If you go more than this, it is not a big deal, but it may result in slightly more query plans than otherwise, as prepared statements will get deallocated from the database connection. For example, on Timescale, with your 101st prepared statement, the first prepared statement will be replaced by it.

Final Statements

Prepared statements rock! Using them can be a pretty easy performance win for your application while also boosting your Postgres database performance. The only thing to be careful about is to make sure that your application is using the libpq version of creating a prepared statement. Basically, rather than writing the raw SQL yourself, make sure you use your ORM’s API to create a prepared statement! Also, in PgBouncer, you don’t have to worry about deallocating a prepared statement ever.

If you’d like to try this out yourself, Timescale offers a free trial—no credit card required!