3 ms·

In the Microsoft SQL Server space, several of these vendors have come and gone. My clients have been burned badly by 'em, so a few quick lessons learned: Be aw

In the Microsoft SQL Server space, several of these vendors have come and gone. My clients have been burned badly by 'em, so a few quick lessons learned: Be aware that there are hundreds of open issues[0] and dozens of pull requests [1], some of which involve clients being unable to connect or not supporting all components of the SQL language. Just because your database supports something, doesn't mean your caching layer will. It gets really ugly when a new version of your database comes out, with brand new features and language enhancements, and the caching layer doesn't support it. It may take months, or in some cases years, before the caching layer is feature-complete with the underlying database. If you want to use some of those language enhancements, then your app may have to maintain two connection strings - one for the caching layer, and one for direct database queries that the caching layer doesn't support. Your support teams need to learn how to diagnose problems with the caching layer. For example, this issue [2] talks about the level of work involved with understanding why newly inserted data isn't showing up in selects. I hope they succeed and deliver the concept, because it's one of the holy grails of databases. [0]: https://github.com/readysettech/readyset/issues https://github.com/readysettech/readyset/issues [1]: https://github.com/readysettech/readyset/pulls https://github.com/readysettech/readyset/pulls [2]: https://github.com/readysettech/readyset/issues/39 https://github.com/readysettech/readyset/issues/39

gilbertbw 3y ago
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).
- gvsg-rs 3y ago
  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.
- dspillett 3y ago
  > 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.
- BrentOzar 3y ago
  > 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.
hobofan 3y ago
I don't think it's fair to hold the number of open issues and pull requests against them. Looking through them for a minute, it looks like 95%+ are from their own team members, with a good chunk of the issues being "low priority" issues. So you are just seeing the typical ever-growing backlog that is normally in a private JIRA instance. Having said that, the way they work with pull requests is unlike anything else I've seen. I see that they are using a merge bot, but apart from that all branch names are completely illegible. As a lot of the team seems to be present in this thread, it would be interesting to get some details about that.
- lukoktonos 3y ago
  We use Gerrit internally and use GitHub as a pseudo-mirror. We allow (and welcome!) community contributions to GitHub via PRs. Our internal Changelists in Gerrit get synced to GitHub as those PRs you are seeing (the branch names correspond to Gerrit change-ids), and any community PRs get copied to our Gerrit first before going through CI and then being merged and synced with GitHub.
gwbas1c 3y ago
They use the database's replication API. In theory, it should avoid situations where the cache doesn't understand query syntax. But, what I'd worry about are situations where an application needs to cache an object that's expensive to build. (And perhaps expensive to build because the programmer doesn't understand how to optimize SQL.)

gilbertbw 3y ago

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).

gvsg-rs 3y ago
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.
dspillett 3y ago
> 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.
BrentOzar 3y ago
> 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.

hobofan 3y ago

I don't think it's fair to hold the number of open issues and pull requests against them. Looking through them for a minute, it looks like 95%+ are from their own team members, with a good chunk of the issues being "low priority" issues. So you are just seeing the typical ever-growing backlog that is normally in a private JIRA instance. Having said that, the way they work with pull requests is unlike anything else I've seen. I see that they are using a merge bot, but apart from that all branch names are completely illegible. As a lot of the team seems to be present in this thread, it would be interesting to get some details about that.

lukoktonos 3y ago
We use Gerrit internally and use GitHub as a pseudo-mirror. We allow (and welcome!) community contributions to GitHub via PRs. Our internal Changelists in Gerrit get synced to GitHub as those PRs you are seeing (the branch names correspond to Gerrit change-ids), and any community PRs get copied to our Gerrit first before going through CI and then being merged and synced with GitHub.

gwbas1c 3y ago

They use the database's replication API. In theory, it should avoid situations where the cache doesn't understand query syntax. But, what I'd worry about are situations where an application needs to cache an object that's expensive to build. (And perhaps expensive to build because the programmer doesn't understand how to optimize SQL.)