merge very slow compared to delete + append on larger dataset

[ibabeo]

Environment

Delta-rs version: 0.13

Binding: python

Environment:

• OS: windows

---

Bug

What happened:
I have a dataset of shape (3066766, 18) and a part of it needs to be updated by a dataset of shape (185004, 18)
The natural way of performing the operation would be using merge().when_matched_update_all() which is super slow ( terminated after waiting for 20mins)
An alternative way I tested is to first dt.delete() then write_deltalake(mode='append'), which is very fast ( < 1s).

What you expected to happen:
Expect the performance of merge().xx() to be faster than delete then append

How to reproduce it:

import pandas as pd
import pyarrow as pa
from deltalake import DeltaTable
from deltalake.writer import write_deltalake
import tempfile

temppath = tempfile.TemporaryDirectory().name
# NYC taxi data sample
df = pd.read_parquet('https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2023-01.parquet')
# make column names lower case due to bug in uppercase #1797 
df.columns = map(str.lower, df.columns)
# timezone timestamp issue #1777 
df = df.drop(columns=['tpep_pickup_datetime', 'tpep_dropoff_datetime'])

# write to dt
df_temp = df.assign(calculationdate='2023-09-05')
write_deltalake(temppath, df_temp, mode='append', partition_by=["calculationdate"])

# load delta table
dt = DeltaTable(temppath)

# source table to be updated 
df_update = df_temp[df_temp.pulocationid.isin([161,  43])]


# method 1 - super slow
dt.merge(
    source=pa.Table.from_pandas(df_update),
    predicate="t.calculationdate = '2023-09-05' and t.calculationdate = s.calculationdate  and s.pulocationid = t.pulocationid",
    source_alias="s",
    target_alias="t",
).when_matched_update_all().execute()

# method 2 - alternative - fast
dt.delete(predicate = "calculationdate = '2023-09-05' and pulocationid in (161,43)")
write_deltalake(temppath, df_update.reset_index(drop=True), mode='append')

#1797 #1777

[ion-elgreco]

This may be due to the fact a physical plan is built versus constructing a logical plan. @Blajda has a PR for this feature open.

[Blajda]

Yeah further enhancements will need to be done after the conversion to logical plans. Currently the operation rewrites the entire table but this can be optimized when upserts are performed.
Also if the number of records being updated is small then I imagine the cost of the join will become a major factor.

[ldacey]

How would this ideally work in practice?

Currently, I process data from some window of time (previous hour, previous day, etc) and save it into a staging dataset.

Then I read the unique partitions which were just changed in the previous step into Polars, sort, and drop duplicates. Then I overwrite the partitions (normally daily or monthly) into my final dataset. The result is that I have the latest version of each unique row based on a unique constraint.

Using merge, I would instead just use when_matched_update_all and insert. Does this trigger the entire table to be overwritten? Some of my tables are hundreds of millions of rows and the data I am upserting would only exist in a single or few partitions.

[Blajda]

Using merge, I would instead just use when_matched_update_all and insert. Does this trigger the entire table to be overwritten? Some of my tables are hundreds of millions of rows and the data I am upserting would only exist in a single or few partitions.

Yes the current implementation does this and it is document on the rust side. We should add a warning for python users that current state it should not be used with large tables.

Ideally in the future merge will be able to perform partition pruning and additional pruning based on source statistics to ensure only that on files with modified/deleted records are removed.
Once those enhancements are implemented there should be no issue with using merge for your workflow.

[leodiegues]

Does #1720 solve this issue?

[Blajda]

No it doesn't. Additional work is still required to support efficient upserts.