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!
3 weeks ago
Hi @Khasim_1
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.
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.
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.
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!
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.
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.
2. External or legacy tables: manage maintenance based on workload
Where PO isn't available or is disabled:
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:
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:
3 weeks ago
Hi @Khasim_1
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.
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.
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.
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!