Why I Stopped Creating Database Indexes at Boot

A login service of mine created its own database indexes on startup. The code was short, idempotent, and non-fatal, the kind of thing nobody looks at twice. I ripped it out anyway. Indexes now get created by hand, per environment, before the deploy that needs them.

TL;DR: creating an index is a schema change, and schema changes belong in a reviewed ops step, not in code that runs every time a process boots. Boot-time createIndex runs on every instance, needs privileges the app shouldn't have, and turns failures into log lines nobody reads. The query has to stay correct without the index, which is exactly what lets the two steps live apart.

What the boot code did

Login emails were matched case-insensitively, so Ahmed@example.com is found when someone types ahmed@example.com. In MongoDB that's a query with a strength-2 collation. The catch: a collation query can only use an index with the same collation. So the service made one at boot for each account collection:

// At server start, for every account collection:
try {
  await collection.createIndex(
    { email: 1 },
    { name: "email_ci", collation: { locale: "en", strength: 2 } },
  );
} catch (error) {
  logger.warn(`Failed to ensure email index: ${String(error)}`);
}

Looks harmless. createIndex is a no-op when the index exists, and a failure is just a warning.

Why it had to go

It runs on every start of every instance. Rolling deploys, crash loops, scale-outs: each one calls it again. On a big collection the first call kicks off a real index build on the primary, right in the middle of a deploy, which is already the riskiest moment you have.

The app needs a privilege it shouldn't have. To create indexes, the service's database user needs index rights. A login service needs to read and write accounts. It has no business changing the schema.

"Idempotent" only holds for the identical index. If an index with that name or those keys already exists with different options, a different collation for example, MongoDB doesn't fix it. It errors.

Non-fatal meant silent. Every one of those failures became a warning, and the service booted normally. Nothing failed, no alert fired. Logins still worked, they just scanned the whole collection each time. A slow query nobody sees is precisely the failure I wanted gone.

It hides the schema. With the index in boot code, the indexes in each environment were whatever the last deployed version happened to create. As a manual step, an index is a named change someone ran on purpose, and someone else can check it.

What stays in the code

The lookup keeps its collation and stays correct without the index. A missing index costs speed, never results, and that property is what makes the split safe. The order becomes:

1. Create the index by hand in each environment, with the same collation.
2. Check it exists.
3. Deploy the code that relies on it being fast.

The code comment now says where the index comes from, so the next person doesn't helpfully add the boot call back.

When boot-time creation is fine

For a side project with one instance and one environment, just do it at boot. It's simple and it works. My rule kicks in when you have several instances, several environments, a least-privilege database user, or enough data that an index build is real work.