Optimization recommendations on Databricks

Slow queries and high compute costs usually come from a few root causes: reading more data than a query needs, shuffling data between nodes, choosing a poor query plan, or rewriting files that didn't change. Databricks addresses many of these causes automatically on workloads ranging from large-scale ETL processing to ad-hoc, interactive queries. Other optimizations apply only when you turn them on or tune them for a specific workload.

This page starts with recommendations for most workloads, then groups optimizations by whether they run automatically or require you to configure them, and describes the problem each one solves. Most Databricks Runtime optimizations require Delta Lake, the default format for tables in Databricks.

Start with these recommendations

These choices deliver the biggest performance gains for most workloads. They also help many of the automatic optimizations on this page work at full effect.

• Use liquid clustering for table layout. Liquid clustering replaces table partitioning and ZORDER. It organizes data by the columns you frequently filter on, which improves data skipping and dynamic file pruning.
• Use Unity Catalog managed tables. Predictive optimization maintains managed tables automatically. See Unity Catalog managed tables for Delta Lake and Apache Iceberg.
• Use Photon-enabled compute. Predictive I/O requires Photon, and so does dynamic file pruning in MERGE, UPDATE, and DELETE statements. See What is Photon?.
• Enable deletion vectors. Predictive I/O uses deletion vectors to avoid rewriting whole files during updates, deletes, and merges, and row-level concurrency requires them. For Delta Lake tables, you must enable deletion vectors explicitly. See Deletion vectors in Databricks.
• Use the latest Databricks Runtime. New versions include the newest performance enhancements. Some optimizations require a minimum Databricks Runtime version or Photon-enabled compute. See each page for requirements.

Automatic optimizations

These optimizations are on by default. You don't need to enable them, but some deliver more benefit when your tables and compute meet certain conditions, such as using Photon or keeping table statistics current.

• Disk caching: Speeds up queries that repeatedly read the same remote Parquet and Delta Lake data files. Databricks caches copies of the files on the local storage of compute nodes so that later reads run locally. Databricks enables it automatically on worker instance types that support it.
• Dynamic file pruning: Skips files that can't contain matching rows in joins on Delta Lake tables. MERGE, UPDATE, and DELETE statements require Photon-enabled compute for dynamic file pruning.
• Low shuffle merge: Speeds up MERGE operations that update or delete a few rows in each file. Low shuffle merge processes unmodified rows without a shuffle and preserves their existing data layout.
• Adaptive query execution: Re-optimizes query plans while queries run, based on the actual size of the data. This fixes slow join strategies, too many small tasks, and skewed tasks that come from inaccurate estimates.
• Predictive I/O: Speeds up data scans and reduces the data rewritten during updates, deletes, and merges on Photon-enabled compute.
• Cost-based optimizer: Uses table and column statistics to choose efficient query plans, especially for queries with multiple joins. The cost-based optimizer depends on statistics, so collect them and keep them up to date.
• Predictive optimization: Runs OPTIMIZE, VACUUM, and ANALYZE on Unity Catalog managed tables automatically, so you don't need to schedule table maintenance. It also keeps the statistics that the cost-based optimizer uses up to date. To check whether it's enabled for your account, see Verify whether predictive optimization is enabled.

Optimizations you configure

These optimizations require you to turn them on, tune them, or change table configuration. Use them when a workload needs behavior that the defaults don't cover.

• Archival support: Use when cloud lifecycle policies move older table files to archival storage tiers to reduce storage costs. Archival support tells Databricks to ignore files older than a specified period, so the lifecycle policy doesn't break queries that only read recent data. This feature is in Public Preview.

• Range join: Use when joins use a point in interval or interval overlap condition. Databricks SQL optimizes range joins automatically. On other compute, tune range joins with join hints or session configuration.
• Isolation levels and write conflicts: Use when concurrent writes to the same table conflict, or when a workload needs stricter guarantees than the default WriteSerializable isolation level. Serializable isolation reduces throughput for concurrent writes. Row-level concurrency automatically resolves many conflicts between concurrent writes on tables that meet its requirements.
• Full-text search indexes: Use when queries search text columns for substrings or words with the search or isearch functions. The index skips files that can't contain matching rows. This feature is in Beta.

note

Bloom filter indexes are a deprecated data skipping feature. They add write overhead and are difficult to tune. If your tables have them, see Bloom filter indexes (deprecated) to replace and remove them.

Other performance features

• Higher order functions have built-in, optimized performance for many operations that don't have common Spark operators. Higher order functions perform better than user-defined functions.
• Databricks has built-in operators and special syntax for working with complex data types, including arrays, structs, and JSON strings.
• Spark SQL queries JSON strings without parsing them first.
• To control the size of the data files that writes produce, see Control data file size.
• For table design and maintenance guidance, see Best practices: Delta Lake.

Diagnose slow or expensive workloads

If a workload is slower or more expensive than expected and you don't know why, use the Spark UI to find the bottleneck before you change configuration. See Diagnose cost and performance issues using the Spark UI.