Chapter 11: PHP & Laravel: A Polished Service

Laravel's Eloquent ORM treats materialized views with the same straightforward simplicity it applies to everything else. If you can query a table, you can query a materialized view. The framework's task scheduler handles refresh with no additional dependencies, no external services, and no configuration beyond a single method chain. One appreciates the efficiency.

This chapter covers two approaches: the raw Laravel integration (Eloquent model + DB::statement migration) and the tpetry/laravel-postgresql-enhanced package, which elevates Laravel's PostgreSQL support to include Schema-level materialized view creation, refresh, and zero-downtime migrations. It also covers standalone PHP for developers using Symfony, Slim, or no framework at all.

The Model and the Migration

The Eloquent model

class DashboardMetrics extends Model
{
    protected $table = 'mv_dashboard';
    public $timestamps = false;
    public $incrementing = false;
    protected $primaryKey = 'day';
    protected $keyType = 'string';

    protected $casts = [
        'day' => 'date',
        'events' => 'integer',
        'unique_users' => 'integer',
        'revenue' => 'decimal:2',
    ];

    protected static function booted(): void
    {
        static::creating(fn () => false);
        static::updating(fn () => false);
        static::deleting(fn () => false);
    }

    public static function refresh(bool $concurrently = true): void
    {
        $keyword = $concurrently ? 'CONCURRENTLY' : '';
        DB::statement(
            "REFRESH MATERIALIZED VIEW {$keyword} mv_dashboard"
        );
    }
}

$timestamps = false because the materialized view has no created_at or updated_at columns — Eloquent would attempt to set them on every query otherwise. $incrementing = false because the primary key is a date, not an auto-incrementing integer. The booted() method registers model event listeners that return false for creating, updating, and deleting — canceling any write attempt before it reaches the database. This is Eloquent's mechanism for making a model genuinely read-only.

The refresh() static method encapsulates the REFRESH SQL. Call it from a scheduler, a controller, an Artisan command, or a queued job — the materialized view refreshes regardless of where the call originates.

Standard Eloquent querying works immediately:

// In a controller
$metrics = DashboardMetrics::query()
    ->where('day', '>=', now()->subDays(30))
    ->orderByDesc('day')
    ->get();

// Scopes
class DashboardMetrics extends Model
{
    public function scopeLastMonth(Builder $query): Builder
    {
        return $query->where('day', '>=', now()->subMonth());
    }

    public function scopeWithRevenue(Builder $query): Builder
    {
        return $query->where('revenue', '>', 0);
    }
}

// Usage
$profitable = DashboardMetrics::lastMonth()->withRevenue()->get();

// API Resources
return DashboardMetricsResource::collection($metrics);

Eloquent scopes, API Resources, pagination, relationships (if the materialized view includes foreign keys) — everything works as it would for any other model. The materialized view is a table. Eloquent reads from tables.

The migration

