Index Rebuild Ordering During Schema Migration

When a schema change adds a query pattern or tightens a validator, the indexes those writes and reads depend on must already exist and be fully built before enforcement flips — otherwise the migration opens a window where queries fall back to collection scans or a unique constraint rejects a backfill mid-flight. Within the broader Zero-Downtime Schema Migration Automation in MongoDB framework, this workflow pins down the correct ordering of index operations relative to a $jsonSchema or collMod change: which index must be READY on every replica-set member before you touch the validator, how to drop-and-recreate an index without ever leaving the collection without a usable one, and how to prove readiness rather than assume it. The deliverable is a repeatable sequence a platform engineer can run against a live replica set so that no read regresses to an unindexed scan and no write is rejected for the wrong reason. The precise validator-first-or-index-first question is developed in Building Indexes Before Tightening Validators; this page is the surrounding order-of-operations model that child slots into.

Architectural Context & Enforcement Boundaries

An index and a validator are two different metadata artifacts on the same collection, and a migration usually changes both. The failure mode that ordering exists to prevent is a dependency inversion: the new contract goes live while the structure it relies on is still being built or has already been dropped. There are two directions this inverts. A read regression happens when application code that assumes an index ships before the index is READY, so every query runs as a COLLSCAN and P95 latency collapses under production concurrency. A write regression happens when a tightened validator — or a new unique index — starts rejecting writes that the collection cannot yet satisfy, because the supporting index or the backfill it depends on has not completed.

MongoDB evaluates a validator on the write path only, during insert, update, replace, and findAndModify, exactly as described in Strict vs Moderate Validation Levels. An index, by contrast, is consulted on both the read path (the query planner) and the write path (every write maintains every index, and a unique index rejects a duplicate before the validator is even consulted). Because a unique index constraint fires before $jsonSchema evaluation, an index built at the wrong moment can produce a code 11000 duplicate-key error that looks nothing like the code 121 a validator raises — the same logical migration, two entirely different diagnostic fingerprints.

The correct ordering is therefore: build the new index and confirm it is READY on every data-bearing member, then tighten the validator with collMod, then — and only then — drop the index the old contract needed. A drop-and-recreate follows the same principle in miniature: never drop the serving index first. Build the replacement, verify it, let the planner adopt it, and drop the obsolete one last, so there is never an instant where the query has no usable index to resolve.

Prerequisites & Operational Requirements

Confirm each item before touching indexes on a live collection mid-migration.

• MongoDB version: 4.4 or later. From 4.4, an index build runs simultaneously across all data-bearing replica-set members and commits only once a commit quorum of voting members has finished building — this is what makes a single createIndexes call safe on the whole deployment. On 4.2–4.3 you get the optimized hybrid build but member-by-member commit semantics differ; earlier than 4.2 you must plan a manual rolling build.
• The background option is obsolete. From MongoDB 4.2 every index build uses one optimized hybrid build that holds an exclusive lock only briefly at the start and end and yields to reads and writes in between. Passing background: true is accepted and ignored; do not design your ordering around a “foreground vs background” distinction that no longer exists.
• Driver: PyMongo 4.x (pip install "pymongo>=4.6,<5"), pinned in the automation image so OperationFailure.code, create_indexes, and command cursor semantics stay stable across builds.
• Permissions: createIndex and dropIndex actions (granted by dbAdmin) for the index operations, plus the collMod action for the validator change. Reading build progress via $currentOp requires the inprog privilege (in the clusterMonitor role).
• Disk headroom: a build materializes the full index plus temporary side-table space. Confirm free space exceeds the projected index size with margin before starting, or the build aborts partway.
• Oplog window: on a large collection the build can run for minutes to hours while the collection keeps taking writes. Confirm the oplog window comfortably exceeds the expected build time so a slow secondary never falls off it.

Idempotent Deployment / Implementation Workflow

Each step below is independently verifiable and safe to re-run. The example migrates an orders collection whose new contract requires a unique order_ref and queries by (tenant_id, created_at).

