Laravel PostgreSQL Enhanced (tpetry/laravel-postgresql-enhanced)

Laravel PostgreSQL Enhanced

https://github.com/tpetry/laravel-postgresql-enhanced
Admin
Support for many missing PostgreSQL specific features

Tokens:17,250
Snippets:93
Trust Score:8.5
Update:7 months ago
Show doc for...
Context Summary (auto-generated)
Raw
# Laravel PostgreSQL Enhanced

Laravel PostgreSQL Enhanced is a comprehensive extension package that unlocks advanced PostgreSQL features within Laravel applications. While Laravel provides excellent database abstraction, it necessarily limits itself to features common across all supported databases. This package removes those limitations for PostgreSQL users, providing native access to powerful PostgreSQL-specific capabilities including advanced indexing strategies, specialized column types, full-text search, CTEs, materialized views, and zero-downtime migrations.

The package seamlessly integrates with Laravel's existing migration and query builder systems through drop-in replacements and traits. Developers can leverage PostgreSQL's rich type system (arrays, ranges, vectors, ltree), create sophisticated indexes with partial conditions and included columns, implement triggers and functions, and utilize modern features like RETURNING clauses and lateral joins. Whether building high-performance applications requiring TimescaleDB integration, implementing full-text search with proper language stemming, or ensuring zero-downtime deployments with migration timeouts, this package provides production-ready tools that extend Laravel's capabilities without compromising its elegant API.

## Schema Migration - Advanced Column Types

Creating PostgreSQL-specific column types in migrations

```php
use Illuminate\Database\Migrations\Migration;
use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('products', function (Blueprint $table) {
            $table->id();

            // Integer arrays for storing tag IDs
            $table->integerArray('tag_ids');

            // Date ranges for booking periods
            $table->dateRange('availability');

            // Case-insensitive email storage
            $table->caseInsensitiveText('email');

            // Vector embeddings for AI search (1536 dimensions for OpenAI)
            $table->vector('embeddings', 1536);

            // Label tree for hierarchical categories
            $table->labelTree('category_path');

            // IP network in CIDR notation
            $table->ipNetwork('allowed_network');

            // Bit strings for bitmap flags
            $table->bit('feature_flags', 32);
            $table->varbit('dynamic_flags');

            // International product numbers with validation
            $table->internationalStandardBookNumber('isbn');
            $table->europeanArticleNumber13('ean');

            $table->timestampsTz();
        });
    }
};
```

## Schema Migration - Advanced Indexes

Creating sophisticated indexes with PostgreSQL-specific features

```php
use Illuminate\Database\Migrations\Migration;
use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

return new class extends Migration
{
    public $withinTransaction = false;

    public function up(): void
    {
        Schema::table('users', function (Blueprint $table) {
            // Unique index instead of constraint (required for advanced features)
            $table->uniqueIndex('email');

            // Partial index for soft-deletes (only indexes undeleted rows)
            $table->uniqueIndex('email')
                ->where('deleted_at IS NULL');

            // Index with included columns for index-only scans
            $table->index('email')
                ->include(['firstname', 'lastname']);

            // Functional index with lowercase transformation
            $table->uniqueIndex('(LOWER(email))', 'users_email_lower_unique');

            // Index with custom column options and NULL positioning
            $table->index(['age DESC NULLS LAST', 'created_at DESC']);

            // Concurrent index creation (non-blocking)
            $table->index(['last_login_at', 'status'])->concurrently();

            // Index with storage parameters (important for GIN indexes)
            $table->index('metadata')->algorithm('gin')
                ->with(['fastupdate' => false]);

            // Full-text index with language and weighting
            $table->fullText(['title', 'description'])
                ->language('spanish')
                ->weight(['A', 'B']);

            // Nulls not distinct for proper NULL handling in unique indexes
            $table->uniqueIndex(['user_id', 'cancelled_at'])
                ->nullsNotDistinct();

            // Conditional index creation (idempotent)
            $table->index(['status', 'priority'])->ifNotExists();
        });
    }
};
```

## Schema Migration - Zero-Downtime Operations

Ensuring migrations never exceed time limits to prevent production locks

