Table Maintenance

Iceberg tables require periodic maintenance to ensure optimal query performance and storage efficiency. Maintenance operations work across all format versions (v1, v2, v3).

OPTIMIZE (Compaction)

Compaction rewrites small files into larger, optimally-sized files and merges delete files:

-- Rewrite data files
OPTIMIZE TABLE orders;

-- Compact a specific partition range
OPTIMIZE TABLE orders WHERE order_date >= '2025-01-01';

-- Merge delete files only (faster than full rewrite)
OPTIMIZE TABLE orders PURGE DELETES;

The default target file size is 256 MB.

Clustering Strategies

OPTIMIZE supports multiple clustering strategies for data layout optimization. The strategy determines how rows are ordered within each output file, which directly affects query performance for different access patterns.

Strategy	Description	Best For
Sort	Sort data by one or more columns in lexicographic order	Point lookups and range scans on the sort key columns. Most effective when queries consistently filter on a known set of columns.
Z-Order	Z-order (Morton) curve interleaving across multiple columns	Multi-column filter queries where no single column dominates. Provides balanced pruning across 2-4 columns.
Hilbert	Hilbert space-filling curve ordering	Better spatial locality than Z-order for high-dimensional data (4+ columns). Higher computation cost during compaction but improved query-time pruning.

-- Sort clustering: optimal for queries filtering on order_date
OPTIMIZE TABLE orders
CLUSTER BY (order_date)
USING SORT;

-- Z-order clustering: balanced pruning on both region and order_date
OPTIMIZE TABLE orders
CLUSTER BY (region, order_date)
USING ZORDER;

-- Hilbert clustering: high-dimensional locality
OPTIMIZE TABLE events
CLUSTER BY (event_type, user_id, device_type, region)
USING HILBERT;

Selective Partition Optimization

Target specific partitions to avoid rewriting the entire table. This is the recommended approach for large tables where only recent partitions have fragmented files.

-- Optimize only recent partitions
OPTIMIZE TABLE orders WHERE order_date >= '2025-01-01';

-- Optimize a specific region partition
OPTIMIZE TABLE orders WHERE region = 'US';

Delete File Consolidation

OPTIMIZE TABLE ... PURGE DELETES rewrites data files to physically remove rows that have been logically deleted. On v2 tables this consolidates position delete files; on v3 tables it also handles deletion vector merging. After purging, queries no longer need to process delete files, which reduces scan overhead.

-- Purge delete files from the entire table
OPTIMIZE TABLE orders PURGE DELETES;

-- Purge delete files from specific partitions
OPTIMIZE TABLE orders PURGE DELETES WHERE order_date >= '2025-01-01';

The engine automatically recommends delete compaction when:

• Delete files per partition exceed 200
• p95 deletes per file is high
• Deleted rows ratio exceeds 30% of total rows
• Delete processing time exceeds 40% of total scan time

When to Compact

• After many small INSERT operations
• When the average file size drops below 100 MB
• When file count per partition exceeds 100
• When delete files accumulate (visible in EXPLAIN ANALYZE as delete_files_applied)

VACUUM (Orphan Cleanup)

VACUUM removes data files from storage that are no longer referenced by any snapshot, and optionally expires old snapshots:

-- Remove orphan files and expire snapshots older than 7 days
VACUUM TABLE orders RETAIN 7 DAYS;

-- Use default retention (5 days)
VACUUM TABLE orders;

-- Preview what would be deleted without actually deleting
VACUUM TABLE orders RETAIN 7 DAYS DRY RUN;

Retention Guarantees

VACUUM enforces safety constraints to prevent deleting files needed by active queries or recent snapshots:

• Minimum snapshot retention: 5 snapshots are always kept regardless of the retention period. This ensures that concurrent readers can complete without encountering missing files.
• Orphan file age: Files must be unreferenced for at least 3 days (default) before they are eligible for deletion. This prevents race conditions with in-progress write commits.
• Minimum retention period: The retention period cannot be set below 1 day.

