I want to show a number of posts, ordered by their score, limited to some number. The score is the product of the number of likes they have (from another table) and the recipricol of their normalized (0 to 1) age.
I have an SQL query that works fine for my MVP, but it neccesitates a full scan of the posts table.
SELECT posts.id, posts.title, 1 / (EXTRACT(EPOCH FROM (NOW() - posts.timestamp)) / EXTRACT(EPOCH FROM MIN(posts.timestamp))) * COUNT(likes) AS score, COUNT(likes) AS likes FROM posts LEFT JOIN likes ON likes.post_id = posts.id GROUP BY posts.id ORDER BY score DESC
MIN(posts.timestamp) could be replaced by some arbitrary fixed epoch.)
The calculations have to be done in the database, since the sorting needs to be done before the limiting.
Thinking towards the longer term, as well as adding personalized feeds, how would one go about building something that can take streams of posts and likes, and continually aggregate those to keep an ordered list of posts relative up to date? I'm planning to use Apache Pulsar for some of my other needs, so that seems like a good starting place, but I'm not sure what would be a suitable tool for the aggregation, and what that might look like. Is this a problem something like Spark would be suited towards?
Edit: I thought perhaps I could create a materialized view based on this query and add an index on the sort column, which would go a long way towards what I need for now. However, I thought Postgres implemented them with triggers and so they would stay up to date, but apparently you have to refresh them, so I'm still open to better ideas.