```php
use Illuminate\Database\Migrations\Migration;
use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Schema\Concerns\ZeroDowntimeMigration;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

class ChangeUserNameLength extends Migration
{
    use ZeroDowntimeMigration;

    // Default timeout for both up/down methods (in seconds)
    private float $timeout = 2.0;

    // Or specify different timeouts for each direction
    // private float $timeoutUp = 5.0;
    // private float $timeoutDown = 1.0;

    public function up(): void
    {
        // If this change takes longer than timeout, it will be rolled back
        Schema::table('users', function (Blueprint $table) {
            $table->string('name', 256)->change();
        });
    }

    public function down(): void
    {
        Schema::table('users', function (Blueprint $table) {
            $table->string('name', 128)->change();
        });
    }
};
```

## Schema Migration - Extensions and Functions

Managing PostgreSQL extensions and creating stored functions

```php
use Illuminate\Database\Migrations\Migration;
use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        // Enable required extensions
        Schema::createExtensionIfNotExists('citext');
        Schema::createExtensionIfNotExists('ltree');
        Schema::createExtensionIfNotExists('vector');
        Schema::createExtensionIfNotExists('intarray');

        // Create optimized inline SQL function
        Schema::createFunction(
            name: 'calculate_tax',
            parameters: ['subtotal' => 'numeric', 'rate' => 'numeric'],
            return: 'numeric',
            language: 'sql:expression',
            body: 'subtotal * rate',
            options: [
                'parallel' => 'safe',
                'volatility' => 'immutable',
            ]
        );

        // Create PL/pgSQL function returning table
        Schema::createFunction(
            name: 'search_products',
            parameters: ['pattern' => 'text'],
            return: ['product_id' => 'bigint', 'name' => 'text', 'price' => 'numeric'],
            language: 'plpgsql',
            body: "
                BEGIN
                    RETURN QUERY
                    SELECT id, name, price
                    FROM products
                    WHERE name ILIKE '%' || pattern || '%';
                END;
            "
        );
    }

    public function down(): void
    {
        Schema::dropFunctionIfExists('search_products');
        Schema::dropFunctionIfExists('calculate_tax');
        Schema::dropExtensionIfExists('intarray', 'vector', 'ltree', 'citext');
    }
};
```

## Schema Migration - Views and Triggers

Creating views, materialized views, and database triggers

```php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;
use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        // Create standard view from query builder
        Schema::createView(
            'active_users',
            DB::table('users')->where('status', 'active')->whereNull('deleted_at')
        );

        // Create view with custom column names
        Schema::createView(
            'user_emails',
            DB::table('users')->select('id', 'email'),
            ['user_id', 'contact_email']
        );

        // Create materialized view for expensive queries
        Schema::createMaterializedView(
            'user_statistics',
            'SELECT user_id, COUNT(*) as order_count, SUM(total) as revenue
             FROM orders GROUP BY user_id'
        );

        // Create materialized view without initial data (populate later)
        Schema::createMaterializedView(
            'slow_aggregation',
            'SELECT * FROM very_expensive_query()',
            withData: false
        );

        // Add trigger to table
        Schema::table('orders', function (Blueprint $table) {
            $table->trigger(
                'update_user_stats',
                'refresh_user_statistics()',
                'AFTER INSERT OR UPDATE OR DELETE'
            )
            ->forEachRow()
            ->whenCondition('NEW.status = \'completed\'');
        });
    }

    public function down(): void
    {
        Schema::table('orders', function (Blueprint $table) {
            $table->dropTriggerIfExists('update_user_stats');
        });

        Schema::dropViewIfExists('active_users', 'user_emails');
        Schema::dropMaterializedViewIfExists('user_statistics', 'slow_aggregation');
    }
};
```

## Schema Migration - Domain Types and Table Options

Creating reusable custom types and optimizing table storage

