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?

    • morrowind
      10·
      4 months ago

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

    • xmunk@sh.itjust.works
      6·
      4 months ago

      CharGPT has its uses, performance tuning is not (yet) one of them. I think ML in performance tuning will eventually be a huge deal when we can leverage rapid iterations and culling to perform a wide number of small tweaks and compare performance… I haven’t seen a good solution to this yet, it’s a really complex problem, but I think it’s inevitable.

    • RonSijm@programming.dev
      23·
      4 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.