Iceberg Optimization: sorting and deletion vectors

Optimizing Iceberg Tables: Sorting, Statistics, and Deletion Vectors

Sorting data when writing to Iceberg tables allows execution engines to skip unnecessary files based on min/max statistics in manifest files. Without sorting, value ranges in files overlap, leading to reading all data even with strict filtering.

Example of creating a table and writing without sorting:

spark.sql("""
CREATE TABLE habr.paper.write_ordered (
  id INT,
  name STRING,
  event STRING
)
USING ICEBERG
""")

data = [(i, f"name_{i}", f"event_{i}") for i in range(1, 1000000)]
df = spark.createDataFrame(data, ["id", "name", "event"]).repartition(16)
df.writeTo("habr.paper.write_ordered").append()

Querying metadata shows chaotic ranges:

| id_min | id_max | file_path |

|--------|--------|-----------|

| 2 | 999994 | ... |

| 43 | 999988 | ... |

A filter with id < 100000 will read all files.

Enabling sorting:

spark.sql("""
ALTER TABLE habr.paper.write_ordered
WRITE ORDERED BY id
""")

Rewriting yields sequential ranges:

| id_min | id_max | file_path |

|--------|--------|-----------|

| 1 | 196479 | ... |

| 196480 | 395983 | ... |

Now, a filter with id < 196470 reads only one file.

Configuring Column Statistics Collection

Iceberg by default collects full min/max/truncated statistics for all columns, increasing write time without benefiting queries. In typical tables, filtering occurs on 1-2 columns, making statistics unnecessary for others.

Example of a flat table with 10 columns:

spark.sql("""
CREATE TABLE habr.paper.typical_flat_table (
  id INT,
  name STRING,
  col_1 STRING,
  col_2 STRING,
  col_3 STRING,
  col_4 STRING,
  col_5 STRING,
  col_6 STRING,
  col_7 STRING,
  col_8 STRING
)
USING ICEBERG
""")

Configuration via TBLPROPERTIES:

spark.sql("""
ALTER TABLE habr.paper.typical_flat_table SET TBLPROPERTIES (
    'write.metadata.metrics.default' = 'counts',
    'write.metadata.metrics.column.id' = 'full',
    'write.metadata.metrics.column.col_1' = 'none'
)
""")

• default: 'counts' — only row counts for all columns
• full: min/max + truncated for critical filters (id)
• none: disabled for unused columns

This reduces write overhead and metadata volume without losing data skipping on key fields.

Vectorized Deletion in MoR

In Merge-on-Read strategy, read performance depends on the deletion application mechanism. Classic delete files require joins with data.

Types of delete files:

• Equality delete: deletion rules (deprecated in v3)
• Position delete: row positions in files

With Iceberg v3, position delete evolved into Deletion vectors based on the Puffin format. A deletion vector is a bitmap mask applied in one cycle to a data file.

Example of position delete:

| file_path | pos |

|-----------|-----|

| ...parquet | 873 |

| ...parquet | 230 |

Instead of a join: load bitmap + logical AND operation. This speeds up reading MoR tables by 5-10x with a high deletion percentage.

Deletion vectors are especially effective for:

• Batch ETL with frequent updates
• Tables with slowly changing dimensions
• Streaming with MoR

Key Takeaways

• Sorting with WRITE ORDERED BY on filterable columns reduces readable files in analytics
• Configuring write.metadata.metrics.* minimizes write overhead while preserving data skipping
• Deletion vectors in Iceberg v3 replace position delete, accelerating MoR reading via bitmap
• Optimizations work without changing queries or ETL logic
• Effects accumulate: fewer files, less metadata, reduced object storage load

— Editorial Team