```php
use Illuminate\Database\Migrations\Migration;
use Tpetry\PostgresqlEnhanced\Query\Builder;
use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        // Create domain types for consistent column definitions
        Schema::createDomain('price', 'numeric(12,2)', 'VALUE >= 0');
        Schema::createDomain('email', 'citext', fn(Builder $q) =>
            $q->where('VALUE', '~', '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z]{2,}$')
        );

        // Use domain types in table
        Schema::create('products', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->domain('unit_price', 'price');
            $table->domain('contact_email', 'email');

            // Unlogged table for high-write temporary data
            $table->unlogged();

            // Storage parameters for performance tuning
            $table->with([
                'fillfactor' => 90, // Leave space for HOT updates
                'autovacuum_analyze_scale_factor' => 0.02,
            ]);
        });

        // Column-specific options
        Schema::table('articles', function (Blueprint $table) {
            // Compression for large text columns
            $table->text('content')->compression('lz4');

            // Initial value for existing rows (no default for new rows)
            $table->boolean('email_verified')->initial(false);

            // Custom type conversion with USING clause
            $table->jsonb('settings')->using("settings::text::jsonb")->change();
        });
    }

    public function down(): void
    {
        Schema::dropTable('products');
        Schema::dropDomainIfExists('price', 'email');
    }
};
```

## Query Builder - Advanced WHERE Clauses

PostgreSQL-specific query filtering and conditions

```php
use Illuminate\Support\Facades\DB;
use Tpetry\PostgresqlEnhanced\Query\Builder;

// ANY/ALL operators for array value comparisons
DB::table('invoices')
    ->whereAnyValue('invoice_number', 'like', ['RV-%', 'RZ-%'])
    ->get();

DB::table('documents')
    ->whereAllValues('tags', '??', ['important', 'reviewed'])
    ->get();

// Boolean comparisons (avoids PostgreSQL integer casting errors)
DB::table('users')
    ->whereBoolean('is_admin', true)
    ->whereNotBoolean('is_banned', false)
    ->get();

// Case-insensitive LIKE searches
DB::table('products')
    ->whereLike('name', '%laptop%', caseSensitive: false)
    ->get();

// Symmetric BETWEEN (auto-reorders values)
$minPrice = $request->integer('min');
$maxPrice = $request->integer('max');
DB::table('products')
    ->whereBetweenSymmetric('price', [$minPrice, $maxPrice]) // Works regardless of order
    ->get();

// Integer array matching with boolean logic
DB::table('posts')
    ->whereIntegerArrayMatches('tag_ids', '1&2&(3|4)&!5') // Has 1 AND 2 AND (3 OR 4) NOT 5
    ->get();
```

## Query Builder - RETURNING Clause

Retrieving data from INSERT, UPDATE, and DELETE operations

```php
use Illuminate\Support\Facades\DB;

// Delete and get deleted rows
$deletedUsers = DB::table('users')
    ->where('last_login_at', '<', now()->subYear())
    ->deleteReturning(['id', 'email', 'name']);

foreach ($deletedUsers as $user) {
    Log::info("Deleted inactive user: {$user->email}");
}

// Insert and retrieve generated data
$newProducts = DB::table('products')
    ->insertReturning([
        ['name' => 'Widget', 'sku' => null],
        ['name' => 'Gadget', 'sku' => null],
    ], ['id', 'name', 'sku', 'created_at']);

// Update and get affected rows
$updated = DB::table('orders')
    ->where('status', 'pending')
    ->where('created_at', '<', now()->subHours(24))
    ->updateReturning(['status' => 'cancelled'], ['id', 'order_number']);

// Upsert with returning
$results = DB::table('inventory')
    ->upsertReturning(
        [
            ['product_id' => 1, 'quantity' => 100],
            ['product_id' => 2, 'quantity' => 50],
        ],
        ['product_id'], // Unique columns
        ['quantity'],   // Update columns
        ['product_id', 'quantity', 'updated_at'] // Return columns
    );

// Insert or ignore with returning
$inserted = DB::table('tags')
    ->insertOrIgnoreReturning(
        [['name' => 'php'], ['name' => 'laravel']],
        ['id', 'name']
    );
```

## Query Builder - Full-Text Search

PostgreSQL full-text search with language support and ranking