return new class extends Migration {
    public function up(): void
    {
        DB::statement('
            CREATE MATERIALIZED VIEW mv_dashboard AS
            SELECT
                date_trunc(\'day\', created_at)::date AS day,
                COUNT(*)::integer AS events,
                COUNT(DISTINCT user_id)::integer AS unique_users,
                SUM(CASE WHEN event_type = \'purchase\'
                    THEN amount ELSE 0 END)::numeric(13,2) AS revenue
            FROM events
            WHERE created_at >= CURRENT_DATE - INTERVAL \'90 days\'
            GROUP BY 1
            WITH DATA
        ');

        DB::statement('
            CREATE UNIQUE INDEX idx_mv_dashboard_day ON mv_dashboard (day)
        ');
    }

    public function down(): void
    {
        DB::statement('DROP MATERIALIZED VIEW IF EXISTS mv_dashboard CASCADE');
    }
};

DB::statement() executes raw SQL — the only option since Laravel's Schema builder does not natively support CREATE MATERIALIZED VIEW. The down() method enables php artisan migrate:rollback to clean up. When the materialized view's query needs to change, create a new migration that drops and recreates — the same pattern as every other framework, since PostgreSQL has no CREATE OR REPLACE MATERIALIZED VIEW.

tpetry/laravel-postgresql-enhanced

For teams managing multiple materialized views, or who prefer Schema-level consistency with the rest of their migrations, the tpetry/laravel-postgresql-enhanced package provides dedicated support. With over 1.4 million Packagist downloads and active maintenance, it is the Laravel ecosystem's answer to Rails' Scenic gem.

composer require tpetry/laravel-postgresql-enhanced

No service provider registration needed — Laravel auto-discovers it.

Schema-level creation

use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

// With raw SQL
Schema::createMaterializedView('mv_dashboard',
    "SELECT date_trunc('day', created_at)::date AS day,
            COUNT(*)::integer AS events,
            COUNT(DISTINCT user_id)::integer AS unique_users
     FROM events
     WHERE created_at >= CURRENT_DATE - INTERVAL '90 days'
     GROUP BY 1"
);

// With query builder
Schema::createMaterializedView('mv_dashboard',
    DB::table('events')
        ->selectRaw("date_trunc('day', created_at)::date AS day")
        ->selectRaw('COUNT(*)::integer AS events')
        ->selectRaw('COUNT(DISTINCT user_id)::integer AS unique_users')
        ->whereRaw("created_at >= CURRENT_DATE - INTERVAL '90 days'")
        ->groupByRaw('1')
);

// Without data — for deferred population
Schema::createMaterializedView('mv_dashboard', '...', withData: false);

Schema-level refresh

Schema::refreshMaterializedView('mv_dashboard');
Schema::refreshMaterializedView('mv_dashboard', concurrently: true);
Schema::refreshMaterializedView('mv_dashboard', withData: false);

Clean, expressive, consistent with Laravel's Schema facade conventions. No raw SQL strings in your refresh logic.

Schema-level drop

Schema::dropMaterializedView('mv_dashboard');
Schema::dropMaterializedViewIfExists('mv_dashboard');

Zero-downtime migrations

The package provides a ZeroDowntimeMigration concern that sets a statement timeout on the database connection, cancelling operations that exceed your deployment window:

use Tpetry\PostgresqlEnhanced\Schema\Concerns\ZeroDowntimeMigration;

class CreateDashboardMv extends Migration
{
    use ZeroDowntimeMigration;

    public function up(): void
    {
        Schema::createMaterializedView('mv_dashboard', '...');
        DB::statement('CREATE UNIQUE INDEX idx_mv_dashboard_day ON mv_dashboard (day)');
    }

    public function down(): void
    {
        Schema::dropMaterializedViewIfExists('mv_dashboard');
    }
}

If the migration exceeds 30 seconds, it is cancelled and the schema is rolled back to its original state. Your deployment continues without interruption. Create the materialized view with withData: false and populate it after deployment via a scheduled refresh.

The same package also provides $table->unlogged() for creating UNLOGGED tables — the Redis session replacement discussed in Chapter 6. One package, two Redis-replacement features.

When to use the package versus raw DB::statement(): the package is worthwhile when managing multiple materialized views, when Schema-level consistency matters to your team, or when you need zero-downtime migration support. For a single materialized view in a small application, raw DB::statement() is simpler and has no additional dependency.

Scheduling the Refresh

Laravel's task scheduler requires no additional packages, no external cron daemon configuration beyond a single system cron entry, and no queue worker for simple scheduled tasks.

// routes/console.php (Laravel 11+)
// or app/Console/Kernel.php (Laravel 10 and earlier)

use Illuminate\Support\Facades\Schedule;

Schedule::call(function () {
    DashboardMetrics::refresh();
})->everyFifteenMinutes()
  ->name('refresh-dashboard-mv')
  ->withoutOverlapping()
  ->onFailure(function () {
      Log::error('Dashboard MV refresh failed');
  });

Four method calls, each earning its place:

everyFifteenMinutes() — self-explanatory, and the interval I recommend as a starting default for most applications.

name('refresh-dashboard-mv') — identifies the task for withoutOverlapping() and for monitoring in schedule:list.

withoutOverlapping() — Laravel acquires a cache lock before execution and releases it after completion. If the previous run is still executing, or another instance in a multi-server deployment attempts the same task, the current run is skipped. This is Laravel's built-in equivalent of the advisory lock pattern from Chapter 5 — the framework handles the coordination that other ecosystems require manual implementation to achieve.

onFailure() — routes errors to your alerting pipeline. Swap Log::error() for a Slack notification, a Sentry capture, or whatever your team uses.

The single system cron entry that powers Laravel's scheduler:

* * * * * cd /var/www/app && php artisan schedule:run >> /dev/null 2>&1

This runs every minute. Laravel's scheduler determines which tasks are due and executes them. The cron daemon only needs to know about this one entry — Laravel manages everything else internally.

Queued job alternative

For applications with Laravel Horizon or a queue worker already running:

class RefreshDashboardJob implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable;

    public $tries = 3;
    public $backoff = 60;

    public function handle(): void
    {
        DashboardMetrics::refresh();
    }
}

Schedule::job(new RefreshDashboardJob)
    ->everyFifteenMinutes()
    ->withoutOverlapping();

$tries = 3 with $backoff = 60 provides automatic retry with 60-second delays — the equivalent of Celery's max_retries and default_retry_delay.

Laravel's queue system supports a database driver backed by PostgreSQL. No Redis required. Run php artisan queue:table && php artisan migrate to create the jobs table, set QUEUE_CONNECTION=database in your .env, and your queue worker processes jobs from PostgreSQL. For the specific use case of refreshing a materialized view every 15 minutes, this eliminates the last Redis dependency in your stack.

And as with every framework chapter: if the refresh requires no application logic, pg_cron is three lines of SQL inside PostgreSQL. No PHP code. No scheduler. No queue worker. No cron daemon.

Standalone PHP

Without Laravel, PDO handles materialized views directly:

$pdo = new PDO(
    'pgsql:host=localhost;dbname=myapp',
    'postgres',
    'secret',
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);

// Refresh
$pdo->exec('REFRESH MATERIALIZED VIEW CONCURRENTLY mv_dashboard');

// Query
$stmt = $pdo->query(
    'SELECT * FROM mv_dashboard ORDER BY day DESC LIMIT 30'
);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($rows as $row) {
    echo "{$row['day']}: {$row['unique_users']} users, ${$row['revenue']}\n";
}

For Symfony applications, Doctrine DBAL's $connection->executeStatement() handles the REFRESH, and standard Doctrine query builder or native SQL handles the reads. Schedule with Symfony's console commands and system cron.

For scheduling without a framework, system cron calling a PHP script:

*/15 * * * * /usr/bin/php /var/www/app/refresh_views.php >> /var/log/mv_refresh.log 2>&1

Or pg_cron inside PostgreSQL — no PHP involvement at all. The materialized view does not know or care which language scheduled its refresh.

Laravel's approach to materialized views reflects the framework's broader philosophy: convention over configuration, sensible defaults, and the assumption that developers would rather build features than configure infrastructure. The Eloquent model works. The task scheduler works. withoutOverlapping() prevents the concurrency problem that other frameworks require advisory locks to solve. The tpetry/laravel-postgresql-enhanced package provides Schema-level elegance for teams that want it.

One appreciates the efficiency.

In the next chapter, we attend to Go — where the philosophy is rather different, the documentation is rather more concise, and the materialized view, as always, does not mind.