Tame complex queries with database views
Accept-Language: en, fr, *;q=0.1
Please ask questions 🙋
(or share your knowledge)
What you'll (hopefully) learn
- What are DB views?
- When are they useful?
- How to use them with Rails
Why are you here talking about views?
I hear you ask
About me
Back to the subject at hand
Code walkthrough
To recap
Views
- Are a way to turn an SQL query into something that quacks like a table
- Behave like a subquery from a performance perspective
- Can have a standard
ActiveRecord::Basein front of them (and be tested in much the same way) - Can be created and managed with the Scenic gem
Materialized views
- Are written to disk
- Behave like a real table from a performance perspective
- Can be indexed
- Need periodic refreshing
- Can be refreshed concurrently provided there's a unique index
How are they useful?
Example 1
Combine two resources into one cohesive whole
Example 2
Make faceted search logic easier to maintain and reason about
Example 3
Provide useful abstractions across different domains
In general
- Views can be a great tool to DRY up your code base by creating an abstraction for complex or duplicated query logic
- They can postpone the need for manual denormalization, ETLs, or additional datastores by leveraging what you already have
Real-world example
Mastodon
What are the trade-offs?
Trade-offs
Views
- Are only as performant as the queries that define them, their underlying tables, and the planner's ability to sort things out
- Can make it very easy to paper over mountains of complexity
Trade-offs
Materialized views
- Easier to reason about for reads
- Refreshes can be quite taxing and may become a limiting factor
- Stale data
💁 pg_ivm seems promising, and there has been talk of a first-party solution.
Trade-offs
Performance?
- You are probably not Shopify
- Humans are expensive
- Measure measure measure