hudi-agent commented on code in PR #19273: URL: https://github.com/apache/hudi/pull/19273#discussion_r3762011039
########## website/blog/2026-08-14-hudi-for-read-heavy-workloads-point-lookups-with-indexes.md: ########## @@ -0,0 +1,146 @@ +--- +title: "Point Lookups on the Lakehouse: How Hudi Indexes Accelerate Read-Heavy Workloads" +excerpt: "Partition pruning and min/max file statistics stop helping when queries filter on high-cardinality columns. Hudi's record-level, secondary, and expression indexes prune point lookups down to the handful of files that actually contain matching rows." +description: "How Apache Hudi's record-level, secondary, and expression indexes prune files at query planning time to accelerate point lookups and selective reads." +authors: [sivabalan] +category: deep-dive +image: /assets/images/blog/2024-03-30-record-level-indexing-apache-hudi-delivers-70-faster-point.png +tags: +- indexing +- performance +- querying +- data skipping +--- + +Analytical scans are not the only workload a lakehouse table serves. In many production query mixes, a large share of queries are needle-in-haystack reads: fetch one order by `order_id`, pull a user's profile by `user_id`, trace a request by `uuid`, list all events for one `customer_id`. These queries touch a few rows out of billions — and they are exactly where the lake's two standard pruning tools, partition pruning and min/max file statistics, stop helping. Apache Hudi answers this with queryable indexes maintained inside its [metadata table](/docs/metadata): a record-level index that maps each record key to its file group, secondary indexes that map non-key column values to record keys, expression indexes over transformed columns, and centrally stored bloom filters. At query planning time, these indexes prune the scan down to the few files that actually contain matching rows. The effect is not subtle: on a 400 GB table with 20,000 file groups, a record-key lookup published in [Hudi's indexing deep-dive series](/blog/2025/11/12/deep-dive-into-hudis-indexing-subsystem-part-2-of-2) dropped from 977 seconds to 12 seconds once the record index was in play. + +This post is the read-side companion to our [write-heavy comparison](/blog/2026/08/12/hudi-vs-delta-lake-for-write-heavy-workloads). Same approach: mechanisms first, and only benchmark numbers that have already been published, with dates and sources. + +## Why Selective Queries Are Hard on a Data Lake + +Lakehouse tables are laid out for scans: large immutable columnar files, grouped into partitions, described by file-level statistics. Every standard read optimization prunes at one of those granularities, and each one has a blind spot for selective predicates on high-cardinality columns: + +- **Partition pruning** only helps if the filter column is the partition column. Nobody partitions by `user_id` or `uuid` — the cardinality is far too high — so a point lookup on such a column matches *every* partition. +- **Min/max file statistics** help when values correlate with file layout. A filter on an ingestion timestamp prunes beautifully, because each file covers a narrow time range. But a random key like a UUID is uniformly spread: every file's min/max range spans nearly the whole keyspace, so every file "might" contain the value and nothing is pruned. Sorting or clustering the data can rescue statistics for *one* column, but a table can only be physically ordered one way. +- **Parquet footer bloom filters and page indexes** operate per file — the engine still has to open every candidate file to consult them, which at thousands of files is itself the bottleneck. + +The result is familiar to anyone who has run `SELECT * FROM events WHERE request_id = '...'` on a large table: a full scan of the key column across the table, minutes of compute, and (on scan-priced engines) a bill proportional to table size rather than result size. What the query needed was a database-style answer to "which files contain this value?" — an index. + +## How Hudi Indexes Serve Reads + +Hudi has maintained indexes since its inception, originally to make upserts and deletes fast — the write side of the same problem, as covered in the [write-heavy post](/blog/2026/08/12/hudi-vs-delta-lake-for-write-heavy-workloads). With the [multi-modal indexing subsystem](/docs/indexes#multi-modal-indexing), those same structures are consulted at *query planning time*. The indexes live as partitions of Hudi's metadata table — itself a Merge-on-Read Hudi table using an HFile format optimized for point lookups — and are updated transactionally with every commit, so index results are always consistent with the data. The read-relevant ones: + +- **Record-level index (RLI)** — an exact mapping from record key to file location, hash-sharded across file groups to scale to very large keyspaces. A query with an equality predicate on the record key (`WHERE uuid = '...'`) resolves directly to the file group holding that key; only that file is scanned. +- **Secondary index** — introduced in [Hudi 1.0](/blog/2025/04/02/secondary-index), an index on any non-key column. It maps secondary key values (e.g., `city`, `driver`, `customer_id`) to the record keys that carry them; the record index then maps those keys to file locations. Equality and `IN` predicates on indexed columns prune to exactly the files containing matches. +- **Expression index** — an index on a *function* of a column, in two flavors: column-stats over transformed values (e.g., `from_unixtime(ts)` for date filters on epoch columns) and bloom filters over transformed values for equality matching on high-cardinality columns. +- **Bloom filter index** — bloom filters for all data files stored centrally in the metadata table, so candidate files can be eliminated without touching each file's footer. +- **Column stats and partition stats indexes** — the min/max statistics story, but stored in the scalable metadata table and usable for range predicates and partition-level skipping. + +The planning flow for a secondary-index lookup, as described in the [indexing deep dive](/blog/2025/11/12/deep-dive-into-hudis-indexing-subsystem-part-2-of-2): the engine pushes the equality predicate down to Hudi's integration layer, the secondary index returns the matching record keys, the record index returns the enclosing file locations, and the engine plans a scan over just those files. Two point lookups against compact metadata replace a scan over the table — the same shape a database index lookup takes, running over lake storage. + +This combination of write-side and read-side indexing is one of the [things that distinguish Hudi architecturally](/blog/2025/03/05/hudi-21-unique-differentiators): the storage format deliberately spends extra space on indexes to serve both record-level mutation and selective reads, rather than optimizing for vanilla scans alone. + +## Using It from Spark SQL + +Index-accelerated reads are plain SQL. Create a table with the record index enabled (secondary indexes require it, along with a primary key and the `COMMIT_TIME_ORDERING` merge mode), then create indexes with `CREATE INDEX`: + +```sql +CREATE TABLE hudi_table ( + ts BIGINT, + uuid STRING, + rider STRING, + driver STRING, + fare DOUBLE, + city STRING +) USING hudi +OPTIONS ( + primaryKey = 'uuid', + hoodie.metadata.record.index.enable = 'true', + hoodie.write.record.merge.mode = 'COMMIT_TIME_ORDERING' +) +PARTITIONED BY (city); + +-- record index first; secondary indexes build on it +CREATE INDEX record_index ON hudi_table (uuid); +-- secondary index on a non-key, high-cardinality column +CREATE INDEX idx_rider ON hudi_table (rider); +``` + +Queries need no hints — equality predicates on indexed columns are pruned automatically during planning: + +```sql +-- point lookup on the record key, served by the record-level index +SELECT * FROM hudi_table +WHERE uuid = 'c8abbe79-8d89-47ea-b4ce-4d224bae5bfa'; + +-- selective filter on a non-key column, served by the secondary index +SELECT * FROM hudi_table WHERE rider = 'rider-B'; +``` + +In the walkthrough in the [SQL queries documentation](/docs/sql_queries), the second query scans one file instead of three after the index is created — on the toy table that is the whole point demonstrated; on production tables the pruning ratio scales with file count. Expression indexes cover predicates with inline transformations, and bloom-filter expression indexes handle equality matching where an exact mapping would be overkill: + +```sql +-- date filters on an epoch column +CREATE INDEX idx_column_ts ON hudi_table + USING column_stats(ts) OPTIONS(expr='from_unixtime', format='yyyy-MM-dd'); + +-- bloom-filter pruning for equality predicates on driver +CREATE INDEX idx_bloom_driver ON hudi_table + USING bloom_filters(driver) OPTIONS(expr='identity'); +``` + +`SHOW INDEXES FROM hudi_table` lists what exists, `DROP INDEX` removes one, and session settings such as `SET hoodie.metadata.record.index.enable=true` and `SET hoodie.metadata.column.stats.enable=true` control which indexes the reader consults — see [SQL queries](/docs/sql_queries) for the full set. + +## What About Trino? + +Honestly stated: index-based pruning through the record-level, secondary, and expression indexes is a **Spark SQL capability today**. When secondary indexes shipped in Hudi 1.0, [support was planned for Flink, Presto, and Trino](/blog/2025/04/02/secondary-index) in a subsequent release; that work rides on the fact that the indexes are engine-neutral storage structures — partitions of the metadata table on disk, not Spark-private state — so an engine integration implements the lookup against index data that is already there. Review Comment: 🤖 This section could add a brief currency caveat on where Trino index support actually stands. The secondary-index post you link says this engine support was "planned for Flink, Presto, and Trino in Hudi 1.1" — but 1.1 (and now 1.2) have shipped without record/secondary-index read pruning landing for Trino, and that same post's FAQ notes Trino dropped Hudi metadata-table reads in Trino 419, which is the mechanism index pruning would depend on. Framing it here as "converging on one engine story as connector-side index support lands" (and "on the roadmap the community has published" in the conclusion) reads as more imminent than the current state. A one-line note on where this stands today would keep readers from over-planning a Trino index-accelerated deployment. <sub><i>⚠️ AI-generated; verify before applying. React 👍/👎 to flag quality.</i></sub> -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
