Proposal: Opt-in cache "plugin"

[nadilas]

Hi @aarondl,

I came across the following package https://github.com/knocknote/rapidash and I am thinking about checking it out as an opt-in plugin idea (including the relevant template entries) for sqlboiler.

Now I must admit I haven't done a deep dive yet, so before starting out I wanted to ask you if you, looking at the benchmark data, would think this is somewhat interesting to you guys. I'd like to get your opinion on whether it would boost sqlboiler's already good speed. I guess we would have to do some benchmarking without implementing it all really. What is your stance on caching?

[aarondl]

We already do a lot of caching actually. Currently we use mutex locked maps. Can this library be faster than that? Also what data were you thinking to cache?

[ImVexed]

+1

From what I can tell sqlboiler only caches Insert/Update/Upsert which isn't very helpful in my use case or largely read-heavy workload (messaging/chat). Rapidash seems to cache SELECT and even eagerly warms up the cache if desired which would be a game changer for us if it was supported by sqlboiler.

This would also help with cache scalability, since rapidash can be backed with either Redis or Memcached it would allow us to scale up without worrying about cache locality

[nadilas]

@ImVexed that was the idea, but I didn’t yet get around to test it out. Do you want to start working on the plugin?

[ImVexed]

@nadilas I'm afraid between my day job and other pressing side projects I don't have the bandwidth to undertake all of it. I would be able to offer assistance where I can though.

[nadilas]

@ImVexed I see you kinda' started on this. Was it partially successful? Any way I can help out?

[elijahcarrel]

We heavily use enum tables. It would be great if SQLBoiler had a opt-in way to mark certain tables as ones that should be cached on select, so that SQLBoiler need not make a full roundtrip to the database when selecting rows that are already in the cache. That cache could live either in-memory or in an existing cache solution like Redis. Ideally, SQLBoiler could then opaquely use the cache whenever callers try to use SQLBoiler to select already-cached items from a table.

Is anything like that currently possible? @nadilas and @ImVexed were either of you able to get anything working by expanding on SQLBoiler templates? I personally am not sure I follow how templates could solve this problem, since my thinking is that we would need to modify SQLBoiler internals like Bind and Query. Maybe my use case is different than the one proposed here?

[nadilas]

@elijahcarrel I haven’t explored any further. One solution would be to add a middle man without needing to change your codebase: https://github.com/readysettech/readyset

I haven’t tested this approach as we use mssql in our case.

[ImVexed]

@elijahcarrel I never made any attempts due to Rapidash's dependence on mysql specific syntax. Though @kanataxa or @goccy may have more insight on the project not receiving updates anymore.

fwiw, I'm still really interested in having functionality like this. readyset looks pretty cool but another external service seems tedious to manage, I really like the direction that Rapidash was going, by keeping everything in tiered memory LRUs.

My original idea around the templates was to take advantage of the fact that we don't have to start with parsing raw sql statements, and can put logic into sqlboilers pre/post-write hooks to invalidate or update the cache fairly trivially.