Row Embedded Cache: Experimenting with a new pattern of caching

> KISS to the rescue.

Ha. KISS would be sticking to left joins and profiling their performance (with the appropriate indexes added) and finding alternative solutions only when it’s proven to be a problem, instead of… declaring left joins to be slow and immediately jumping the gun

This is just denormalization.

If you need to invalidate this "cache", it's extremely expensive to find the records that need updating. It's probably better to use a summary table that's populated with triggers, or to just optimize your indexes and use a JOIN (the world won't end, I promise).

All the relations: this is a bit excessive.

But before CTEs this sort of thing had regular use with recursive tree structures, in caching hierarchies. You'd make use of whatever SQL options you had to concatenate a group of IDs into a comma-separated string.

e.g. all the parent nodes of a given node in the hierarchy, and even (selectively) for caching all the descendent node IDs of enclosures. Though there are better ways to do this for trees that could be truly arbitrarily deep.

You can similarly use materialized views for this type of denormalization. When an update to the source tables occur you can trigger a view refresh.

As the quote goes: there are 2 hard problems in computer science: cache invalidation, naming things, and off-by-1 errors. This doesn't cover the hard part yet.