Skip to content

Iceberg Table Maintenance

Ontul keeps Iceberg tables healthy with the same maintenance operations as Apache Spark — small-file compaction, snapshot expiry, manifest rewrite, orphan-file cleanup, position-delete consolidation, and rollback — available both as on-demand SQL procedures and as a scheduled auto-maintenance service (configurable per table from the Admin UI, including a UNIX cron schedule). Parameter names mirror the Apache Iceberg Spark procedures.

Auto-maintenance & scheduling

A built-in maintenance service runs periodic tasks per registered Iceberg table:

  • Expire snapshots beyond a retention window (default 7 days), optionally keeping the last N (retain_last)
  • Rewrite data files — small-file compaction, delete-aware; optionally windowed (window_hours) and gated by min_input_files
  • Rewrite manifests for faster planning
  • Remove orphan files that no live snapshot references (with a dry_run preview)
  • Rewrite position-delete files — consolidate v2 delete files

The same operations are available as on-demand SQL (see procedure reference for the full per-procedure parameter list and examples):

ALTER TABLE ice.ns.t EXECUTE optimize(file_size_threshold_mb => 128, min_input_files => 5);
ALTER TABLE ice.ns.t EXECUTE expire_snapshots(retain_last => 100);
ALTER TABLE ice.ns.t EXECUTE remove_orphan_files(dry_run => true);

Auto-maintenance (Admin UI → Iceberg Maintenance). Each table has a config — per-operation toggles, target file size, window_hours, min_input_files, snapshot retention + retain_last, the orphan safety window, and the compaction concurrency-safety controls (window cooldown minutes, sequence margin, skip-active-partitions, max commit retries) — plus a schedule: either a fixed interval (hours) or, when set, a 5-field UNIX cron expression that overrides the interval (e.g. 0 */2 * * * = every 2 hours). A newly saved cron waits for its next fire time; it does not run the table straight away. The table key must be catalog.schema.table: a shorter key is refused when it is loaded, not only when it is saved. This makes incremental, scheduled compaction practical for streaming tables: e.g. a cron of 0 * * * * with window_hours => 1 compacts just the last hour's small files every hour. Unlike ad-hoc EXECUTE optimize (safety filters OFF by default), scheduled auto-maintenance applies safe defaults (cooldown 10 min, seq_margin 1, skip-active on, retries 3) so it never conflicts with in-flight writes — see Running compaction alongside live writes. The same finer-grained parameters can be set optionally per table, and a Manual Trigger runs any single operation (or all) on demand against a wildcard table pattern.

For tests and quick teardown, set polaris.config.drop-with-purge.enabled=true on the Polaris catalog so DROP TABLE actually deletes the underlying S3 data.

Where maintenance runs

The scheduled cycle is a cluster singleton: it runs on the Master leader only. Two masters compacting the same table would collide at commit — Iceberg keeps the data correct, but one side wastes the rewrite and retries — and concurrent remove_orphan_files runs are genuinely unsafe. Manual triggers need no special handling: they arrive through the admin API, which already forwards a follower's request to the leader.

Per-table maintenance configuration lives in the metadata store, which the leader replicates to followers, so a schedule survives a leader handoff and a restart.

Storage access

