orptech/laravel-migration-partition
Laravel package that adds migration helpers for creating partitioned database tables. Define partition strategies in your migrations and let the package generate and manage partitioned schemas, keeping large datasets fast without hand-written SQL.
The package extends Laravel’s schema builder so partition clauses can live inside normal migrations instead of raw DB::statement() calls. It is most useful for append-only, time-series tables: logs, events, audit trails, notifications, metrics, and similar tables that grow forever and are usually queried by a date range.
Minimal setup:
composer require orptech/laravel-migration-partition
There is normally no config file to publish. The service provider is auto-discovered and swaps in the package’s schema grammar/blueprint extensions. Confirm the package’s README for your installed version, then check the package source under src/Schema to see which blueprint methods and drivers are supported.
A first migration should stay simple. This creates an ID-range partitioned table:
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration
{
public function up(): void
{
Schema::create('audit_logs', function (Blueprint $table) {
$table->id();
$table->string('event');
$table->json('payload')->nullable();
$table->timestamp('created_at')->useCurrent();
$table->partitionByRange('id', [
'p0' => 'LESS THAN (1000000)',
'p1' => 'LESS THAN (2000000)',
'pmax' => 'MAXVALUE',
]);
});
}
public function down(): void
{
Schema::dropIfExists('audit_logs');
}
};
If your version of the package requires its own blueprint class, import Orptech\MigrationPartition\Schema\Blueprint instead of Laravel’s. If the package registers macros globally, the standard Illuminate\Database\Schema\Blueprint works unchanged.
Immediately after the first migration, verify what the database actually created:
-- MySQL
SHOW CREATE TABLE audit_logs\G
-- PostgreSQL
\d+ audit_logs
Check two things: the PARTITION BY ... clause exists, and the expected partitions are present. Also confirm the driver: MySQL 8+/MariaDB and PostgreSQL 11+ have real partitioning; SQLite does not. Use a driver-aware migration or run partition tests only against MySQL/PostgreSQL.
The best first production use case is a monthly range-partitioned event/log table where old data is dropped by partition rather than deleted row by row.
Generate rolling date partitions in the migration. For a real log table, partition by created_at and generate partitions from a helper instead of hand-writing dozens of clauses:
private function monthlyPartitions(int $months = 12): array
{
$partitions = [];
$start = now()->startOfMonth();
for ($i = 0; $i < $months; $i++) {
$from = $start->copy()->addMonths($i);
$to = $from->copy()->addMonth();
$partitions["p{$from->format('Y_m')}"] =
"LESS THAN ('{$to->toDateTimeString()}')";
}
$partitions['pmax'] = 'MAXVALUE';
return $partitions;
}
Then use it in the blueprint:
$table->partitionByRange('created_at', $this->monthlyPartitions(12));
Because time-range partitioning requires the partition key in every unique key, a common schema is:
$table->unsignedBigInteger('id')->autoIncrement();
$table->timestamp('created_at');
$table->primary(['id', 'created_at']);
Use list partitioning for tenant or region isolation. This is useful when a small number of large tenants dominate write volume:
$table->partitionByList('tenant_id', [
'p_acme' => 'IN (1)',
'p_globex' => 'IN (2)',
'p_default' => 'DEFAULT',
]);
Provisioning a new large tenant then means an ALTER TABLE ... ADD PARTITION migration or command.
Use hash/key partitioning for even write distribution. When there is no natural range or list key, hash partitioning spreads writes:
$table->partitionByHash('id', 16);
Do not expect this to help range queries. It helps write throughput and maintenance granularity.
Treat partition maintenance as a separate scheduled concern. The package is strongest at table creation. Day-to-day rolling-window maintenance is usually clearer as explicit SQL in an artisan command:
DB::statement("
ALTER TABLE events
ADD PARTITION (
PARTITION p2025_02 VALUES LESS THAN ('2025-03-01 00:00:00')
)
");
And old partitions are removed cheaply:
DB::statement('ALTER TABLE events DROP PARTITION p2024_02');
If using a MAXVALUE catch-all partition, split it with REORGANIZE PARTITION before adding the next range:
ALTER TABLE events REORGANIZE PARTITION pmax INTO (
PARTITION p2025_02 VALUES LESS THAN ('2025-03-01 00:00:00'),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
Keep the partition key in queries and indexes. Partition pruning only works when queries filter on the partition column. A query like where created_at between ? and ? can touch one partition; where tenant_id = ? alone will scan all of them. Use EXPLAIN and inspect the partitions column (MySQL) or the plan (PostgreSQL). Put the partition key first in important composite indexes.
Make tests driver-aware. RefreshDatabase on SQLite will not exercise partitioning. Either run the suite against MySQL/PostgreSQL, or guard the clause:
if (DB::getDriverName() !== 'sqlite') {
$table->partitionByRange('created_at', $this->monthlyPartitions());
}
Add an integration assertion that the table is actually partitioned:
$partitioned = DB::table('information_schema.partitions')
->where('table_schema', DB::getDatabaseName())
->where('table_name', 'events')
->whereNotNull('partition_name')
->exists();
Use a dedicated migration for the partitioned table. Do not mix partition creation with later arbitrary schema changes on the same table. Some ALTER TABLE operations on partitioned tables are expensive or unsupported, especially in MySQL. Creating the table with its partition scheme up front and evolving it carefully is safer.
$table->id() alone cannot be used with PARTITION BY RANGE (created_at). Use a composite primary key such as (id, created_at) or partition on id.DEFAULT/MAXVALUE partitions carefully. A catch-all partition prevents insert failures, but rows that land there cannot always be moved without rebuilding or reorganizing the partition.php artisan schema:dump preserves the partition clauses for your database and tooling. Test migrate:fresh against a real MySQL/PostgreSQL instance, not SQLite.DROP PARTITION is fast and reclaims storage; DELETE FROM ... WHERE created_at < ... is slow and fragments the table. Prefer partition drops for retention.ALTER TABLE statements before relying on them.Blueprint::macro(), subclass the package blueprint, or encapsulate maintenance SQL in a small schema helper/service. For syntax the package does not expose—such as MySQL RANGE COLUMNS, subpartitions, or PostgreSQL DETACH PARTITION CONCURRENTLY—fall back to DB::statement() in a migration or command.How can I help you explore Laravel packages today?