Introducing Indexes for the Lakehouse

Summary

This video introduces two new indexing capabilities coming to Databricks: Full-Text Search indexes for fast substring, regex, and IP-address matching with up to 20x query speedup, and Standard Secondary indexes for point lookups with 30–60x speedup on non-clustered columns.
Both index types work without changes to file format, data layout, or query syntax—the optimizer automatically detects and uses indexes once they are created, making adoption seamless for existing workloads.
Indexes complement Liquid Clustering and data skipping and are designed for high-selectivity workloads such as log analytics, SIEM, and UUID lookups where full table scans are currently unavoidable.

Introducing Indexes for the Lakehouse

Substring searches that take hours. Point lookups that scan terabytes. Pattern-matching queries that force you to maintain a separate tools alongside your lakehouse. These are problems that traditional databases solved decades ago — and that the lakehouse is now solving too. In this video, we'll introduce two new indexing capabilities coming to Databricks: Full-Text Search indexes for fast substring, regex, and IP-address matching with up to 20x query speedup; Standard (Secondary) indexes for needle-in-a-haystack point lookups with 30-60x speedup on non-clustered columns; We'll cover how each index type works under the hood, when to use which, and how they complement Liquid Clustering and data skipping. Then we'll show a live demo — creating indexes on real tables, running queries that previously took minutes in under seconds. If you're running log analytics, SIEM workloads, UUID lookups, or any query pattern where you're scanning far more data than you need to — this video is for you.
Talk By: Ivan Vezilić, Staff Software Engineer, Databricks ;

Chapters

FAQs

Why are point lookups and substring searches expensive in a Lakehouse today?

Lakehouse file formats like Delta Lake are optimized for large sequential scans—reading many rows quickly for aggregations and dashboards—but provide no built-in mechanism to jump directly to a specific row. Finding one record in a table with tens of billions of rows currently means scanning the entire table, which is wasteful for targeted lookups.

What is the difference between a Full-Text Search index and a Secondary index in Databricks?

Full-Text Search indexes accelerate substring, regex, and IP-address matching queries, delivering up to 20x speedup for pattern-matching workloads such as log analytics and SIEM. Secondary indexes target needle-in-a-haystack point lookups on non-clustered columns, delivering 30–60x speedup for queries like finding a specific UUID or transaction ID.

Do Databricks indexes require changes to existing queries or data layouts?

No—indexes are created once on a managed table and the query optimizer automatically detects and uses them without requiring query rewrites or changes to the underlying data layout or file format. This makes adoption low-risk for existing workloads where engineers want faster performance without restructuring their pipelines.

How do the new Databricks indexes complement Liquid Clustering?

Liquid Clustering organizes data physically to improve scan efficiency for common filter patterns, while Secondary and Full-Text Search indexes provide targeted acceleration for high-selectivity lookups that clustering alone cannot fully optimize. Stacking both techniques gives the query optimizer multiple strategies to minimize the amount of data read for any given query.

Full transcript

