Refreshing comments on an active post is one of the hardest-hitting requests for the server performance-wise in 0.18.1 era.

see comments

  • RoundSparrowOP
    1·
    1 year ago

    That API call is generating this SQL query to PostgreSQL back-end:

    SELECT "comment"."id", "comment"."creator_id", "comment"."post_id", "comment"."content", "comment"."removed", "comment"."published", "comment"."updated", "comment"."deleted", "comment"."ap_id", "comment"."local", "comment"."path", "comment"."distinguished", "comment"."language_id", "person"."id", "person"."name", "person"."display_name", "person"."avatar", "person"."banned", "person"."published", "person"."updated", "person"."actor_id", "person"."bio", "person"."local", "person"."private_key", "person"."public_key", "person"."last_refreshed_at", "person"."banner", "person"."deleted", "person"."inbox_url", "person"."shared_inbox_url", "person"."matrix_user_id", "person"."admin", "person"."bot_account", "person"."ban_expires", "person"."instance_id", "post"."id", "post"."name", "post"."url", "post"."body", "post"."creator_id", "post"."community_id", "post"."removed", "post"."locked", "post"."published", "post"."updated", "post"."deleted", "post"."nsfw", "post"."embed_title", "post"."embed_description", "post"."thumbnail_url", "post"."ap_id", "post"."local", "post"."embed_video_url", "post"."language_id", "post"."featured_community", "post"."featured_local", "community"."id", "community"."name", "community"."title", "community"."description", "community"."removed", "community"."published", "community"."updated", "community"."deleted", "community"."nsfw", "community"."actor_id", "community"."local", "community"."private_key", "community"."public_key", "community"."last_refreshed_at", "community"."icon", "community"."banner", "community"."followers_url", "community"."inbox_url", "community"."shared_inbox_url", "community"."hidden", "community"."posting_restricted_to_mods", "community"."instance_id", "community"."moderators_url", "community"."featured_url", "comment_aggregates"."id", "comment_aggregates"."comment_id", "comment_aggregates"."score", "comment_aggregates"."upvotes", "comment_aggregates"."downvotes", "comment_aggregates"."published", "comment_aggregates"."child_count", "comment_aggregates"."hot_rank", "community_person_ban"."id", "community_person_ban"."community_id", "community_person_ban"."person_id", "community_person_ban"."published", "community_person_ban"."expires", "community_follower"."id", "community_follower"."community_id", "community_follower"."person_id", "community_follower"."published", "community_follower"."pending", "comment_saved"."id", "comment_saved"."comment_id", "comment_saved"."person_id", "comment_saved"."published", "person_block"."id", "person_block"."person_id", "person_block"."target_id", "person_block"."published", "comment_like"."score"

FROM ((((((((((("comment"
INNER JOIN "person" ON ("comment"."creator_id" = "person"."id"))
INNER JOIN "post" ON ("comment"."post_id" = "post"."id"))
INNER JOIN "community" ON ("post"."community_id" = "community"."id"))
INNER JOIN "comment_aggregates" ON ("comment_aggregates"."comment_id" = "comment"."id"))
LEFT OUTER JOIN "community_person_ban" ON (("community"."id" = "community_person_ban"."community_id") AND ("community_person_ban"."person_id" = "comment"."creator_id")))
LEFT OUTER JOIN "community_follower" ON (("post"."community_id" = "community_follower"."community_id") AND ("community_follower"."person_id" = $1)))
LEFT OUTER JOIN "comment_saved" ON (("comment"."id" = "comment_saved"."comment_id") AND ("comment_saved"."person_id" = $2)))
LEFT OUTER JOIN "person_block" ON (("comment"."creator_id" = "person_block"."target_id") AND ("person_block"."person_id" = $3)))
LEFT OUTER JOIN "community_block" ON (("community"."id" = "community_block"."community_id") AND ("community_block"."person_id" = $4)))
LEFT OUTER JOIN "comment_like" ON (("comment"."id" = "comment_like"."comment_id") AND ("comment_like"."person_id" = $5)))
LEFT OUTER JOIN "local_user_language" ON (("comment"."language_id" = "local_user_language"."language_id") AND ("local_user_language"."local_user_id" = $6)))

WHERE (("comment"."post_id" = $7) AND (("community"."hidden" = $8) OR ("community_follower"."person_id" = $9))) ORDER BY "comment"."published" DESC LIMIT $10 OFFSET $11
    • RoundSparrowOP
      1·
      1 year ago

      I propose that /fastAPI/v1/ path does not use this backend Query and focuses more on performance.

      JOINS:
      community-blocking. If a user is banned, can’t they just create another account or use anonymous?
      comment_like - in high performance, knowing if the person reading has liked which comments on this posting could be cached independent. Another API call to get comment_like for an individual user and posting comments.
      local_user_language - let clients get the language preferences for an end-user and take this off the query for every single load of comments off the server.
      post - do not JOIN on post, require another API call and the client application use that data. Don’t return it on every single comment.
      community - same as post.
      person - return the raw creator_id and an API to fetch all the creator_id mappings for the comments. Allow the client to cache and merge those manually.

      INTERNAL CACHING:

      The API should use a cache and not require the database to reference ("community"."hidden" = $8) in the WHERE clause. The number of communities is in the order of 100,000 or less, and caching if those are hidden in RAM (or SQLite database) wold be of value because you would never execute this entire query if the post was in a hidden community.

      Which raises another issue about the Lemmy API and concepts. Comment links are orphan, they are not tied to a community. But in practice, they are tied to a post, but the URL links that end-users see on lemmy-ui (and search engines) do not have the post or the community in mind. Should the community_id or name be required to load comments, so that posting ID is no longer system-wide?