The performance problem with Iceberg

Apache Iceberg is the standard. Snowflake, Databricks, AWS, and every major query engine read and write it natively. The format question is settled. The performance question is not.

Most production Iceberg tables are slower than they need to be — not because of anything wrong with the format, but because the physical state of the table has degraded over time. Thousands of small files from streaming writes. Manifests fragmented across hundreds of snapshots. Data scattered randomly across files with no correlation to how it's actually queried. Delete files piling up from CDC pipelines.

The result: engines scan 5–10x more data than necessary. Query planning takes seconds instead of milliseconds. S3 API costs spike. And every engine — Trino, Spark, Snowflake, Athena, DuckDB — is equally affected because they all read the same physical data layout.

This guide covers what determines Iceberg query performance, how to fix it, and the two paths available: intelligent continuous optimization through a control plane, and the manual approach with SQL and cron. Both paths target the same physical levers — the difference is whether they adapt over time or require ongoing human attention.

How Iceberg scan planning works

When a query engine receives a SELECT against an Iceberg table, it runs a scan planning process on a single node that determines which files to read. This is one of Iceberg's core advantages over Hive-style directory listing — no distributed scan needed just to find your data.

Planning operates in three progressive levels of elimination:

Level 1: Manifest list pruning. The engine reads the snapshot's manifest list and checks partition value ranges in each manifest's summary. If a manifest's date range doesn't overlap with the query's filter, it's skipped entirely — along with all data files it references. One check eliminates hundreds of manifest files.

Level 2: File-level data skipping. For surviving manifests, the engine reads per-file column statistics (min/max bounds, null counts, row counts) and evaluates predicates against them. A query for amount > 500 skips every file where the amount column's maximum is below 500. This is where sort order determines everything — tight, non-overlapping min/max ranges mean more files eliminated.

Level 3: Row group pruning. Within surviving files, Parquet row group statistics and bloom filters skip sub-file data blocks. If only one of four row groups matches, 75% of the file's I/O is avoided.

The effectiveness of each level depends entirely on the physical state of the table. A detailed walkthrough covers these internals in depth.

Why tables degrade

Iceberg tables don't start slow. They become slow because the physical layout drifts away from what's optimal for the queries hitting them.

These problems don't happen once and get fixed. They're continuous. They happen every day, on every table, as long as data is being written and queried.

Two paths to query performance

There are fundamentally two approaches to keeping Iceberg tables performant:

Path 1: Intelligent continuous optimization. A system that observes query patterns, understands table state, and takes the right action at the right time — autonomously, adaptively, and in the correct sequence. This is the control plane approach.

Path 2: The manual path. SQL procedures, cron jobs, and configuration tuning. You run the same operations — compaction, manifest rewrites, snapshot expiration — but you decide when, how, and on which tables. You choose the sort order. You monitor for degradation. You sequence the operations correctly.

Both paths target the same physical levers described above. The difference is not what gets done — it's whether it adapts continuously or requires ongoing engineering attention.

Path 1: Intelligent continuous optimization

LakeOps is an autonomous control plane for Apache Iceberg. It connects to your existing catalogs and query engines, observes table state and query patterns, and continuously applies the optimizations that make queries faster — without human intervention, without Spark clusters, and without static configurations that go stale.

Query-aware data layout

This is the highest-impact capability. Sort order is the single most impactful lever for Iceberg query performance — sorted tables scan 51% less data per query than unsorted ones. But choosing the right sort order requires knowing which columns production queries actually filter on. That knowledge changes over time.

LakeOps captures query telemetry from every connected engine — Spark, Trino, Flink, Snowflake, Athena, DuckDB, StarRocks — and identifies which columns appear in WHERE, JOIN, and GROUP BY clauses for each table. During compaction, data is physically re-sorted by those columns.

The result: Parquet row-group min/max statistics become maximally tight. The scan planner eliminates the vast majority of files at Level 2 because each file covers a narrow, non-overlapping value range for the columns queries actually use.

The sort strategy adapts. If a new dashboard starts filtering on customer_segment alongside event_date, the next compaction pass incorporates it. No manual ALTER TABLE SET SORT ORDER needed. On three consecutive runs of a 1.2 TB table, the system improved runtime from 22 min → 18 min → 11 min as it converged on optimal column ordering — zero configuration changes.

Production numbers: a table with 47,000 scattered files, after query-aware sort compaction to 280 files, saw query time drop from 52 seconds to 5.8 seconds. A 9x improvement from layout optimization alone.

Compaction at streaming speed

Sort compaction is the most impactful maintenance operation — it solves both the small-file problem and the sort-order problem in one pass. But on Spark, it's slow and expensive. A 200 GB sort compaction takes ~25 minutes and costs ~$3.50 on EMR. At that speed, running it multiple times per day on streaming tables is impractical. Most teams default to binpack (size-only) because sort is too expensive — leaving the biggest performance lever unused.

LakeOps's compaction engine is built in Rust on Apache DataFusion. No JVM. No garbage collection. No OOM crashes. The speed difference makes sort compaction viable even for streaming tables:

Because it's fast enough to run frequently, tables stay both consolidated and sorted continuously — never degrading between maintenance windows.

Coordinated maintenance pipeline

