Currently, all prepared statements of a single session are kept in memory until either the session stops (could be due to a node restart) or the prepared statements are dropped via DEALLOCATE command. If a user has long-lived sessions (e.g. due to the usage of the connection pooling) and creates new prepared statements without deallocating the previous ones, the memory usage of all of the prepared statements on the node can reach --max-sql-memory budget in which case the node can effectively become unavailable.

We should consider limiting the memory usage of all prepared statements at the session level, perhaps by implementing LRU cache. Note that the current behavior of never internally deallocating prepared statements is consistent with Postgres, so evicting the prepared statements once the threshold is reached can break the existing applications. However, it seems that the downside of that (an app hitting an error) might be smaller than the downside of keeping all prepared statements live and in-memory (node becoming unavailable).

Jira issue: CRDB-24930