Combine action tables by dullbananas · Pull Request #4459 · LemmyNet/lemmy
github.com
This should cause a huge improvement in query plans, especially for queries that previously reached the from/join collapse limits. For example, getting saved posts might now start with an index sca...

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@programming.dev
    121·
    9 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.

    • morrowind@lemmy.ml
      9·
      9 months ago

      I’m pretty sure the developers have better than chatgpt level knowledge. There’s a reason they’re asking for experts

    • RonSijm@programming.dev
      33·
      9 months ago

      You’re getting downvoted, but it’s not the worst idea to ask it in the general sense:

      Q: is which cases is denormalization of sql tables a good idea? especially PostgreSQL?

      A:

      Denormalization of SQL tables, including PostgreSQL, might be a good idea in the following cases:

      1. Read-Heavy Workloads: When your system mostly reads data from tables rather than writing or updating them, denormalization might enhance the performance of the system. It does this by minimizing the number of joins required to fetch the data.
      2. Performance Optimization: Denormalization can speed up data retrieval times by storing related data in a single table, reducing the need for complex, time-consuming joins.
      3. Complex Queries: If the database is dealing with extremely complicated queries, then making these queries simple, clear, and less complicated can be managed by denormalizing the tables.
      4. Reporting and Analytics: In a reporting database or data warehouse, denormalization helps in faster data retrieval and simplifies queries. It can also make it easier to process aggregated data and generate reports.
      5. Data Redundancy: To increase data redundancy, you might consider denormalization. It helps in data recovery as well.
      6. Schema Simplicity: Denormalization can also simplify the application code by reducing the number of tables that need to be addressed.

      Remember, with denormalization comes redundancies and anomalies, more complex updates/inserts/deletes due to redundant data, along with increased storage costs due to redundancy. So the decision to denormalize should be made carefully considering all these aspects. It’s often a balancing act between improved read performance versus drawbacks like increased storage requirements and potential data inconsistencies.