Laravel Postgresql Enhanced Laravel Package

Getting Started

Install and confirm the environment

composer require tpetry/laravel-postgresql-enhanced

There is nothing to publish or configure — no service provider, no config file. The package works by replacing parts of Laravel's Postgres grammar and blueprint, so the first thing to verify is your database version: many features are version-gated upstream (e.g. NULLS NOT DISTINCT needs PG 15, column compression needs PG 14, sql:expression function inlining improves significantly on newer versions). Check select version(); before designing a migration around a feature.

Where to look first

1. The README's feature index — skim it once end-to-end; nothing here is discoverable from Laravel's docs.
2. The package's Schema\Blueprint and Support\Facades\Schema classes. These are the two things you swap into your migrations.
3. Run composer require --dev phpstan/extension-installer (or add ./vendor/tpetry/laravel-postgresql-enhanced/phpstan-extension.neon to phpstan.neon). The extension methods are invisible to static analysis otherwise. For PhpStorm + Laravel Idea, run the IDE Helper code-generation action to get autocomplete.

First use case

The lowest-risk, highest-value first step is usually a partial or full-text index on an existing table, written in its own migration:

use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::table('posts', function (Blueprint $table): void {
    $table->fullText(['title', 'body']);           // GIN tsvector index
    $table->index('author_id')->where('deleted_at is null'); // partial index
});

If that migration runs and EXPLAIN shows an index scan, you have validated the whole setup: grammar override, blueprint extension, and query planner cooperation.

Implementation Patterns

Use the package namespaces consistently. Every migration that touches a Postgres-specific feature must import Tpetry\PostgresqlEnhanced\Schema\Blueprint (not Laravel's) and, for non-blueprint operations, Tpetry\PostgresqlEnhanced\Support\Facades\Schema. Standard $table->string(), $table->index() calls keep working — the package blueprint is a superset.

Layer your schema into three kinds of migrations:

Because functions are referenced by triggers and indexes reference functions (sql:expression, functional indexes), foundation migrations must be ordered before everything that depends on them. Treat dropFunction as a breaking change and add explicit down() methods.

Trigger + function rollups. Instead of recomputing aggregates in PHP, push them into the database:

Schema::createFunctionOrReplace(
    name: 'sales_tax', parameters: ['subtotal' => 'numeric'], return: 'numeric',
    language: 'sql:expression', body: 'subtotal * 0.06',
    options: ['volatility' => 'immutable', 'parallel' => 'safe'],
);

Schema::table('projects', function (Blueprint $table): void {
    $table->trigger('rollup_quota', 'update_quota_by_projects()', 'AFTER INSERT OR DELETE')
        ->forEachRow()
        ->whenCondition(fn ($q) => $q->where('NEW.type', 4));
});

Prefer sql:expression over plpgsql where the body is a single expression — it gets inlined into the outer query instead of being called per row.

Zero-downtime migrations for risky DDL. Any ->change() or index build that might lock a hot table should use the trait:

class WidenUserName extends Migration
{
    use ZeroDowntimeMigration;
    private float $timeoutUp = 2.0;
    private float $timeoutDown = 2.0;
}

This is a safety net, not a licence to run arbitrary DDL: it cancels the statement and rolls back the schema if the lock exceeds the timeout, so you still need a strategy for the migration to eventually succeed (typically CONCURRENTLY, or backfill + swap).

Query-side patterns. Reach for these in application code and artisan commands:

• ->returning(['id', 'created_at']) on insert/update/delete to avoid a follow-up SELECT (and to get server-side defaults back).
• ->lazyByCursor() for exports/large scans where chunkById on a non-indexed or composite key is awkward.
• CTEs (withExpression / withRecursiveExpression) for hierarchical data instead of recursive queries in PHP.
• joinLateral() when you need "top N per parent row" — the alternative in plain SQL is a correlated subquery or window function.
• orderByNullsFirst()/orderByNullsLast() instead of orderByRaw.
• Schema::explain($query) in tinker or an artisan command; use it in tests to assert a plan, not just a result.

Eloquent integration. Use the package casts for types Laravel doesn't know (citext, arrays, ranges, bit strings, IP networks, hstore, vectors) instead of hand-rolled accessors. Add RefreshDataOnSave to models whose tables have triggers or generated columns — otherwise your in-memory model is silently stale after save(). Set the package's date format for timestamptz columns so timezones round-trip correctly rather than being flattened to UTC strings.

Gotchas and Tips

SQLite in tests will hide everything. Laravel's default test setup will happily accept migrations that use these features and then quietly ignore or fail on them. Run the test suite against a real Postgres container and guard driver-specific migrations with DB::getDriverName() === 'pgsql' in up()/down().

CREATE INDEX CONCURRENTLY cannot run inside a transaction. Laravel wraps migrations in a transaction when the connection supports it. Even with ->concurrently() you must set public $withinTransaction = false; on the migration, or Postgres errors with "cannot run inside a transaction block". The same applies to concurrent REINDEX-style operations.

A failed concurrent index build leaves an INVALID index behind. It still consumes write overhead and will not be used by the planner. Use ->ifNotExists() on retries with caution, and know how to find the corpse:

select indexrelid::regclass from pg_index where not indisvalid;

You must DROP INDEX it before rebuilding.

Mixing blueprint classes in one file. A helper class or a shared trait that imports Illuminate\Database\Schema\Blueprint will silently strip your package methods at the call site — the methods exist, the type is wrong, and PHPStan is the only thing that will tell you. Keep imports disciplined and let the PHPStan extension catch drift.

Version policy: a Laravel upgrade can force a major version bump. The package follows semver for itself, but when Laravel adds a builder method with the same name and different semantics (this has happened with full-text and where-style methods), the maintainers release a major version to align. Pin the package with a caret constraint, read the changelog and "breaking changes" section during every Laravel upgrade, and re-run any test that asserts generated SQL.

Volatility declarations matter. An sql:expression function declared volatile will not be used in a functional index, and one declared immutable when it is not will corrupt that index silently. When in doubt use stable and skip indexing on it.

Extension creation needs privileges. On managed Postgres (RDS, Cloud SQL, Supabase) createExtension may fail for a subset of extensions, and extensions your provider blocks cannot be worked around in a migration. Prefer createExtensionIfNotExists and verify per-environment during the first deploy of the migration.

dropExtension is a blunt instrument. Dropping an extension can cascade into dependent indexes and functions; scope it to a rollback-only path and never run it in a migration that other environments may have already applied differently.

EXPLAIN ANALYZE executes the statement. If you expose it through a debug command, wrap it in a transaction you always roll back for write queries.

suppress_redundant_updates_trigger. Postgres updates rows even when the value is unchanged, which bloats tables and fires triggers. Add this BEFORE UPDATE trigger to hot tables rather than fighting it at the application layer.

Trigger granularity default is statement-level. If your trigger function references NEW/OLD, you must call ->forEachRow() — the default ->forEachStatement() will fail at runtime with "record NEW is not assigned yet", and only when the trigger actually fires.

Validate zero-downtime migrations on a production-sized copy. A 1-second timeout that passes against an empty local table tells you nothing about a 40M-row table. Dry-run against a restored snapshot with realistic concurrency, and prefer $timeoutUp/$timeoutDown over a single $timeout when rollback is the riskier direction.