DB: scoring function as materialized view #1
Description
Abstract
Late night thought: replace scoring function with postgres materialized views and replace current cache handling, done with an Async LRU Cache-alike, with postgresql's views and a repeated pg_cron job for view refreshing.
idea
Replace calculation of scores with count queries (see queries in api/db/schema.py with materialized views.
benefits
No need to handle cache (for queries) and run count queries repeatedly. Will simplify overall runtime work. Might also help a lot with perf depending in efficient implementation of views. Should also reduce overall strain on DB for scoring queries.
downsides
- We still (should) handle cache for overall call. Basically doubling the overall caching of scoring queries. Might also have unforseen side-effects.
- requires some sort of sqlalchemy/ Gino extension for creating & managing views. Might also mess with alembic
- Will require some sort of refresh mechanism for views (pg_cron seems appropiate)
- Will make stack more complex for beginners