Lemmy Project Priorities Observations

If you read enough EXPLAIN ANALYZE output from PostgreSQL you start to get some ideas...

PostgreSQL makes estimates of queries before it dives into it. The same concept could be applied to Lemmy sorting orders and page numbers.

The whole project seem stuck on fringe cases. The reality is that the servers are running slow and overloading with routine fetches of Hot and Active post listings on page number 1, 2, or 3. Sure, some of the apps have speed scrolling to get to page 5 quickly... but there is too much concern out there for how page 970 behaves with a 5 month old posting... and the servers are crashing. Did I mention that the servers have been nonstop crashing since I came around in May?

Today I'm gong to experiment with doing what PostgreSQL does internally before picking an INDEX or a TABLE scan... some hints to the query where to go. For every sort order, there is a natural way to approach routine conventional paging... the trick is how to apply that to both single community and a blend of many communities.

CREATE OR REPLACE FUNCTION post_aggregates_score ()
    RETURNS TRIGGER
    LANGUAGE plpgsql
    AS $$
BEGIN
    IF (TG_OP = 'INSERT') THEN
        UPDATE
            post_aggregates pa
        SET
            score = score + NEW.score,
            upvotes = CASE WHEN NEW.score = 1 THEN
                upvotes + 1
            ELSE
                upvotes
            END,
            downvotes = CASE WHEN NEW.score = - 1 THEN
                downvotes + 1
            ELSE
                downvotes
            END,
            controversy_rank = controversy_rank (pa.upvotes + CASE WHEN NEW.score = 1 THEN
                    1
                ELSE
                    0
                END::numeric, pa.downvotes + CASE WHEN NEW.score = - 1 THEN
                    1
                ELSE
                    0
                END::numeric)
        WHERE
            pa.post_id = NEW.post_id;
    ELSIF (TG_OP = 'DELETE') THEN
        -- Join to post because that post may not exist anymore
        UPDATE
            post_aggregates pa
        SET
            score = score - OLD.score,
            upvotes = CASE WHEN OLD.score = 1 THEN
                upvotes - 1
            ELSE
                upvotes
            END,
            downvotes = CASE WHEN OLD.score = - 1 THEN
                downvotes - 1
            ELSE
                downvotes
            END,
            controversy_rank = controversy_rank (pa.upvotes + CASE WHEN NEW.score = 1 THEN
                    1
                ELSE
                    0
                END::numeric, pa.downvotes + CASE WHEN NEW.score = - 1 THEN
                    1
                ELSE
                    0
                END::numeric)
        FROM
            post p
        WHERE
            pa.post_id = p.id
            AND pa.post_id = OLD.post_id;
    END IF;
    RETURN NULL;
END
$$;

It is interesting to ponder why Reddit had a multi-reddit 100 limit and similar for /r/All blocking limit of 100. As subscribed home page didn't have a 100 limit. What performance reason would be solved by 100 limit if you had to render home pages with 2500 subreddits anyway?

 hot_rank_now_minus1second_score_250 |        current_now         
-------------------------------------+----------------------------
                                6899 | 2023-08-18 19:02:54.807922
(1 row)

 hot_rank_now_minus1day_score_250 |        current_now         
----------------------------------+----------------------------
                               68 | 2023-08-17 19:02:55.808032
(1 row)

 hot_rank_now_minus2day_score_250 |        current_now         
----------------------------------+----------------------------
                               21 | 2023-08-16 19:02:55.808132
(1 row)

 hot_rank_now_minus3day_score_250 |        current_now        
----------------------------------+---------------------------
                               10 | 2023-08-15 19:02:55.80823
(1 row)

 hot_rank_now_minus6day_score_250 |        current_now         
----------------------------------+----------------------------
                                3 | 2023-08-12 19:02:55.808326
(1 row)

 hot_rank_now_minus9day_score_250 |        current_now         
----------------------------------+----------------------------
                                1 | 2023-08-09 19:02:55.808419
(1 row)

 hot_rank_now_minus12day_score_250 |        current_now         
-----------------------------------+----------------------------
                                 0 | 2023-08-06 19:02:55.808512
(1 row)

 hot_rank_now_minus22day_score_250 |        current_now         
-----------------------------------+----------------------------
                                 0 | 2023-07-27 19:02:55.808606
(1 row)

from

executing this:

-- when does hot_rank go flat like old soda?

SELECT hot_rank(250::numeric, timezone('utc', now() - interval '1 second')) AS hot_rank_now_minus1second_score_250, timezone('utc', now() - interval '1 second') AS current_now;

