Resumable index builds

Published reference · snapshot 2026-10-20 · skill mongodb-expert/references/resumable-index-builds.md

Also known as: Index build resumability and checkpoints

Source-anchored research for Resumable index builds. Run status: COMPLETED.

These notes link each claim to its source. Some citations identify archived research reports that are no longer hosted on this site. A published reference means the content is available; it does not certify independent review or accuracy.Read the editorial policy and follow the sources before relying on a claim.

Definitions

'Resumable' means three different things across engines. MongoDB resumes by itself after a clean shutdown or rollback, and its manual documents no command to pause or resume a build. SQL Server lets a DBA pause, resume and abort a resumable operation. PostgreSQL has no resume: a failed concurrent build must be dropped and retried. (medium confidence) [source]

How it works

Index builds can take from moments to days. Before resumable builds, a restart discarded the finished scan and sort work and started the build again. Resume keeps that work when a node restarts mid-build, which saves CPU, I/O, cache pressure and elapsed time on large collections. (medium confidence) [source]
Since 5.0, a rollback during an index build saves the build state with the same mechanism as clean shutdown. If work remains after the rollback, mongod recovers the build and continues from the checkpoint. If the rollback reverts the build itself, the user must re-create the index. (high confidence) [source]
A member restarted as a standalone does not resume the build. SERVER-37637 (Fixed 4.3.4) made standalone nodes ignore in-progress builds so they do not deviate from the replica set, and says the builds are recovered and finished when the node rejoins in replica set mode. The manual says the build stays paused until someone drops it. (medium confidence) [source]
MongoDB tests detect a resume by log line only. SERVER-128117 (Backlog, unresolved) proposes an OpenTelemetry metric named index_builds.resume.started with a phase attribute, because the bounded log buffer has dropped the resume line in test failures (BF-43501, BF-40886). (low confidence) (verified as of 2026-08-25) [source]
On a MongoDB primary or secondary, a clean shutdown with force: true or a SIGTERM during an index build saves the build's progress to disk when the commit quorum is the default votingMembers. After restart, mongod recovers the build automatically and continues from the saved checkpoint. (high confidence) [source]
Since MongoDB 5.0, a node that rolls back during an index build saves the build's progress to disk. If work remains when the rollback ends, mongod recovers the build and continues from the saved checkpoint. The server README says the rollback algorithm (recover to a timestamp) supports resuming a build interrupted at any phase. If the rollback reverts the build itself, the operator must re-create the index. (high confidence) [source]
MongoDB's primary-driven index build (PDIB) is a second build mode: only the primary builds the index, every internal write replicates through the oplog, and commit quorum does not apply. The server README says PDIB resumes on step-up after a failover or primary restart, at any point of the build and for any build configuration, using an on-disk state format similar to the two-phase mechanism. A Jira ticket says the PDIB resume path was introduced behind the feature flag featureFlagResumablePrimaryDrivenIndexBuilds. (medium confidence) (verified as of 2026-10-20) [source]
When a primary stepdown interrupts a PDIB, the old primary tears down its in-memory build and finishes the index as a secondary, and the new primary resumes and commits the build. The new primary records the metric index_builds.resume.succeeded. SERVER-128273 (created 2026-06-04, fixed in 9.0.0-rc0) stopped the teardown from being counted in index_builds.failed. The README describes the same step-up: the coordinator reads each active PDIB's saved state and tries to resume it. (medium confidence) (verified as of 2026-10-20) [source]
A resumable SQL Server index operation does not run as one large transaction. The transaction log can truncate during the build, which avoids large log growth, and the data needed to resume is stored in the database's data files. A non-resumable online build keeps the log untruncated until it finishes so that it can roll back, although the log can still be backed up. dbi services ran a log backup during a paused rebuild and concluded that a paused operation holds no active transaction that blocks log truncation. (high confidence) [source]
A pattern for fitting a long SQL Server index build into maintenance windows: start it with RESUMABLE = ON and a MAX_DURATION so that it pauses itself, then resume it in the next window, optionally with a new MAXDOP and WAIT_AT_LOW_PRIORITY. Microsoft says you can pause and restart a build multiple times to fit your windows. Its examples use MAX_DURATION = 240, and dbi services resumed a rebuild with MAX_DURATION = 120 MINUTES to protect the production workload. (high confidence) [source]
After an interrupted MongoDB build, the mongod log shows "Index build: wrote resumable state to disk" when state is saved and "Found index from unfinished build" when startup finds a build to recover. db.currentOp() reports a percentage-complete figure for the current stage of a running build. (low confidence) [source]

