All jobs

Add Missing Foreign Key Indexes With an Ecto Migration

Yes. An agent finds Ecto references with no index, writes a concurrent index migration, tests it up and down against Postgres and opens a pull request.

Run this on RushOpens the console with this prompt ready to send, in a Project that has your repository.

What the agent does

The agent reads the schema as the migrations built it. In a Phoenix app like thechangelog/changelog.com there is no structure.sql, only 129 files in priv/repo/migrations, so it walks them in order and records every add ... references(...) and every create index or create unique_index, including later alter table changes and the raw execute statements that add GIN indexes. A foreign key is covered when some index starts with its column. Four are not: news_queue.item_id, news_sponsorships.sponsor_id, feeds.owner_id, and sponsor_reps.person_id, where the unique index on [:sponsor_id, :person_id] covers sponsor_id but cannot serve a lookup by person_id alone.

Then it checks that each missing index matters. lib/changelog/schema/news/news_queue.ex looks entries up with Repo.get_by(NewsQueue, item_id: item.id) in three places. Sponsor counts news sponsorships with where: q.sponsor_id == ^sponsor.id. Person declares has_many :feeds, Feed, foreign_key: :owner_id. Deletes matter too: 20180510145419_adjust_foreign_key_constraints.exs makes news_queue.item_id and news_sponsorships.sponsor_id cascade, 20240319161447_create_feeds.exs gives owner_id on_delete: :delete_all, and 20241024142033_move_delete_alls_to_database.exs does the same for both sponsor_reps columns. So every Repo.delete!(person) in the admin person controller, and every person PersonBouncer removes, has Postgres scan feeds and sponsor_reps for rows to delete.

The agent reads how migrations reach production before writing one. Each fly.toml under fly.io/ sets release_command = "on.deploy" with a bluegreen strategy, and docker/scripts/on.deploy runs mix ecto.migrate, so the migration runs while the current release is serving. It writes one migration with @disable_ddl_transaction true and @disable_migration_lock true and four create index(..., concurrently: true) lines, because CREATE INDEX CONCURRENTLY cannot run inside a transaction and does not block writes the way a plain CREATE INDEX does. Each index has an explicit name so the down migration drops exactly what the up created.

To prove it, the agent installs PostgreSQL and the Erlang and Elixir versions in mise.toml, creates changelog_test with the credentials in config/test.exs, and runs MIX_ENV=test mix ecto.migrate, mix ecto.rollback --step 1 and mix ecto.migrate again so both directions work. It inserts a few rows, runs EXPLAIN on each lookup with SET enable_seqscan = off to show the planner can use the new index, and runs mix test, which migrates through its alias in mix.exs.

Start it with one API call

POST https://api.phoenix.so/o/{org}/sessions with this body:

{
  "agent": "claude",
  "input": "Find foreign keys with no index and add them. Read every migration in priv/repo/migrations in order and list each references() column with whether an index starts with it. For each one missing, find the queries and on_delete cascades that use it in lib/. Read fly.io/*/fly.toml and docker/scripts/on.deploy to see how migrations run. Write one migration that creates the missing indexes concurrently with @disable_ddl_transaction and @disable_migration_lock and named indexes. Install Postgres, migrate, roll back and migrate again, show EXPLAIN for each lookup, run mix test, and open a pull request with the table and the output.",
  "resources": [
    {
      "url": "https://github.com/your-org/your-app"
    }
  ]
}
  1. Get an API key. In the console, open Settings, then API keys, and create a key for your org. Send it in the Authorization header as a Bearer token.
  2. Send the request. POST the body in request.json to https://api.phoenix.so/o/{org}/sessions, with your repository as the resources URL. The run starts in its own cloud sandbox.
  3. Read what came back. Follow the run on its live stream or wait for the run.completed webhook, then read the result and its trajectory.

What comes back

What comes back is a pull request that adds one file to priv/repo/migrations and changes nothing else. The description opens with a table of every foreign key in the schema: the table and column, the migration that created it, the index that covers it or none, and for the four uncovered ones, the query or cascade that reads them with its file and line.

Below it are the migrate, rollback and migrate output from the sandbox, the EXPLAIN plan for each lookup showing an index scan on the new index, and the mix test result. The pull request says what it cannot know from the repository: how many rows each table holds in production and how long the concurrent builds will take there. It gives the query that answers it, SELECT relname, n_live_tup, seq_scan FROM pg_stat_user_tables, for someone with production access.

A reviewer checks that the deploy path really runs migrations outside a transaction, since a concurrent index that fails part-way leaves an INVALID index behind that must be dropped by hand, and decides whether to ship the four indexes together or one at a time.

Run it on every Monday morning

Missing indexes come back whenever someone adds a reference and forgets the index. An automation in the Project with a schedule trigger, cron 0 8 * * 1 in your timezone, can run a narrower prompt every week: read the migrations added since last Monday, and open a pull request only if one of them adds a references column with no index. A week with nothing new ends with a one-line answer and no branch.

From a CI job, the same check is one POST /o/{org}/sessions with this body and the repository in resources, started when a pull request touches priv/repo/migrations. The key's webhook URL receives run.completed with the session_url, so the job can post the answer back to the pull request.

The use case this job follows, with its automation and trigger: Turn a Linear label into a PR.

Why run it on Rush?

Finding missing foreign key indexes from 129 migrations is a bookkeeping job: it is easy to miss a reference added in an alter table years later, or a composite index whose first column is not the one you need. The agent reads them all in order, and the run's record keeps the table it built, so a reviewer can check any row against the migration it cites.

A concurrent index migration has to be run to be trusted, because the module attributes and the transaction rules are easy to get wrong. The sandbox has its own Postgres, so the migration runs up, down and up again, and EXPLAIN shows the planner using each index, before anyone points it at production.

Frequently asked questions

Why does Ecto need both @disable_ddl_transaction and @disable_migration_lock?

CREATE INDEX CONCURRENTLY cannot run inside a transaction, so @disable_ddl_transaction stops Ecto from wrapping the migration in one. Ecto's migration lock also opens a transaction by default, so @disable_migration_lock is needed too, which is what Ecto's documentation recommends for concurrent indexes.

Doesn't Postgres index foreign keys automatically?

No. Postgres indexes the referenced side, the primary key, but not the referencing column. A lookup or a cascading delete on the referencing column scans the table unless you create the index yourself.

Why not use a unique index on sponsor_reps.person_id?

One person can represent several sponsors, so person_id alone is not unique. The existing unique index on [:sponsor_id, :person_id] stays, and the new one is a plain index on person_id.

Will the migration lock the tables during deploy?

A concurrent build takes a lock that allows reads and writes to continue, so the app keeps serving while mix ecto.migrate runs in the release command. It does wait for transactions already running on the table to finish before it starts.

The same job on other stacks

Keep reading