Accelerate search queries with full-text search indexes on Databricks

Summary

Databricks introduces full-text search indexes in Beta on Databricks Runtime 18.2 to accelerate substring and keyword queries on large open-format tables without changing their layouts. The indexes tokenize text columns into a compact lookup structure mapping tokens to matching rows; at query time, the engine uses it to identify candidate files and skip most of the table. They are maintained asynchronously, require no query hints, preserve complete results when stale by scanning indexed and non-indexed data as needed, and support Unity Catalog managed Delta and Iceberg tables on serverless and classic compute. A Trust and Safety team reported a substring search running more than 100x faster on a petabyte-scale table, while Liquid clustering remains complementary because it optimizes column-value filters rather than text within fields.

Takeaways

1. 1

   Full-text search indexes target substring and keyword queries, including high-cardinality lookups across multiple text columns.

2. 2

   Indexes are maintained asynchronously and selected automatically by the query engine, with no query hints required.

3. 3

   A Trust and Safety team achieved more than 100x faster substring-search performance on a petabyte-scale table.