How-to and procedures

To stop an in-progress build, run dropIndexes or the dropIndex and dropIndexes helpers. The manual says not to use killOp on index builds in replica sets or sharded clusters. (low confidence) [source]

Measurements and reference values

Since 7.1, a build stops when a member's free disk space falls below indexBuildMinAvailableDiskSpaceMB (default 500 MB), and a primary does not start a new build below that level. Setting the value to 0 disables the check. A member that has already voted to commit does not stop the build. (high confidence) (verified as of 2026-10-14) [source]
A failed PostgreSQL concurrent build keeps costing write overhead until someone drops the invalid index. PostgresAI measured that writes update the invalid index, that VACUUM and WAL work include it, and that HOT updates on its columns fall to 0% (96.8% for a non-indexed column). Monitoring shows 0 scans, so it can pass for an unused index. Find these indexes with indisvalid = false. (medium confidence) [source]

Problems, failure modes and limitations

Resume needs a clean shutdown. The manual names two triggers: the shutdown command with force: true, or a SIGTERM signal, while the commit quorum is the default votingMembers. If mongod crashes, the build cannot resume whatever the commit quorum is, and it restarts from the beginning after the node comes back. (medium confidence) [source]
SERVER-83857 (Fixed 7.3.0-rc0) allows index builds to resume on non-voting nodes. The ticket says builds had been restricted to voting members and asks whether that limit could go now that PBWM is gone. (low confidence) [source]
The external sorter spills to local, unreplicated temp files under dbpath/_tmp when a build reaches its memory limit. The resume state includes the sorter state, so these files must survive a clean shutdown. SERVER-86648 treats the sorter files as part of the persisted resume state and fixed their missing fsync. (high confidence) [source]
A SQL Server DBA controls a resumable operation with ALTER INDEX ... PAUSE, RESUME and ABORT, or by re-running the original statement with the same parameters. MAX_DURATION (above 0 and up to 10080 minutes) pauses the build automatically. A pause or a failure leaves the operation paused indefinitely until someone resumes or aborts it. RESUME can change MAXDOP. sys.index_resumable_operations shows state and progress. (high confidence) [source]
Resumable SQL Server builds avoid one large transaction, so the log can truncate during the build, and the data needed to resume lives in the database data files. Microsoft reports generally no performance difference from non-resumable online builds. A constant overhead can noticeably slow resumable CREATE INDEX on small tables. (high confidence) [source]
On self-managed deployments, createIndexes spills to dbpath/_tmp after the memory limit. maxIndexBuildMemoryUsageMegabytes defaults to 200 MB per createIndexes command, shared equally among its indexes. maxNumActiveUserIndexBuilds defaults to 3, so total build memory can reach three times that. On Atlas the memory limit follows the cluster tier (100 MB on M10 to M20, up to 2048 MB on M200 and above) and cannot be changed. (high confidence) (verified as of 2026-10-14) [source]
A paused SQL Server operation keeps costing resources. The original index and the new index both occupy disk, and DML must update both. Ghost cleanup stays blocked on the in-build index. Update-heavy workloads can lose throughput. A transaction that needs a table-level exclusive lock, such as INSERT ... WITH (TABLOCK), fails with error 10637. (high confidence) [source]
Do not rely on blog claims that MongoDB resumes any interrupted build on restart. OneUptime (2026-03-31) says MongoDB 4.4 and later automatically resume the build on startup, and ScaleGrid (2017) says MongoDB picks up the build where it left off. Neither cites the manual or release notes. The manual and MongoDB tickets limit resume to a clean shutdown or rollback with a saved checkpoint, and place the feature in the 4.7 development series that shipped as 5.0. (medium confidence) [source]
SERVER-127943 describes primary-driven builds as flag-gated off in production: its test suite enables featureFlagPrimaryDrivenIndexBuilds, featureFlagResumablePrimaryDrivenIndexBuilds and featureFlagContainerWrites. The ticket (Fixed 9.0.0-rc0, 8.3 backport requested) fixed a crash: resuming a primary-driven build of a multikey wildcard index at the start of bulk load made the secondary fail with WT_DUPLICATE_KEY, because the resume path did not restore the flag that deduplicates wildcard multikey metadata keys. (low confidence) (verified as of 2026-10-20) [source]
SQL Server limits resumable operations. SORT_IN_TEMPDB = ON is unsupported and the statement cannot run inside an explicit transaction. ALTER INDEX REBUILD ALL, ALTER TABLE REBUILD, columnstore indexes, filtered indexes and disabled indexes are unsupported. A computed or timestamp key column, or a computed or LOB included column, blocks a resumable rebuild. (high confidence) (verified as of 2026-10-19) [source]
If a PDIB cannot be resumed on step-up for any reason, the server aborts the build. A two-phase build that fails to resume at startup restarts from the beginning instead. (low confidence) (verified as of 2026-10-20) [source]
In SQL Server a failure or a pause does not end a resumable operation. It stays in an indefinite pause state until someone resumes it or runs ALTER INDEX ... ABORT. An aborted operation cannot be resumed. In a 2017 dbi services test, a manual availability-group failover during a resumable rebuild left the rebuild paused, not lost. (medium confidence) [source]
SQL Server resumable index operations do not support SORT_IN_TEMPDB = ON, disabled indexes, filtered indexes, columnstore indexes, ALTER INDEX REBUILD ALL or ALTER TABLE REBUILD. They cannot run inside an explicit transaction. They cannot rebuild an index that has a computed or timestamp (rowversion) key column, or a computed or LOB included column. Microsoft labels these the current functional limitations. (high confidence) (verified as of 2026-10-20) [source]
While a SQL Server online index operation is paused, a transaction that needs a table-level exclusive lock on that table, such as INSERT ... WITH (TABLOCK), fails with error 10637: "Cannot perform this operation on '<object name>' with ID (<object ID>) as one or more indexes are currently in resumable index rebuild state. Please refer to sys.index_resumable_operations for more details." Remove the TABLOCK hint, or resume the operation and wait for it to finish. (low confidence) [source]
Resuming a SQL Server build in a loop after each log-full error can be extremely slow. In a test dated 2025-01-04 on SQL Server 2022 CU16 (a nonclustered index on a 52.9-million-row table), a resumable build took about 1m18s unconstrained but 3h19m10s, about 150 times longer, with the log capped at 512 MB and a TRY...CATCH loop that backed up the log and resumed. The session was CPU-bound in the Online Index Insert operator at under 4,900 rows per second, and only when FastLoadContext was enabled. Trace flag 692 (disables FastLoadContext) cut the run to about 2m30s. Frequent log backups on a separate connection also avoided the slowdown. (low confidence) (verified as of 2025-01-04) [source]
A failed PostgreSQL concurrent build leaves an invalid index that keeps the index name, so retrying the same CREATE INDEX CONCURRENTLY fails with ERROR: relation "idx_orders_created_at" already exists, which can break migration pipelines until someone drops the index. PostgresAI also reports that invalid indexes are updated on every INSERT and UPDATE, generate WAL, are vacuumed, and block HOT updates on their columns. Detect them with pg_index where not indisvalid and remove them with DROP INDEX CONCURRENTLY. (medium confidence) [source]
CockroachDB runs schema changes, including index backfills, as background jobs that can be paused, resumed and cancelled. Schema changes that trigger an index backfill, such as CREATE INDEX, ALTER PRIMARY KEY and ADD COLUMN with INDEX or UNIQUE, pause automatically when the node running them is low on disk space. A failed schema change is cleaned up automatically. The page does not say whether an interrupted backfill resumes from a checkpoint after a node failure. (low confidence) (verified as of 2026-10-20) [source]
Before 2024, MongoDB's saved resume state for a build was not fully durable: the external sorter files were never fsynced, so a hard machine reset after a clean shutdown could lose data. SERVER-86648 (closed 2024-04-11) fixed this in 5.0.27, 6.0.16, 7.0.9, 7.3.2, 8.0.0-rc3 and 8.1.0-rc0, and the 5.0.27 release announcement of 2024-06-06 lists it. The ticket says the server would detect an incorrectly persisted state and fail with an assertion before completing the index. (medium confidence) [source]
MongoDB ticket SERVER-90037 (Backlog, unresolved, last updated 2025-10-16): after an unclean shutdown with no checkpoint, a replicated build can be absent from the catalog yet present in the oplog, and oplog replay restarts it at startup. If the build caused the shutdown, for example by running out of memory, the node crash-loops. The build cannot be dropped in standalone mode because it is not reconciled to the catalog, and recoverFromOplogAsStandalone=true does not help. The ticket's only mitigation is a larger instance so the build finishes during startup. (low confidence) (verified as of 2026-10-20) [source]
On a MongoDB replica set member restarted as a standalone (replSetName removed), the saved build cannot restart and stays in a paused state until an operator drops it. On a true standalone mongod, a shutdown during a build loses the job and all progress; restarting does not restart the build, and the operator must re-issue createIndex(). (low confidence) [source]

