Copy-on-write or merge-on-read: choosing how an Iceberg table pays for a delete

• The question this setting answers
• The same delete, measured twice
• What a delete file actually is
• The modes are per operation
• Which one does your workload want?
• What neither mode does
• How to tell which mode a table is using
• Common misconceptions
• A model worth keeping
• References
• Trademarks

TL;DR

• The same DELETE on two tables differing only in write.delete.mode left 2 data files holding 3,900 records under copy-on-write, and 2 data files still holding 4,000 records plus a 100-record delete file under merge-on-read. Both queries return 3,900 rows.
• The snapshot’s operation names the choice: overwrite for copy-on-write, delete for merge-on-read.
• The setting is per operation, not per table: write.delete.mode, write.update.mode and write.merge.mode are independent, so a table can rewrite on DELETE and defer on MERGE.
• Merge-on-read moves cost from write time to every read until compaction runs. A CDC table that never compacts gets slower every hour.
• Neither mode reclaims space on its own. The files the old snapshot still references stay until expire_snapshots.

The question this setting answers

Deleting one row from a table stored as immutable Parquet files is not obvious. The file holding that row cannot be edited in place, so something has to give.

Iceberg offers two answers, and you choose per table.

Copy-on-write rewrites the whole data file without the deleted row, and the new snapshot points at the replacement. The cost is paid now, once, by the writer.

Merge-on-read leaves the data file alone and writes a small delete file recording which rows no longer count. The cost is paid later, repeatedly, by every reader that has to apply it.

That is the entire trade, and it is worth seeing in the files rather than taking on faith.

The same delete, measured twice

Two tables, identical except for their mode, each loaded with 4,000 rows in 2 data files:

CREATE TABLE local.db.cow (id BIGINT, v STRING) USING iceberg
TBLPROPERTIES ('write.delete.mode'='copy-on-write');

CREATE TABLE local.db.mor (id BIGINT, v STRING) USING iceberg
TBLPROPERTIES ('write.delete.mode'='merge-on-read');

Then the identical statement against each:

DELETE FROM local.db.<table> WHERE id < 100;

Reading the accounting from the files metadata table, where content is 0 for data and 1 for position deletes:

That table is the whole post. Under copy-on-write the data files were rewritten: still two files, now containing 3,900 records, the 100 deleted rows physically gone from the current snapshot. Under merge-on-read the data files were never touched — they still hold all 4,000 records — and a separate file records the 100 positions to skip. Both queries return 3,900 rows, because the reader merges the delete file on the way past.

The snapshot log names what happened, without you having to count files:

copy-on-write   snapshot ops: ['append', 'overwrite']
merge-on-read   snapshot ops: ['append', 'delete']

overwrite means files were replaced. delete means a delete file was added. If you ever need to know which mode a past operation used, that column is the record.

What a delete file actually is

The merge-on-read run produced one extra file next to the data:

00000-45-0ec67ff2-63f5-4f54-83a2-58f5ac1bdb24-00001-deletes.parquet

It is a Parquet file like any other, which is a detail worth internalising: counting *.parquet in a table directory does not tell you how many data files there are. My first attempt at this measurement did exactly that and reported merge-on-read as having gained a data file. It had not; it had gained a delete file. The files metadata table and its content column are the honest source.

There are two kinds, and the difference matters operationally:

A position delete is cheap to apply, because the reader knows exactly which rows in which file to skip. An equality delete is a predicate, so the reader must evaluate it against rows it reads, and it applies to every data file in scope rather than one named file. Equality deletes let a streaming writer delete without first finding out where the row lives, which is why Flink CDC pipelines produce them, and they are the more expensive of the two to read.

flowchart LR
    subgraph COW["copy-on-write"]
      D1["data file<br/>4000 rows"] -->|"DELETE rewrites it"| D2["new data file<br/>3900 rows"]
    end
    subgraph MOR["merge-on-read"]
      E1["data file<br/>4000 rows, untouched"] --> R["reader merges"]
      E2["delete file<br/>100 positions"] --> R
      R --> O["3900 rows"]
    end

The modes are per operation

This is the part most summaries flatten. There is no single table-level mode; there are three independent properties:

A freshly created table reports none of them:

current-snapshot-id = none
format = iceberg/parquet
format-version = 2
write.parquet.compression-codec = zstd