Every maintenance operation — scheduled or manual — reads and writes table files through the S3 settings of the Ontul catalog (s3.endpoint, s3.accessKey / s3.secretKey, s3.pathStyle), not through whatever endpoint or credentials the REST catalog advertises with the table. A catalog such as Polaris may advertise the endpoint as clients outside the cluster reach it (for example http://localhost:8000 next to an internal http://nginx:80); writing a rewritten manifest or deleting expired files through that address fails from inside the cluster. Using the catalog's own S3 settings keeps maintenance on the same storage path as Ontul's queries and writes.

Procedure reference

All maintenance ops are on-demand ALTER TABLE <table> EXECUTE <procedure>(param => value, …) procedures (Trino-style named arguments). The same operations run on a schedule via the auto-maintenance service and the Admin UI. Parameter names mirror the Apache Iceberg Spark procedures so the syntax is familiar; a few ontul-specific extensions and convenience aliases are called out per procedure. Parameters are passed by name and are order-independent; quote string/timestamp values.

Every procedure below is verified end-to-end — tests/test-iceberg-maintenance-e2e.sh (Ontul-only) and tests/test-iceberg-maintenance-trino-e2e.sh (run via Ontul, read back through Trino against the same Polaris REST catalog + S3 to prove the resulting metadata is valid for any engine).


optimize — compact small files (rewrite_data_files)

Reads each data file together with its applicable position- and equality-delete files, writes the surviving rows into one compacted file per partition (each file keeps its partition value and is written under that partition's path; a partition with only one file and no deletes is left as it is), and drops the consumed delete files in a single atomic RewriteFiles commit. This is delete-aware: rows previously DELETEd/UPDATEd are not resurrected, and the table format (write.format.default) is preserved. Run it to fix the small-file problem caused by frequent commits (especially streaming).

Parameter Type Default Description
file_size_threshold_mb int 128 Target file size. Files smaller than half this are treated as "small" and eligible for compaction.
min_input_files int 5 (2 for the OPTIMIZE shorthand) Skip compaction unless at least this many small files would be combined (Spark min-input-files). Avoids churning an already-optimized table.
window_hours int — (whole table) ontul extension. Compact only the append-only files added by commits in the last N hours. Delete-bearing files are skipped (run a full optimize for those). Ideal for streaming tables — you never rescan history.
window_cooldown_minutes int 0 (ad-hoc) Concurrency-safety. Exclude files added in the last N minutes — the "hot zone" an active writer is appending to. Pulls the window's upper bound back to now − N min.
seq_margin int 0 (ad-hoc) Concurrency-safety. Only rewrite files whose data sequence number is ≤ tip − margin, i.e. committed strictly behind the live tip. Clock-independent "behind the writer".
skip_active_partitions bool false (ad-hoc) Concurrency-safety. Skip any partition written within the cooldown window (window_cooldown_minutes), so compaction never touches a partition an active writer is appending to. A partition is active exactly when it was written within the cooldown: with a cooldown of 0 no partition is active. (A hidden 10-minute floor used to apply, so with cooldown 0 a freshly written partitioned table was never compacted while the same unpartitioned table was.)
max_commit_retries int 3 (always on) Bounded re-plan + retry on a concurrent-commit conflict (CommitFailedException/ValidationException). Pure safety net; absorbs transient races so nothing surfaces to the caller.
-- Full-table compaction: combine small files into ~128 MB files, only if ≥5 small files exist
ALTER TABLE ice.sales.orders EXECUTE optimize(file_size_threshold_mb => 128, min_input_files => 5);

-- Shorthand for a whole-table compaction with defaults
OPTIMIZE ice.sales.orders;

-- Incremental compaction for a streaming table: only files written in the last hour / last day
ALTER TABLE ice.sales.orders EXECUTE optimize(window_hours => 1,  min_input_files => 2);
ALTER TABLE ice.sales.orders EXECUTE optimize(window_hours => 24);

-- Concurrency-safe compaction while writers are active (single-committer-style avoidance)
ALTER TABLE ice.sales.orders EXECUTE optimize(
  window_cooldown_minutes => 10, seq_margin => 1, skip_active_partitions => true, max_commit_retries => 5);

Running compaction alongside live writes

Compaction commits a RewriteFiles that replaces existing data files. If that races with a concurrent append / MERGE / UPDATE / DELETE, Iceberg rejects the loser with CommitFailedException / ValidationException. ontul avoids the conflict instead of fighting it — a single-committer-style strategy that keeps compaction strictly behind the writer:

  • Cooldown (window_cooldown_minutes) and active-partition skip (skip_active_partitions) keep the rewrite out of the time/partition region a writer is actively touching.
  • Sequence margin (seq_margin) only rewrites files at least N sequence numbers behind the live tip — clock-independent, so it's robust even with clock skew or bursty commits.
  • Bounded retry (max_commit_retries) re-plans and retries on any residual conflict, so transient races never surface to the caller.

When these filters leave too few files to compact, the result says which files were left alone and why, e.g. No compaction needed (12 files, 0 small cold, 0 rewritable, min_input_files=5; skipped 12 written within the cooldown (10 min)) or ...; skipped 3 in the last 1 commit(s) (seq_margin), 6 in 3 active partition(s) (written within the cooldown 10 min). Scheduled maintenance and Run Now record such a run in the history as skipped (with that text), not as a successful compaction and not silently.

Defaults differ by caller: ad-hoc EXECUTE optimize keeps the safety filters OFF (predictable — rewrites the whole table just as before, but now with retry on), while scheduled auto-maintenance passes SAFE defaults (cooldown 10 min, seq_margin 1, skip-active on, retries 3) from the per-table MaintenanceConfig. With the filters ON, a full optimize becomes selective — it leaves hot/active files untouched rather than collapsing the whole table into one file.

Verified: 5 small files (10 rows) → 1 file with all 10 rows preserved; windowed run reports Optimized (window 1h): 3 recent small files → 1 file (6 rows). Concurrency-safety verified e2e (tests/test-compaction-concurrent-e2e.sh): a writer appended continuously to a partitioned table while safe-default compaction ran 38 rounds concurrently — no row loss (final = baseline + every appended row), zero failures escaping to the client, and 0 commit conflicts even arose (the cold-file / seq-margin selection kept compaction behind the live writer; the retry net was not even needed).

Streaming sinks are the main reason to schedule this

An Ontul Flow commits on an interval, so it steadily produces small files. Turn on scheduled compaction for the flow's sink table and leave the flow running — the safe defaults above are exactly what keeps the rewrite behind a live streaming writer, so ingestion never has to stop for maintenance.


expire_snapshots — drop old snapshots and their files

Removes snapshots (table versions) that are no longer needed and physically deletes the data/metadata files that only those expired snapshots referenced. Keeps time-travel storage bounded. Files still referenced by a retained snapshot are never touched.

Parameter Type Default Description
older_than timestamp — Expire snapshots older than this instant (Spark older_than). Accepts ISO offset (2026-06-01T00:00:00Z), ISO local (2026-06-01T00:00:00 / 2026-06-01 00:00:00), or date-only (2026-06-01).
retain_last int — Always keep at least the N most recent snapshots regardless of age (Spark retain_last).
retention_hours int 168 (7 days) ontul convenience alias for older_than expressed as "now − N hours". Ignored if older_than is given.
-- Keep the last 7 days of snapshots
ALTER TABLE ice.sales.orders EXECUTE expire_snapshots(retention_hours => 168);

-- Keep the last 100 snapshots regardless of age (good for high-frequency streaming commits)
ALTER TABLE ice.sales.orders EXECUTE expire_snapshots(retain_last => 100);

-- Combine: expire anything before a date, but never drop below 10 snapshots
ALTER TABLE ice.sales.orders EXECUTE expire_snapshots(older_than => '2026-06-01T00:00:00', retain_last => 10);

-- Shorthand
EXPIRE SNAPSHOTS ice.sales.orders RETAIN LAST 5;

Verified: expire_snapshots(retention_hours => 0, retain_last => 2) reduces a 7-snapshot table to exactly 2 snapshots, current data (10 rows) intact and readable by Trino.


rewrite_manifests — compact manifest metadata

Rewrites the table's manifest files so each scan plans over fewer, well-clustered manifests. Cheap metadata-only operation; useful after many small commits have fragmented the manifest list.

Parameter Type Default Description
spec_id int all specs Rewrite only manifests belonging to the given partition-spec id (Spark spec_id). Use after partition evolution to consolidate one spec's manifests.
ALTER TABLE ice.sales.orders EXECUTE rewrite_manifests();
ALTER TABLE ice.sales.orders EXECUTE rewrite_manifests(spec_id => 0);

The data manifests of the current snapshot are merged per partition spec into as few as the target manifest size (commit.manifest.target-size-bytes) allows, in one commit; the data files stay the same. The answer names the counts, e.g. Manifests rewritten: data manifests 3 → 1.

When there is nothing to merge -- no snapshot yet, or fewer than two data manifests (of spec_id, when given) -- nothing is committed: the procedure answers No manifests to rewrite: nothing to merge: 1 data manifest, and scheduled maintenance records skipped with that reason. Earlier builds committed an empty replace snapshot on every run without merging anything; on a schedule that moved main forward for no reason, and a WAP branch staged before such a run could no longer be published with fast_forward (Cannot fast-forward: main is not an ancestor of …).


rewrite_position_delete_files — consolidate v2 delete files

For Iceberg v2 tables, consolidates multiple Parquet position-delete files that target the same data file into a single delete file per data file, committed via RowDelta. Reduces the per-scan delete-merge cost on tables with heavy DELETE/UPDATE/MERGE traffic. Puffin deletion-vector files are left as-is.

No parameters.

ALTER TABLE ice.sales.orders EXECUTE rewrite_position_delete_files();

remove_orphan_files — delete unreferenced S3 files

Lists every object under the table's location and deletes those that no live snapshot references (left behind by failed writes, aborted compactions, etc.). Reachability is computed from current table metadata, so this complements expire_snapshots (which removes snapshots but can leave their now-unreferenced physical files behind on some object stores).

Parameter Type Default Description
older_than timestamp — Only consider files last-modified before this instant (Spark older_than). Same formats as expire_snapshots.
older_than_hours int 72 ontul convenience alias — safety window "now − N hours". The default 72 h ensures files from in-flight writes are never deleted. Ignored if older_than is given.
dry_run bool false Scan and report the files that would be deleted without deleting anything (Spark dry_run). Always preview first on a new table.
-- Preview only — counts and reports orphans, deletes nothing
ALTER TABLE ice.sales.orders EXECUTE remove_orphan_files(dry_run => true);

-- Actually delete orphans older than the 72 h safety window (default)
ALTER TABLE ice.sales.orders EXECUTE remove_orphan_files(older_than_hours => 72);

-- Shorthand
REMOVE ORPHAN FILES ice.sales.orders;

Verified: after compaction + expiry, remove_orphan_files(dry_run => true) reports scanned=23 wouldDelete=14 yet the table still has all 10 rows (nothing deleted).

⚠️ remove_orphan_files requires S3 credentials in the catalog config and uses the table location as the listing root — point catalogs at a dedicated prefix per table so the listing is bounded.

The listing uses path-style S3 addressing against the catalog's s3.endpoint, exactly like every other read, write and maintenance operation. It used to fall back to virtual-host style (bucket.host) unless the catalog said s3.pathStyle=true, and failed with UnknownHostException on an endpoint such as http://nginx:80 while expire_snapshots / rewrite_* on the same table worked.

Errors carry their cause. A failing maintenance operation reports the whole cause chain, ending with the root cause — e.g. ... See cause for the exact endpoint ... <- SdkClientException: Unable to execute HTTP request: s3-host <- UnknownHostException: s3-host — in the SQL error, in the maintenance history entry and in the master log (with the stack trace).

History per table. GET /admin/maintenance/history filters on one table (exact catalog.schema.table) by ?table=, the X-Table-Key header, or the older X-Table-Filter header; plus status (ok, skipped, error), operation, since, until and limit. The Maintenance page has the same table filter above the Job History. POST /admin/maintenance/run without tableKey, with an unknown operation, or with a body that is not a JSON object answers 400 with a message (it used to be a 500).

Finding tables and saved configurations. GET /admin/maintenance/tables?q=&limit= returns the Iceberg tables whose catalog.schema.table contains q (case-insensitive; exact name first, then names starting with q), at most limit (default 20, 1–100), as {tables, total, limit}; it searches the tables the engine already knows, without listing the catalogs. GET /admin/maintenance/configs?q=&offset=&limit= returns the tables that have a saved configuration, {configs: [{tableKey, config}], total, offset, limit} (default 10 per page, limit 1–1000); a table without a saved configuration runs on the defaults. A bad limit or offset answers 400. For a non-administrator both list only tables they may read (data:Select).


rollback_to_timestamp / rollback_to_snapshot — revert the table

Resets the table's current snapshot to an earlier version — to recover from a bad write, or to publish/inspect a known-good state. rollback_to_timestamp selects the snapshot that was current at the given instant; rollback_to_snapshot targets an explicit snapshot id. Find the snapshot id to roll back to by querying the $snapshots / $history metadata tables — e.g. SELECT committed_at, snapshot_id, operation, add_rec FROM ice.sales."orders$snapshots" ORDER BY committed_at.

Procedure Parameter Type Description
rollback_to_timestamp timestamp (required) timestamp Roll back to the snapshot current at this instant. Same formats as expire_snapshots.
rollback_to_snapshot snapshot_id (required) long Roll back to this exact snapshot id.
ALTER TABLE ice.sales.orders EXECUTE rollback_to_timestamp(timestamp => '2026-06-15T23:19:25');
ALTER TABLE ice.sales.orders EXECUTE rollback_to_snapshot(snapshot_id => 3821550127947089009);

Verified: a table grown to 5 rows rolls back to its 2-row first snapshot; Trino reading the same catalog afterward also sees exactly 2 rows (the rolled-back rows are invisible to every engine).


Cross-engine guarantee

Because every procedure commits standard Iceberg metadata through the REST catalog, the results are readable by any Iceberg engine. tests/test-iceberg-maintenance-trino-e2e.sh runs the full set via Ontul and then queries the same tables through Trino (trinodb/trino, same Polaris catalog + ShannonStore S3): row counts and aggregates match exactly, and a rollback_to_timestamp performed in Ontul is reflected in Trino's reads.

For tests and quick teardown, set polaris.config.drop-with-purge.enabled=true on the Polaris catalog so DROP TABLE actually deletes the underlying S3 data.