Scaling to a Billion Rows: PostgreSQL Partitioning in Laravel 🐘

The 100-Million Row Wall

In enterprise B2B SaaS platforms at Smart Tech Devs, tracking historical data is a compliance requirement. Tables like audit_logs, api_requests, or telemetry_events grow exponentially. When a single PostgreSQL table hits 100 million rows, standard B-Tree indexes become massive and no longer fit into RAM. Query performance degrades from milliseconds to seconds.

Worse, pruning old data becomes an operational nightmare. Running a standard DELETE FROM audit_logs WHERE created_at < '2022-01-01' on a massive table will trigger a massive transaction lock, block incoming inserts, and bloat the database with dead tuples (requiring expensive VACUUM operations). To architect for infinite scale, you must break the monolith using Table Partitioning.

The Solution: Range Partitioning

PostgreSQL Native Table Partitioning allows you to split one massive logical table into multiple smaller physical tables under the hood. For time-series data, we use Range Partitioning by month.

To the Laravel application, you still query AuditLog::all(). But PostgreSQL intercepts the query and instantly routes it to the specific physical table (e.g., audit_logs_2026_06). When you need to delete data older than 2 years, you simply drop the old partition table. It happens in 10 milliseconds, uses zero CPU, and creates zero table locks.

Step 1: Architecting the Partitioned Migration

Laravel's default Blueprint doesn't support native partitioning, so we drop down to raw SQL in our migration to establish the root table and the first few monthly partitions.

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

class CreatePartitionedAuditLogsTable extends Migration
{
    public function up(): void
    {
        // 1. Create the Master Table (Logical wrapper)
        // Notice we do NOT create a standard primary key, as partition keys 
        // must be part of any unique index.
        DB::statement('
            CREATE TABLE audit_logs (
                id UUID NOT NULL,
                tenant_id BIGINT NOT NULL,
                action VARCHAR(255) NOT NULL,
                created_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL
            ) PARTITION BY RANGE (created_at);
        ');

        // 2. Create the Physical Partitions for upcoming months
        DB::statement("
            CREATE TABLE audit_logs_2026_05 
            PARTITION OF audit_logs 
            FOR VALUES FROM ('2026-05-01 00:00:00') TO ('2026-06-01 00:00:00');
        ");

        DB::statement("
            CREATE TABLE audit_logs_2026_06 
            PARTITION OF audit_logs 
            FOR VALUES FROM ('2026-06-01 00:00:00') TO ('2026-07-01 00:00:00');
        ");

        // 3. Create indexes ON the master table (PostgreSQL auto-applies them to partitions)
        DB::statement('CREATE INDEX audit_logs_tenant_idx ON audit_logs (tenant_id);');
    }

    public function down(): void
    {
        DB::statement('DROP TABLE IF EXISTS audit_logs CASCADE;');
    }
}

Step 2: Automating Future Partitions

Because you cannot insert data into a partition that doesn't exist, you must automate partition creation. In Laravel, we set up a simple scheduled Command that runs on the 25th of every month to create the physical table for the next month.

// app/Console/Commands/CreateNextMonthPartition.php
$nextMonth = now()->addMonth();
$tableName = 'audit_logs_' . $nextMonth->format('Y_m');

$start = $nextMonth->startOfMonth()->toDateTimeString();
$end = $nextMonth->addMonth()->startOfMonth()->toDateTimeString();

DB::statement("
    CREATE TABLE IF NOT EXISTS {$tableName} 
    PARTITION OF audit_logs 
    FOR VALUES FROM ('{$start}') TO ('{$end}');
");

The Engineering ROI

Table Partitioning is the ultimate database scalability pattern for time-series logs. It keeps your active indexes small and fully loaded in RAM, making recent data queries blazingly fast. More importantly, it turns the terrifying operation of deleting 50 million old records into a harmless, 10-millisecond DROP TABLE command, ensuring your SaaS never experiences a maintenance window crash.