Comparisons and alternatives

A saved checkpoint resumes a build once. If mongod restarts again during the same build, the build restarts from the beginning instead of resuming. Only the manual states this limit. (low confidence) [source]
The manual says that on Atlas Infinite clusters an interrupted createIndexes operation always resumes, and that this differs from Atlas Core clusters and self-managed deployments. No Atlas page found explains the mechanism. (low confidence) (verified as of 2026-10-19) [source]
Since 7.1, a secondary that hits an index build error asks the primary to stop the build instead of crashing. A crashed mongod cannot resume a build. If the secondary has already voted to commit, it cannot retract the vote and still crashes. (high confidence) [source]
A resumable index build saves its progress so that a pause, failover or restart continues the build instead of restarting it. The covered interruptions differ by engine. SQL Server resumes after an unexpected failure, a database failover or an ALTER INDEX PAUSE command. MongoDB resumes after a clean shutdown, a SIGTERM or a rollback. The PostgreSQL 18 documentation (18.6, read 2026-10-20) describes no resume for CREATE INDEX CONCURRENTLY. (high confidence) (verified as of 2026-10-20) [source]
If a MongoDB primary or secondary crashes instead of shutting down cleanly, the index build does not resume, whatever the commit quorum. It restarts from the beginning when mongod returns. SERVER-90037 describes the same path from the oplog side: after an unclean shutdown with no checkpoint, the build can be missing from the catalog but still in the oplog, and oplog replay restarts it. (high confidence) [source]
A saved MongoDB checkpoint is usable once. If mongod restarts again while the same build is still running, the build restarts from the beginning instead of resuming. (low confidence) [source]
Since MongoDB 7.1, a build aborts instead of pausing when a replica set member's free disk space falls below indexBuildMinAvailableDiskSpaceMB (500 MB by default per the server README), and on a primary the build does not start if disk space is low. A member that already voted to commit is not stopped. By contrast, Microsoft documents that a SQL Server resumable build can recover from running out of disk space without restarting from the beginning. (medium confidence) (verified as of 2026-10-20) [source]
A TiDB GitHub issue (#52805, opened 2024-04-22, closed 2024-05-13 by PR #53134, labels affects-7.1, affects-7.5, affects-8.1) reports that an ingest-mode ADD INDEX on v7.1.5 ended in 'rollback done' instead of finishing when the TiDB servers were rolling-restarted. The issue page does not explain the mechanism or the fix beyond the PR title 'ddl: add context cancel check before commit'. (low confidence) (verified as of 2026-10-20) [source]
To keep a MongoDB build resumable during planned maintenance: stop the node with a clean shutdown or SIGTERM, not a kill that crashes it; keep the default votingMembers commit quorum; and do not restart the same node twice during one build, because a checkpoint resumes once. The trade-off is that the manual warns the default quorum can hang a build until an unreachable data-bearing voting node returns. This advice is derived from the documented conditions, and neither source states what happens to a build with a non-default quorum, so treat such a build as non-resumable unless tested. (medium confidence) [source]

Facts and statements

MongoDB saves a running index build's progress to disk on clean shutdown. After the restart, startup recovery reads the saved state and continues the build from the saved phase. The build does not rescan the collection from the start. (high confidence) [source]
The saved resume state names the phase the build was in: initialized, collection scan, bulk load, or drain writes. It also holds phase-specific state: the external sorter state and the idents of the side-writes, duplicate-key and skipped-record tables. If the server fails to resume for any reason, the build restarts from the beginning. (high confidence) [source]
The 2020 resume work landed phase by phase. SERVER-49409 resumes a build shut down during the collection scan. SERVER-49408 resumes a build shut down during bulk load: the server drops and recreates the unfinished index tables empty, restarts the bulk load for all tables, and deletes each sorter temp file only after the load phase succeeds. Both tickets are Fixed in 4.7.0. (high confidence) [source]
Resumable builds are on by default from MongoDB 5.0. SERVER-49847 (Fixed 4.7.0, the development series that became 5.0) turned the feature on by default. SERVER-86648 lists 5.0.0 as the earliest version with the resume-state persistence, and the manual dates resume-on-rollback to 5.0. Before this work, SERVER-49408 (created 2020-07-09) records: 'Currently, index builds are restarted from the beginning.' (medium confidence) [source]
A resumable build uses a majority read cursor for its collection scan. It waits for the majority commit point to advance before the scan starts, after the side-writes table is installed. The wait lets a rollback resume the build. The wait was unbounded at first. SERVER-50519 (Fixed 4.8.0) found builds stuck in it during a kill-secondaries test suite, and SERVER-50539 (Fixed 4.7.0) extended the wait to support a deadline. (medium confidence) [source]
The manual and the master design doc tie resume to the default commit quorum votingMembers. The design doc lists two general conditions: the build runs on a voting member with commit quorum votingMembers, and majority read concern is enabled. The code check is IndexBuildsCoordinator::isIndexBuildResumable(). (medium confidence) (verified as of 2026-10-20) [source]
Replica set builds have been simultaneous (two-phase) since 4.4. Every data-bearing node builds from its own data, and the node that starts a build need not be the node that commits it. These builds survive primary changes without resume. Resume covers node restart and rollback. (medium confidence) [source]
The master-branch design doc describes primary-driven index builds. Only the primary builds the index, and it replicates sorter spills and side writes through the oplog, so commit quorum does not apply. These builds resume on step-up after a failover or primary restart, at any point of the build and for any build configuration. The server rewrites resume state at each sorter spill during the scan, at intervals during the load (parameter primaryDrivenIndexBuildLoadResumeStateWriteIntervalKeys), and once on entering the drain phase. (medium confidence) (verified as of 2026-10-20) [source]
SQL Server supports resumable online index operations. ALTER INDEX ... REBUILD has supported them since SQL Server 2017, CREATE INDEX since SQL Server 2019, and Azure SQL Database and Azure SQL Managed Instance support both. The statement needs ONLINE = ON and RESUMABLE = ON. SQL Server does not store RESUMABLE in index metadata, so each statement must set it. (high confidence) [source]
PostgreSQL has no resumable index build. If CREATE INDEX CONCURRENTLY fails, for example on a deadlock or a unique violation, it leaves an INVALID index that queries ignore but writes still maintain. The documented recovery is to drop the index and run the build again, or to run REINDEX INDEX CONCURRENTLY. The development docs (20devel, 2026-10-18) show no change. (high confidence) (verified as of 2026-10-18) [source]
A build that saves resume state logs 'Index build: wrote resumable state to disk'. A restart that finds an unfinished build logs 'Found index from unfinished build'. MongoDB's own tests detect a resume through log ID 4841700, which reports the phase the build resumes from. (medium confidence) [source]
Plan restarts of a member that is building an index. Stop it with a clean shutdown (shutdown with force: true, or SIGTERM) and restart it in replica set mode with its replSetName. A crash, a second restart of the same build, or a standalone restart loses the checkpoint or leaves the build ignored. (medium confidence) [source]
SQL Server's database-scoped setting ELEVATE_RESUMABLE (OFF, WHEN_SUPPORTED or FAIL_UNSUPPORTED) makes supported index DDL resumable by default. A statement can still set RESUMABLE = OFF. (low confidence) [source]
With the default commit quorum votingMembers, a build can hang at commit if a data-bearing voting node becomes unreachable, until that node returns. The commit quorum of a running build can be changed with the setIndexCommitQuorum command. (medium confidence) [source]
Open bug SERVER-130777 (In Code Review, Unresolved on 2026-10-12; affects 6.0, 7.0 and 8.0): a resumed hybrid build can lose the multikey flag set by concurrent writes. The build commits an index that holds multikey entries but is flagged non-multikey. Compound-interval predicates such as {a: {$gte: 4, $lte: 3}} can then miss documents with array values, covered projections can return an arbitrary array element, and validate reports the index invalid. The ticket names no workaround; wrong results persist until the index is rebuilt. (low confidence) (verified as of 2026-10-12) [source]
Bug SERVER-86648 (Fixed 5.0.27, 6.0.16, 7.0.9, 7.3.2, 8.0.0-rc3, 8.1.0-rc0): the server never fsynced sorter files on clean shutdown, so a hard reset after a clean shutdown could lose resume data. The ticket expects the server to detect the loss with an assertion before the index completes. (low confidence) [source]
MongoDB introduced resumable index builds in 5.0. The 5.0.0-rc0 announcement of 2021-05-27 says in-flight index builds now automatically resume from where they left off after a node restarts. The per-phase resume work was fixed in the 4.7.0 development line in 2020: collection scan (SERVER-49409), bulk load (SERVER-49408) and side-table catch-up (SERVER-48417). Each of those tickets records the earlier behavior: index builds are restarted from the beginning. (high confidence) [source]
A resumable MongoDB two-phase (hybrid) build records, at clean shutdown, which phase it was in: initialized, collection scan, bulk load or drain writes. It also saves what that phase needs: the external sorter state and the identifiers of the side-writes, duplicate-key and skipped-records tables. The 2020 design keeps one progress record per build, keyed by build UUID, with a phase field, and preserves the hybrid build's temporary tables across startup. Startup recovery reads this state and resumes in the saved phase. If resume fails for any reason, the build restarts from the beginning. (high confidence) [source]
In the 2020 design for the bulk-load phase (SERVER-49408), an interrupted bulk load begins anew for all index tables: unfinished index tables are emptied by dropping and recreating them, and each sorter temporary file is deleted only after the second phase completes. In that design the resume point saves the collection scan, not partial bulk-load work. (low confidence) [source]
For a MongoDB two-phase build, saved progress depends on the commit quorum. The manual ties clean-shutdown saving to the default commit quorum votingMembers. The server README lists two general conditions for resumability: the build runs on a voting member with the default votingMembers commit quorum, and majority read concern is enabled. The README defers the full criteria to IndexBuildsCoordinator::isIndexBuildResumable(). (high confidence) [source]
The MongoDB manual states that on Atlas Infinite clusters an interrupted createIndexes operation always resumes. It contrasts this with Atlas Core clusters and self-managed deployments, where the clean-shutdown, crash and standalone rules apply. (low confidence) (verified as of 2026-10-19) [source]
PDIB persists resume state at three points. During the collection scan it rewrites the state each time the sorter spills. During the load phase it persists each range's iterator position every N keys, set by the server parameter primaryDrivenIndexBuildLoadResumeStateWriteIntervalKeys. On entering the drain phase it writes the state for all indexes once. Key 1 of the state table holds the build-wide phase and keys 2 to N+1 hold per-index state. (medium confidence) (verified as of 2026-10-20) [source]
SQL Server lets an online index operation run as resumable with RESUMABLE = ON. ALTER INDEX ... REBUILD supports it from SQL Server 2017 and CREATE INDEX from SQL Server 2019. Azure SQL Database and Azure SQL Managed Instance also support it. RESUMABLE requires ONLINE = ON. The option is not stored in index metadata and applies only to the current statement, so each statement must specify RESUMABLE = ON. (high confidence) [source]
A SQL Server resumable operation is controlled with ALTER INDEX ... PAUSE (or KILL of the session) to pause, ALTER INDEX ... RESUME [WITH (MAXDOP, MAX_DURATION, WAIT_AT_LOW_PRIORITY)] to continue, and ALTER INDEX ... ABORT to cancel. Re-running the original ALTER INDEX REBUILD statement with the same parameters also resumes it. MAX_DURATION (more than 0 and up to 10080 minutes) pauses the build automatically after that many minutes; if omitted, the build runs until it completes or fails. MAXDOP can change at resume. (high confidence) [source]
The view sys.index_resumable_operations (SQL Server 2017 and later, Azure SQL) reports each resumable operation: state (0 = running, 1 = paused), state_desc, start_time, last_pause_time, total_execution_time in minutes, percent_complete, page_count (pages allocated for the new and mapping indexes), last_max_dop and sql_text. SELECT * FROM sys.index_resumable_operations WHERE state = 1 lists the paused operations. (high confidence) [source]
Leaving a SQL Server resumable build paused for a long time is costly. While paused, the original index and the new index both occupy disk space and both must be updated by every DML statement. Ghost cleanup stays blocked on the in-build index, and DDL against the index or its table is not allowed. Microsoft says read-mostly workloads see negligible slowdown, and update-heavy workloads may lose some throughput. (high confidence) [source]
SQL Server database-scoped configurations ELEVATE_ONLINE and ELEVATE_RESUMABLE (values OFF, WHEN_SUPPORTED, FAIL_UNSUPPORTED) make index DDL online or resumable by default. They apply only to statements submitted without an explicit ONLINE or RESUMABLE option, so a statement with RESUMABLE = OFF overrides FAIL_UNSUPPORTED. They do not apply to XML index operations. (low confidence) [source]
The PostgreSQL 18 documentation describes no way to resume an interrupted CREATE INDEX CONCURRENTLY. If a problem arises during the table scan, such as a deadlock or a uniqueness violation, the command fails and leaves an INVALID index that queries ignore but updates still maintain. The documented recovery is to drop the index and run CREATE INDEX CONCURRENTLY again, or to rebuild it with REINDEX INDEX CONCURRENTLY. (high confidence) (verified as of 2026-10-20) [source]
TiDB's ADMIN PAUSE DDL JOBS pauses a running DDL job and ADMIN RESUME DDL JOBS resumes it. The TiDB page says machine restarts and cluster restarts do not pause DDL jobs, except that a cluster upgrade pauses existing and new DDL jobs automatically and they resume afterwards. The page says nothing about checkpoints or about ADD INDEX specifically. (low confidence) (verified as of 2026-10-20) [source]
Open MongoDB bug SERVER-130777: a hybrid build resumed after a clean shutdown can lose multikey state from side writes. The saved resume state captures multikey information only from the bulk builder, so an array value inserted while the build was paused is not reflected. The committed index is not flagged multikey, a query such as {a: {$gte: 4, $lte: 3}} hinted on {a: 1} returns nothing, and validate reports the collection invalid. The ticket lists affected versions 6.0.0, 7.0.0 and 8.0.0, status In Code Review and unresolved (last updated 2026-10-12), and names rebuilding the index as the only remediation. (low confidence) (verified as of 2026-10-20) [source]
After a MongoDB build resumes on 6.0, 7.0 or 8.0, run validate on collections that received array values while the build was interrupted. SERVER-130777 can leave the committed index without its multikey flag, and the ticket says validate reports the collection invalid in that case. (low confidence) (verified as of 2026-10-20) [source]
The MongoDB manual documents no pause or resume command for index builds. Resumption happens automatically at startup or after a rollback. To end a build, use dropIndexes (dropIndex() or dropIndexes()). The manual says not to use killOp on index builds in replica sets or sharded clusters. (low confidence) [source]

Children

Commit quorum and votingMembers (frontier)
External sorter spill files in dbpath/_tmp (frontier)
MongoDB commit quorum and two-phase index builds (frontier)
MongoDB primary-driven index builds (PDIB) (frontier)
MongoDB rolling index builds (frontier)
PostgreSQL invalid indexes from failed CONCURRENTLY builds (frontier)
PostgreSQL invalid-index cleanup and REINDEX CONCURRENTLY (frontier)
Primary-driven index builds (frontier)
Rolling index builds
SQL Server resumable index operations: ELEVATE_RESUMABLE and resumable constraints (frontier)
SQL Server resumable online index operations (frontier)
Side-writes table and hybrid index builds (frontier)
Two-phase (simultaneous) index builds (frontier)

Frontier under this node: Commit quorum and votingMembers, External sorter spill files in dbpath/_tmp, MongoDB commit quorum and two-phase index builds, MongoDB primary-driven index builds (PDIB), MongoDB rolling index builds, PostgreSQL invalid indexes from failed CONCURRENTLY builds, PostgreSQL invalid-index cleanup and REINDEX CONCURRENTLY, Primary-driven index builds, SQL Server resumable index operations: ELEVATE_RESUMABLE and resumable constraints, SQL Server resumable online index operations, Side-writes table and hybrid index builds, Two-phase (simultaneous) index builds

← the whole tree · 3D view· how to read this page