When Bulk UPDATE Costs More Than Rebuilding

WHEN THE COST FLIPS

Cases where rebuilding begins to win

There is no universal threshold. Benchmark the real workload, but these patterns raise mutation cost.

60–100% rows

Large changed fraction

UPDATE loses selectivity while retaining per-row versioning and logging.

secondary indexes

Many indexes

Changed values require repeated index-entry maintenance.

PostgreSQL · InnoDB

MVCC versions

Old snapshots require prior row versions and later cleanup.

primary + replicas

Logging amplification

Every mutation must be recoverable and replicated.

parts · compression

Columnar mutation

Scattered row changes can become compressed-part rewrites.

partition · sort · encoding

Physical redesign

Rebuilding transforms and reorganises in one pass.

poor locality

Scattered writes

UPDATE may jump pages while CTAS reads and writes sequentially.

vacuum · purge · merge

Future read cost

Cost continues into scans, caches, backups and cleanup.

join · cast · normalize

One-off transformation

A set-oriented pipeline writes the intended destination directly.

PHYSICAL WORK

One SQL statement can imply very different physical work.

Compare bytes, logs, indexes, locks, cleanup and replica impact—not elapsed time alone.

Bulk UPDATE

Locate qualifying rows
Create/rewrite row versions
Write undo/redo or WAL
Maintain indexes
Replicate mutations
Vacuum, purge or merge later

changed_rows × (row_write + log + index_work + cleanup)

CTAS / Rebuild

Sequential source scan
Set-oriented transformation
Compact destination write
Bulk index build
Validate constraints and counts
Swap table or partition

all_rows × (scan + transform + sequential_write) + cutover

INTERACTIVE COST MODEL

Explore where approximate cost begins to flip

This builds intuition rather than replacing a benchmark.

Rows Rows changed Secondary indexes Average row width

—UPDATE proxy

—Rebuild proxy

—UPDATE / rebuild

—Confirm with a real benchmark

LIVE MARIADB LAB · MEASURE ONE BOUNDARY AT A TIME

Separate copy time from UPDATE, then compare the transformation work itself.

The source table is experiment setup. Its time is reported separately and excluded from the primary comparison.

SETUP · excluded

Build the indexed source and record its checksum before both paths begin.

PATH A · UPDATE ONLY

Copy source into a working table first, then time only the UPDATE statement.

PATH B · TRANSFORM → NEW

Time the source scan, CASE transformation and write into a new table.

RowsRows changedSecondary indexes

CONNECTING TO MARIADB…
Choose a bounded size and repeat runs; the first can include cold-cache and page-allocation effects.

PATH A · COPY PREPARED, THEN UPDATE

UPDATE copy_table
SET amount = amount * 2 + 7,
    segment = MOD(segment + 1, 12)
WHERE MOD(id, 100) < :changed_percent;

PATH B · TRANSFORM → NEW TABLE

CREATE TEMPORARY TABLE new_table AS
SELECT id,
       CASE WHEN changed THEN amount * 2 + 7 ELSE amount END amount,
       CASE WHEN changed THEN MOD(segment + 1, 12) ELSE segment END segment
FROM source_table;

PATH A · MUTATION

UPDATE existing copy

—UPDATE statement only

Prepare working copy: —
Build copy indexes: —
Whole path: —

PATH B · IMMUTABLE TRANSFORM

Transform → new table

—scan + transform + table write

Build new indexes: —
Whole path: —
Equivalent result: —

PRIMARY COMPARISON—

UPDATE only ÷ transform → new table. Copy and index times remain visible for transparency but are not mixed into this ratio.

SETUP—

Not yet run

How to read the result without overclaiming

1 · If UPDATE alone is slower
Per-row mutation work, including index maintenance and logging, exceeded scan-transform-write for this workload, even before copy time.

2 · If UPDATE remains faster
Increase changed fraction or indexes and rerun. The crossover depends on engine, cache, row width and storage.

3 · Do not trust one elapsed time
Repeat, inspect the median and observe bytes, logs and locks in production. This lab teaches measurement boundaries—not a universal winner.

ENGINE-AWARE SQL

Shared idea; different syntax and cutover.

Always inspect engine transaction, lock, replication and DDL semantics.

CREATE TABLE customer_new AS SELECT id, normalize_email(email) email FROM customer;
CREATE INDEX ON customer_new(id); ANALYZE customer_new;
-- validate, catch up concurrent changes, then plan transactional cutover

CREATE TABLE customer_new LIKE customer;
INSERT INTO customer_new SELECT id, LOWER(TRIM(email)) FROM customer;
RENAME TABLE customer TO customer_old, customer_new TO customer;

CREATE TABLE events_new AS events;
INSERT INTO events_new SELECT * REPLACE (transform(x) AS x) FROM events;
-- validate then exchange tables, or replace only affected partitions

CREATE TABLE facts_new AS SELECT * REPLACE (standardize(amount) AS amount) FROM facts;
-- validate and swap names in a controlled transaction

DECISION GUIDE

When to update and when to rebuild

Choose the method that preserves correctness and recovery—not merely apparent speed.

Situation	Likely choice	Reason
<5–10% rows	UPDATE	Selective work can beat a complete rewrite.
Most rows + many indexes	Benchmark rebuild	Logging and index amplification may dominate.
Partition/sort/encoding changes	Rebuild/partition replace	The physical layout itself changes.
Continuous writes	Online migration	CTAS alone misses post-snapshot changes.
Low free disk	Chunked UPDATE	Rebuild temporarily needs two copies plus indexes.
Strict rollback	Build–validate–swap	The old table remains a rollback target.

Most important

With concurrent writes, define snapshot boundaries, CDC catch-up, validation, cutover locking and rollback—or the fast table may lose data.

SAFE REBUILD

Speed matters only when cutover is safe.

Adapt this checklist to the engine and SLA.

01Snapshot boundary

02Build + transform

03Catch up changes

04Validate + cutover

05Observe + rollback