PostgreSQL statement caching

[pimeys]

The upcoming 2.1.0 release will introduce prepared statement caching for PostgreSQL connector. The cache will allow us to reuse compiled statements in identical parameterized queries.

Example

SELECT * FROM user WHERE name = "John";
SELECT * FROM user WHERE name = "Brenda";

The two queries after parameterization will be the same, and the second query can skip the preparing step, saving database CPU and one extra roundtrip. Query after parameterization:

SELECT * FROM user WHERE name = $1

The connection string for PostgreSQL has now an extra parameter statement_cache_size=N, where one can tweak the number of items stored in the cache, N being the number of unique statements stored. After the cache gets full, adding a new statement will remove the oldest statement from the cache.

By default, we store 500 statements per connection. This means if using more than one connection per instance of Prisma, every connection will have a separate cache. Testing this with 33 connections and cache of 500, the average extra memory needed was about 14-18 megabytes. Caching made our test queries to be 12-33% faster depending on the query.

Due to the nature of pgBouncer, if the pgbouncer parameter is set to true, the statement cache is automatically disabled for that connection.

[pimeys]

PR in Quaint: prisma/quaint#143
Issue: prisma/prisma#2720

[mavilein]

One small extra comment: Caching will also decrease the load on the database server.

[pimeys]

The two queries after parameterization will be the same, and the second query can skip the preparing step, saving database CPU and one extra roundtrip.

I think I wrote about it here. But yeah, you want to cache due to quite big savings in database CPU and a nice cut in response times.