Laravel Optimized Postgres Laravel Package

Getting Started

Pick your version line before you type composer require. This package tracks Laravel majors one-for-one, so the version you pin is the framework decision:

Install with the constraint explicit so Composer can't silently resolve you somewhere you don't want:

composer require genealabs/laravel-optimized-postgres:^13.0 -W --dry-run

Then read the resolved constraints rather than the changelog — the release notes describe additive platform support and say nothing about what was dropped at the major boundary:

composer show genealabs/laravel-optimized-postgres

Look at the service provider first, not the README. Open vendor/genealabs/laravel-optimized-postgres/src/ and read the registered provider. The single question it answers determines everything downstream:

• Does it call Blueprint::macro(...) / add schema grammar macros only? → augmentation, reversible by removing the package.
• Does it rebind db.connection / swap the PostgresConnection or PostgresGrammar? → effective driver replacement, structural exit.

That distinction is your rollback plan. Write it down before you write any code.

First use case: a brand-new table that needs a jsonb column, an array column, and a GIN index — then query it from tinker. Don't retrofit existing migrations on day one; a new table gives you a clean rollback (drop table) and a place to compare generated DDL against what stock Laravel produces.

Verification harness. Build a throwaway test that asserts both the DDL and the emitted SQL. Silent grammar changes returning plausible-but-wrong rows are the failure mode to design against:

it('generates the postgres types we expect', function () {
    Schema::create('probe', function (Blueprint $table) {
        $table->id();
        $table->jsonb('payload')->nullable();
        // verify the exact method name in src/ — array/hstore/citext are the
        // likely value-add here, since jsonb/uuid are already native Laravel
        $table->textArray('tags')->nullable();
    });

    $types = collect(DB::select(
        "select column_name, data_type from information_schema.columns
         where table_name = 'probe'"
    ))->pluck('data_type', 'column_name');

    expect($types['payload'])->toBe('jsonb');
    expect($types['tags'])->toBe('ARRAY');
});

it('emits the sql we think it does', function () {
    DB::enableQueryLog();
    DB::table('probe')->whereJsonContains('payload->roles', 'admin')->get();
    dump(DB::getQueryLog()[0]['query']); // eyeball before asserting
});

Implementation Patterns

Migrations: express intent, let the grammar do the work

The point of the package is that your migrations read like Postgres, not like portable SQL. Compare:

// The thing you're trying to delete from your codebase
DB::statement("alter table docs add column search_vector tsvector");
DB::statement("create index docs_search_idx on docs using gin(search_vector)");
DB::statement("alter table users alter column email type citext");

// What you want instead
$table->jsonb('payload')->default('{}');
$table->textArray('tags')->nullable();
$table->timestampTz('published_at')->nullable();

Keep the raw DB::statement() versions in a comment or in the PR description when you migrate — it's the only cheap way to prove the package earned its keep.

JSONB: query with Laravel, index with the package

Most JSONB querying is already native (whereJsonContains, whereJsonLength, -> path operators). The real win is the schema side — column type and GIN index — because that's what makes the native query fast:

$table->jsonb('attributes')->default('{}');
$table->index('attributes', 'attributes_gin', 'gin'); // verify index-type arg

Keep the model cast in lockstep:

protected function casts(): array
{
    return [
        'attributes' => 'array',
        'tags'       => 'array',
        'published_at' => 'datetime',
    ];
}

Full-text search: tsvector column + GIN + generated column

The maintenance-elimination play. One generated column and one index replaces a LIKE '%term%' scan or a DB::raw in every repository:

