Looks like I'm going to have to denormalize upvotes either by putting them into their own table or adding them directly to the items table. Currently upvotes are in an Item act table with tips, boost, and job payments which makes certain queries easier but more important queries slower. This has become exacerbated by job payments which now dominate the table given the relative quantity.

The main reason I wouldn't want to add them directly to items is that when trust is recomputed, all old items would effectively go stale in terms of their upvotes and things like 'top' could become inaccurate over long time periods.

Upvotes in their own table though might also have limited scalability at some point.

🤕