Prepared Statement Caching: An Easy Win?

In PostgreSQL, prepared statements are a means of optimization for executing SQL queries. Normally, SQL queries go through multiple phases of processing before being executed: parsing, rewriting, and planning. If your program executes the same query multiple times (as applications often do), then rerunning all of these steps each time is a bit redundant. By “preparing” the statement ahead of time, you can parse and rewrite the query once and then reuse the result. This means that a prepared statement only needs to be planned (and not parsed or rewritten) before execution.

PostgreSQL client libraries often expose a manual API for preparing and executing statements. This allows you, as a programmer, to be intentional about where and when to utilize this optimization. However, since most queries in an application end up being executed more than once, it’d be nice if the client library could automatically and transparently prepare and cache SQL statements under the hood. Well, I have some good news for you! Many libraries can and do do this.

For example, the Java JDBC driver, the Go pgx driver, the NodeJS Postgres.js driver, and the Python psycopg2 and asyncpg drivers all support automatic prepared statement caching. They all even enable it by default! Some other client libraries, however, don’t include this behavior. The node-postgres library, for example, supports prepared statement caching but you have to intentionally generate and provide a unique name parameter when executing queries. Thankfully, unique names can be easily generated by hashing the raw, parameterized SQL.

When implemented correctly, the application users (and database admins) benefit from faster, cheaper queries essentially for free. That being said, are there any reasons to not implement and utilize transparent statement caching? Based on my lived experience (and research) there is at least one downside: some PostgreSQL proxies don’t support prepared statements. One of the most popular database proxies, PgBouncer didn’t support them until version 1.12.0 which was released in October 2023. So, in the event that your database is deployed behind a proxy that doesn’t support prepared statements, your application won’t be able to reap these benefits.

For everyone else, though, you should be able to enjoy your free performance boost without any worry. Assuming you are using a PostgreSQL client library that supports this feature, your application’s queries will be cached and reused without you having to lift a finger. I think that’s a pretty great and well-designed optimization!