VACUUM is safe to run at any time. It only removes files that are not referenced by the current snapshot or any retained snapshot. VACUUM supports both data file and metadata file cleanup, with configurable parallelism and detailed audit logging of deleted files.

caution

After expiring snapshots, time travel queries to those snapshots will no longer work. Ensure your retention period accommodates any time travel requirements.

COMPACT (Small File Merge)

COMPACT merges small files into optimally-sized files without rewriting files that are already at or above the target size. This is a lighter operation than OPTIMIZE, as it only touches undersized files.

-- Merge small files in the entire table
COMPACT TABLE orders;

-- Merge small files in specific partitions
COMPACT TABLE orders WHERE order_date >= '2025-01-01';

Behavior

• Target file size: 128 MB (default). Files at or above this size are not rewritten.
• Only rewrites undersized files: Files below the target threshold are grouped and merged. Large files are left untouched.
• No clustering: COMPACT does not reorder rows. Use OPTIMIZE if you need sorted or Z-ordered output.

COMPACT is appropriate when you have many small files from frequent INSERT operations but do not need to change the data layout. It is faster and cheaper than a full OPTIMIZE because it reads and writes fewer bytes.

Snapshot Expiry

Remove old snapshots to free metadata storage:

ALTER TABLE orders EXECUTE EXPIRE_SNAPSHOTS
SET ('older-than' = '2025-01-01T00:00:00Z');

caution

After expiring snapshots, time travel queries to those snapshots will no longer work. Run VACUUM after expiring snapshots to reclaim the storage used by now-unreferenced files.

Manifest Rewrite

Rewrite manifests to reduce metadata overhead when many small manifests have accumulated:

ALTER TABLE orders EXECUTE REWRITE_MANIFESTS;

Monitoring Maintenance Status

Use SHOW TABLE MAINTENANCE STATUS to inspect the current state of a table and determine whether maintenance is needed:

SHOW TABLE MAINTENANCE STATUS FOR orders;

This returns a summary including:

Metric	Description
total_data_files	Number of data files in the current snapshot
total_delete_files	Number of delete files in the current snapshot
avg_file_size_mb	Average data file size in megabytes
min_file_size_mb	Smallest data file size
delete_ratio	Ratio of deleted rows to total rows
files_below_target	Number of files below the target file size (candidates for COMPACT)
last_optimized	Timestamp of the last OPTIMIZE operation
last_vacuumed	Timestamp of the last VACUUM operation

Use these metrics to determine which maintenance operation to run. For example, a high delete_ratio suggests OPTIMIZE ... PURGE DELETES, a low avg_file_size_mb suggests COMPACT, and a large total_delete_files count suggests delete-only compaction.

Automated Maintenance

For production deployments, schedule maintenance operations to run automatically. Gnok supports task-based scheduling:

-- Schedule daily compaction of recent partitions
CREATE TASK daily_optimize
WAREHOUSE = 'maintenance_wh'
SCHEDULE = 'USING CRON 0 2 * * * UTC'
AS
OPTIMIZE TABLE orders WHERE order_date >= CURRENT_DATE - INTERVAL '7 days';

-- Schedule weekly vacuum
CREATE TASK weekly_vacuum
WAREHOUSE = 'maintenance_wh'
SCHEDULE = 'USING CRON 0 3 * * 0 UTC'
AFTER daily_optimize
AS
VACUUM TABLE orders RETAIN 7 DAYS;

You can also use external schedulers (cron, Airflow, Dagster) to invoke maintenance operations via SQL.

Recommended Schedule

Operation	Frequency	Notes
OPTIMIZE	Daily or weekly	Focus on partitions with recent writes
OPTIMIZE PURGE DELETES	Daily	After UPDATE/DELETE-heavy workloads
COMPACT	Daily	After INSERT-heavy workloads with many small files
EXPIRE_SNAPSHOTS	Weekly	Retain enough for time travel needs
VACUUM	Weekly (after expire)	Always run after snapshot expiry
REWRITE_MANIFESTS	Monthly	Only if manifest count grows large