Individual maintenance operations interact. The order matters:

  1. Expire snapshots — release references to old data files
  2. Remove orphans — delete unreferenced files from storage
  3. Compact — merge remaining files with optimal sort order
  4. Rewrite manifests — consolidate metadata over the new, clean file set

Running these in the wrong order wastes work. Compacting before expiring snapshots means you might rewrite files that should be deleted. Rewriting manifests before compaction means the structure changes again during the compaction commit.

LakeOps sequences operations automatically. Each step produces a clean input for the next. The manifest rewrite runs on an already-consolidated, properly-sorted file set — producing the leanest possible metadata layer. This coordination eliminates the redundant work that independent cron jobs inevitably create.

Event-driven triggers

A streaming table that accumulates 500 files per hour needs compaction multiple times per day. A stable batch table with weekly loads needs compaction monthly. A CDC table with accumulating delete files needs cleanup when the read-time overhead crosses a threshold.

LakeOps triggers operations based on actual table state — file count thresholds, delete-file ratios, manifest fragmentation, partition skew — not arbitrary cron schedules. Tables get exactly the maintenance they need, when they need it. No wasted runs on healthy tables, no missed runs on degrading ones.

Multi-engine query routing and workload optimization

Different engines have different performance profiles: a point lookup takes 0.5s on DuckDB vs 2.3s on Athena. A full-table scan costs $5 on Athena vs $50 on Trino. Without routing, every query goes to the same engine regardless of its shape.

LakeOps includes QueryFlux — an open-source, Rust-based SQL proxy with 0.35ms overhead — for multi-engine routing. Queries route based on shape, latency targets, cost ceilings, engine availability, and table health status. The routing layer learns from execution history: if a query shape consistently runs faster on one engine, future executions route there.

In benchmarking, workload-aware routing reduced total query cost by up to 80%, with individual queries sometimes dropping by 90%.

Observability and self-improvement

You can't optimize what you can't see. LakeOps classifies every table into health tiers (Critical, Warning, Healthy) based on file fragmentation, manifest depth, snapshot velocity, delete ratios, and sort-order staleness. Insights surface at four severity levels — before degradation becomes noticeable in query times.

The system records per-table throughput, partition structure, and memory usage from each compaction run. Subsequent passes execute faster as the planner converges on optimal resource allocation. Every operation feeds back into the next decision.

What setup looks like

Connect your catalogs (AWS Glue, REST catalogs like Polaris/Nessie/Lakekeeper, S3 Tables) and storage. ~10 minutes. No agents to deploy, no data movement, no pipeline changes. Your data stays in your account. The system discovers tables, classifies health, begins autonomous maintenance according to policies you define.

Production results across customers: up to 12x average query acceleration, up to 80% total cost reduction, 786+ tables managed autonomously across 112+ PB.

Path 2: The manual approach

Every optimization LakeOps automates is also achievable with SQL procedures, engine configuration, and scheduling. Here's how to implement each one by hand.

Diagnosing the bottleneck

Before optimizing, use Iceberg's metadata tables to identify what's actually wrong:

-- File health: count, size distribution, small file ratio
SELECT
  COUNT(*) as file_count,
  AVG(file_size_in_bytes) / 1048576 as avg_mb,
  COUNT(CASE WHEN file_size_in_bytes < 67108864 THEN 1 END) as small_files
FROM prod.db.events.files;

-- Manifest health: fragmentation
SELECT COUNT(*) as manifest_count,
  AVG(added_data_files) as avg_files_per_manifest
FROM prod.db.events.manifests;

If file_count is in the tens of thousands or small_files is high, you need compaction. If manifest_count is high relative to files, you need a manifest rewrite.

Running compaction

In Spark, you can run:

CALL iceberg.system.rewrite_data_files(
  table => 'prod.db.events',
  strategy => 'sort',
  sort_order => 'event_date, customer_id'
);

This merges small files and sorts by the specified columns. But it's expensive: a 200 GB sort compaction takes ~25 minutes and costs ~$3.50 on EMR. For streaming tables, you'll need to run it frequently — but that cost may be prohibitive.

Expiring snapshots

CALL iceberg.system.expire_snapshots(
  table => 'prod.db.events',
  older_than => TIMESTAMP '2024-01-01 00:00:00'
);

This removes old metadata and data files no longer referenced. Run it before compaction to avoid rewriting files that will be deleted anyway.

Rewriting manifests

CALL iceberg.system.rewrite_manifests('prod.db.events');

This consolidates manifest files, reducing planning overhead. Run it after compaction to get the leanest metadata.

Scheduling

Use cron or Airflow to run these in sequence: expire → orphan cleanup → compact → rewrite manifests. But you must monitor table state to adjust frequency. Without feedback, you'll either waste resources or let tables degrade.

Why you should care

If you run Iceberg tables in production, the physical state directly impacts your query performance and costs. Whether you choose autonomous optimization or manual maintenance, you need to act. Ignoring it means your tables will keep slowing down.

Next steps

  1. Run the diagnostic queries above on your most critical tables.
  2. If you see fragmentation, schedule a compaction job.
  3. Evaluate whether you need a continuous solution like LakeOps or if manual cron is sufficient for your scale.