1. Build the new index with an explicit commit quorum. createIndexes is synchronous from the client’s perspective: it returns once the build has committed across the quorum. Name every index explicitly so later verification and rollback are unambiguous:

   db.runCommand({
     createIndexes: "orders",
     indexes: [
       { key: { tenant_id: 1, created_at: -1 }, name: "tenant_created_idx" }
     ],
     commitQuorum: "votingMembers"
   })
   // Expected output: { numIndexesBefore: 1, numIndexesAfter: 2, ok: 1 }

2. Watch the build while it runs. From a second session, an in-progress build is visible through $currentOp, including a completion percentage. getIndexes alone is not enough during the build — it is the confirmation for step 3, not a progress meter:

   db.getSiblingDB("admin").aggregate([
     { $currentOp: { allUsers: true, idleConnections: false } },
     { $match: { "command.createIndexes": { $exists: true } } },
     { $project: { msg: 1, "progress.done": 1, "progress.total": 1 } }
   ])
   // Expected output: docs with msg like "Index Build: scanning collection" and a progress fraction

3. Confirm the index is READY on every data-bearing member. Only a completed index appears in listIndexes/getIndexes. Poll each member (or, on 4.4+, trust the quorum commit and verify on the primary plus a sampled secondary) before proceeding:

   db.orders.getIndexes().map(ix => ix.name)
   // Expected output: [ "_id_", "tenant_created_idx" ]  ← the new index is present, i.e. built

4. Tighten the validator now that its support exists. With the read index in place, apply the collMod. If the new contract also needs a unique index, that index must likewise be READY first — the ordering rule and its failure modes are the whole subject of Building Indexes Before Tightening Validators:

   db.runCommand({
     collMod: "orders",
     validator: { $jsonSchema: {
       bsonType: "object",
       required: ["tenant_id", "order_ref", "created_at"],
       properties: {
         tenant_id:  { bsonType: "string" },
         order_ref:  { bsonType: "string", pattern: "^ORD-[0-9]{8}$" },
         created_at: { bsonType: "date" }
       }
     }},
     validationLevel: "moderate",
     validationAction: "warn"
   })
   // Expected output: { ok: 1 }

5. Drop the obsolete index last. Once the planner is serving reads from the new index and nothing references the old one, remove it. Dropping it earlier would strand queries on a scan for the whole build window:

   db.orders.dropIndex("legacy_status_idx")
   // Expected output: { nIndexesWas: 3, ok: 1 }

Production-Ready Automation Implementation

The following PyMongo module encodes the ordering as code: it creates each requested index with a commit quorum, polls until the index is registered and no build for it remains in $currentOp, and refuses to return control to the caller (who will next issue the collMod) until readiness is proven on the primary. It is idempotent — an index that already exists is a no-op — and it classifies the duplicate-key abort that a unique build hits on dirty data as non-retryable.

import logging
import time
from typing import Any, Dict, List, Optional

from pymongo.collection import Collection
from pymongo.errors import OperationFailure

logger = logging.getLogger(__name__)

# A unique-index build aborting on existing duplicates is deterministic — never retry it.
DUPLICATE_KEY = 11000
INDEX_BUILD_ABORTED = 279