```php
use App\Models\Article;
use Illuminate\Support\Facades\DB;

// Basic full-text search
$articles = Article::whereFullText(['title', 'content'], 'PostgreSQL database')
    ->get();

// Search with specific language (stemming)
$articles = Article::whereFullText(
    ['title', 'content'],
    'base de datos PostgreSQL',
    ['language' => 'spanish']
)->get();

// Phrase search (exact word order)
$articles = Article::whereFullText(
    ['title', 'content'],
    'PostgreSQL database optimization',
    ['mode' => 'phrase']
)->get();

// Web search mode (supports quotes, OR, and exclusion)
$articles = Article::whereFullText(
    ['title', 'content'],
    '"PostgreSQL 16" OR "PostgreSQL 17" -MySQL',
    ['mode' => 'websearch']
)->get();

// Weighted search for ranking (title more important than content)
$articles = Article::whereFullText(
    ['title', 'content'],
    'database performance',
    [
        'mode' => 'plain',
        'weight' => ['A', 'B'], // Title=A (highest), Content=B
    ]
)->get();

// Simple language for exact matching (no stemming)
$articles = Article::whereFullText(
    ['title', 'content'],
    'PostgreSQL',
    ['language' => 'simple']
)->get();
```

## Query Builder - Common Table Expressions

Complex queries with CTEs including recursive and materialized options

```php
use App\Models\User;
use App\Models\Login;
use Illuminate\Support\Facades\DB;

// Basic CTE for query organization
$lastLoginQuery = Login::query()
    ->selectRaw('user_id, MAX(created_at) AS last_login_at')
    ->groupBy('user_id');

$activeUsers = User::query()
    ->withExpression('user_last_login', $lastLoginQuery)
    ->join('user_last_login', 'user_last_login.user_id', 'users.id')
    ->where('user_last_login.last_login_at', '>=', now()->subDay())
    ->get();

// Materialized CTE (forces evaluation before main query)
User::query()
    ->withExpression('expensive_calc', $complexQuery, [
        'materialized' => true
    ])
    ->join('expensive_calc', 'expensive_calc.user_id', 'users.id')
    ->get();

// Recursive CTE with cycle detection (organizational hierarchy)
$orgHierarchy = DB::table('employees')
    ->withExpression('org_tree', "
        SELECT id, name, manager_id, ARRAY[id] as path, 1 as level
        FROM employees
        WHERE manager_id IS NULL

        UNION ALL

        SELECT e.id, e.name, e.manager_id, path || e.id, level + 1
        FROM employees e
        INNER JOIN org_tree ot ON e.manager_id = ot.id
        WHERE NOT e.id = ANY(path)
    ", [
        'recursive' => true,
        'cycle' => 'id SET is_cycle USING path',
        'search' => 'BREADTH FIRST BY name SET ordercol'
    ])
    ->select('*')
    ->from('org_tree')
    ->orderBy('ordercol')
    ->get();
```

## Query Builder - Lateral Joins and Cursors

Advanced join patterns and memory-efficient iteration

```php
use App\Models\User;
use App\Models\Order;
use Illuminate\Support\Facades\DB;

// Lateral join - get top 3 orders per user (for-each loop pattern)
$results = User::query()
    ->select('users.id', 'users.email', 'orders.*')
    ->leftJoinSubLateral(
        Order::query()
            ->whereColumn('orders.user_id', 'users.id')
            ->orderBy('total', 'desc')
            ->limit(3),
        'orders'
    )
    ->get();

// Cursor-based lazy loading (memory-efficient for large datasets)
DB::transaction(function () {
    User::lazyByCursor(500)->each(function (User $user) {
        // Process one user at a time, loading max 500 into memory
        processUser($user);
    });

    // Query builder also supports cursor lazy loading
    DB::table('logs')
        ->where('created_at', '<', now()->subMonths(6))
        ->lazyByCursor(1000)
        ->each(function ($log) {
            archiveLog($log);
        });
});

// EXPLAIN with analysis for query optimization
$plan = DB::table('orders')
    ->join('users', 'users.id', '=', 'orders.user_id')
    ->where('orders.total', '>', 1000)
    ->explain(analyze: true);

dd($plan); // Shows query plan with timing and buffer statistics
```

## Query Builder - Ordering and Vector Search

Advanced sorting with NULL handling and vector similarity

```php
use Illuminate\Support\Facades\DB;

// Custom NULL positioning in ORDER BY
DB::table('products')
    ->orderBy('priority', 'desc', nulls: 'last') // NULLs appear last
    ->get();

// Helper methods for NULL ordering
DB::table('users')
    ->orderByNullsFirst('last_login_at', 'desc')
    ->get();

DB::table('tasks')
    ->orderByNullsLast('completed_at', 'asc')
    ->get();

// Vector similarity search (AI embeddings)
$searchEmbedding = [0.234, 0.876, 0.432, /* ... 1533 more values */];

$similar = DB::table('articles')
    ->orderByVectorSimilarity('embeddings', $searchEmbedding, distance: 'cosine')
    ->limit(10)
    ->get();

// L2 distance for vector search
$nearest = DB::table('products')
    ->where('category', 'electronics')
    ->orderByVectorSimilarity('feature_vector', $targetVector, distance: 'l2')
    ->limit(5)
    ->get();
```

