PostgreSQL Migrations: Tips & Tricks

Tip: Always Write Reversible Migrations

public function up(): void
{
    Schema::table('posts', fn($t) => $t->string('slug')->unique());
}

public function down(): void
{
    Schema::table('posts', fn($t) => $t->dropUnique(['slug']));
    Schema::table('posts', fn($t) => $t->dropColumn('slug'));
}

Gotcha: CONCURRENTLY for Indexes on Large Tables

DB::statement('CREATE INDEX CONCURRENTLY idx_posts_slug ON posts(slug)');

Doesn't lock the table during index creation. But can't run inside a transaction.

Tip: Safe Column Addition

1. Add nullable column
2. Backfill data in a separate migration
3. Make not nullable

// Migration 1
$table->string('slug')->nullable();

// Migration 2 (backfill)
Post::each(fn($p) => $p->update(['slug' => Str::slug($p->title)]));

// Migration 3
$table->string('slug')->nullable(false)->change();

Gotcha: change() Needs doctrine/dbal

For altering existing columns, install:

composer require doctrine/dbal

Tip: PostgreSQL Extensions in Migrations

public function up(): void
{
    DB::statement('CREATE EXTENSION IF NOT EXISTS "uuid-ossp"');
    DB::statement('CREATE EXTENSION IF NOT EXISTS pg_trgm');
}

Gotcha: Transaction Safety

Most migrations run in a transaction. But CREATE INDEX CONCURRENTLY can't. Wrap carefully:

public function up(): void
{
    DB::statement('CREATE INDEX CONCURRENTLY ...'); // Outside transaction
}

Tip: Seed After Migration

php artisan migrate:fresh --seed

Fresh database with seed data in one command.

Gotcha: Rollback Order

Migrations rollback in reverse order. Make sure down() methods are correct.

Tip: EXPLAIN (ANALYZE, BUFFERS) Is Your Best Friend

For query debugging, always use EXPLAIN (ANALYZE, BUFFERS) instead of plain EXPLAIN. The BUFFERS option shows hit/miss rates for every node, revealing whether your indexes are actually in memory.

Tip: Partial Indexes Are Underutilized

CREATE INDEX ON orders (status) WHERE status = 'pending' creates a tiny index that covers only the rows your query needs. It's faster to scan and cheaper to maintain than a full-column index.

Gotcha: NULL Sorting Is Non-Obvious

By default, NULLs sort AFTER non-null values in ascending order. ORDER BY col DESC puts NULLs FIRST. Use NULLS LAST or NULLS FIRST to be explicit.

Senior Insight

Database migrations in Laravel work well with PostgreSQL, but there are gotchas. PostgreSQL doesn't support CHANGE column — you must use ALTER TABLE ... ALTER COLUMN instead. Laravel's schema builder handles this, but custom raw migrations often fail. Also, PostgreSQL migrations are transactional — if a migration fails mid-way, the entire transaction rolls back. This is usually good, but it means long migrations can hold locks for extended periods.

Source: pganalyze Blog (https://pganalyze.com/blog), PostgreSQL Docs (https://www.postgresql.org/docs/current/), Crunchy Data Blog (https://www.crunchydata.com/blog)