SELECT hot_rank(250::numeric, timezone('utc', now() - interval '1 day')) AS hot_rank_now_minus1day_score_250, timezone('utc', now() - interval '1 day') AS current_now;

SELECT hot_rank(250::numeric, timezone('utc', now() - interval '2 day')) AS hot_rank_now_minus2day_score_250, timezone('utc', now() - interval '2 day') AS current_now;

SELECT hot_rank(250::numeric, timezone('utc', now() - interval '3 day')) AS hot_rank_now_minus3day_score_250, timezone('utc', now() - interval '3 day') AS current_now;

SELECT hot_rank(250::numeric, timezone('utc', now() - interval '6 day')) AS hot_rank_now_minus6day_score_250, timezone('utc', now() - interval '6 day') AS current_now;

SELECT hot_rank(250::numeric, timezone('utc', now() - interval '9 day')) AS hot_rank_now_minus9day_score_250, timezone('utc', now() - interval '9 day') AS current_now;

SELECT hot_rank(250::numeric, timezone('utc', now() - interval '12 day')) AS hot_rank_now_minus12day_score_250, timezone('utc', now() - interval '12 day') AS current_now;

SELECT hot_rank(250::numeric, timezone('utc', now() - interval '22 day')) AS hot_rank_now_minus22day_score_250, timezone('utc', now() - interval '22 day') AS current_now;

    query = match options.sort.unwrap_or(SortType::Hot) {
      SortType::Active => query
        .then_order_by(post_aggregates::hot_rank_active.desc())
        .then_order_by(post_aggregates::published.desc()),
      SortType::Hot => query
        .then_order_by(post_aggregates::hot_rank.desc())
        .then_order_by(post_aggregates::published.desc()),
      SortType::Controversial => query.then_order_by(post_aggregates::controversy_rank.desc()),
      SortType::New => query.then_order_by(post_aggregates::published.desc()),
      SortType::Old => query.then_order_by(post_aggregates::published.asc()),
      SortType::NewComments => query.then_order_by(post_aggregates::newest_comment_time.desc()),
      SortType::MostComments => query
        .then_order_by(post_aggregates::comments.desc())
        .then_order_by(post_aggregates::published.desc()),
      SortType::TopAll => query
        .then_order_by(post_aggregates::score.desc())
        .then_order_by(post_aggregates::published.desc()),
      SortType::TopYear => query
        .filter(post_aggregates::published.gt(now - 1.years()))
        .then_order_by(post_aggregates::score.desc())
        .then_order_by(post_aggregates::published.desc()),
      SortType::TopMonth => query
        .filter(post_aggregates::published.gt(now - 1.months()))
        .then_order_by(post_aggregates::score.desc())
        .then_order_by(post_aggregates::published.desc()),
      SortType::TopWeek => query
        .filter(post_aggregates::published.gt(now - 1.weeks()))
        .then_order_by(post_aggregates::score.desc())
        .then_order_by(post_aggregates::published.desc()),
      SortType::TopDay => query
        .filter(post_aggregates::published.gt(now - 1.days()))
        .then_order_by(post_aggregates::score.desc())
        .then_order_by(post_aggregates::published.desc()),
      SortType::TopHour => query
        .filter(post_aggregates::published.gt(now - 1.hours()))
        .then_order_by(post_aggregates::score.desc())
        .then_order_by(post_aggregates::published.desc()),
      SortType::TopSixHour => query
        .filter(post_aggregates::published.gt(now - 6.hours()))
        .then_order_by(post_aggregates::score.desc())
        .then_order_by(post_aggregates::published.desc()),
      SortType::TopTwelveHour => query
        .filter(post_aggregates::published.gt(now - 12.hours()))
        .then_order_by(post_aggregates::score.desc())
        .then_order_by(post_aggregates::published.desc()),
      SortType::TopThreeMonths => query
        .filter(post_aggregates::published.gt(now - 3.months()))
        .then_order_by(post_aggregates::score.desc())
        .then_order_by(post_aggregates::published.desc()),
      SortType::TopSixMonths => query
        .filter(post_aggregates::published.gt(now - 6.months()))
        .then_order_by(post_aggregates::score.desc())
        .then_order_by(post_aggregates::published.desc()),
      SortType::TopNineMonths => query
        .filter(post_aggregates::published.gt(now - 9.months()))
        .then_order_by(post_aggregates::score.desc())
        .then_order_by(post_aggregates::published.desc()),
    };