UpdateBuilder vs MergeInsertBuilder in Lance: Two Update Strategies for Massive AI Datasets

Conclusion

Use UpdateBuilder for a small number of rows, clear conditions, and expression‑based modifications; use MergeInsertBuilder for bulk key‑based replacement of vectors or features. For large‑scale vector updates, MergeInsertBuilder is the preferred choice.

Background – Why Lance Needs a Dedicated Update Mechanism

Lance is a lakehouse format for multimodal AI/ML workflows, supporting high‑performance vector search, full‑text search, random access, feature engineering, and automatic versioning. In these workloads, an update is not a simple SQL row update because:

Embeddings are stored as FixedSizeList<Float32>, making a column very wide.

Tables can contain tens of millions to billions of rows.

Data resides in object storage and cannot be overwritten in‑place.

After an update, row id, fragment, bitmap index, version commit, and concurrency conflicts must be handled.

Both vector and scalar indexes may be affected.

Lance therefore provides two distinct update tools: UpdateBuilder: behaves like UPDATE … SET … WHERE …. MergeInsertBuilder: behaves like MERGE INTO ….

What Is UpdateBuilder ?

UpdateBuilder

is the Rust‑side builder that constructs an update operation, mirroring SQL UPDATE and allowing expression‑based modification of columns.

Example:

use std::sync::Arc;
use lance::{Dataset, Result};
use lance::dataset::UpdateBuilder;

async fn update_region(dataset: Arc<Dataset>) -> Result<()> {
    let result = UpdateBuilder::new(dataset)
        .update_where("region_id = 10")?
        .set("region_name", "'New York'")?
        .build()?
        .execute()
        .await?;
    println!("rows updated = {}", result.rows_updated);
    Ok(())
}

Corresponding SQL:

UPDATE dataset
SET region_name = 'New York'
WHERE region_id = 10;

Suitable for the "known condition + modification expression" scenario.

Problems Solved by UpdateBuilder

Changing status = 'pending' to status = 'done'.

Renaming a region.

Computing a new value via an expression.

Applying a uniform change to rows matched by a SQL filter.

Advantages:

Simple API.

No need to construct a source table.

Ideal for small‑scale conditional modifications.

Not suitable for massive updates such as millions of rows or billions of embeddings; in those cases MergeInsertBuilder is recommended.

How UpdateBuilder Works

Step 1: Build a Scanner

Dataset → Scanner → Filter Pushdown

If .update_where(...) is present, the filter is pushed down.

Step 2: Scan Matching Rows

RowId → RowAddress → Old Values

Step 3: Apply SET Expressions

.set("score", "score + 1")

The builder creates a DataFusion physical expression, evaluates it, and produces a new batch.

Old Batch → Expression Evaluation → New Batch

Step 4: Write a New Fragment

Lance does not modify in place; it marks the old rows as deleted and writes the new rows to a new fragment.

Old Row → Mark Deleted
New Row → Write New Fragment

Step 5: Commit the Transaction

The operation is recorded as Operation::Update and a new version is generated.

Using UpdateBuilder

Conditional Update

let result = UpdateBuilder::new(dataset.clone())
    .update_where("id = 42")?
    .set("status", "'done'")?
    .set("updated_at", "now()")?
    .build()?
    .execute()
    .await?;
println!("updated rows: {}", result.rows_updated);

Updating an FSL Vector Column

Suitable for a few rows, temporary fixes, or debugging—not for millions of embeddings.

let result = UpdateBuilder::new(dataset.clone())
    .update_where("id = 10086")?
    .set("vector", "[0.1, 0.2, 0.3, 0.4]")
    .build()?
    .execute()
    .await?;

What Is MergeInsertBuilder ?

MergeInsertBuilder

is Lance’s bulk merge/upsert builder, analogous to the SQL MERGE INTO statement.

SQL form:

MERGE INTO target
USING source
ON target.id = source.id
WHEN MATCHED THEN UPDATE
WHEN NOT MATCHED THEN INSERT;

Its goal is to merge a batch of source data into the target dataset.

Problems Solved by MergeInsertBuilder

Scenario 1 – Bulk Embedding Update

When a set of vectors has been recomputed, the most sensible approach is a single merge rather than iterating row‑by‑row with individual updates.

Source Table → MergeInsert → Target Dataset

Scenario 2 – Upsert

Exists → Update
Not Exists → Insert

