measure random primary-key update performance

[AntiO2]

A few days ago, Lance 9.0 was released, and I noticed the new MemWAL-related features. I was interested in understanding how much MemWAL can improve the performance of random primary-key updates, but I could not find benchmark results that directly cover this workload.

The existing MemWAL benchmarks appear to focus mainly on write throughput, vector/FTS index maintenance, and primary-key point lookups. For example, the current KV point-lookup benchmark measures unique-key ingestion and lookup performance, but it does not measure repeated updates to existing primary keys or the complete read-modify-write path required by partial updates.

If this would be useful, I would be willing to contribute a microbenchmark for this workload.

Motivation

There are at least two different primary-key update paths that have very different costs:

1. Full after-image upsert

   The caller already provides the complete new row. MemWAL can append the new version directly to the active MemTable and WAL without reading the previous version.

2. Partial update / read-modify-write

   The caller provides only the primary key and changed columns, for example:

   UPDATE table
   SET counter = counter + 1
   WHERE id = ?

   This requires looking up the newest version of the row, merging the updated columns with the existing row, and writing a complete new version.

The second path may have very different performance depending on whether the previous version is located in:

• the Base Table;
• an on-disk MemWAL SSTable;
• the Active MemTable.

For a key that exists only in the Base Table, the update may require a scalar-index lookup and a data-file read from object storage. Once the same key has been updated and its latest version is in the Active MemTable, subsequent updates may be served almost entirely from memory.

I think measuring this cold-to-hot transition would help clarify what kind of update workloads MemWAL accelerates.

Proposed benchmark

I propose adding a standalone Rust benchmark such as:

rust/lance/benches/mem_wal/update/mem_wal_partial_update_bench.rs

The benchmark would use a simple schema:

id: Int64 primary key
counter: Int64
revision: Int64
payload: Utf8

The partial-update operation would be equivalent to:

counter = counter + 1
revision = revision + 1

while preserving the payload.

The benchmark would compare three modes:

lookup-only

Measure the cost of retrieving the latest row with the tombstone-preserving point-lookup API.

full-upsert

Provide a complete after-image and write it directly with ShardWriter::put_no_wait, without reading the old row.

partial-update

Execute:

point lookup
→ merge changed columns with the old row
→ build a complete after-image
→ put_no_wait
→ await the returned watcher

Storage-tier cases

The benchmark would create disjoint primary-key ranges whose latest versions are located in:

• Base Table;
• MemWAL SSTable;
• Active MemTable;
• a mixed workload containing keys from all three tiers.

It could also support repeated rounds over the same key set:

round 0: latest version is in Base or SSTable
round 1+: latest version is in Active MemTable

This would make the Base/SSTable-to-Active transition directly measurable.

Metrics

I would like to separate the end-to-end update latency into:

lookup latency
merge / RecordBatch construction latency
put_no_wait latency
watcher completion latency
end-to-end latency

The benchmark would report throughput and P50/P95/P99 latency for different batch sizes.

It would also record whether watcher completion means:

• index-visible, when durable writes are disabled;
• index-visible and WAL-durable, when durable writes are enabled.

Initial variables

The initial version could cover:

batch size:        1, 10, 100, 1000
source tier:       base, sstable, active, mixed
payload size:      128 B, 1 KiB
durable write:     true, false
in-flight batches: 1, 8, 32
Base PK index:     BTree, no index

The Base BTree/no-index comparison would help isolate the cost of scalar-index lookup and row materialization for Base-resident updates.

Potential optimization identified by the benchmark

The current tombstone-preserving batch lookup path appears to resolve keys one by one:

lookup_many_keep_tombstone
→ repeated lookup_keep_tombstone calls
→ one execution plan per key

The benchmark may show that this becomes the dominant cost as update batch size increases.

However, I would keep any optimization out of the initial PR. The first contribution would only add the benchmark and correctness checks. A batched tombstone-preserving BTree lookup could be proposed separately if the results show that it is worthwhile.

Non-goals for the initial benchmark

To keep the contribution reviewable, I would not include the following in the first version:

• standard Dataset.update or merge_insert comparisons;
• multiple shards;
• RocksDB comparisons;
• compaction steady-state measurements;
• production-code changes;
• S3 request-count instrumentation.

The first version would focus only on the Lance Core read-merge-put path across the different MemWAL tiers.

Would this benchmark be useful to the project, and would the proposed location and scope be appropriate? I would be happy to implement it if the maintainers agree with the direction.

Related to #3985.

The proposed benchmark would build on the partial-update primitives introduced in
#7362 and #7482.