Document prepared statements behaviour

[dsandip]

Document prepared statements optimisation/cost. Some information available in a blog post. More information on cost of using this feature is also needed.

[BenoitRanque]

From the postgres documentation:

Prepared statements potentially have the largest performance advantage when a single session is being used to execute a large number of similar statements. The performance difference will be particularly significant if the statements are complex to plan or rewrite, e.g., if the query involves a join of many tables or requires the application of several rules. If the statement is relatively simple to plan and rewrite but relatively expensive to execute, the performance advantage of prepared statements will be less noticeable.

When enabled, statements are prepared before execution.
Because hasura releases connections into the pool after every operation, and a project may be backed by multiple instances, a high throughput is required for prepared statements to make sense:

A query will only use a previously prepared statement if the statement happens to be prepared already for that connection.
Therefore, to see a consistent positive impact, throughput must be high enough to reach instance and connection saturation, where all connections of all instances will have prepared the statement already. This can be challenging given that connections are short lived.

Projects that may not see high throughput on individual queries, and instead favor many simpler but more diverse queries, may instead see a net negative impact from using prepared statements. This is because prepared statements incur a cost: the round-trip to the database to prepare the statement.

[ajohnson1200]

This can be challenging given that connections are short lived.

They don't have to be short-lived though, right? ie: you can / should configure a proper "Connection Lifetime" and if you do that, you'll likely have a really high hit rate on prepared statements, assuming you can any kind of high throughput system, especially since the cost of establishing a database connection is relatively high.

[BenoitRanque]

Fair, I'm assuming default values of 10 minutes as max connection lifetime.
You absolutely can configure them otherwise.

However, I would contend that if changes to pooling behavior are required for optimal use of prepared statements, it should be documented.