Scenario 3 – Find‑Or‑Create

Exists → Do Nothing
Not Exists → Insert

Scenario 4 – Replace a Month’s Data

Matched → Update
Not Matched → Insert
Target Only → Delete

How MergeInsertBuilder Works

Source‑Target Join

Source JOIN Target (on key "id")

Three Record Types

Matched : source and target both have the row.

Not Matched : source has the row, target does not.

Not Matched By Source : target has the row, source does not.

WhenMatched

Example: WhenMatched::UpdateAll translates to delete the old row and insert the new row.

WhenNotMatched

Example: WhenNotMatched::InsertAll inserts rows that are absent in the target.

WhenNotMatchedBySource

Example: DeleteIf(...) deletes target rows that have no matching source and satisfy a condition.

DataFusion Execution Plan

Source Stream → Inject Sentinel Column → Join → Generate Action → Physical Plan → Write Fragments

Sentinel Column

The source adds a column __merge_source_sentinel to correctly distinguish NULL = NULL cases after the join.

Scalar Index Acceleration

If a scalar index exists on the key (e.g., id), the merge can use the index to avoid a full table scan, yielding huge benefits when updating, for example, 1 million rows in a table of 1 billion rows.

Source Keys → Scalar Index Lookup → Target Rows

Using MergeInsertBuilder

Find‑Or‑Create

let (updated_dataset, stats) = MergeInsertBuilder::try_new(
    dataset.clone(),
    vec!["id".to_string()],
)?
    .try_build()?
    .execute(new_data_stream)
    .await?;
println!("inserted rows = {}", stats.num_inserted_rows);

Upsert

use lance::dataset::{MergeInsertBuilder, WhenMatched, WhenNotMatched};
let (updated_dataset, stats) = MergeInsertBuilder::try_new(
    dataset.clone(),
    vec!["id".to_string()],
)?
    .when_matched(WhenMatched::UpdateAll)
    .when_not_matched(WhenNotMatched::InsertAll)
    .try_build()?
    .execute(new_data_stream)
    .await?;

Bulk Embedding Update

MergeInsertBuilder::try_new(
    dataset.clone(),
    vec!["id".to_string()],
)?
    .when_matched(WhenMatched::UpdateAll)
    .when_not_matched(WhenNotMatched::DoNothing)
    .try_build()?
    .execute(new_vectors_stream)
    .await?;

This is the recommended approach for massive vector updates.

Comparison Between UpdateBuilder and MergeInsertBuilder

SQL Equivalent : UPDATE vs MERGE INTO Input : Filter + Expressions vs Source Data + Keys

Requires Source Table : No vs Yes

Supports Insert : No vs Yes

Supports Delete : No vs Yes

Supports Upsert : No vs Yes

Supports Replace Partition : No vs Yes

Small‑scale Conditional Modification : Best for UpdateBuilder, possible for MergeInsertBuilder Bulk Embedding Update : Not Recommended for UpdateBuilder, Recommended for MergeInsertBuilder Large‑scale Data Sync : Not Recommended for UpdateBuilder, Recommended for

MergeInsertBuilder

Which Builder for a Billion‑Row FSL Vector Column?

Schema:

id: UInt64
vector: FixedSizeList<Float32>[1536]

Need to update 10 million vectors.

Do Not Use Per‑Row Update Loop

for each id {
    UpdateBuilder::new(...)
}

Reasons: excessive commits, fragment explosion, growing deletion bitmap, high index‑maintenance cost.

Recommended Approach

Construct a source table with id, new_vector and run a single merge:

MergeInsertBuilder
    .when_matched(UpdateAll)
    .when_not_matched(DoNothing)
    .execute(...)

This performs one join, one transaction, and one commit—far superior to millions of individual updates.

What About Indexes After an Update?

Neither builder rebuilds indexes automatically. After the update you must call optimize_indices() to incorporate the new data into the index.

For IVF‑PQ indexes, the system reuses existing centroids and reassigns vectors; only when the retrain flag is true does it rebuild the entire index.

Summary

UpdateBuilder

Modifies existing rows based on conditions; corresponds to SQL UPDATE.

MergeInsertBuilder

Merges a batch of new data into the old table using keys; corresponds to SQL MERGE INTO.

For a scenario with a billion rows and ten million embedding updates, the recommended solution is:

MergeInsertBuilder + Scalar Index + Bulk Source Stream + optimize_indices()