Financial services
Expertise in core banking, BaaS integrations, payments, and GenAI-enhanced financial solutions.
Healthcare
People-centric healthcare design and solutions, from virtual care, integrations, to smart devices.
Insurance
Modern solutions including self-service, on-demand, and algorithm-driven personalization.
We’re purposefully shaping the digital future across a range of industries.
Discover some of our specific industry services.
Discover moreOctober 29, 2024
Discover how materialized views in Rails can improve query performance for read-heavy apps. Learn setup and refresh techniques with SearchCraft!
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.
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.
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 |
Materialized views are especially useful in the following scenarios:
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.
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.
Instead of defining a materialized view directly in Postgres through migrations, we can use SearchCraft::Builder to simplify the process. This brings several advantages:
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
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;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.
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.
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.
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.
Receive regular updates about our latest work
When a new client came to Qubika due to significant performance issues with their React Native mobile application on the brink of release, Qubika’s App Solutions Studio took the lead in solving the problem. Through a comprehensive approach involving code analysis, performance testing, and targeted optimizations, we optimized the React Native app’s performance and significantly enhanced the user experience.
Gain insights from our project management experts on high-performance teams and strategies to help them thrive in the constantly evolving software industry.
A step-by-step guide to some of our mobile app designers’ best practices.
Get in touch with our experts to review your idea or product, and discuss options for the best approach
Get in touch