Postgres Won't Index Your Foreign Keys. Vacuum Fails the Build

Short version for the impatient: PostgreSQL does not create an index when you add a foreign key, Laravel's ->constrained() doesn't either, and a new package called Vacuum will fail your CI build the moment somebody ships a migration like that. If you want to know why I care, and where the package is oversold, read on.

I found out about the foreign key thing the expensive way. Two years ago I moved a client's Laravel app from MySQL to Postgres (I wrote up the migration itself in the row that kept my write freeze), and for about a month afterwards a soft-delete cleanup job that used to take seconds took eleven minutes. Same schema, same data, same queries. The difference was that MySQL had been quietly indexing every foreign key for us and Postgres never does that. Deleting a parent row means Postgres has to scan the child table for references, and with no index on orders.customer_id that scan was the whole table, every time, for every parent.

So when Laravel News covered Vacuum this week and one of the headline features was "flags unindexed foreign keys in CI", I installed it the same afternoon.

What the package reads, and what it refuses to do

Vacuum is a Laravel package from Rati Rukhadze that reads the statistics views Postgres already maintains (pg_stat_user_tables, pg_stat_user_indexes, pg_stat_activity, pg_stat_statements if you have it, and pg_class) and turns them into thirteen findings with a severity, a cost estimate, and the SQL that would fix each one. It shows you the statement. It never runs it. I like that design choice more than anything else in the package, because the last "auto-tuning" tool I let near a database dropped an index it decided was unused during the one week of the year that index mattered.

The dashboard at /vacuum is only reachable in the local environment unless you register an auth callback:

use Heyosseus\Vacuum\Vacuum;

Vacuum::auth(fn (Request $request) => $request->user()?->isAdmin() === true);

The README lists the thirteen rules. Most describe a database that is slower than it should be: dead tuples, table bloat, unused indexes, duplicate indexes, a poor cache hit ratio, sessions idle in a transaction. Two describe a database that stops. wraparound watches the 32-bit transaction counter, and multixact-wraparound watches the second clock that most people (me included, until this week) didn't know existed. Multixacts are what Postgres allocates when more than one transaction holds a lock on the same row, which happens constantly on any table that has foreign keys pointing at it. That clock has its own horizon, autovacuum_multixact_freeze_max_age, and the Postgres docs on routine vacuuming are clear that either one running out puts the server into a refuse-all-writes state. I had only ever monitored the first one.

Version 1.2.0 needs PostgreSQL 14 and Laravel 11 or newer. Install is two commands:

composer require heyosseus/vacuum
php artisan vacuum:install

The lint command is the part that pays for itself

Here is the thing I want you to take from this post. There are two pipeline commands, and they answer different questions.

vacuum:check runs the full advisor against a database that has been running. It belongs on a schedule against staging, and its default is to fail only on a critical finding, because a build going red overnight because bloat grew is a build people learn to ignore.

vacuum:lint runs against a database that has only been migrated. That's the database your test job has: a Postgres container ninety seconds old, migrations freshly applied, zero rows. The advisor rules find nothing there, and a perfect score on a database nobody has used tells you nothing. So lint reads the catalog instead of the statistics and asks the six questions that are answerable the moment php artisan migrate finishes: is there a foreign key with no index behind it, does a foreign key reference a column of a different type (so the index exists and the planner can't use it), is a primary key integer when it should be bigint, is there a table with no primary key at all, is there a polymorphic pair with no composite index, and is a column json where jsonb was almost certainly meant.

All six are true or false at migration time, so the package goes in require-dev and the check goes into the job you already run:

- run: php artisan vacuum:lint --format=github

The --format=github flag emits workflow commands, so each finding lands as an annotation on the pull request diff, on the migration file and line that introduced it. It finds that line by parsing database/migrations with PHP's own tokenizer. If your table name is a variable, or you've run schema:dump --prune and deleted the migrations, you still get the finding, just without a file and line attached.

The migration you've probably written

