Boost Rails app performance with database view backed model

We recently ran into a performance bottleneck in our Rails app. The issue? Calculating the total amount of invoice on the fly, considering discounts, service charges, taxes, and other adjustments.

Every time we load invoice, Rails had to fetch:

• All associated records
• Perform calculations in Ruby
• Return the final amount

This was fine at first, but as our database grew, the response times became unacceptable.

We also needed fancy reports and datatables that displays all invoice totals at once, which became slower and slower. There are a lot of solutions out there, like caching, but they come with their own challenges.

We needed a better way, specially when you're pulling data from different tables.

The solution: Creating database view with scenic gem

We prefer scenic gem, because it provides versioned database views.

1. Install scenic, Add the gem to your Gemfile:

   gem "scenic"

2. Create database view

   $ rails generate scenic:model invoice_totals

   Above generator will create A migration file, A SQL file to add define view, and model that is backed by invoice_totals database view.

3. Now, define view with a SQL query:

   SELECT
     invoices.id AS invoice_id,
     SUM(invoice_items.amount)
     - COALESCE(SUM(discounts.amount), 0)
     + COALESCE(SUM(service_charges.amount), 0)
     + COALESCE(SUM(taxes.amount), 0) AS total
   FROM invoices
   LEFT JOIN invoice_items ON invoice_items.invoice_id = invoices.id
   LEFT JOIN discounts ON discounts.invoice_id = invoices.id
   LEFT JOIN service_charges ON service_charges.invoice_id = invoices.id
   LEFT JOIN taxes ON taxes.invoice_id = invoices.id
   GROUP BY invoices.id;

   Above is simplified version, You can use Common Table Expression (CTE) to simplify complex queries and make them more readable.

4. Run migration

   $ rails db:migrate

5. Let's update InvoiceTotal model to include primary key:

   class InvoiceTotal < ApplicationRecord
     self.primary_key = :invoice_id
   end

   This allows us to query invoice totals easily:

   InvoiceTotal.find(1).total

   No more expensive queries, preloading or Ruby calculations, just instant results!

The impact

After implementing scenic and database views,

• Our codebase became cleaner and simpler by eliminating complex joins and aggregations.
• Later we added total_discounts, total_tax etc to render invoice summary - super easy!
• We removed duplicate logic for calculating these fields
• Reports that took seconds now return in milliseconds

Views are read-only, but for cases like this, where we only need to read precomputed totals, they're perfect.

If your Rails app is struggling with expensive queries and aggregations, may be this is right time to use database views + scenic!

Resources:

• Announcing scenic
• Greatest-Per-Group Query in Rails Using a View Backed Model -- A must-read!