Query cache for CrateDB

[amotl]

About

We regularly share a few thoughts about query caching in CrateDB, to increase read performance for frequent redundant queries. This discussion thread is meant to better materialize them.

Details

PostgreSQL had an early PostgreSQL Query Cache (*2011), see pqc, but has probably been superseded by Pgpool's In Memory Query Cache and other implementations today. Internally, PostgreSQL's caching exclusively applies to save disk i/o as a page-level cache, right?

Note there is also a long-running request to improve corresponding documentation about the underlying caching mechanisms of CrateDB inherited by Elasticsearch.

• Document CrateDB caching crate#11236

Note that some are also asking about How to clear the query cache?, while there isn't anyone yet.

Thoughts

Of course, you can always employ an application-based cache, but sometimes, you just can't. In this spirit, this discussion has a primary focus on transparent caching like Pgpool is doing it, in a very traditional way to trade memory for speed.

[amotl]

Landscape

Coming from pqc and Pgpool, but also looking at other solutions, transparent proxying or not.

• https://github.com/snaga/pqc
• https://github.com/dpaks/pqc-master
• Pgpool memqcache (source, docs, tutorial)
• https://github.com/rapidloop/ellycache
• https://github.com/readysettech/readyset
• https://github.com/gatewayd-io/gatewayd-plugin-cache (docs)
• https://github.com/tempest98/pgcache
• https://github.com/stevenewald/lattice

[amotl]

We started demonstrating how to use Pgpool-II and Readyset with CrateDB in the cratedb-examples repository.

• Pgpool-II: Example about "In Memory Query Cache" cratedb-examples#1292
• Readyset: Example about query caching cratedb-examples#1293

[seut]

We regularly share a few thoughts about query caching in CrateDB, to increase read performance for frequent redundant queries. This discussion thread is meant to better materialize them.

Would be great if we could come up with some rough numbers to understand if using a dedicated query cache will increase read performance at all, given also that CrateDB has it's own (single node) query cache.

[amotl]

Thanks for your reply and interest on this topic. Yes I agree. It is the node query cache you are referring to here, right? This has also been mentioned over there at crate/crate to elaborate/educate better about it.

• Document CrateDB caching crate#11236

By reading up on the ES documentation, it reveals to me that, other than the cache is per-node, while it might be a query-based cache, the cached entities are actually Lucene segments, so it is not a total query result cache, contrary to what I was looking for, right?

Caching is done on a per segment basis if a segment contains at least 10000 documents and the segment has at least 3% of the total documents of a shard. Because caching is per segment, merging segments can invalidate cached queries.

The relevant details are also revealing that the cache will automatically get invalidated on segment merges. While this is certainly the right decision for this type of built-in cache, I was deliberately in search for a dedicated result cache where you can configure cache expiration ttl values, when applicable, exactly like Pgpool-II or Readyset are providing this feature.

In this spirit, transparent caching proxies of this kind can act as solid buffer elements towards intensively frequent and recurring read queries of the same kind, which should deliberately be cached by policies the system designer wants to enforce differently, possibly for certain high-traffic read paths only, like dashboarding. I know this adds an anomaly to the promise that CrateDB can handle it all, but I think it's a valuable proposition because it's just the same component other people are using for the very same use cases, to make redundant read requests of the same kind not bother the database at all, when required.