3 ms·
Another approach are materialized views, where you basically create a caching table with exactly the fields you need. Can be used for any query, but particularl
by bdcravens 2y ago
Another approach are materialized views, where you basically create a caching table with exactly the fields you need. Can be used for any query, but particularly valuable for rich reporting where you may be pulling data from all over your associations. It requires you to write the source query in raw SQL, but with the Scenic gem, pulls it into the Active Record ecosystem with models, migrations, etc.
- Lio 2y agoI try to avoid materialized views when I can due to the problem of keeping them up to date. If I see a materialized view I’d definitely go back to see if I could eke out any other optimisation in a straight view rather than use it. With regard to Scenic, I like the way it separates out SQL but I’ve always found how it versions view files with an iteration in the file name a pain. I wish it had the option to just pick up changes in the source file without needing a migration to bump that version number. On larger projects it’s easy to run into version number clashes in different pull requests.
- nesarkvechnep 2y agoYou can have multiple small materialised views, which get updated when appropriate and are composed by a regular view. It’s more convenient than a single large materialised view.
- bdcravens 2y agoOn projects I've used it, we're already running scheduled jobs anyways, so another job for refreshing wasn't a problem.
- pawelduda 2y agoOne problem (at least in Postgres) is that you can't partially refresh the view, so it inevitably takes more time as your database grows. There's pg_ivm extension but it limits what can be put in the view definition. Otherwise you'll have to be smart about composing it of smaller, independent views.