Use materialized views for all charts
jace opened this issue · 7 comments
In the spirit of #418, materialized views can be used wherever we have expensive queries that collate data for a day, week or month. These include:
- The statistics chart for job posts
- The statistics chart for campaigns
- The admin dashboard graphs for hourly (48h) and daily (2 weeks)
- The admin historical dashboard
@jace creating the mviews need a call to create_all() or Table.create(). What's the best way to do this that you can think of, so that we dont have to call it manually every time something changes? migrations? but then we'll have to import the mview models to migrations.
Migrations, and always make a copy of the model in the migration.
I went through that link and the method to declare views in sqlalchemy all of last week. But it seems sqlalchemy doesn't really work anymore like it's described there. Even when I got it to create the mviews, it'll still try to create a table with the given columns from the query.
From my previous experience, I think it might be enough to -
- write a Model class with the columns defined and the query in a private variable just for reference
- find a way to make alembic ignore that model and write migration to actually create the mviews
- leave comment in the mview model class that - If we ever need to change the query in the mview, then just write another migration dropping and recreating the mview with the new query
This won't work because we don't use Alembic for new deployments. See point 6 in hasgeek/coaster#100.
Have you tried using __abstract__ to prevent table creation? http://docs.sqlalchemy.org/en/latest/orm/inheritance.html?highlight=abstract#abstract-concrete-classes
I know about abstract, I haven't checked if abstract models can be queried just like regular models in sqlalchemy or if there is any limitation internally.