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

Understanding the performance machinery is essential for both approaches. 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.

Streaming writes create files at every checkpoint interval (every 1–5 minutes). A table receiving continuous events generates hundreds or thousands of files per day — each one tiny, each one adding a manifest entry that must be evaluated during planning.

Static sort orders lose relevance. A sort key chosen at table creation doesn't reflect what queries actually filter on six months later. As access patterns evolve, the sort becomes meaningless and min/max statistics become wide and useless for pruning.

Maintenance runs in isolation. Compaction, snapshot expiration, manifest rewriting, and orphan cleanup each affect each other's effectiveness. Running them independently — or in the wrong order — means wasted work and suboptimal results.

CDC pipelines accumulate delete files. Every update writes a small delete file. After weeks, hundreds of delete files must be reconciled at read time — each query pays the cost.

No feedback loop. Without observing which columns queries actually filter on, which tables are degrading fastest, and which maintenance actions produce the most benefit — optimization is guesswork.

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.

Here's what it does and why each piece improves query performance.

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:

  • Binpack: 221 seconds vs 1,612s (Spark) vs 6,300s (S3 Tables) on identical 200 GB dataset
  • Sort: 780 seconds vs estimated 3,000+ for Spark
  • Cost: $0.21 vs $1.54 for 200 GB binpack
  • Peak throughput: 2,522 MB/s — TB-scale tables in minutes
  • Bounded memory: spills to disk gracefully, no OOM regardless of table size

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_count) as avg_files_per_manifest
FROM prod.db.events.manifests;

-- Delete file accumulation (MoR tables)
SELECT COUNT(*) as delete_file_count
FROM prod.db.events.delete_files;

-- Snapshot accumulation
SELECT COUNT(*) as snapshot_count,
       MIN(committed_at) as oldest
FROM prod.db.events.snapshots;

Enter fullscreen mode Exit fullscreen mode

Thresholds: avg file size < 128 MB → compaction needed. Manifest count > 200 → rewrite manifests. Delete files > 50 → compact to apply. Snapshots > 1,000 → expire.

Use EXPLAIN to verify predicates push down to the scan level rather than appearing as residual filters.

Sort order configuration

Choose columns based on your most common query patterns:

-- Set sort order (first column gets tightest clustering)
ALTER TABLE prod.db.events WRITE ORDERED BY event_date, user_id;

-- Sort compaction to apply the order to existing data
CALL system.rewrite_data_files(
  table => 'prod.db.events',
  strategy => 'sort',
  sort_order => 'event_date ASC NULLS LAST, user_id ASC NULLS LAST'
);

-- For tables with diverse multi-column filters, use Z-order
CALL system.rewrite_data_files(
  table => 'prod.db.events',
  strategy => 'sort',
  sort_order => 'zorder(event_date, user_id, region)'
);

Enter fullscreen mode Exit fullscreen mode

Guidelines for choosing sort columns:

  • First key: Column most frequently in WHERE clauses. Gets tightest min/max ranges
  • Second key: Diminishing returns, but meaningful for two-column filters
  • Z-order: When no single column dominates filter patterns

The challenge: you must manually identify which columns queries filter on, and update the sort order when access patterns change. Most teams don't revisit this after initial setup.

File consolidation (compaction) 

-- Binpack: merge small files by size (fast, no reordering)
CALL system.rewrite_data_files(
  table => 'prod.db.events',
  strategy => 'binpack',
  options => map('target-file-size-bytes', '268435456', 'min-file-size-bytes', '67108864')
);

-- Sort compaction: merge + apply sort order (slower, better for queries)
CALL system.rewrite_data_files(
  table => 'prod.db.events',
  strategy => 'sort',
  sort_order => 'event_date ASC NULLS LAST, user_id ASC NULLS LAST',
  options => map('target-file-size-bytes', '268435456')
);

Enter fullscreen mode Exit fullscreen mode

Target: 256–512 MB files for analytical workloads. Schedule via Airflow/cron — daily for streaming tables, weekly for batch.

Preventing small files at write time

The cheapest compaction is the one you never need:

ALTER TABLE prod.db.events SET TBLPROPERTIES (
  'write.target-file-size-bytes' = '268435456',    -- 256 MB
  'write.distribution-mode' = 'hash',              -- Group by partition before write
  'write.parquet.compression-codec' = 'zstd'       -- Better compression
);

Enter fullscreen mode Exit fullscreen mode

