The work in this PR is required before deploying trouni/predictor-vue#212
Creation of materialized database views
Since we have a lot of calculations needed that involve data between different tables, the different queries and calculations outside of ActiveRecord have been hitting our database pretty badly, and loading the matches or leaderboards often took a few seconds for a single API call.
This PR uses the scenic gem to create SQL views (i.e. "virtual" tables based on the results of a specific query) that we can access with the convenience of a Rails model. Normal views simply run the query every time, which would already be a significant performance improvement for us, but I have set the views as "materialized" meaning that the results of the query are stored in an actual database table to further speed up the query time. This however means that the views need to be manually refreshed when adding or updating relevant data. I have added callbacks in the relevant models to refresh the materialized views on create or update.
I have created three materialized views:
match_results
user_scores (dependent on match_results)
leaderboard_rankings (dependent on match_results and user_scores
Other database migrations
I have made some migrations to remove some of the complexity in the code by adding some redundancies in the columns:
the choice enum has been converted to a string enum in order to make the SQL queries more robust and explicit (the queries need to be made based on the actual value stored in the DB, so 0, 1 or 2 for the choice is not great)
the round_id and competition_id is now always set on a match, even if it belongs to a group. This is a bit redundant, but makes it much simpler to get all the rounds and it allows us to remove the super || group&.round workaround to access the correct round from a match, which was creating a lot of N+1 queries. A set_round method automatically assigns the round from the group if it is present before running validations, so there is no need to change the seeds or the way we create rounds.
add a points attribute to rounds, instead of relying on a method. This allows to use the points inside of the SQL views, and also to override the points value if we want to for specific rounds. Here as well, the points are automatically set with the same rule in a before_validation callback, so nothing needs to be changed in the seeds or scraper
Removal of unnecessary code
I have removed methods that were not used, most notably in the Group and Team models since we are not showing the competition's score and don't have plans to do so at this stage.
Some methods in the User and Match model were unused or made obsolete by the database views, and have been removed.
Refactoring of views
The views have been cleaned up using partials.
Other changes
the bullet gem was added to the development dependencies
the progress_bar gem was added to display progress in the migration, but we could also use it for the seeds
The work in this PR is required before deploying trouni/predictor-vue#212
Creation of materialized database views
Since we have a lot of calculations needed that involve data between different tables, the different queries and calculations outside of ActiveRecord have been hitting our database pretty badly, and loading the matches or leaderboards often took a few seconds for a single API call.
This PR uses the
scenic
gem to create SQL views (i.e. "virtual" tables based on the results of a specific query) that we can access with the convenience of a Rails model. Normal views simply run the query every time, which would already be a significant performance improvement for us, but I have set the views as "materialized" meaning that the results of the query are stored in an actual database table to further speed up the query time. This however means that the views need to be manually refreshed when adding or updating relevant data. I have added callbacks in the relevant models to refresh the materialized views on create or update.I have created three materialized views:
match_results
user_scores
(dependent onmatch_results
)leaderboard_rankings
(dependent onmatch_results
anduser_scores
Other database migrations
I have made some migrations to remove some of the complexity in the code by adding some redundancies in the columns:
choice
enum has been converted to astring
enum in order to make the SQL queries more robust and explicit (the queries need to be made based on the actual value stored in the DB, so 0, 1 or 2 for thechoice
is not great)round_id
andcompetition_id
is now always set on a match, even if it belongs to a group. This is a bit redundant, but makes it much simpler to get all the rounds and it allows us to remove thesuper || group&.round
workaround to access the correct round from a match, which was creating a lot of N+1 queries. Aset_round
method automatically assigns the round from the group if it is present before running validations, so there is no need to change the seeds or the way we create rounds.points
attribute to rounds, instead of relying on a method. This allows to use the points inside of the SQL views, and also to override the points value if we want to for specific rounds. Here as well, the points are automatically set with the same rule in abefore_validation
callback, so nothing needs to be changed in the seeds or scraperRemoval of unnecessary code
I have removed methods that were not used, most notably in the Group and Team models since we are not showing the competition's score and don't have plans to do so at this stage.
Some methods in the User and Match model were unused or made obsolete by the database views, and have been removed.
Refactoring of views
The views have been cleaned up using partials.
Other changes
bullet
gem was added to the development dependenciesprogress_bar
gem was added to display progress in the migration, but we could also use it for the seeds