• RoundSparrow@lemmy.mlOPM
    link
    fedilink
    arrow-up
    1
    ·
    edit-2
    1 year ago

    An even less-intrusive approach is to not add any new field to existing tables. Establish a reference table say called include_range. There is already an ENUM value for each sort type, so include_range table with these columns: sort_type ENUM, lowest_id BigInt, highest_id BigInt

    Run a variation of this to populate that table:

    FROM
      (
         SELECT id, community_id, published,
            rank() OVER (
               PARTITION BY community_id
               ORDER BY published DESC, id DESC
               )
         FROM post_aggregates) ranked_recency
    WHERE rank <= 1000
    

    Against every sort order, including OLD. Capture only two BigInt results: the MIN(id) and the MAX(id) - that will give a range over the whole table. Then every SELECT on post_aggregates / post table includes a WHERE id >= lowest_id AND id <= highest_id

    That would put in a basic sanity check that ages-out content, and it would be right against the primary key!

    • RoundSparrow@lemmy.mlOPM
      link
      fedilink
      arrow-up
      1
      ·
      edit-2
      1 year ago

      A core design issue of either approach is that server operators can modify the building of this data without needing to modify or restart the lemmy_server Rust code.

      Using a smallint also gives some flexibility (or a new field if going with the id min max approach)… if page greater than 10 for a particular sort, go to include > 1 and fall into tiers.