CQRS with Materialized Views: Scaling Laravel Read Models

Last month, I spent three days fighting a dashboard that brought our primary database to its knees whenever a specific report request hit the API. The culprit wasn't bad code; it was the sheer volume of joins required to aggregate millions of rows in real-time. We were trying to perform analytical heavy lifting on the same table that handled our transactional writes, creating a massive bottleneck.

To fix this, we moved toward a CQRS pattern, separating our transactional write path from a dedicated read model. By leveraging Materialized Views in Postgres and the Laravel Query Builder: Build Complex Database Queries Without Eloquent, we cut our average dashboard load time from roughly 1.8 seconds down to a snappy 240ms.

Why CQRS and Materialized Views?

The core problem in most Laravel applications is the "one-size-fits-all" database schema. Eloquent is fantastic for rapid development, but it’s an abstraction layer that introduces overhead. When you need to display aggregated data, Eloquent models often force the database to perform expensive GROUP BY and SUM operations on every request.

By implementing CQRS, you acknowledge that your read requirements and write requirements are fundamentally different. You don't need the overhead of a full Eloquent model to fetch a pre-calculated report. Instead, you can pull directly from a materialized view. As discussed in my guide on Materialized views for database performance in complex analytical queries, these views act as physical snapshots of your data, turning complex runtime queries into simple, high-speed sequential reads.

Architecting the Read Model

We started by creating a dedicated migration to define our materialized view. We didn't want this view to be live-synced on every write, as that would lock our write tables. Instead, we chose an asynchronous refresh strategy.

PHP
#6A9955">// Database migration
public function up()
{
    DB::statement("
        CREATE MATERIALIZED VIEW sales_summary_report AS
        SELECT product_id, SUM(amount) as total_revenue, COUNT(*) as sales_count
        FROM orders
        GROUP BY product_id
    ");
}

The key to keeping this performant is index management. A materialized view is just a table on disk; if you don't index it, you’re just shifting the performance problem from a complex join to a full table scan.

PHP
#6A9955">// Add an index to the view
DB::statement("CREATE INDEX idx_sales_summary_product_id ON sales_summary_report(product_id)");

Refreshing Data Safely

One common mistake engineers make is trying to refresh the view inside the request lifecycle. That’s a recipe for disaster under high load. We utilize Laravel's queue system to handle updates. If you're interested in how to manage these background tasks efficiently, check out my thoughts on Laravel Performance Optimization: Building Content-Aware Batching Pipelines to keep your workers from getting clogged by stale tasks.

We trigger the refresh using a console command that we fire after our batch processing jobs finish:

PHP
#6A9955">// In your Console Command
public function handle()
{
    DB::statement("REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary_report");
}

The CONCURRENTLY keyword is vital here. Without it, Postgres places an exclusive lock on the view, meaning no one can read from it while it’s refreshing. With it, the database builds a new version of the view in the background and swaps it in, keeping your API responsive even during updates.

Lessons Learned and Trade-offs

We initially tried to use a standard database view, but it didn't solve the latency issues because it calculated the result on the fly every time. Moving to materialized views required us to accept "eventual consistency." Our reports are now about 60 seconds behind the actual live data. For our use case, that’s perfectly acceptable, but it’s a trade-off you must communicate to your stakeholders.

Another hurdle was managing the schema. When our underlying orders table changed, the materialized view broke. We had to implement a deployment script that drops and recreates the views whenever the migration involves the orders table. It’s a bit more manual work during deployments, but the performance gains are undeniable.

FAQ

Does this make my application more complex? Yes. You’re introducing a secondary structure that needs maintenance. Only use this when your read queries become the primary bottleneck of your system.

Can I use Eloquent with Materialized Views? Technically, yes, you can point an Eloquent model at the view. However, I prefer using the Query Builder directly for read models. It’s faster, avoids the overhead of model instantiation, and keeps the separation of concerns clear.

What if I need real-time data? If your business requirements demand zero-latency consistency, materialized views might not be for you. You might look into database proxy strategies or caching layers instead.

I’m still experimenting with how to handle partial refreshes for very large datasets, as REFRESH MATERIALIZED VIEW can get heavy if the table is massive. For now, this architecture has saved our production environment from unnecessary stress. Next time, I might look into partitioning the underlying table to see if we can get even more granular with our refresh cycles.