Their absence means the format default applies rather than that no mode exists. Set them explicitly on any table where the choice matters, because a default you did not choose is a default you will not remember when the table starts behaving unexpectedly.

The independence is useful. A dimension table that receives occasional small corrections and is read constantly might use copy-on-write for DELETE and UPDATE, while a MERGE INTO that lands a large nightly changelog uses merge-on-read to avoid rewriting half the table in one statement.

Note also format-version = 2 in that output: delete files are a format v2 feature, so merge-on-read requires v2 or later. On a v1 table the modes do not apply, because there is no way to express a delete except by rewriting.

Which one does your workload want?

The decision follows from the ratio of writes to reads, and from what runs between them.

That last row is the one that bites. Merge-on-read is not a way to avoid the cost of a delete; it is a way to defer it. Every delete file that has not been compacted is applied by every subsequent read of the files it covers. A CDC pipeline writing delete files every minute and compacting never will get measurably slower every hour, and the symptom — queries degrading with no change in data volume — looks nothing like its cause.

If you choose merge-on-read, you are also choosing to run rewrite_data_files and rewrite_position_delete_files on a schedule. The mechanics of both, and the separate step needed to actually reclaim storage, are in table maintenance for Iceberg and Hudi.

What neither mode does

Neither reclaims space, and this catches people who expect copy-on-write to be self-cleaning.

Under copy-on-write the rewritten file replaces the old one in the current snapshot. The old file is still referenced by the previous snapshot, which time travel still needs, so it stays on disk until expire_snapshots removes the snapshot that references it. A table that deletes rows daily under copy-on-write and never expires snapshots grows on disk while shrinking logically.

Under merge-on-read the original data file stays by definition, and the delete file joins it. Compaction merges them into a clean data file, and then that leaves the pre-compaction files behind until expiry.

The rule that covers both: compaction changes what queries read; expiry changes what storage costs. They are different jobs and you need both.

How to tell which mode a table is using

Three ways, in increasing order of certainty.

The table properties, which tell you the configured intent:

SHOW TBLPROPERTIES local.db.orders;

The snapshot log, which tells you what actually happened:

SELECT snapshot_id, operation FROM local.db.orders.snapshots ORDER BY committed_at;

overwrite for a rewrite, delete for a delete file. This is the one to trust, because it reflects the mode in force at the time of each operation rather than the mode configured now.

The file accounting, which tells you what you are carrying:

SELECT content, count(*) files, sum(record_count) records
FROM local.db.orders.files GROUP BY content;

content = 1 rows are position delete files. A growing count there, on a table whose queries are slowing down, is the signal that compaction is not keeping up.

Common misconceptions

“Merge-on-read is faster.” It is faster to write. Reads pay for it until compaction runs, and the total cost can exceed copy-on-write if the data is read far more often than written.

“Copy-on-write rewrites the whole table.” It rewrites the data files that contain affected rows. Deleting one row from a well-partitioned table rewrites one file, not the table.

“A delete file means the rows are gone.” The rows are still in the data file. They are filtered at read time, and they remain readable through time travel to an earlier snapshot.

“The mode is a table-level setting.” It is three settings, one per operation, and they can disagree.

“Copy-on-write frees the space immediately.” The replaced file lives on until the snapshot referencing it is expired.

A model worth keeping

Copy-on-write pays the delete once, at write time, and leaves a clean table. Merge-on-read pays nothing now and a little on every read, until a compaction job settles the account.

So the question is not which is faster. It is who should pay, and have you scheduled the job that pays off the debt. Choose merge-on-read only with compaction already scheduled; otherwise copy-on-write is the honest default.

References

• Iceberg table properties for write.delete.mode, write.update.mode and write.merge.mode
• Iceberg spec: delete files for position and equality delete semantics
• Iceberg maintenance procedures for rewrite_data_files and rewrite_position_delete_files
• Apache Iceberg architecture for the metadata tree these files live in
• Table maintenance for Iceberg and Hudi for the compaction and expiry jobs this post assumes

Trademarks

Apache Iceberg, Apache Spark, Apache Flink, Apache Parquet, Apache and the Apache feather logo are either registered trademarks or trademarks of The Apache Software Foundation in the United States and other countries.