Introduction
As applications grow, performance bottlenecks can become a major concern, particularly when dealing with large datasets and complex queries. One powerful tool for improving query performance is the use of materialized views.
In this article, we’ll explore what materialized views are, how they differ from regular views, and how you can leverage them in a Ruby on Rails application using the SearchCraft gem. We’ll also look at a practical example where we aggregate sales data, focusing on how to set up, use, and refresh materialized views seamlessly.
What are materialized views?
A materialized view is a database object that stores the result of a query physically. Unlike a regular view, which acts like a virtual table that recalculates the query every time it’s accessed, a materialized view persists the data. This makes it significantly faster for read-heavy operations, particularly when dealing with complex queries or large datasets.
Materialized views are not exclusive to PostgreSQL; they’re also available in other relational databases like Oracle, MSSQL Server, and even some NoSQL database engines.
Materialized views versus regular views
Both materialized and regular views offer different advantages depending on your use case. Here’s a summary of their key differences.
| Regular View | Materialized View |
| Does not store data, only the query | Stores the result of the query physically in the database |
| Recomputes the query each time it’s accessed | Returns precomputed data for faster reads |
| Always up-to-date | Can become stale, requires manual or scheduled refresh |
| Lightweight, for real-time data | Ideal for heavy, complex queries that are run frequently |
When to use materialized views
Materialized views are especially useful in the following scenarios:
- Expensive queries: Queries with large joins, aggregations, or calculations are slow to execute. Materialized views cache the results, reducing the need for recomputation.
- Read-heavy applications: When your app frequently reads data that doesn’t change often, materialized views reduce the load on the database.
- Reporting & analytics: Dashboards or reports that involve heavy aggregations can benefit greatly from materialized views, allowing near-instant data retrieval.
In short, materialized views are perfect when you need fast read performance on complex or resource-intensive queries but can tolerate data that’s not real-time fresh.
Implementing materialized views in Rails with SearchCraft
Let’s walk through a practical example. We’ll optimize query performance in a Rails application by using SearchCraft to define a materialized view that aggregates sales information. The view will calculate total sales for each product and limit the results to the top 10 revenue-generating products.
Bottlenecks and issues we’re addressing
- Expensive queries: Without a materialized view, calculating total sales involves a JOIN between products and orders tables with a SUM aggregation. As data grows, these queries become slow.
- Frequent reads: In read-heavy applications (e.g., dashboards), running this query repeatedly puts a load on the database.
- Performance optimization: Adding indexes to the materialized view (on id and total_sales columns) optimizes filtering and retrieval performance.
SearchCraft benefits
Instead of defining a materialized view directly in Postgres through migrations, we can use SearchCraft::Builder to simplify the process. This brings several advantages:
- Cleaner code: No need for migrations; the view definition is tightly related to its associated, making it more maintainable.
- Easier modifications: Updates to the view query and indexes can be made directly in the builder.
- Easier indexation: The view_indexes method lets you define indexes on the materialized view at the same time.
Defining the materialized view
For our example, let’s assume that we have the following models backed by their corresponding tables
rails g model Product name:string
rails g model Client name:string
rails g model Order product:references client:references quantity:integer price:decimalHere’s how to define the materialized view using SearchCraft. Create a new builder file at app/searchcraft/top_products_builder.rb:
class TopProductBuilder < SearchCraft::Builder
def view_select_sql
<<-SQL
SELECT p.id, p.name, SUM(o.quantity * o.price) AS total_sales
FROM products p
JOIN orders o ON o.product_id = p.id
GROUP BY p.id, p.name
ORDER BY total_sales DESC
LIMIT 10
SQL
end
def view_indexes
[
{ columns: ['id'], unique: true },
{ columns: ['total_sales'] }
]
end
end
Interacting with the materialized view
To interact with the materialized view, we’ll create an ActiveRecord model that treats it like a regular database table. This allows us to perform queries and joins as we would with any other model.
Create the model for the materialized view at app/models/top_product.rb including SearchCraft::Model
class TopProduct < ApplicationRecord
include SearchCraft::Model
self.table_name = 'top_products'
self.primary_key = :id
belongs_to :product, foreign_key: :id
end
Also, let’s link the Product to TopProduct:
class Product < ApplicationRecord
has_many :orders
has_one :top_product, foreign_key: :id
end
If we run migrations now, even though there are no pending migrations, we’ll see the materialized view reflected in our database schema, created by Searchcraft using the builder we’ve defined:
--
-- Name: top_products; Type: MATERIALIZED VIEW; Schema: public; Owner: -
--
CREATE MATERIALIZED VIEW public.top_products AS
SELECT p.id,
p.name,
SUM(((o.quantity)::numeric * o.price)) AS total_sales
FROM (public.products p
JOIN public.orders o ON ((o.product_id = p.id)))
GROUP BY p.id, p.name
ORDER BY (SUM(((o.quantity)::numeric * o.price))) DESC
LIMIT 10
WITH NO DATA;Refreshing the materialized view
By default, materialized views are populated when they are created. However, if more data has been added to the tables after creation, you can refresh the view with:
TopProduct.refresh!Or, to refresh all SearchCraft models in your app, use:
SearchCraft::Model.refresh_all!You can refresh the materialized view either on a scheduled basis (e.g., through a background job) or whenever new data (like an order) is added, depending on your app’s requirements.
Querying the materialized view
Now that the materialized view is set up and up to date, we can start querying it.
To return the top-selling products, we can simply run:
TopProduct.allGiven that we have an index on the total_sales column, you can efficiently filter top-selling products based on a revenue threshold. For example, if you want to find all products that have generated at least $500,000 in total revenue, the query will utilize this index for faster performance:
TopProduct.where('total_sales >= ?', 500_000)If you want to join top products with clients to find clients who ordered any of the top-selling products, you can use the following query as you would do with any ActiveRecord model. (don’t forget to add has_many :orders to the Client model)
Client.joins(orders: { product: :top_product }).distinctThis will return all clients who have purchased at least one of the top 10 revenue-generating products.
Maximizing Rails app performance with materialized views and SearchCraft for faster query results
Materialized views offer a simple yet powerful way to optimize performance for complex, read-heavy queries in your Rails app. By caching the results of expensive queries, you can drastically reduce query execution time, especially in reporting or analytics-heavy features.
Combining them with SearchCraft’s easy-to-use interface for defining views and indexes makes it even more seamless to integrate into a Rails application. Now you can start identifying those slow, frequent queries and leverage materialized views to supercharge your app’s performance.
Explore our Platform Engineering Studio
Our Platform Engineering Studio builds and optimizes the foundations for your business's success. From frontend and backend development to architecture design, we deliver tailored solutions.
