Databricks Indexes Speed Up Text Search

Databricks is introducing beta full-text search indexes designed to tackle the performance bottleneck of text queries on large datasets. This new feature promises to accelerate searches by up to 100x or more on open-format tables without requiring modifications to existing table layouts or query syntax. This aims to unlock new use cases for data teams struggling with slow lookups across massive logs, security data, or compliance records.

The challenge is common: as data tables balloon into terabytes or petabytes, finding specific text strings becomes a slow, inefficient process. Traditional workarounds often involve duplicating data, building separate search systems like Elasticsearch, or complex table restructuring, all of which introduce overhead and complexity. Databricks' solution aims to integrate this capability directly into the data platform.

Full-text search indexes work by creating a compact lookup structure from tokenized text content within specified columns. At query time, the Databricks engine uses this index to pinpoint relevant files, drastically reducing the amount of data that needs to be scanned. This means substring and keyword queries, which previously might have scanned entire tables, now only access a fraction of the data.

How it works under the hood

These indexes are stored separately from the base table and are maintained asynchronously, ensuring that write performance to the base table remains unaffected. The Databricks query engine automatically identifies and utilizes available indexes for query optimization, eliminating the need for manual query hints. Crucially, even if an index is slightly out of sync with the base table, query correctness is guaranteed as Databricks will scan both indexed and non-indexed portions as needed.

The indexes support both Delta and Iceberg tables managed under Unity Catalog, and are compatible with both serverless and classic compute options. For those familiar with data organization, this feature complements, rather than replaces, techniques like Liquid Clustering. While Liquid Clustering optimizes physical data layout for equality and range filters, full-text search indexes specifically target the challenge of finding patterns within text fields.

Customer performance results

Early adopters have reported significant gains. One Trust and Safety team saw a substring search on a petabyte-scale table accelerate by over 100x, transforming interactive investigations from a chore into a practical reality.

Getting started

Full-text search indexes are currently available in Beta on Databricks Runtime 18.2. Users can create an index using a simple SQL statement. Databricks plans to integrate these indexes more deeply with Unity Catalog for automatic permission inheritance and introduce automatic maintenance through Predictive Optimization in upcoming releases, eliminating the need for manual index refreshes.