I went back through a project I shipped last year. Here is the migration, more or less verbatim:

Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->foreignId('customer_id')->constrained();
    $table->foreignId('warehouse_id')->constrained();
    $table->string('status');
    $table->timestamps();
});

On MySQL, constrained() gives you a foreign key constraint and InnoDB gives you an index on customer_id for free, because InnoDB requires one. On Postgres, constrained() gives you the constraint and nothing else. Every DELETE FROM customers WHERE id = ? scans orders in full, and every JOIN orders ON orders.customer_id = customers.id does too, until the planner gives up on a hash join and you notice in production.

The fix is one method call per column, and I now consider it part of spelling constrained() correctly on Postgres:

Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->foreignId('customer_id')->constrained()->index();
    $table->foreignId('warehouse_id')->constrained()->index();
    $table->string('status');
    $table->timestamps();
});

If you'd rather find the existing damage before installing anything, this query lists foreign keys with no index whose leading column matches:

SELECT c.conrelid::regclass AS table_name,
       a.attname            AS column_name
FROM   pg_constraint c
JOIN   pg_attribute a ON a.attrelid = c.conrelid
                     AND a.attnum = ANY (c.conkey)
WHERE  c.contype = 'f'
AND    NOT EXISTS (
  SELECT 1 FROM pg_index i
  WHERE  i.indrelid = c.conrelid
  AND    i.indkey[0] = c.conkey[1]
);

Run that on the project I mentioned and it returned nine rows. Nine. On a codebase where I would have told you, with a straight face, that I index my foreign keys.

Adopting it on a schema that predates it

This is where I expected the package to fall over, and it didn't. Run vacuum:lint on a five-year-old app and it will find a lot. Four hundred findings is accurate and completely useless, because nobody is fixing four hundred things this afternoon and a build that stays red is a build that gets muted.

So the linter has a baseline, in the same shape PHPStan and Psalm use:

php artisan vacuum:lint --generate-baseline

That writes vacuum-baseline.json. Commit it. From then on, existing findings are excused and only new ones fail the build. The baseline matches on rule and subject only, so a later release rewording a rule or bumping its severity doesn't invalidate the file. When an entry stops matching anything because somebody fixed it, lint reports that as an info finding instead of silently carrying dead weight, which is the right call. A baseline nobody prunes is where the next defect hides.

One detail I appreciated: the number of suppressed findings is printed with every run and emitted as a ::notice on GitHub. A green build that quietly ignored four hundred problems is the kind of green this package exists to argue against, and it says so in its own README.

Where I'd push back

Two things.

First, the health score. The advisor rolls findings into a grade out of 100, and the score is computed from the findings themselves so the two can never disagree. Fine. But a single unused index on a big table knocks off 25 points, and I've watched a client treat a "D" as an emergency when the actual finding was an index that a quarterly report needs and nothing else touches. Scores are for dashboards that executives look at. Read the findings.

Second, the slow-statement rule depends on pg_stat_statements, and if you don't have the extension loaded the dashboard just tells you it's missing. That extension is the single most useful thing you can enable on a Postgres server, and I've written before about reading it for four queries before adding an index. If Vacuum is the reason you finally turn it on, good, but don't mistake the package for the extension. Vacuum is a nicer window onto data Postgres was already collecting. The collecting is the hard part, and Postgres did it.

I also haven't run the history snapshots long enough to say whether the forecasts are worth anything. Two days of data forecasts nothing. Ask me in a month.

What to do this week

Add heyosseus/vacuum to require-dev, put php artisan vacuum:lint --format=github in the test job that already spins up Postgres, and open a pull request with a deliberately bad migration to watch the annotation land. If the build lights up with a hundred existing findings, generate the baseline, commit it, and fix the unindexed foreign keys first. They're the cheapest fix with the largest effect, and if you came from MySQL, I'd bet money you have some. Most of the Laravel-on-Postgres work I do for clients starts with exactly that query and exactly that list.

---

Originally published at abrarqasim.com. I write there about React, PHP, Rust, Go and the AI tooling around them.