this post was submitted on 19 Feb 2024
32 points (100.0% liked)

PostgreSQL

668 readers
1 users here now

The world's most advanced open source relational database

Project
Events
Podcasts
Related Fediverse communities

founded 1 year ago
MODERATORS
Ategon
starman
jnovinger
32
Request for SQL experts: Lemmy PR to combine action tables (github.com)
submitted 9 months ago by [email protected] to c/postgresql
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
[–] RonSijm 0 points 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.