Request for SQL experts: Lemmy PR to combine action tables (github.com)

submitted 8 months ago by dessalines@lemmy.ml to c/postgresql@programming.dev

13 comments fedilink hide all child comments

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?

you are viewing a single comment's thread
view the rest of the comments

[-] dessalines@lemmy.ml 2 points 8 months ago

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_action table would be fine.

[-] xmunk@sh.itjust.works 4 points 8 months ago

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!