[gilbertbw]

Regarding the new database version issue, I wonder why the caching layer can't just pass any query it is unable to process on to the underlying database?

This would be more complex if the feature you are using does not return a normal table of results back (e.g. the pub/sub support in Postgres).

[BrentOzar]

> I wonder why the caching layer can't just pass any query it is unable to process on to the underlying database?

Because sometimes it's not about the query you pass in - it's about a new data type, for example. The layer may think it understands the query, but may not be able to handle the format of the results, or may cache them incorrectly.

For example, when SQL Server added the JSON data type, one of my customers found that the caching layer mangled it for some clients, but not others, or would return the correct result the first time, but the cached result was formatted incorrectly.

Another problem can be the connection itself, like when SQL Server added support for Always Encrypted, an end-to-end encryption method.

[dspillett]

> I wonder why the caching layer can't just pass any query it is unable to process on to the underlying database?

A caching layer can do that, and I'd be surprised if serious ones don't. It might not be perfect when moving between DB versions though: the new version might perhaps introduce syntax that is mistaken for something else which the layer _thinks_ it understands. While this can be tested for to a large extent by the devs having access to alph/beta/rc versions, but I can still see things slipping through and you are relying on people keeping their versions of the caching layer as up-to-date as everything else which is not a given.

[gvsg-rs]

That is pretty much what Readyset does. The SQL parser identifies queries that are supported (and cached), and passes through all other requests to the backend database.

As we expand query support, we allow users to cache more and more queries.