class IndexReadinessManager:
    """Create indexes and block until they are READY, before any validator change."""

    def __init__(self, collection: Collection, poll_interval: float = 2.0, timeout: float = 3600.0):
        self.collection = collection
        self.poll_interval = poll_interval
        self.timeout = timeout

    def existing_index_names(self) -> set:
        return {ix["name"] for ix in self.collection.list_indexes()}

    def _build_in_progress(self, index_name: str) -> bool:
        """True while an index build for this name is still running on the primary."""
        admin = self.collection.database.client.admin
        cursor = admin.aggregate([
            {"$currentOp": {"allUsers": True, "idleConnections": False}},
            {"$match": {"command.createIndexes": self.collection.name}},
        ])
        for op in cursor:
            indexes = op.get("command", {}).get("indexes", [])
            if any(spec.get("name") == index_name for spec in indexes):
                return True
        return False

    def create_and_wait(
        self,
        keys: List[tuple],
        name: str,
        commit_quorum: str = "votingMembers",
        **options: Any,
    ) -> bool:
        """Create one index with a commit quorum and wait until it is READY.

        Returns True if the index was built, False if it already existed.
        """
        if name in self.existing_index_names():
            logger.info("Index %s already present; skipping build.", name)
            return False

        spec = {"key": dict(keys), "name": name, **options}
        try:
            self.collection.database.command(
                "createIndexes",
                self.collection.name,
                indexes=[spec],
                commitQuorum=commit_quorum,
            )
        except OperationFailure as exc:
            if exc.code == DUPLICATE_KEY:
                logger.error(
                    "Unique index %s aborted on existing duplicates — deduplicate "
                    "before retrying. Detail: %s", name, exc.details,
                )
                raise
            if exc.code == INDEX_BUILD_ABORTED:
                logger.error("Index build %s was aborted (code 279): %s", name, exc)
                raise
            logger.error("createIndexes for %s failed (code %s): %s", name, exc.code, exc)
            raise

        # createIndexes is synchronous, but confirm READY defensively before the caller
        # proceeds to tighten the validator.
        deadline = time.monotonic() + self.timeout
        while time.monotonic() < deadline:
            if name in self.existing_index_names() and not self._build_in_progress(name):
                logger.info("Index %s is READY.", name)
                return True
            time.sleep(self.poll_interval)

        raise TimeoutError(f"Index {name} did not reach READY within {self.timeout}s")


def migrate_indexes_then_validator(
    collection: Collection,
    index_plan: List[Dict[str, Any]],
    apply_validator,  # callable that issues the collMod once indexes are ready
) -> None:
    """Build every planned index to READY, THEN hand off to the validator change."""
    mgr = IndexReadinessManager(collection)
    for plan in index_plan:
        mgr.create_and_wait(
            keys=plan["keys"], name=plan["name"], **plan.get("options", {})
        )
    logger.info("All indexes READY; applying validator change.")
    apply_validator()  # only reached after every index is confirmed built

The load-bearing guarantee is the last line: apply_validator() is unreachable until every index in the plan is READY, which is exactly the ordering invariant the whole workflow depends on. Wire index_plan into the same declarative pipeline that carries your schema so the index set and the validator ship as one reviewed change.

Diagnostic Fingerprints & Fast Resolution

An out-of-order or failed index operation surfaces through a small set of exact signatures. Match on these to route the incident immediately.

Signature	Root cause	Resolution
OperationFailure code 11000 (E11000 duplicate key) during createIndexes on a unique index	Existing documents already violate the uniqueness the index declares	Deduplicate (or route dupes out) before the build; a unique index cannot be created over duplicate data
OperationFailure code 279 (IndexBuildAborted)	The build was killed — dropIndexes during build, primary stepdown, or disk exhaustion	Free disk / stabilize the primary, then re-issue createIndexes; the aborted build leaves no partial index
Query latency spikes to COLLSCAN right after a deploy	New query pattern shipped before its index was READY, or the serving index was dropped first	Confirm db.coll.getIndexes() lists the index; rebuild it and hold the app change until it is present
WriteError code 121 after collMod, but the field looks indexed	Validator tightened before the backfill the index supports completed — this is the inversion the child page prevents	Roll the validator back to warn, finish the backfill, re-promote
Mass unexpected deletions after adding an index	The new index was a TTL index (expireAfterSeconds); the TTL monitor began reaping already-expired documents	Confirm expireAfterSeconds was intended; a TTL index deletes retroactively, it is not inert
OperationFailure 16755 / Can't extract geo keys during a 2dsphere build	A document holds invalid GeoJSON, aborting the whole build	Fix or quarantine the malformed geometry, then rebuild

To read the live index inventory and confirm a build is genuinely finished rather than merely started, use getIndexes plus a $currentOp sweep:

from pymongo import MongoClient

client = MongoClient("mongodb://primary:27017/?replicaSet=rs0")
db = client.production_db

names = [ix["name"] for ix in db.orders.list_indexes()]
print("Built indexes:", names)

