Lemmy currently uses distinct tables like post_like: (post_id, person_id, score) and post_saved. Unfortunately this causes performance issues when we have to join many of these tables to create views.
One suggestion in this PR, is to combine these into a single post_action table, with a lot of optional columns depending on the action. This solution scares me a little, because I’m afraid we might lose data integrity, and many of our constraints with so many optional columns.
Is there a better way of doing this in SQL?
No probs, thx for your help! We should be okay with writes in close succession, as long as it only updates that specific column. I think I’m coming around to the idea that a
post_actiontable would be fine.Yea, I’ve worked as a data architect - I share your knee-jerk fear of denormalization but I read the proposal over and I agree: it’s dangerous if done sloppily but as long as you’re careful it’s do-able.
I’ve been working for a while on a product for medical professionals. It’s an absolute blessing to my hair-line that Doctors consider anything faster than two minutes to essentially be instantaneous. Unfortunately the lemmy user base hasn’t been dissuaded that a better world could exist by decades of horribly written software. Locks and setting aside a few dozen milliseconds for mat view refreshes are perfectly acceptable in my day job… but these darn Lemmy users expect a performant and stable product.
Just again though, thanks for the good work!