[BE] Denormalize community_id into post_aggregates for a 1000x speed-up when loading posts #3653 (github.com)

submitted 1 year ago by issue_tracking_bot@lemm.ee to c/issue_tracker@lemm.ee

3 comments fedilink hide all child comments

Credit to @phiresky for this idea, originally posted in comments of #2994

This PR adds community_id to post_aggregates (& a new index on post_aggregates) to enable joining community directly to post_aggregates when querying posts.

On lemm.ee, this optimization speeds up the query for front page of subscribed posts ~1000x, from several seconds to to just milliseconds. You can check a before/after of query plans here: https://gist.github.com/sunaurus/856e03165bb0c0010505afeebde45230

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

[-] issue_tracking_bot@lemm.ee 1 points 1 year ago

post_aggregates should be the "main" table that the others are joined to, and primarily filter from.

Is there a specific reason the post_aggregates and post table are spilt? In general I think it would make sense to have everything in the same table. (same for comments /person...). 1:1 relations aren't that great in postgresql in general imo, tables with a huge amount of columns work pretty well. It would increase contention a bit because every change in votes would lock the post row for a bit (just writes, not reads) but I don't think that justifies the split. Or is there other reasons for the spilt?

Originally posted by phiresky in #3653

this post was submitted on 18 Jul 2023

0 points (50.0% liked)

Issue Tracker

143 readers

1 users here now

Welcome to the Lemmy Issue Tracker!

Here you can share your ideas and report issues related to the Lemmy project. We welcome all feedback and suggestions to help us improve the platform.

Guidelines for submitting issues

Please write a single idea or issue per post/comment.
Use the following format for your post title:
- [BE] or [UI] for backend or web-ui issues.
- The type of issue: [Bug], [Feature Request], etc.
If the issue is resolved, please add [Closed] at the start of the comment.

How to contribute

If you want to contribute to the development of Lemmy, please check out our GitHub repository. You can also join our Matrix chat to discuss ideas and issues with the community.

Thank you for your support and contributions to the Lemmy project!

founded 1 year ago

MODERATORS

GodOfThunder@lemm.ee