- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
3 weeks ago
Hi everyone,
I’m currently working with Delta Lake and wanted to start a discussion around performance tuning, maintenance, and best practices.
I’m interested in hearing how others in the community approach a few key areas:
Maintenance Operations: How often do you run OPTIMIZE (compaction) and VACUUM on your large production Delta tables?
Partitioning vs. Z-Ordering: What guidelines do you follow when choosing between partitioning a table and using Z-Ordering?
Would love to hear your experiences, lessons learned, or tips for keeping Delta tables running efficiently at scale!
- Labels:
-
Delta Lake
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
3 weeks ago
Hi @Khasim_1
Short version for UC managed tables: turn on predictive optimization and let it run OPTIMIZE and VACUUM for you, and use liquid clustering instead of choosing between partitioning and Z-order. Hand-scheduled maintenance and Z-order are now mainly for external tables or older runtimes.
Maintenance:
- Predictive optimization (enabled at account/catalog/schema level) runs OPTIMIZE, VACUUM and ANALYZE on UC managed tables based on actual usage, so there's no schedule to tune: https://docs.databricks.com/aws/en/optimizations/predictive-optimization
- If you schedule it yourself: OPTIMIZE daily (or after big loads) on tables with frequent small writes, weekly for the rest. VACUUM weekly with the default 7-day retention - don't go lower unless you're sure no reader, stream or time-travel query needs older versions.
- For streaming/frequent MERGE tables, enable delta.autoOptimize.optimizeWrite and delta.autoOptimize.autoCompact so you get fewer small files in the first place, and turn on deletion vectors to make MERGE/DELETE cheaper.
Partitioning vs. Z-order:
- Default to liquid clustering: CREATE TABLE ... CLUSTER BY (col1, col2) or ALTER TABLE t CLUSTER BY (...) on an existing table, picking the columns you filter/join on most. Keys can change later without rewriting the table, and OPTIMIZE clusters incrementally. CLUSTER BY AUTO lets Databricks pick keys from your query history: https://docs.databricks.com/aws/en/delta/clustering
- Only partition if the table is well over 1 TB and you have a low-cardinality column (usually a date) where each partition holds at least ~1 GB. Over-partitioning (high-cardinality columns, tiny partitions) is the most common cause of slow Delta tables.
- Z-order only on existing partitioned tables where you can't move to liquid yet - it can't be combined with liquid clustering, and it rewrites data on every run.
Quick health check on any table: DESCRIBE DETAIL t - if numFiles is high and sizeInBytes / numFiles is far below ~100 MB, it needs compaction or better clustering.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
3 weeks ago
A few practical recommendations based on table type and workload:
1. Unity Catalog managed tables: prefer Predictive Optimization
For eligible UC managed tables, Predictive Optimization (PO) automatically evaluates and runs OPTIMIZE, VACUUM, and ANALYZE when beneficial.
- Generally, no separate scheduled maintenance jobs are needed.
- Verify PO is enabled; maintenance is asynchronous and its serverless compute is billable.
- For modern managed tables, retain Databricks' default automatic file-size tuning settings.
2. External or legacy tables: manage maintenance based on workload
Where PO isn't available or is disabled:
- Auto Optimize: Optimized Writes and Auto Compaction help reduce small files during and after writes. They complement PO and can operate independently.
- OPTIMIZE: Start daily for heavily written tables or weekly for lower-volume tables, adjust based on file sizes and query performance.
- VACUUM: Weekly is a reasonable starting cadence, with the default 7-day retention or longer for time travel, long-running operations and streaming consumers.
- ANALYZE: Refresh statistics after significant changes or when query plans indicate a need.
3. Liquid clustering, partitioning or Z-ORDER?
For most new Delta tables, prefer liquid clustering (CLUSTER BY AUTO where supported). It adapts better to evolving query patterns and cannot be combined with traditional partitioning or Z-ORDER.
When does traditional partitioning still make sense?
Consider it when workloads have explicit operational boundaries, such as daily/monthly partition replacements or independently scheduled writers processing distinct date ranges.
However:
- Avoid over-partitioning. Databricks generally advises against partitioning below 1 TB and recommends at least 1 GB per partition.
- Partitioning isn't required for selective overwrites: REPLACE WHERE and, on supported runtimes, REPLACE USING also support unpartitioned tables.
- Separate partition boundaries don't guarantee conflict-free writes. Tables with identity columns, for example, don't support concurrent transactions.
- For existing Z-ORDER tables, continue using it only when query patterns justify it. PO doesn't automatically maintain Z-ORDER.
4. Monitor workload behaviour and use AI-assisted recommendations
Use Query Profile, Query Performance Insights, DESCRIBE HISTORY and file-pruning metrics to identify actual bottlenecks before applying optimizations.
Where available, Genie Code's Optimize capabilities can help analyze query performance and recommend SQL or compute improvements. Review recommendations and measure the impact before applying changes.
Overall : Start with UC managed Delta tables + Predictive Optimization + liquid clustering where supported. Use Auto Optimize and scheduled maintenance where automatic capabilities aren't sufficient, and traditional partitioning only when it provides a measurable operational benefit.
References:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
3 weeks ago
Hi @Khasim_1
Maintenance operations: OPTIMIZE and VACUUM
The frequency of maintenance operations should depend on the table’s ingestion pattern, update/delete volume, file sizes, query performance, and storage cost. Avoid running them on a fixed schedule without checking whether they are needed.
OPTIMIZE | Daily for frequently updated tables, or weekly for stable tables, as a starting point | Compacts small files and improves read performance |
VACUUM | Often weekly, or according to retention and compliance requirements | Removes eligible obsolete data files and reduces storage usage |
ANALYZE TABLE | After significant data changes, or when query plans indicate stale statistics | Helps the optimizer make better decisions |
Liquid clustering / data skipping | Evaluate for tables with large volumes and selective filters | Can improve data skipping without traditional partition management |
These are starting points, not universal rules. Monitor file counts, average file sizes, query latency, and write amplification to decide when to run maintenance.
Example: Optimize a Delta table
For tables queried frequently by customer and date, you can consider Z-Ordering if those columns are commonly used in selective filters:
optimize sales zorder by (customer_id, order_date);
Z-Ordering reorganizes data to improve data skipping. It consumes compute resources, so use it on columns that meaningfully reduce the amount of data scanned.
Example: Vacuum obsolete files
VACUUM sales;
VACUUM is a data-retention operation, not a substitute for compaction. It permanently deletes eligible obsolete data files, subject to retention rules. The default retention period in standard Delta Lake behavior is seven days. Do not shorten it casually: it can affect time travel, long-running readers, and concurrent operations. Confirm your recovery and compliance requirements before changing it.
2. Partitioning vs. Z-Ordering vs. Liquid Clustering
The key is to choose a data layout based on the table size and actual query patterns, rather than applying every optimization technique to every table.
Partitioning
Use for specific cases where partitions are large, reasonably low-cardinality, and frequently filtered.
Example: partitioning historical data by year or month when each partition contains substantial data.
Avoid partitioning by customer_id, transaction ID, or high-cardinality timestamps. Too many small partitions create overhead.
Z-Ordering
Use for existing, non-liquid-clustered Delta tables when queries repeatedly filter on selective columns.
Example: customer_id, product_id, or order_date.
Requires OPTIMIZE. Its effectiveness decreases as you add more Z-Order columns, so prioritize the most valuable filter columns.
Liquid clustering
Prefer this for many new Databricks Delta tables, particularly large tables with high-cardinality filters, changing query patterns, or skewed data.
Example: clustering a sales table by customer_id and order_date.
Clustering keys can be changed as query patterns evolve. Liquid clustering is incompatible with traditional partitioning and Z-Ordering on the same table.
Hope this solution clarifies all your open questions!