Architecting Complexity: CTEs, Views, and Partitioning in Laravel (Performance Part 5)

Eloquent and Laravel’s query builder are incredibly powerful, but sometimes you face a reporting or data-processing task that’s just plain gnarly. A 50-line query with multiple subqueries can quickly become a maintenance nightmare.

This is when we can reach into our PostgreSQL toolbox for more advanced structures that help us manage complexity and, in the right circumstances, boost performance. We’ll look at three key tools: Common Table Expressions (CTEs), Views, and Table Partitioning.

1. Common Table Expressions (CTEs): For Cleaner Complex Queries

Think of a CTE as a temporary, named result set that exists only for a single query. It’s like creating a temporary variable in your PHP code to hold some data before you use it in the next step.

When to use it: Use a CTE when you have a multi-step query, and you want to avoid messy nested subqueries. This makes your SQL much more readable and easier to debug.

A Laravel Scenario:

Imagine you need a report that first calculates the monthly revenue per user, and then calculates the average of that monthly revenue across all users.

The Messy Way (Nested Subquery):

SELECT avg(monthly_revenue) as average_monthly_revenue
FROM (
    SELECT user_id, sum(amount) as monthly_revenue
    FROM orders
    WHERE created_at >= '2025-07-01'
    GROUP BY user_id
) as user_monthly_revenue;

The Clean Way (with a CTE): This is where we can shine in Laravel. While Laravel doesn’t have a dedicated ->with() method for CTEs like it does for relationships, we can easily use DB::raw() or the withExpression() method.

// In a controller or service class
use Illuminate\Support\Facades\DB;

$averageRevenue = DB::table('orders')
    ->selectRaw('avg(monthly_revenue) as average_monthly_revenue')
    ->withExpression('user_monthly_revenue', function ($query) {
        $query->from('orders')
            ->selectRaw('user_id, sum(amount) as monthly_revenue')
            ->where('created_at', '>=', '2025-07-01')
            ->groupBy('user_id');
    })
    ->from('user_monthly_revenue')
    ->first();

This generates much cleaner SQL and is far easier to read and maintain. The book notes that modern versions of PostgreSQL (12+) are very smart about optimizing CTEs, often “inlining” them into the main query for better performance.

2. Database Views: For Reusable Logic

A View is essentially a stored query that you can interact with as if it were a virtual table. It’s a fantastic way to encapsulate complex business logic that you need to reuse in many different places.

When to use it: Use a View when you have a complex join or calculation that multiple parts of your application (and even external reporting tools) need to access. This keeps your logic DRY (Don’t Repeat Yourself) at the database level.

A Laravel Scenario:

Let’s say you frequently need to get stats for your users: their total post count and latest post date. Instead of writing that join and aggregate query everywhere, let’s create a view.

Step 1: Create the View in a Migration

// database/migrations/xxxx_xx_xx_xxxxxx_create_user_stats_view.php
use Illuminate\Support\Facades\DB;

public function up()
{
    DB::statement("
        CREATE VIEW user_stats_view AS
        SELECT
            users.id as user_id,
            users.name,
            users.email,
            COUNT(posts.id) as total_posts,
            MAX(posts.created_at) as last_post_date
        FROM users
        LEFT JOIN posts ON users.id = posts.user_id
        GROUP BY users.id, users.name, users.email
    ");
}

public function down()
{
    DB::statement("DROP VIEW IF EXISTS user_stats_view");
}

Step 2: Create a Read-Only Eloquent Model for the View

// app/Models/UserStat.php
namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class UserStat extends Model
{
    protected $table = 'user_stats_view'; // Tell Eloquent to use our view
    public $timestamps = false; // The view doesn't have timestamp columns

    // You can even define relationships
    public function user()
    {
        return $this->belongsTo(User::class);
    }
}

Now, you can access this complex data with clean, simple Eloquent syntax anywhere in your app:

$userStats = UserStat::where('total_posts', '>', 10)->get();

A Word of Caution: As the book warns, be careful not to treat a view exactly like a table. If you join a view to other tables, the planner might not be able to “push down” your WHERE conditions efficiently, potentially leading to slow queries. They are best used for encapsulating a complete, logical unit of data.

3. Table Partitioning: For Managing Massive Tables

This is the most advanced tool in our kit. Partitioning lets you take one massive, logically single table and split its data into smaller, physically separate tables (partitions) based on a specific key. Think of it like a huge filing cabinet where instead of one giant drawer, you have separate drawers for each year.

When to use it: Use partitioning when you have a table that is growing to an enormous size (hundreds of millions or billions of rows), like a logging table, an audit trail, or an events table.

A Laravel Scenario:

Imagine you have an events table that logs every user action. It’s growing by millions of rows a month. Queries for recent events are fast, but historical reports are grindingly slow. We can partition this table by month.

Creating partitioned tables in a Laravel migration requires raw SQL, as it’s a very specific database feature.

// database/migrations/xxxx_xx_xx_xxxxxx_create_partitioned_events_table.php

public function up()
{
    // 1. Create the "parent" partitioned table
    DB::statement("
        CREATE TABLE events (
            id bigserial not null,
            user_id bigint not null,
            event_type varchar(255) not null,
            created_at timestamp(0) without time zone not null
        ) PARTITION BY RANGE (created_at);
    ");

    // 2. Create the first partition for the current month
    DB::statement("
        CREATE TABLE events_2025_08 PARTITION OF events
        FOR VALUES FROM ('2025-08-01') TO ('2025-09-01');
    ");
}

You would then need a scheduled command (php artisan schedule:run) to automatically create new partitions for upcoming months.

The Performance Win: When you query this table with a date range, PostgreSQL is smart enough to only scan the relevant partitions. This is called partition pruning.

-- This query will ONLY scan the events_2025_08 table, ignoring all others.
SELECT * FROM events WHERE created_at >= '2025-08-10';

This dramatically speeds up queries on large time-series data and also makes deleting old data incredibly fast—you just DROP an old partition instead of running a massive DELETE command.

You now have a powerful set of tools for structuring complex queries and managing huge datasets. The key is knowing when to reach for them.

Next, we’ll confront the biggest challenge in the application-database relationship: the infamous “N+1 Problem” and how the book’s NORM pattern offers a radical but powerful solution.