PHP code example of brokenice / laravel-pgsql-partition

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

brokenice / laravel-pgsql-partition example snippets

'providers' => [
  /*
   * Package Service Providers...
   */
  Brokenice\LaravelPgsqlPartition\PartitionServiceProvider::class,
],

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Brokenice\LaravelPgsqlPartition\Models\Partition;
use Brokenice\LaravelPgsqlPartition\Schema\Schema;

class CreatePartitionedOrdersTable extends Migration
{
    public function up()
    {
        // Create the partitioned table
        DB::statement('
            CREATE TABLE orders (
                id BIGSERIAL,
                customer_id BIGINT NOT NULL,
                order_date DATE NOT NULL,
                total DECIMAL(10,2),
                created_at TIMESTAMP,
                updated_at TIMESTAMP,
                PRIMARY KEY (id, order_date)
            ) PARTITION BY RANGE (order_date)
        ');

        // Add partitions for years
        Schema::partitionByYears('orders', 'order_date', 2022, 2025);
    }

    public function down()
    {
        Schema::drop('orders');
    }
}

use Brokenice\LaravelPgsqlPartition\Models\Partition;
use Brokenice\LaravelPgsqlPartition\Schema\Schema;

// Add individual range partitions
Schema::addRangePartition('orders', 'orders_2024', '2024-01-01', '2025-01-01');
Schema::addRangePartition('orders', 'orders_2025', '2025-01-01', '2026-01-01');

// Or create multiple partitions at once using Partition objects
$partitions = [
    Partition::range('orders_2024', '2024-01-01', '2025-01-01'),
    Partition::range('orders_2025', '2025-01-01', '2026-01-01'),
];
Schema::partitionByRange('orders', 'order_date', $partitions);

// Add a default partition for future values
Schema::addDefaultPartition('orders', 'orders_default');

// Create LIST partitions
Schema::addListPartition('users', 'users_europe', ['IT', 'FR', 'DE', 'ES']);
Schema::addListPartition('users', 'users_america', ['US', 'CA', 'MX', 'BR']);

// Or using Partition objects
$partitions = [
    Partition::list('server_east', [1, 43, 65, 12, 56, 73]),
    Partition::list('server_west', [534, 6422, 196, 956, 22]),
];
Schema::partitionByList('servers', 'region_id', $partitions);

// Create 4 hash partitions
Schema::partitionByHash('logs', 'user_id', 4);

// This creates:
// logs_p0 - FOR VALUES WITH (MODULUS 4, REMAINDER 0)
// logs_p1 - FOR VALUES WITH (MODULUS 4, REMAINDER 1)
// logs_p2 - FOR VALUES WITH (MODULUS 4, REMAINDER 2)
// logs_p3 - FOR VALUES WITH (MODULUS 4, REMAINDER 3)

// Create yearly partitions from 2020 to 2025
Schema::partitionByYears('events', 'event_date', 2020, 2025);

// Omit end year to use current year
Schema::partitionByYears('events', 'event_date', 2020);

// Create monthly partitions for 2024
Schema::partitionByYearsAndMonths('logs', 'created_at', 2024);

// Create monthly partitions from 2023 to 2024
Schema::partitionByYearsAndMonths('logs', 'created_at', 2023, 2024);

Schema::detachPartition('orders', 'orders_2022');
// orders_2022 is now a standalone table

$partitionDef = Partition::range('orders_2022', '2022-01-01', '2023-01-01');
Schema::attachPartition('orders', 'orders_2022', $partitionDef);

Schema::dropPartition('orders_2022');

Schema::truncatePartition('orders_2022');

// Or multiple partitions
Schema::truncatePartitions(['orders_2022', 'orders_2023']);

Schema::vacuumPartition('orders_2024');
Schema::vacuumPartition('orders_2024', true); // VACUUM FULL

Schema::analyzePartition('orders_2024');
Schema::analyzePartitions(['orders_2024', 'orders_2025']);

Schema::reindexPartition('orders_2024');

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Order extends Model
{
    protected $table = 'orders';
}

// Query the orders_2024 partition directly
Order::partition('orders_2024')->where('total', '>', 100)->get();

// Query multiple partitions
Order::partitions(['orders_2024', 'orders_2025'])->get();

namespace App\Models;

use Brokenice\LaravelPgsqlPartition\Models\MultipleSchemaModel;

class Order extends MultipleSchemaModel
{
    protected $table = 'orders';
}

// Query with schema
$order = new Order();
$order->setSchema('sales');
$order->save();

// Or save directly
$order->saveOnSchema('sales');

shell
php artisan migrate

shell
php artisan laravel-pgsql-partition {action} [options]