SET AUTO OPTIMIZE

Enables or disables automatic post-write compaction and optimized writes for a Delta table.

Category: maintenanceDeltaForge extension

Syntax

SET AUTO OPTIMIZE <table> [ON | OFF] [TARGET_SIZE <n>]

Description

## Overview SET AUTO OPTIMIZE persists auto-optimization settings as Delta table properties. When enabled, the DML executor reads these properties at write time and triggers post-write compaction to reduce small file accumulation. ## Behavior The command uses AlterTableBuilder to write the following table properties into the Delta transaction log: | Property | ON value | OFF value | |----------|----------|-----------| | `delta.autoOptimize.autoCompact` | `true` | `false` | | `delta.autoOptimize.optimizeWrite` | `true` | `false` | | `delta.autoCompact.maxFileSize` | Value of TARGET_SIZE (if provided) | Not modified | Both `autoCompact` and `optimizeWrite` are always set together. There is no way to enable one without the other through this command; use ALTER TABLE SET TBLPROPERTIES for independent control. When TARGET_SIZE is specified, it is persisted alongside the enable/disable flags. When omitted, the engine default of 128 MB applies. ## How Auto-Compaction Works Once enabled, the background AutoOptimizeManager monitors write operations against the table. When accumulated small files exceed configured thresholds (file count, total size, or time since last optimization), a compaction job is enqueued and processed asynchronously. The compaction reuses the same OPTIMIZE infrastructure, including bin-packing and optional Z-ORDER. ## Access Control | Privilege | Object | Notes | |-----------|--------|-------| | Ownership or write | Table | Required to modify table properties in the Delta log. | ## Compatibility SET AUTO OPTIMIZE is a DeltaForge extension. The table properties it writes (`delta.autoOptimize.*`) follow the Delta Lake convention, so other Delta readers will recognize and respect these settings.

Parameters

Name	Type	Description
`table`		Specify the name or path of the Delta table to configure. The table must be registered in the session (via CREATE DELTA TABLE or OPEN DELTA TABLE). Fully qualified names (zone.schema.table) are supported.
`enabled`		Set to ON to enable or OFF to disable both auto-compaction and optimized writes. When enabled, the DML executor triggers post-write compaction automatically based on file count and size thresholds.
`target_file_size`		Specify the target file size in bytes for auto-compaction. When omitted, defaults to 134217728 (128 MB). This value is persisted as the `delta.autoCompact.maxFileSize` table property.

Examples

-- Enable auto-optimize with default target file size (128 MB)
SET AUTO OPTIMIZE orders ON;

-- Disable auto-optimize for a table
SET AUTO OPTIMIZE orders OFF;

-- Enable with a custom target file size of 64 MB
SET AUTO OPTIMIZE events ON TARGET_SIZE 67108864;

-- Enable on a fully qualified table name
SET AUTO OPTIMIZE warehouse.sales.transactions ON;

-- Enable with a larger target size for append-heavy workloads
SET AUTO OPTIMIZE logs ON TARGET_SIZE 268435456;

-- Verify the configuration after enabling
SET AUTO OPTIMIZE orders ON TARGET_SIZE 67108864;
DESCRIBE AUTO OPTIMIZE orders;

Pitfalls

Enabling auto-optimize does not retroactively compact existing small files. Run OPTIMIZE manually to address pre-existing fragmentation before enabling auto-optimize for ongoing writes.
Both autoCompact and optimizeWrite are toggled together. To control them independently, use ALTER TABLE SET TBLPROPERTIES with the individual property keys.
The TARGET_SIZE parameter accepts raw bytes, not human-readable units. Passing 64 instead of 67108864 sets a 64-byte target, which will cause excessive file splitting.
Disabling auto-optimize (OFF) does not cancel in-progress compaction jobs that were already enqueued by the AutoOptimizeManager. Those jobs will complete before the new setting takes effect.