Critical: write.distribution-mode = 'none' on partitioned tables is the #1 source of small files. With 200 Spark tasks writing to 365 daily partitions, you get 73,000 tiny files per job. Always use hash for partitioned tables.

For Spark, align AQE with your target file size:

spark.conf.set("spark.sql.adaptive.enabled", "true")
spark.conf.set("spark.sql.adaptive.coalescePartitions.enabled", "true")
spark.conf.set("spark.sql.adaptive.advisoryPartitionSizeInBytes", "268435456")

Enter fullscreen mode Exit fullscreen mode

For Flink streaming writes, increase checkpoint intervals to reduce file frequency:

SET 'execution.checkpointing.interval' = '5min';
SET 'sink.committer.operator.commit-interval' = '5min';

Enter fullscreen mode Exit fullscreen mode

Bloom filters for point lookups

When queries use equality predicates on high-cardinality columns (WHERE user_id = 12345), bloom filters eliminate row groups that provably don't contain the value:

ALTER TABLE prod.db.events SET TBLPROPERTIES (
  'write.parquet.bloom-filter-enabled.column.user_id' = 'true',
  'write.parquet.bloom-filter-enabled.column.session_id' = 'true',
  'write.parquet.bloom-filter-fpp.column.user_id' = '0.05'
);

-- Generate filters on existing data via compaction
CALL system.rewrite_data_files(
  table => 'prod.db.events',
  options => map(
    'write.parquet.bloom-filter-enabled.column.user_id', 'true',
    'write.parquet.bloom-filter-fpp.column.user_id', '0.05'
  )
);

Enter fullscreen mode Exit fullscreen mode

Limit to 2–3 columns per table (each adds ~1 MB to every file footer). Only useful for equality predicates — range queries don't benefit.

Manifest consolidation 

-- Check if needed
SELECT COUNT(*) FROM prod.db.events.manifests;

-- Consolidate (run AFTER compaction)
CALL system.rewrite_manifests('prod.db.events');

Enter fullscreen mode Exit fullscreen mode

Target: 10–50 manifests. Run after compaction stabilizes the file set — not before.

Snapshot expiration and orphan cleanup 

-- Expire snapshots older than 7 days, keep at least 5
CALL system.expire_snapshots(
  table => 'prod.db.events',
  older_than => TIMESTAMP '2026-07-26 00:00:00',
  retain_last => 5
);

-- Remove orphan files from failed writes
CALL system.remove_orphan_files(
  table => 'prod.db.events',
  older_than => TIMESTAMP '2026-07-26 00:00:00'
);

Enter fullscreen mode Exit fullscreen mode

Guidelines: 3–7 days retention for streaming tables, 30–90 days for batch. Always retain_last >= 2 to avoid breaking concurrent readers.

Delete file cleanup (MoR tables) 

-- Compact to apply pending deletes
CALL system.rewrite_data_files(
  table => 'prod.db.events',
  options => map('delete-file-threshold', '3')
);

Enter fullscreen mode Exit fullscreen mode

Monitor regularly. A table accumulating 200+ delete files/day will see read times increase from 3s → 22s over two weeks.

Partition strategy 

-- Set partition (at table creation or via evolution)
CREATE TABLE prod.db.events (
  event_id BIGINT,
  event_time TIMESTAMP,
  user_id BIGINT,
  region STRING
) USING iceberg
PARTITIONED BY (day(event_time));

-- Evolve without data rewrite (metadata-only, instant)
ALTER TABLE prod.db.events SET PARTITION SPEC (day(event_time));

Enter fullscreen mode Exit fullscreen mode

Rules: each partition should hold at least 128 MB. Daily partitioning on a 10 MB/day table → use monthly. bucket(col, 1024) on a 50 GB table → try 16–64 buckets instead.

Puffin statistics for join planning 

-- Generate NDV statistics for cost-based optimization
CALL system.compute_table_stats(table => 'prod.db.events');

-- Or via ANALYZE TABLE (Spark)
ANALYZE TABLE prod.db.events COMPUTE STATISTICS FOR COLUMNS
  user_id, event_type, region;

Enter fullscreen mode Exit fullscreen mode

Recompute after major data changes. For details: Puffin Statistics Guide.

Parquet-level tuning 

