5 ms·

Row Embedded Cache: Experimenting with a new pattern of caching

31 points by recroad 2y ago

arrowleaf 2y ago
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.
- recroad 2y ago
  Yes, very possible. This is a multi-tenant application so I don't want to trigger a view refresh so frequently as most updates don't affect most people.
bastawhiz 2y ago
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).
SPBS 2y ago
> 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
- recroad 2y ago
  Hey, thank you - already did that. Think 100 orders displayed on a page with each row having total, subtotals, discounts, tax owing, and gift card uses. I also get to save a lot of money on server costs by doing this as DB is completely avoided.
ecjhdnc2025 2y ago
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.
fulafel 2y ago
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.

5 ms·

Row Embedded Cache: Experimenting with a new pattern of caching

31 points by recroad 2y ago

arrowleaf 2y ago
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.
- recroad 2y ago
  Yes, very possible. This is a multi-tenant application so I don't want to trigger a view refresh so frequently as most updates don't affect most people.
bastawhiz 2y ago
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).
SPBS 2y ago
> 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
- recroad 2y ago
  Hey, thank you - already did that. Think 100 orders displayed on a page with each row having total, subtotals, discounts, tax owing, and gift card uses. I also get to save a lot of money on server costs by doing this as DB is completely avoided.
ecjhdnc2025 2y ago
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.
fulafel 2y ago
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.