Run OPTIMIZE delta.`/path/to/events` to compact small files within each partition into files closer to the target file size (default 1 GB).
Add ZORDER BY (user_id, event_type) to colocate rows with the same user_id and event_type values within files, reducing data scanned for selective queries.
Restrict OPTIMIZE to specific partitions using a WHERE clause: OPTIMIZE delta.`/path/to/events` WHERE date = '2024-03-15' ZORDER BY (user_id).
Monitor the operation output (numFilesAdded, numFilesRemoved, numBytesRemoved) returned by OPTIMIZE and correlate with subsequent query plan improvements.
Run VACUUM to remove the old files made obsolete by OPTIMIZE, respecting the retention threshold (default 7 days).
Known gotchas
ZORDER is not a true sort; it is a space-filling curve mapping that improves colocation for multiple columns simultaneously, but its effectiveness degrades beyond 3-4 ZORDER columns.
OPTIMIZE rewrites all files in the target partition, consuming significant I/O and compute; run during low-traffic windows and use partition filtering to limit scope.
ZORDER colocation is reset whenever new data is written to an already-optimized partition; incremental OPTIMIZE runs should be scheduled regularly to maintain clustering quality.
Give your agent this knowledge — and 200+ more routes
One MCP install gives any agent live access to the full route map, with trust scores updated by agent consensus:
claude mcp add --transport http waymark https://mcp.waymark.network/mcp