ALTER TABLE prod.db.events SET TBLPROPERTIES (
  'write.parquet.row-group-size-bytes' = '134217728',   -- 128 MB row groups
  'write.parquet.page-size-bytes' = '1048576',          -- 1 MB pages
  'write.parquet.dict-size-bytes' = '2097152',          -- 2 MB dict threshold

  -- Disable stats for columns never filtered on (reduces manifest bloat)
  'write.metadata.metrics.column.raw_payload' = 'none',
  'write.metadata.metrics.column.debug_info' = 'none'
);

Enter fullscreen mode Exit fullscreen mode

For read-side tuning in Spark:

spark.conf.set("spark.sql.files.maxPartitionBytes", "268435456")  # 256 MB per task
spark.conf.set("spark.sql.files.openCostInBytes", "4194304")      # 4 MB open cost

Enter fullscreen mode Exit fullscreen mode

Copy-on-Write vs. Merge-on-Read

Choose your update strategy based on your read/write ratio:

Workload Strategy Why
Batch reporting (updated nightly, read all day) Copy-on-Write Zero read overhead, clean files always
Streaming CDC (Debezium/Kafka) Merge-on-Read + compaction Write speed critical, compact to restore reads
Infrequent GDPR/compliance deletes Merge-on-Read + periodic cleanup Fast compliance, clean up later
High-frequency MERGE INTO Deletion Vectors (V3) Best write + read performance

The correct maintenance sequence

Whether automated or manual, the order of operations matters:

  1. Expire snapshots → releases references to dead files
  2. Remove orphans → deletes unreferenced files from storage (cheaper subsequent compaction)
  3. Compact (with sort) → merges files, applies sort order, resolves delete files
  4. Rewrite manifests → consolidates metadata over the clean, final file set

Running these out of order wastes compute. Compacting before expiring rewrites files that should be deleted. Manifest rewrites before compaction get invalidated immediately.

Performance troubleshooting checklist

Symptom Likely cause Fix
Slow range queries Unsorted data, many small files Sort compaction on filter columns
Slow point lookups No bloom filters Enable per-column bloom filters + compact
Slow query planning (>5s) Too many manifests rewrite_manifests
Full table scans Wrong partition scheme Partition evolution
Gradually slowing reads Accumulating delete files Compact to apply deletes
High S3 API costs Thousands of small files Compaction + write-side tuning
Dashboard timeouts Multiple degradation factors Full maintenance pipeline + routing

Choosing your path

The manual path works. Every SQL procedure shown above is production-proven. The challenge is sustaining it:

  • Choosing the right sort order requires analyzing query logs across all engines — and updating when patterns change
  • Scheduling compaction requires different frequencies for different tables — streaming tables need it hourly, batch tables weekly
  • Sequencing operations correctly requires understanding their dependencies
  • Monitoring 50+ tables for degradation signals requires dashboard infrastructure
  • Scaling to hundreds of tables means the maintenance burden grows linearly with your data estate

For teams with fewer than 10–20 tables and stable access patterns, the manual path is manageable. Beyond that, the operational burden compounds — and the sort order inevitably goes stale because nobody has time to analyze query patterns per table per quarter.

The control plane approach eliminates this scaling problem. It handles the continuous observation, adaptation, and execution at any table count — and because its compaction engine is 95% faster than Spark, it can apply sort compaction at frequencies that would be cost-prohibitive manually.

Both paths lead to the same destination: tables where queries are 12x faster because the physical layout matches how data is actually accessed. The question is whether you want that as a continuous property of your lake, or as a point-in-time optimization that needs periodic human re-tuning.

Summary

Iceberg query performance is determined by the physical state of your tables — not by the format spec, not by the engine, and not by hardware. A well-maintained table is fast on every engine. A degraded table is slow on all of them.

The physical levers that matter:

  1. Sort order aligned to query patterns — 51% less data scanned, up to 12x faster queries
  2. File consolidation — 5–10x faster planning, reduced I/O
  3. Bloom filters — near-instant elimination for equality lookups
  4. Manifest optimization — 2–5x faster planning
  5. Write-side configuration — prevent degradation at source
  6. Delete file cleanup — restore read performance on MoR tables
  7. Snapshot lifecycle — lean metadata, lower storage costs
  8. Partition strategy — coarse-grained elimination at the foundation
  9. Multi-engine routing — right engine per query, up to 56% cost reduction

The intelligent path keeps all of these optimal continuously. The manual path gives you the tools to do it yourself. Either way — the performance ceiling is the physical state of your tables, and now you know how to raise it.

Further reading: