If something is read much more frequently than it changes, store it client-side, or store it temporarily in an in-memory-only, not-persisted-to-disk "persistence" layer like Redis.
For example, if you're running an online store, your product list doesn't change all that often, but it's queried constantly. The single source of truth lives in a relational database, but when your app needs to fetch the list of products, it should first check the caching layer to see if it's available there. If not, fetch it from the database, but then write it into the cache so that it's available more quickly the next time you need it.
> When and how to denormalize, why is it needed?
When you need to join several tables together in order to retrieve a result set, and especially when you need to do grouping to get the result set, and the retrieval & grouping is presenting a performance problem, then pre-bake that data on a regular basis, flattening it out into a table optimized for read performance.
Again with the online store example, let's say you want to show the 10 most popular products, with the average review score for each product. As your store grows and you have millions of reviews, you don't really want to calculate that data every time the web page renders. You would build a simpler table that just has the top 10 products, names, IDs, average rating, etc. Rendering the page becomes much more simple because you can just fetch that list from the table. If the average review counts are slightly out of date by a day or two, it doesn't really matter.
> Why append-only and how?
If you have a lot of users fighting over the same row, trying to update it, you can run into blocking problems. Consider just storing new versions of rows.
But now we're starting to get into the much more challenging things that require big application code changes - that's why the grandparent post listed 'em in this order. If you do the first two things I cover above there, you can go a long, long, long way.
If something is read much more frequently than it changes, store it client-side, or store it temporarily in an in-memory-only, not-persisted-to-disk "persistence" layer like Redis.
For example, if you're running an online store, your product list doesn't change all that often, but it's queried constantly. The single source of truth lives in a relational database, but when your app needs to fetch the list of products, it should first check the caching layer to see if it's available there. If not, fetch it from the database, but then write it into the cache so that it's available more quickly the next time you need it.
> When and how to denormalize, why is it needed?
When you need to join several tables together in order to retrieve a result set, and especially when you need to do grouping to get the result set, and the retrieval & grouping is presenting a performance problem, then pre-bake that data on a regular basis, flattening it out into a table optimized for read performance.
Again with the online store example, let's say you want to show the 10 most popular products, with the average review score for each product. As your store grows and you have millions of reviews, you don't really want to calculate that data every time the web page renders. You would build a simpler table that just has the top 10 products, names, IDs, average rating, etc. Rendering the page becomes much more simple because you can just fetch that list from the table. If the average review counts are slightly out of date by a day or two, it doesn't really matter.
> Why append-only and how?
If you have a lot of users fighting over the same row, trying to update it, you can run into blocking problems. Consider just storing new versions of rows.
But now we're starting to get into the much more challenging things that require big application code changes - that's why the grandparent post listed 'em in this order. If you do the first two things I cover above there, you can go a long, long, long way.