Set up maintenance for a table
Table maintenance is work that nobody requested, that produces no new information and that leaves every answer the same. It exists because a table's physical layout gets worse as data arrives, whether or not anything is wrong: files drift small, sort order decays, deleted rows and unreferenced files pile up. Without a plan, maintenance is either switched off or run on a schedule nobody derived. What should run on this table, and when?
You will end with a maintenance plan for one table: what runs, when it fires, how long history is kept, and how each rewrite is checked.
Work through these in order.
Work through it
Find where the small files come from
Files per hour are a product of writers, partitions and commits, not a function of volume: 50 writers committing once a minute into 100 partitions make 5,000 files a minute while moving almost no bytes. Each fix moves the cost: pre-shuffling by partition key pays a shuffle now; a longer commit interval pays in freshness; auto-compaction pays in background compute later.
Set the target file size
The best size is the per-request fixed cost divided by streaming bandwidth: with 50 ms and 100 MB/s, a 5 MB file loses half its time to overhead and a 500 MB file about 1%. That is why targets sit between 128 MB and 1 GB. Files can also be too large for skipping, parallelism and copy-on-write; maintenance restores files toward the size the layout chose.
Trigger compaction by cost, not by habit
With C the cost of one rewrite and r the rate at which extra read cost grows, the total cost per unit of time is C / T + r · T / 2, smallest at T = √(2C / r). A 1,800 compute-second compaction with r = 0.25 gives 120 hours; four times the writes gives 60. The cadence scales with the square root of the write rate, so neither "constantly" nor "nightly" is right.
Set retention from the longest reader
Vacuum can compute which files no snapshot references, but not which files a running query still holds open: you cannot list your readers. So the retention window is a bound on the maximum query duration. A query that runs longer fails mid-scan with file-not-found. Set the window above your longest reader, and know that the long-running reader is the one who pays.
Rewrite files with deleted rows one by one
A file with a fraction d of rows deleted still costs a full read. Rewrite it when d × expected reads > 1: d = 0.2 with 50 reads, yes; with 3 reads, no. The rewrite moves rows, so merge delete vectors into the new file, and name who refreshes the statistics and indexes that described the old layout.
Clean orphans rarely, with a long grace period
Orphan detection needs a full listing and an anti-join. Run it rarely, delete only files older than a long grace period (for example 3 days), never while a known long writer is active, and log every deleted path.
Verify every rewrite before publishing it
Compaction already reads every byte, so a row count and a sum of per-row hashes on the input and on the output cost almost nothing, and they must match. A corruption caused by a rewrite has no failing job and no user-visible trigger.
Tier only what has been dormant long enough
A cold tier pays only when an object's dormancy exceeds the retrieval fee divided by the monthly saving, about 3.5 months at typical prices. Transitions are per-object requests, so a table of small files is expensive to tier too.