PERF: Cache ranks for featured badges, to simplify user serialization (PR #8698)

This introduces a new featured_rank column on the user_badges table. This is kept up-to-date automatically, and makes it much easier to load badges for user profiles and user cards.


You’ve signed the CLA, davidtaylorhq. Thank you! This pull request is ready for review.

It does seem like updating the featured ranks of all badges could be quite costly. Can you confirm that you tested the update_featured_ranks! method on a large dataset to see how it performs?

        #{"AND user_id = #{user_id.to_i}" if user_id}

Protect ourselves against injection in the future?

        #{"WHERE user_badges.user_id = #{user_id.to_i}" if user_id}

This worries me a bit, what is the cost of the new big query on one of our larger sites?

will this lock up the table in a transaction? do we need to be careful here and run it outside of a transaction?

Overall I really like this change, we just have to be super careful with upgrade and need to make sure we are not introducing extreme costs to our daily schedule.

During the transaction, it holds RowExclusiveLocks over the rows being updated. That means existing rows in the UserBadge table cannot be updated by any other process during this time. I think new rows could still be added, since no table-level locks area being held.

Yes, I did a few tests to make sure it’s not too expensive. Running this as a SELECT, rather than UPDATE, I tested this on a few sites.

On meta with 41k users, and 94k user_badges, it took 0.6 seconds. On a site with 4.2 million users, and 590k user_badges, it took 2.3 seconds