// migration
DB::statement("
    alter table posts add column search_vector tsvector
    generated always as (
        setweight(to_tsvector('english', coalesce(title,'')), 'A') ||
        setweight(to_tsvector('english', coalesce(body,'')), 'B')
    ) stored
");
DB::statement("create index posts_search_idx on posts using gin(search_vector)");

// query — use the package's helper if it has one, otherwise this is the shape
Post::whereFullText('search_vector', $term) // native
    ->orderByRaw("ts_rank(search_vector, plainto_tsquery('english', ?)) desc", [$term])
    ->get();

If the package ships a tsvector() Blueprint method or a search scope, prefer it — but only after your test proves the generated index is the one you wanted.

Upserts

Laravel's upsert() already emits ON CONFLICT for Postgres. Before adopting the package "for upserts," confirm it actually changes the output:

DB::enableQueryLog();
Model::upsert($rows, ['external_id'], ['name', 'updated_at']);
Log::info(DB::getQueryLog()[0]['query']); // if unchanged, this isn't a reason to adopt

Rollout sequencing

1. New tables only. Package-specific column types on new tables, raw SQL on existing ones.
2. One service. Prove it in the smallest Postgres-primary app first.
3. Tests run against real Postgres. Add a postgres:16 service to CI before the first package migration lands — see gotchas.
4. Retrofit last. Rewriting existing DB::raw() call sites is the payoff, but it's also where regressions live. Do it per-repository with query-log diffs.

Test-strategy split

If you keep SQLite for the fast test tier, it will not understand jsonb, arrays, or GIN indexes. Two viable stances:

• Purist: all tests hit Postgres (service container or RefreshDatabase against a local instance). Simplest, slowest.
• Split: SQLite for pure-unit tests, Postgres for anything touching a package migration. Requires Schema::hasTable-free fixtures and careful ->skip() hygiene. Usually not worth it — pick the purist path.

Gotchas and Tips

Half the feature list is already native Laravel. jsonb, uuid, ipAddress, and ON CONFLICT upserts have been in Laravel's Postgres grammar for years. Before you credit the package, run each claimed capability against a stock install and see what's actually additive. The plausible genuine additions are hstore, CITEXT, native array columns, range types, and FTS/GIN helpers — verify those specifically.

PostgreSQL extensions are not installed by the package. citext, hstore, and pg_trgm are server-level. Add an early migration (or a pre-deploy step) or you'll get migration failures on fresh databases and CI that nobody can reproduce locally:

DB::statement('create extension if not exists citext');
DB::statement('create extension if not exists hstore');

Check your Postgres version too — GIN on jsonb_path_ops, generated columns, and tsvector behaviour all vary. Pin the major in CI to match production.

Array and hstore columns come back as strings over PDO. {a,b,c} and "k"=>"v" won't be PHP arrays unless the connection or a cast converts them. Test a round-trip (insert then Model::find()), not just the DDL — this is the classic silent-wrong-result bug with Postgres array columns.

Register-order collisions are the real integration risk. Any package that macros the Blueprint or extends the query grammar can collide with this one. Check before you commit:

composer why-not genealabs/laravel-optimized-postgres
# and grep your app + deps for Blueprint::macro / Grammar overrides
grep -rn "Blueprint::macro" vendor/ --include=*.php | head -50

Pin the exact version and commit the lockfile. Single maintainer, ~58 stars, zero dependents. You are the reference implementation — nobody else's production has stress-tested this. Use an exact constraint (13.0.0, not ^13.0) and review the diff on every bump:

composer update genealabs/laravel-optimized-postgres --dry-run
git diff vendor/genealabs/laravel-optimized-postgres

The Composer name and the repo host disagree (genealabs/* on Packagist, mikebronner on GitHub). Benign, but confirm the composer.json source/dist URLs point where you expect, and note it in your dependency register so the next person doesn't file a supply-chain ticket.

Treat every major as breaking until diffed. Two-line changelog on a major bump usually equals "dropped the previous framework major," possibly plus altered method signatures. Diff tags before upgrading:

git diff 12.0.1 13.0.0 -- src/

Don't sell it as a performance win. There are no benchmarks. If you need to justify adoption, measure it yourself on a representative dataset with EXPLAIN (ANALYZE, BUFFERS) before/after on your heaviest JSONB or FTS query — otherwise frame the case as "we deleted N raw-SQL call sites."

Extension point if the grammar is wrong for you: subclass the package's connection/grammar, register your own db.connection binding in a DatabaseServiceProvider, and call parent:: for everything you don't need to change. That keeps your patches in one file instead of a fork — and gives you a clean path to fork outright if the maintainer goes quiet.

Exit plan, written on day one: (a) revert config/database.php if augmentation-only, (b) composer remove + dropColumn migrations if you've shipped package-specific types — note those columns are yours now, the package is just sugar, so dropping it doesn't lose data, and (c) a fork branch with your patches applied, tested against the current Laravel major. Revisit at every Laravel release, not when it breaks.