When loading tens of millions of rows into an existing table, teams often drop the table's indexes first and rebuild them after the load. Why is that faster, and when would it be the wrong choice?

## Two ways to get an index built **Incremental maintenance** — the index exists, and each inserted row adds an entry to it. Per row, per index: compute the key, descend the tree, read the target leaf page (possibly a physical read), insert in sorted position, mark dirty, log it, and split the page if it is full. The target page is determined by the key, so for anything but a monotonically increasing key, successive rows hit unrelated pages. Ten million rows across four indexes is forty million such operations, scattered across the whole address space of each index. **Bulk build** — the index does not exist during the load; afterwards the engine reads the table once (sequentially), extracts the keys, sorts them using a large work area and sequential spill files, then writes the leaf level in order and constructs the upper levels on top. Sorting is the dominant cost and it is I/O-efficient. Nothing splits, because pages are filled to the intended fill factor as they are created, and the tree is built once with the right shape. The difference is the same as the difference between inserting into a sorted array one element at a time and sorting the array once at the end. For large volumes the bulk path routinely wins by a large factor, and it also produces a *better* index: densely packed, physically ordered, with no fragmentation from mid-page splits. ## The full cost accounting Dropping and rebuilding also avoids: - Log volume for millions of individual index modifications. - Buffer-cache thrash as index pages for four different indexes compete during the load. - The garbage left behind if the load includes updates or upserts. But it introduces its own costs: - The rebuild itself is a large, resource-hungry operation needing temporary space for sorting and significant memory. - The table is unindexed for the duration of load plus rebuild. - Rebuilding is all-or-nothing work over the *entire* table, not just the newly loaded rows. ## When the tradeoff flips **Ratio of new rows to existing rows.** This is the decisive number. Loading 40M rows into an empty or small table: drop and rebuild, easily. Loading 40M into a table already holding 3B: the rebuild processes 3.04B rows to save maintenance on 40M — a clear loss. A rough heuristic: the strategy pays when the new data is a substantial fraction of the total, and stops paying when it is a small percentage. **Availability requirements.** If the table serves live traffic, dropping indexes is usually off the table — queries collapse to scans and the system may not survive it. Engines that support building an index without blocking writes change this calculus: the build is slower and takes more resources, but the table stays usable. **Constraints.** A unique index is not just an accelerator; it is the enforcement mechanism for uniqueness. Dropping it means duplicates can enter during the load and the rebuild will then fail at the end — after hours of work — leaving you to find and remove the duplicates. Foreign keys referencing the table have similar dependencies. The safe pattern is: keep constraint-bearing indexes, drop only the pure-performance secondary ones. **Failure modes.** A load that dies halfway leaves a partially populated, completely unindexed table. Whether that is acceptable depends on whether the target is a live table or a staging one. Loading into a fresh staging table and then swapping it in (via a rename or a partition attach) sidesteps almost every one of these problems and is the pattern of choice when the schema allows it. ## Related levers worth mentioning - **Sort the input by the leading index key** before loading. If rows arrive in index order, even incremental maintenance becomes near-sequential: entries append to the same hot leaf page, splits are clean right-edge splits, and cache behaviour is excellent. This can capture much of the benefit without dropping anything. - **Batch commits** rather than one transaction per row, to amortise log flushes. - **Load into a new partition** and attach it, so the live table's existing indexes are never touched and only the new partition's indexes are built. - **Refresh statistics after the load**, always — a freshly loaded table with month-old statistics will produce bad plans regardless of how well the indexes were built. ## The interview-grade answer Explain the random-insert versus sort-and-build mechanism, then immediately qualify it with the ratio question and the availability/constraint constraints. A candidate who says "always drop indexes before a big load" has learned a rule without its boundary conditions; a candidate who asks "how big is the load relative to the table, and is the table serving traffic?" is thinking correctly.