[Bug]: activity table will overflow 32bit signed int at some point

[phiresky]

Requirements

• [X] Is this a bug report? For questions or discussions use https://lemmy.ml/c/lemmy_support
• [X] Did you check to see if this issue already exists?
• [X] Is this only a single bug? Do not put multiple bugs in one issue.
• [X] Is this a backend issue? Use the lemmy-ui repo for UI / frontend issues.

Summary

The activity table uses integer as a primary key, so insertions will start failing with nextval: reached maximum value of sequence when 2 billion rows have been inserted. Right now select max(id) from activity is at around 20 million on lemm.ee, probably more on the bigger instances.

for reference, reddit had around 238862690 comments, 4463366 posts in december 2022, with 7 score per comment on average and 45 score per post on average, so assuming double the score as votes that's ((238862690 × (1 + 7 * 2)) + (4463366 × (1 + 45 * 2))) = 3.9 billion activities per month.

Steps to Reproduce

Technical Details

Version

0.18.1

Lemmy Instance URL

No response

[Nutomic]

The id column is actually not used at all in the code, so we could remove it without any problem (assuming it doesnt have negative impacts on postgres performance). I will make a PR in that area soon so we can discuss it there.