## Eloquent Models - Automatic Features

Traits for automatic data refresh and flexible date handling

```php
use Illuminate\Database\Eloquent\Model;
use Tpetry\PostgresqlEnhanced\Eloquent\Concerns\RefreshDataOnSave;
use Tpetry\PostgresqlEnhanced\Eloquent\Concerns\AutomaticDateFormatWithMilliseconds;
use Tpetry\PostgresqlEnhanced\Eloquent\Casts\IntegerArrayCast;
use Tpetry\PostgresqlEnhanced\Eloquent\Casts\VectorArray;

class Product extends Model
{
    use RefreshDataOnSave; // Auto-refresh computed/generated columns on save
    use AutomaticDateFormatWithMilliseconds; // Handle millisecond precision timestamps

    protected $casts = [
        'tag_ids' => IntegerArrayCast::class,
        'embeddings' => VectorArray::class,
        'metadata' => 'json',
    ];
}

// Usage - computed columns automatically refreshed
$product = Product::create([
    'name' => 'Widget',
    'price' => 99.99,
    // Assuming 'slug' column is computed as LOWER(REPLACE(name, ' ', '-'))
]);

// Without RefreshDataOnSave:
dump($product->slug); // null - need to refresh manually

// With RefreshDataOnSave:
dump($product->slug); // 'widget' - automatically populated!

// Update also refreshes
$product->update(['name' => 'Super Widget']);
dump($product->slug); // 'super-widget' - automatically updated!

// Integer array handling
$product = Product::create([
    'name' => 'Laptop',
    'tag_ids' => [1, 5, 12, 45], // Array stored natively in PostgreSQL
]);

$product->tag_ids; // Returns: [1, 5, 12, 45]
$product->tag_ids[] = 99;
$product->save(); // Saves as PostgreSQL array
```

## Schema Migration - Foreign Keys

Not-enforced foreign keys for performance with visual relationships

```php
use Illuminate\Database\Migrations\Migration;
use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('posts', function (Blueprint $table) {
            $table->id();
            $table->string('title');
            $table->text('content');

            // Enforced in dev/test, not enforced in production
            // - Production: No performance impact, but shows relationships in DB tools
            // - Development: Catches foreign key violations early
            $table->foreignId('user_id')
                ->constrained()
                ->notEnforced(app()->isProduction());

            $table->foreignId('category_id')
                ->constrained('categories')
                ->notEnforced(config('database.foreign_keys_enforced') === false);

            $table->timestampsTz();
        });
    }
};
```

## Expressions - UUID Generation

Database-native UUID v7 generation for time-sorted identifiers

```php
use Illuminate\Database\Migrations\Migration;
use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;
use Tpetry\PostgresqlEnhanced\Expressions\Uuid7;

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('events', function (Blueprint $table) {
            $table->id();

            // Generate UUID v7 in database (time-sorted, Laravel/PHP implementation)
            $table->uuid('public_id')->default(new Uuid7())->unique();

            // Use native PostgreSQL 18+ implementation (faster)
            // $table->uuid('public_id')->default(new Uuid7(native: true))->unique();

            $table->string('event_type');
            $table->jsonb('payload');
            $table->timestampTz('created_at');
        });

        // UUIDs are now generated by database on INSERT
        // Benefits:
        // - Works with raw INSERT queries
        // - Works with GUI database tools
        // - Works with bulk INSERT INTO ... SELECT operations
        // - Time-sorted for better index performance than UUID v4
    }
};
```

## TimescaleDB Integration - Hypertables

Creating and managing TimescaleDB hypertables with compression and policies