[00:09] Now, imagine uh you got a table with 10 bi- 10s of billions of row rows and you need exactly one of them. So, one UID, one transaction, one log line that says connection refused, right? Or headphones not working.
[00:22] So, uh today finding that single row usually means reading the entire table to get it, okay? Uh I'm Ivan. I work on the query engine behind the lakehouses. Uh and today I want to introduce
[00:34] something that we are super excited about. It's the indexes for the lakehouse. So, the new index formats will give you fast point lookups and high selectivity searches over your managed tables with
[00:47] no changes to your file format, uh no changes to your data layout, and no rewriting of your queries. So, you create the indexes once and the optimizer take it from there. Let me show you how.
[01:01] So, the plan is um I'm going to tell you first uh Okay. I'm going to tell you first what the problem with the point lookups uh is in the lakehouses, why it's hard.
[01:14] Uh then I'm going to show you two index types that we are actually adding, the full text search indexes and the secondary indexes. I'm going to give you a little bit of details of how they work, what they're for, and how fast they actually are. Uh I'll show you a quick pre-recorded
[01:28] demo, uh tell you how to get the hands on the indexes, and then I'll leave a couple of minutes for questions both in the sessions and afterwards. My team is also here, so any questions that you might have, uh you can ask us directly.
[01:41] Okay. So, what exactly is the problem that we're trying to solve, right? In the analytics world, the table doesn't stay small. Like, imagine this conference, how much data is generated.
[01:53] Like, for every cell phone, every image, every presentation, every talk, it's an imaginable amount of data. Uh and the formats for the lakehouse are brilliant at one thing. Uh reading a lot of data
[02:05] really fast, really quickly in big sequential scans. And that's exactly what you want if you're building a dashboard, if you're aggregating data, if you want to if you want one number that summarizes stuff that's in your
[02:17] data. But it's really the opposite if what you want and what you're looking for is just one row, right? A targeted seek. Uh give me the records with this ID. Give me this log line. Uh the formats that we
[02:30] have don't play to that strength at all. So, there's as of now no built-in way to jump straight into the row that you want. So, the engine does the only thing that it knows. It scans. So, to find
[02:42] that one needle, you have to read the whole haystack, right? Um and at the small scale, that's fine, right? Couple gigabytes not not a big deal. But at terabyte plus scale, that
[02:54] is slow and expensive. And not only that, you're paying uh to scan the data that you immediately just throw away. And that's the gap we're set out to close, right?
[03:06] To make that point lookups really fast and not as expensive uh as they are now. So, how are we going to do that? We're going to introduce indexes in Databricks
[03:18] to accelerate the point lookups. Um and we're going to not going to introduce one. We're going to introduce two new indexes. The first of these is the
[03:30] full-text search index. It's the search inside the text. So, you can find a substring, a keyword, an IP address buried in a message field without resorting to heavy full table regular
[03:44] expression scans. So, that's the full-text search index. The second is the classic secondary index. It's the point lookup on a simple value. So, find me the row for this UID,
[03:56] find me the customer ID, find me the transaction number, find me exactly one timestamp, right? Um and the secondary index is a lightweight alternative to re-clustering your whole
[04:09] table just to make one column fast. So, you don't have to do that. You don't have to re-cluster your data, you create a secondary index, and then point look ups on the simple query types get really fast. So, two indexes, one
[04:23] shared goal, accelerate the look up so you stop scanning everything, right? Okay, but when someone says, "Okay, we added the indexes." Okay, it's fair to
[04:35] brace yourself, right? So, indexes in a lot of systems comes with come with strings attached. So, it's a separate service that you need to run. It's a migration to a new file format. It's a reshuffle of your data, right? It's a
[04:48] rewrite of your queries to point to the index. Well, none of that exists here. There are no external or satellite processes, no format changes, your table stay exactly as you as they are. So,
[05:01] there's also no data reshuffling. Uh we're not rewriting your layout. And no query rewrites. You don't touch a single line of SQL if that's what you need if that's what you like to benefit from the index.
[05:14] So, this works on the same managed tables you already have on Delta and Iceberg. Uh you add an index and your workloads just get faster. That's the whole promise, like speed without making your data or queries somebody else's problem,
[05:28] okay? And because the indexes are part of the Lakehouse platform, they behave like the Lakehouse platform. So, the index data isn't something that your users see or
[05:41] query directly. It sits under the hood, it inherits the access policies of the source table, so you're not opening a side door around your governance, okay? If someone can't see a row today, they won't be able to see it with the index.
[05:54] The index doesn't change that. And you don't really need to babysit the index, right? Predictive Optimizer keeps your indexes current automatically. Uh there are no manual rebuilds, no cron jobs, no Oh, did someone remember to
[06:07] refresh the index? No, PO will do that for you, okay? So, now we have two new tools on top of what you already have. So, the natural
[06:20] question is which one and when? Uh and the rule is simple, right? Match the index to your access pattern. If you're searching for uh strings inside a text,
[06:33] so substrings, keywords, IP addresses, or you're digging into variants of struct columns, that's the full-text search index. If you're doing exact value lookups on high cardinality columns that aren't
[06:47] your clustering key, so finding a row by a UUID, a customer ID, a transaction number, that's the secondary index. And for your broad access pattern, so for the big range scans that we talked
[07:01] about that your dashboards lean on every day, that's still partitioning and clustering. Uh they're doing that as they've done before, indexes and clustering and partitioning, uh they
[07:13] complement each other. Uh the takeaway is that all of these techniques stack. So, you don't have to pick one over another. Uh a single table can be clustered by its main pattern. So, for example, here, let me see if this works.
[07:27] It does not work, sorry. But, the green thing is the partition by date, and then indexes are built on top of that. So, a single table can be clustered for its main query pattern, carry one or
[07:39] multiple secondary indexes for simple type lookups and multiple full-text search indexes on their text columns all at once. So, you match each tool to the job it's best at, and they complement
[07:52] each other rather than compete. Okay? If there's one thing one slide that I'd like you to take with you once this presentation ends, it's it's this one. They all work together, they stack together
[08:05] to give you fast lookups, fast scan, and fast aggregation. Now, we're going to go a little deeper. This is the full-text search index. As we said, this was fast substring,
[08:18] keyword, and IP address searches. Uh it works on strings, it works on variants, on structs, on map and array columns. So, semi-structured and unstructured data is searchable, too.
[08:30] So, once you ingest the variants, we can search on them immediately as we create the index. And once you create the index, it's done. The optimizer picks it up automatically. So, no hints, no
[08:43] special syntax, no query changes. So, if you're searching for text at scale, uh like in product searches, in job and real estate listings, in legally discovery, uh in theme. So, for example,
[08:57] our Lake Watch that we promi- heavily that was heavily prominent in uh this uh summit, Lake Watch runs on top of full-text search. Um
[09:09] and you know, increasingly popular is the retrieval for rags. So, you can pair the fast keyword searches with vector search. Uh and if you got a big text column that you're kind of sick of doing regular
[09:22] expression scan on, full-text search index is for you. So, here's what it looks like in practice, okay? Uh we also added a small family of functions. So, this one here,
[09:34] the ISEARCH, that's the case insensitive substring search on a column. So, here it's looking for a connection refused string in the message column, okay?
[09:50] And then we have the search function. And what this one what is doing here is case-sensitive multi-column word search. So, you can see we have two columns, we have message, we have error detail, and
[10:02] we're looking for user admin login. So, when you order these three appear in these two columns, the search will find it. Um and there's also IP mode, okay?
[10:14] Right here. Um that looks in that our tokenizer finds all the IP addresses in your data, indexes all of the all of those IPs, and then search function
[10:26] finds all the IPs in your in your in your data. So, this is all just SQL, right? It's your where clause. There's no new query language, there's no separate system to call out to. And but
[10:39] hey, we have the new functions we have to use. No, you can use contains, you can use like, you can use starts with, ends with. So, you don't have to change your query text at all. These are just for convenience. If you don't want to use
[10:52] them, fine. Okay. So, no changes to anything, no changes to SQL, nothing, then it must be slow, right? Uh on representative queries on 10
[11:05] terabytes with 27 billion rows, we're getting 250 times faster. But not only that, like that number is important. We're getting the latency down, but that second number is important as well. When you're paying
[11:17] for scans, indexes help you scan less data. So, with index, you're scanning 1,000 times less data than without the index. And your finance is going to like that, right? Because now we're scanning
[11:30] a lot less data with indexes. Okay. Now, this is a second one. Uh the secondary index. So, what it does is it looks for the exact value on a column that isn't a
[11:45] clustering column, but it could be. You can have partitioning clustering index on top of that. Uh it lets engine skip the data that doesn't match instead of scanning through it. Uh the this index is auto created by PO.
[11:57] So, you don't have to manage that. You don't even have to manage that, right? Uh the query optimizer then uses it automatically. No hints, no query changes. Uh you can use it for high cardinality lookups. So, think IDs where all every
[12:11] uh almost every value is unique and wide tables with a lot of columns. So, we have statistics only for 32 wider columns uh than that you can use the index. So, the key idea with this one is that it's for a lightweight to make one
[12:24] more column fast without paying the cost of re-clustering your entire table. Do it. Uh and then here is the example. That's the SQL. This is like the SQL that you would
[12:36] already write. It's a point lookup by a UID, right? Where ID equals a value narrowed to a partition date. So, the index will make the ID equals faster. Partition date
[12:50] will work as it did now. Uh you can also write search for multiple values with an in clause. And there's nothing special on this page, right? It's SQL. And that's the point. Like you
[13:02] write your queries naturally. The secondary index is uh what makes uh ID equals value fast and it stops being a full table scan. Um
[13:14] the numbers. The same idea. So, this is a TPCDS style benchmark. It's a UID point lookup over 10 terabytes. Uh 86 billion rows, okay? So, with the
[13:27] secondary index, the query is about the queries the representative queries are around 50% 50 times faster and we scan 140 times less data. So, it's a
[13:39] different shape of a problem from the text search, but it's the same conclusion. Stop scanning everything. For point lookups, stop scanning everything and both the queries get faster and you scan less data. Now,
[13:51] uh I'm going to show you just a quick demo that these numbers are not made up. Just to find my cursor. Here it is. Okay. So, we have two tables. Um there's one with no index, there's
[14:03] one with index. Oh, sorry. Up. Okay. One with with index, one without index. If we go ahead and describe it, we can see that it has some columns. The
[14:16] one with that we are focused on is the column number three. It's the records. It's a variant, right? It's It's for unstructured data. Um the other one has the same schema.
[14:36] And we're going to see how much rows we have in in both of these. So, this is 27 billion rows, okay? The other one 27 billion rows, exactly the same. Now,
[14:49] we're going to run a query uh looking for the uh this UID on the records column and we're not going to use the index, okay?
[15:10] Okay. I'm going to let it run now a little bit. Enjoying the talk, yeah? Awesome.
[15:22] I'm going to speed it up a little bit. We only have a couple minutes left. So, I'm going to speed it up to here. Oh, still running. Still running. Come on.
[15:34] Eight. Nine. Done. So, 4 minutes. That's not a bad result, right? It's a 10 terabyte table for a full table scan. It's not a bad result. And we scanned so we had some pruning, right? We scanned
[15:47] 7.3 terabytes and we did that in 4 minutes. Now, uh let's see it with the index. Okay, so where is the row? Where is the actual value? Here it is.
[16:00] It's a request ID that's that's in the the unstructured data. And then, let's run a query with index.
[16:21] Okay, we're going to wait a little bit here as well. Um no, it's done. Okay? That's >>
[16:33] you can see here like we scanned a thousand times less data. That's now gigabytes. That's 7 gigabytes. That's a thousand times less data. But, wait. This was faster, but was it fast enough? Like, we have the also this new
[16:46] thing called the uh real time. Okay. Run it again see it's not a fluke. There it is.
[17:00] But, we also have this new real-time thing, right? It's going to run faster.
[17:13] >> That's 1 second for 10 terabyte scan on uh unstructured data. Oh, we got them faster. We got sub-second when the queries are warm, right? Okay. So, uh that's the demo, right? Now, we're
[17:26] all excited about the indexes. When When can we actually try them? Well, both the full-text search indexes and the secondary index are available for the lakehouse and for lakehouse real time. Uh the full-text search indexes are now
[17:38] in beta. The link here that you see is for the blog. Uh I'm not sure how you'll get the slides, but if you get the slides, the blog is in there. Also, maybe ask us for the link after the presentation. The secondary indexes uh
[17:50] are in private preview. You can also ask us how to get the private preview on the secondary indexes. Okay. So, that's the indexes for the lakehouse. It's the fast point lookups
[18:02] and fast text search over your managed tables with no format changes, no layout changes, and no query rewrites. I'm Ivan, open for questions right now.

Learn more about the Databricks Data and AI platform.

The information provided herein is for general informational purposes only and may not reflect the most current product capabilities or configurations.