Picture the pull request. One migration, a dozen lines, adds an index on customer_id. The reviewer approves it in about ninety seconds, and why wouldn't they? The column shows up in the WHERE clause, so the index looks obviously right. What nobody opened was the repository method that index was meant to serve. It sits three directories away and has picked up a sort since anyone last read it. I think most of our indexing bugs start right there. An index is a deal you make with one specific query, and in a typical PHP project the deal gets written in one file while the query lives in another, so nobody ever checks that the two still agree.

SELECT * FROM order_events
WHERE customer_id = 8842
AND created_at > now() - interval '30 days'
ORDER BY created_at DESC;

Farhad Ali's recent Medium write-up shows how much that gap costs. His team had a PostgreSQL table called order_events, around four million rows of order lifecycle entries, and a query that fetched one customer's events from the last thirty days, newest first. They had indexed customer_id on its own. EXPLAIN ANALYZE showed the planner ignoring that index and doing the work the expensive way, which included sorting a customer's entire history by created_at after finding the rows. Swapping in a composite index on (customer_id, created_at DESC) took the query from a bit over four seconds to 9 milliseconds. The detail I keep coming back to is that it was their third time. The same mismatch had already hit a healthcare scheduling app and an internal analytics tool for a logistics client.

Three projects and one root cause is a pattern, and I'd blame the pattern on how we work rather than on any one developer. Think about how a query actually changes in a Laravel or Symfony app. Someone adds orderByDesc('created_at') to a query builder chain because the product owner wants the newest items on top. A few months later someone else adds a scope that joins a second table. Each change looks like a tiny presentation tweak in the diff. Neither one touches a migration, so the index stays exactly as it was on the day it was created, while the query it was built for slowly turns into a different query.

The ORDER BY is the sneaky part, because hardly anyone thinks of it as a performance decision. It reads like formatting. To PostgreSQL, though, it's a requirement: either an index hands over the rows already in that order, or the database collects every matching row and sorts them itself. Take a long-standing customer with tens of thousands of events. That sort runs on every page load of their dashboard, and when a few of those requests pile up together, your PHP-FPM pool fills with workers that are all just waiting on Postgres.

So here's my pitch. Make the index and the query know about each other. The repository method gets a comment naming the index it depends on, and the migration gets a comment naming the method it was built for. A change to that method's builder chain should trigger the same review question as a schema change: does the index still fit what this query asks for? If your team likes automation, a CI job can seed a table and run EXPLAIN on your hottest queries. Either way, the person changing the sort should be the person who has to think about the index.

The best argument against this is a serious one, and I take it seriously. Indexes are shared infrastructure. If you think in terms of one index per query, an events table ends up with fourteen of them, and every INSERT has to update all fourteen. On a write-heavy log like order_events, that shows up as slower inserts, a busier autovacuum and a planner choosing between near-duplicates. On top of that, planner statistics drift, so an index that's perfect in March can get skipped in November. Tie indexes too closely to individual queries and you trade slow reads for slow writes and a schema nobody dares to touch.

I still land on coupling, and the reason is the bloat itself. Indexes pile up because nobody knows who owns them. An index with no named consumer can't safely be dropped, so it never is, and the next developer adds another one next to it to be safe. When every index lists the queries that depend on it, merging two of them is a quick job: you know exactly which statements to re-run EXPLAIN ANALYZE on, and you can check pg_stat_user_indexes to see which ones never get scanned. Knowing who owns each index is what lets you clean them up. Ignorance gets you the fourteen.

I know teams that handle this with naming conventions, others that keep a markdown file next to the migrations, and at least one that checks idx_scan counts every quarter and drops anything still at zero. I'm curious what actually holds up after two years and three rotations of developers. In your codebase, how does someone changing a query find out which index it depends on? And has that ever stopped a four-second dashboard before a customer saw it?