```php
use Illuminate\Database\Migrations\Migration;
use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Schema\Timescale\Actions\CreateHypertable;
use Tpetry\PostgresqlEnhanced\Schema\Timescale\Actions\EnableColumnstore;
use Tpetry\PostgresqlEnhanced\Schema\Timescale\Actions\CreateColumnstorePolicy;
use Tpetry\PostgresqlEnhanced\Schema\Timescale\Actions\CreateRetentionPolicy;
use Tpetry\PostgresqlEnhanced\Schema\Timescale\Actions\EnableChunkSkipping;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::createExtensionIfNotExists('timescaledb');

        Schema::create('sensor_data', function (Blueprint $table) {
            $table->identity();
            $table->bigInteger('device_id');
            $table->float('temperature');
            $table->float('humidity');
            $table->timestampTz('recorded_at');

            // Must include time column in primary key
            $table->primary(['id', 'recorded_at']);
            $table->index(['device_id', 'recorded_at']);

            // Configure as hypertable with policies
            $table->timescale(
                // Create hypertable partitioned by time (7 day chunks)
                new CreateHypertable('recorded_at', '7 days'),

                // Enable columnstore compression (segmented by device)
                new EnableColumnstore(segmentBy: 'device_id'),

                // Auto-compress data older than 7 days
                new CreateColumnstorePolicy('7 days'),

                // Drop data older than 90 days automatically
                new CreateRetentionPolicy('90 days'),

                // Enable chunk skipping for faster queries
                new EnableChunkSkipping('id'),
            );
        });
    }
};
```

## TimescaleDB Integration - Continuous Aggregates

Creating real-time materialized aggregations with automatic refresh

```php
use Illuminate\Database\Migrations\Migration;
use Tpetry\PostgresqlEnhanced\Schema\Timescale\CaggBlueprint;
use Tpetry\PostgresqlEnhanced\Schema\Timescale\Actions\CreateRefreshPolicy;
use Tpetry\PostgresqlEnhanced\Schema\Timescale\Actions\EnableColumnstore;
use Tpetry\PostgresqlEnhanced\Schema\Timescale\Actions\CreateColumnstorePolicy;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        // Create continuous aggregate (auto-updating materialized view)
        Schema::continuousAggregate('sensor_data_hourly', function (CaggBlueprint $table) {
            $table->as("
                SELECT
                    time_bucket('1 hour', recorded_at) AS bucket,
                    device_id,
                    AVG(temperature) AS avg_temp,
                    MAX(temperature) AS max_temp,
                    MIN(temperature) AS min_temp,
                    AVG(humidity) AS avg_humidity,
                    COUNT(*) AS measurement_count
                FROM sensor_data
                GROUP BY bucket, device_id
            ");

            // Enable real-time aggregation
            $table->realtime();

            // Add indexes for query performance
            $table->index(['device_id', 'bucket']);

            // Apply Timescale features
            $table->timescale(
                // Refresh every 5 minutes for data from last 2 days to 1 hour ago
                new CreateRefreshPolicy('5 minutes', '2 days', '1 hour'),

                // Enable columnstore compression
                new EnableColumnstore(),

                // Compress aggregate data older than 7 days
                new CreateColumnstorePolicy('7 days'),
            );
        });
    }

    public function down(): void
    {
        Schema::dropContinuousAggregateIfExists('sensor_data_hourly');
    }
};
```

## Main Use Cases and Integration Patterns

Laravel PostgreSQL Enhanced is designed for production applications requiring PostgreSQL's advanced capabilities without leaving Laravel's ecosystem. The package excels in high-performance scenarios including time-series data management with TimescaleDB integration, AI-powered applications using vector embeddings for semantic search, and complex business logic implemented through database triggers and functions. Applications handling hierarchical data benefit from ltree columns, while those processing large datasets leverage cursor-based iteration and partial indexes for optimal performance.

Integration is straightforward: replace `use Illuminate\Support\Facades\Schema` with `use Tpetry\PostgresqlEnhanced\Support\Facades\Schema` and swap `Blueprint` imports in migrations. Query builder enhancements work automatically through service provider registration. For zero-downtime migrations, simply add the `ZeroDowntimeMigration` trait to migration classes. Eloquent models gain automatic computed column refreshing via the `RefreshDataOnSave` trait, while specialized casts handle PostgreSQL types like integer arrays and vectors. The package maintains full compatibility with Laravel's API conventions, ensuring teams can adopt advanced PostgreSQL features incrementally without rewriting existing code or learning new paradigms.