builds = list(client.admin.aggregate([
    {"$currentOp": {"allUsers": True}},
    {"$match": {"command.createIndexes": {"$exists": True}}},
]))
print("Builds still in progress:", len(builds))  # must be 0 before touching the validator

Edge Cases, Gotchas & Known Limitations

• A unique index cannot be built over duplicate data. The build scans the collection, and the first duplicate key aborts the entire operation with code 11000 — there is no “skip the bad ones” mode. Resolve duplicates, or use a partialFilterExpression to scope uniqueness to the documents that can satisfy it, before the build.
• TTL indexes are retroactive and immediate. Adding an index with expireAfterSeconds does not start a clean clock; the background TTL monitor immediately begins deleting every document already past the threshold. On a migration this can vaporize historical data within a minute. Treat a TTL index as a destructive operation and stage it deliberately.
• background: true is a no-op from 4.2. Ordering logic that assumes a “background build won’t block” versus a “foreground build will” is reasoning about a distinction MongoDB removed. Every build is the hybrid build; plan around its brief start/end locks and its yielding middle, not the legacy flag.
• listIndexes shows only committed indexes. An index mid-build does not appear in getIndexes(), so its absence is not proof a build failed — it may still be running. Distinguish “not started / failed” from “in progress” with $currentOp, never with getIndexes alone.
• A stepdown aborts an in-progress build. If the primary steps down mid-build on 4.4+, the build is restarted or aborted depending on the phase. Automation must treat a 279 as “re-issue from scratch,” not “resume.”
• Dropping an index the planner is actively using can regress live queries instantly. Always confirm — via explain() on the representative query — that the planner has adopted the replacement index before dropping the old one. Ordering is not only about builds; the drop is the other half.
• Partial and sparse indexes change what “supported” means. A partialFilterExpression index only serves queries whose predicate is provably a subset of the filter. A validator that assumes an index covers a field may be wrong if the index is partial; verify the planner actually uses it for the migration’s query shape.

Verification & Rollback Procedures

Prove the new index serves the intended query before you trust the migration. An explain that reports an IXSCAN on the new index — not a COLLSCAN — is the only positive confirmation:

plan = db.orders.find(
    {"tenant_id": "acme", "created_at": {"$gte": cutoff}}
).explain()

stage = plan["queryPlanner"]["winningPlan"]
# Walk to the leaf stage; it must be IXSCAN on tenant_created_idx, not COLLSCAN.
print(stage)
assert "IXSCAN" in str(stage), "planner is NOT using the new index"

Rollback for an index change is a single dropIndex, and because the ordering never dropped the old serving index until the new one was proven, reverting the new structure restores the prior state without data loss. If a validator was tightened in the same migration, revert it first (one collMod) so no write is rejected while you remove the index:

// 1. Soft-revert the validator so writes stop being rejected.
db.runCommand({ collMod: "orders", validationLevel: "moderate", validationAction: "warn" })

// 2. Drop the index that was added for the new contract (time-to-recover: one dropIndex).
db.orders.dropIndex("tenant_created_idx")
// Expected output: { nIndexesWas: 2, ok: 1 }

Both operations are metadata changes on the primary that replicate through the oplog, so time-to-recover is the replication lag of your slowest secondary — typically seconds. Treat reverting the validator to warn as the immediate incident response, then decide whether to rebuild the index or abandon the migration. Snapshotting the validator before the change, so this revert is exact rather than reconstructed, is covered under the migration section’s rollback automation workflow.

Frequently Asked Questions

Related

• Zero-Downtime Schema Migration Automation in MongoDB — the parent reference defining the migration lifecycle this index-ordering workflow sits inside.
• Building Indexes Before Tightening Validators — the precise sequencing rule for the specific case where a new index must be READY before the validator flips.
• Document Transformation Pipelines for Schema Migration — the backfill and reshaping stages that must finish before a unique index or tightened validator will accept the data.
• Rollback Automation for Schema Changes — snapshotting validator state so the revert half of an index-and-validator change is exact.
• Strict vs Moderate Validation Levels — how the validationLevel you set in step 4 decides which writes the tightened contract checks.