B-tree Indexes on Delta Lake: When They Pay Off (and When They Don't) | DeltaForge
B-tree row-level indexes on Delta tables are new, and the honest question is not "can you" but "when should you." This walkthrough answers it with the engine's own numbers, not opinion.
The scenario is a card-payment ledger: twenty million authorizations across ~144 files, where a cardholder disputes one charge and you have to find that single transaction and refund it. The lookup key is a scattered, high-cardinality id, so Delta's per-file min/max statistics cannot prune it. Every claim is measured live with SHOW STATS ACTUAL, reading one honest signal, rows_consumed:
Point lookup (helps): with the index, rows_consumed is empty. The engine reads the one matching row by its pointer, with no row group to decode.
A range on the same key (no benefit): the matches are scattered across every file, so it stays a full scan, rows_consumed in the millions.
A lookup on a non-indexed column (no benefit): the index on the transaction id does nothing for a different column; full scan.
A broad rollup (no benefit): reading most of the table, the index is irrelevant.
COUNT (no benefit): answered from metadata, the index is never consulted.
The cost: DESCRIBE INDEXES shows one sorted leaf per row (twenty million, about 135 MB), built once and kept current on every write.
Keyed UPDATE (helps): a chargeback flips one transaction to refunded, and the index points the writer straight at the one file to rewrite instead of scanning every file to find it.
The rule it lands on: index a high-cardinality key you look up or update one row at a time, on a big, unsorted table. Skip it for ranges, broad scans, and counts, and for keys Delta already prunes for free.
Everything here is reproducible. It is the delta-row-index-payment-ledger demo that ships with DeltaForge, validated end to end.
0:00 The problem: one transaction in twenty million
0:45 What a B-tree index changes
1:01 Open the demo
1:16 Point lookup: the index serves one row
1:33 Where it has no benefit
2:21 The cost: one leaf per row
2:35 Keyed UPDATE: index-located rewrite
2:49 When to use a B-tree index
#DeltaLake #DeltaForge #DataEngineering #SQL #DatabaseIndex #BTree #Lakehouse #DeltaTable #DataSkipping #QueryPerformance #PointLookup #DeletionVectors #Analytics #DataPlatform #Fintech
Delta Lake, DeltaForge, row-level index, B-tree index, PGM index, CREATE INDEX, SHOW STATS, DESCRIBE INDEXES, rows_consumed, data skipping, point lookup, keyed update, deletion vectors, query performance, lakehouse, when to use an index