I am surprised the author says:
> "Old Yakread" has a lot of slow queries. For example, loading the subscriptions page on my account takes more than 10 seconds: for each of my hundreds of subscriptions, it has to run a query to figure out how many unread posts there are and when the most recent post was published.
I would have thought you would grab all this data in a single query roughly like this:
;; Assuming XTDB v1.19+ or v2
(def q
'{:find [?sub-id ?unread-count ?last-pub]
:in [user-id]
:where [[?sub :subscription/user user-id]
[?sub :subscription/feed feed-id]
;; join to posts in that feed
[?post :post/feed feed-id]
[?post :post/published timestamp]
(not [?post :post/read-by user-id])
]
:find [(count ?post) ?unread-count
(max timestamp) ?last-pub]
:order-by [[?last-pub :desc]]})
Agreed you want to keep data in your main database normalized since it's easier to reason about and avoid bugs/inconsistencies in the data. The inherent trade-off is just that it's more computationally expensive to get the denormalized data.
The idea of materialized views is to get the best of both worlds: your main database stays normalized, and you have a secondary data store (or certain tables/whatever inside your main database, depends on the implementation) that get automatically precomputed from your normalized data. So you can get fast queries without needing to introduce a bunch of logic for maintaining the denormalized data.
The hard part is how do you actually keep those materialized views up to date. e.g. if you're ok with stale data, you can do a daily batch job to update your views. If you want to the materialized views to be always up-to-date then things get harder; the solution described in the article is one attempt at addressing that problem.
4b11b4•6mo ago
jacobobryant•6mo ago