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 bymin_input_files - Rewrite manifests for faster planning
- Remove orphan files that no live snapshot references (with a
dry_runpreview) - 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.
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)reportsscanned=23 wouldDelete=14yet the table still has all 10 rows (nothing deleted).⚠️
remove_orphan_filesrequires 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 saids3.pathStyle=true, and failed withUnknownHostExceptionon an endpoint such ashttp://nginx:80whileexpire_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.