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?

  • @eluvatar
    link
    114 months ago

    This isn’t SQL specific, but a PR whose target is improving performance should measure the performance. It can be a lot of work, especially to get a representative dataset, but it will be worth it, then you can make tweaks to maximize performance, with numbers in hand. Who knows maybe this new design has a flaw and the performance is actually worse, maybe it’s better but it’s not worth the change. Right now you have no idea.

    • @[email protected]
      link
      fedilink
      44 months ago

      Something like a pgmustard or depesz analysis of some painful real world queries can be invaluable here.