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 (
PARTITIONBY community_id
ORDERBY 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!
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.
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 BigIntRun 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 <= 1000Against 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!
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.