Re: Embedding User-Defined Indexes in Apache Parquet Files
Here is a neat demo showing how to use this technique to add a Tantivy full text index as a user defined index into parquet files: https://github.com/jcsherin/datablok/tree/main/crates/parquet-embed-tantivy Quoting from the repo: Summary of findings: * The full-text index provides a 2X to 80X speedup for queries that return zero or very few matching rows. * When queries return more rows, a full table scan is better. The crossover point is ~0.04% of the total rows. * The Parquet file size increases by 80% when we index a single text column with an average length of 42 (3 - 13 words). * The geometric mean of speedup across all 36 benchmark queries is 1.90X. * For instructions on how to run the demo, see the How to Run section below. # Embedding a Tantivy Index In Parquet "Parquet tolerates unknown bytes within the file body and permits arbitrary key/value pairs in its footer metadata. These two features enable embedding user-defined indexes directly in the file—no extra files, no format forks, and no compatibility breakage." >From the DataFusion blog post: Embedding User-Defined Indexes in Apache Parquet Files This demo extends a Parquet file by embedding a Tantivy full-text search index inside it. A custom DataFusion TableProvider implementation uses the embedded full-text index to optimize wildcard LIKE predicates. For example: This demo extends a Parquet file by embedding a Tantivy full-text search index inside it. A custom DataFusion TableProvider implementation uses the embedded full-text index to optimize wildcard LIKE predicates. For example: SELECT id, title FROM t WHERE title LIKE '%dairy cow%' On Fri, Jul 18, 2025 at 1:21 AM Gang Wu wrote: > An orthogonal discussion on the Iceberg dev ML: > https://lists.apache.org/thread/xdkzllzt4p3tvcd3ft4t7jsvyvztr41j > > It proposes to add index support to Iceberg. One use case is to leverage > Parquet files to store inverted indexes. > > Best, > Gang > > > On Fri, Jul 18, 2025 at 1:32 AM Felipe Oliveira Carvalho < > [email protected]> wrote: > > > On Thu, Jul 17, 2025 at 8:18 AM Andrew Lamb > > wrote: > > > > > > 1. Parquet file format seems have index page [1], but I don't know > > who's > > > > > > The INDEX_PAGE type a fascinating point -- I am not sure what benefit > > > writing indexes using that annotation would be 🤔 > > > > > > > Currently I don't know whether we can have some "offcial" sample > index. > > > > > > I am not sure examples need to be "official" -- I suspect people would > be > > > interested in public open source examples of various types of indexes > > that > > > they could adapt to their own needs. > > > > > > > Having at least a central location with namespaced index names and links > to > > the description of their implementation would help more libraries > leverage > > these indexes. Because the lack of standardization means only the > libraries > > that produced the files can leverage them. Which defeats the purpose of > > using an open format somewhat. > > > > -- > > Felipe > > > > > > > Andrew > > > > > > On Wed, Jul 16, 2025 at 7:16 AM wish maple > > wrote: > > > > > > > Seems good. Personally I think > > > > > > > > 1. Parquet file format seems have index page [1], but I don't know > > who's > > > > using it. > > > > 2. Currently, Parquet only have single column bloom filter and column > > > > index. Maybe > > > > some kind of multi-column or other filter might work > > > > 3. Index can have different "levels", like Page Index is designed for > > > > "Page", and bloom > > > > filter / statistics for RowGroup. We can even define index for > > "file" > > > > > > > > Currently I don't know whether we can have some "offcial" sample > index. > > > > Personally I > > > > might be interested in some "sketches" > > > > > > > > Best, > > > > Xuwei Fu > > > > > > > > [1] > > > > > > > > > > > > > > https://github.com/apache/parquet-format/blob/master/src/main/thrift/parquet.thrift#L655 > > > > > > > > Andrew Lamb 于2025年7月16日周三 19:08写道: > > > > > > > > > I wrote a blog with Qi Zhu, Jigao Luo explaining how to embed user > > > > defined > > > > > indexes into Parquet files without needing any changes to the > > > format[1]. > > > > > > > > > > I am sorry for the somewhat shameless self promotion, but I think > > this > > > > > topic may be of general interest to the community in the context of > > > other > > > > > extensions to the format we have discussed recently. Techniques > such > > as > > > > > this widen potential usecases of Parquet without any need for > > > consensus > > > > or > > > > > timeline for ecosystem adoption. > > > > > > > > > > Andrew > > > > > > > > > > [1]: > > > > > > > > > > > > > > > https://datafusion.apache.org/blog/2025/07/14/user-defined-parquet-indexes/ > > > > > > > > > > > > > > >
Re: Embedding User-Defined Indexes in Apache Parquet Files
An orthogonal discussion on the Iceberg dev ML: https://lists.apache.org/thread/xdkzllzt4p3tvcd3ft4t7jsvyvztr41j It proposes to add index support to Iceberg. One use case is to leverage Parquet files to store inverted indexes. Best, Gang On Fri, Jul 18, 2025 at 1:32 AM Felipe Oliveira Carvalho < [email protected]> wrote: > On Thu, Jul 17, 2025 at 8:18 AM Andrew Lamb > wrote: > > > > 1. Parquet file format seems have index page [1], but I don't know > who's > > > > The INDEX_PAGE type a fascinating point -- I am not sure what benefit > > writing indexes using that annotation would be 🤔 > > > > > Currently I don't know whether we can have some "offcial" sample index. > > > > I am not sure examples need to be "official" -- I suspect people would be > > interested in public open source examples of various types of indexes > that > > they could adapt to their own needs. > > > > Having at least a central location with namespaced index names and links to > the description of their implementation would help more libraries leverage > these indexes. Because the lack of standardization means only the libraries > that produced the files can leverage them. Which defeats the purpose of > using an open format somewhat. > > -- > Felipe > > > > Andrew > > > > On Wed, Jul 16, 2025 at 7:16 AM wish maple > wrote: > > > > > Seems good. Personally I think > > > > > > 1. Parquet file format seems have index page [1], but I don't know > who's > > > using it. > > > 2. Currently, Parquet only have single column bloom filter and column > > > index. Maybe > > > some kind of multi-column or other filter might work > > > 3. Index can have different "levels", like Page Index is designed for > > > "Page", and bloom > > > filter / statistics for RowGroup. We can even define index for > "file" > > > > > > Currently I don't know whether we can have some "offcial" sample index. > > > Personally I > > > might be interested in some "sketches" > > > > > > Best, > > > Xuwei Fu > > > > > > [1] > > > > > > > > > https://github.com/apache/parquet-format/blob/master/src/main/thrift/parquet.thrift#L655 > > > > > > Andrew Lamb 于2025年7月16日周三 19:08写道: > > > > > > > I wrote a blog with Qi Zhu, Jigao Luo explaining how to embed user > > > defined > > > > indexes into Parquet files without needing any changes to the > > format[1]. > > > > > > > > I am sorry for the somewhat shameless self promotion, but I think > this > > > > topic may be of general interest to the community in the context of > > other > > > > extensions to the format we have discussed recently. Techniques such > as > > > > this widen potential usecases of Parquet without any need for > > consensus > > > or > > > > timeline for ecosystem adoption. > > > > > > > > Andrew > > > > > > > > [1]: > > > > > > > > > > https://datafusion.apache.org/blog/2025/07/14/user-defined-parquet-indexes/ > > > > > > > > > >
Re: Embedding User-Defined Indexes in Apache Parquet Files
On Thu, Jul 17, 2025 at 8:18 AM Andrew Lamb wrote: > > 1. Parquet file format seems have index page [1], but I don't know who's > > The INDEX_PAGE type a fascinating point -- I am not sure what benefit > writing indexes using that annotation would be 🤔 > > > Currently I don't know whether we can have some "offcial" sample index. > > I am not sure examples need to be "official" -- I suspect people would be > interested in public open source examples of various types of indexes that > they could adapt to their own needs. > Having at least a central location with namespaced index names and links to the description of their implementation would help more libraries leverage these indexes. Because the lack of standardization means only the libraries that produced the files can leverage them. Which defeats the purpose of using an open format somewhat. -- Felipe > Andrew > > On Wed, Jul 16, 2025 at 7:16 AM wish maple wrote: > > > Seems good. Personally I think > > > > 1. Parquet file format seems have index page [1], but I don't know who's > > using it. > > 2. Currently, Parquet only have single column bloom filter and column > > index. Maybe > > some kind of multi-column or other filter might work > > 3. Index can have different "levels", like Page Index is designed for > > "Page", and bloom > > filter / statistics for RowGroup. We can even define index for "file" > > > > Currently I don't know whether we can have some "offcial" sample index. > > Personally I > > might be interested in some "sketches" > > > > Best, > > Xuwei Fu > > > > [1] > > > > > https://github.com/apache/parquet-format/blob/master/src/main/thrift/parquet.thrift#L655 > > > > Andrew Lamb 于2025年7月16日周三 19:08写道: > > > > > I wrote a blog with Qi Zhu, Jigao Luo explaining how to embed user > > defined > > > indexes into Parquet files without needing any changes to the > format[1]. > > > > > > I am sorry for the somewhat shameless self promotion, but I think this > > > topic may be of general interest to the community in the context of > other > > > extensions to the format we have discussed recently. Techniques such as > > > this widen potential usecases of Parquet without any need for > consensus > > or > > > timeline for ecosystem adoption. > > > > > > Andrew > > > > > > [1]: > > > > > > https://datafusion.apache.org/blog/2025/07/14/user-defined-parquet-indexes/ > > > > > >
Re: Embedding User-Defined Indexes in Apache Parquet Files
> I am not sure examples need to be "official" -- I suspect people would be interested in public open source examples of various types of indexes that they could adapt to their own needs. I mean, considering the problems above, maybe it's hard to define some "official" indexes. Even parquet already has "BlockSplitBloomFilter", the indexes involves fast and might be hard to "make it a standard"( But maybe someone interested in this can have a try). Best, Xuwei Fu Andrew Lamb 于2025年7月17日周四 19:18写道: > > 1. Parquet file format seems have index page [1], but I don't know who's > > The INDEX_PAGE type a fascinating point -- I am not sure what benefit > writing indexes using that annotation would be 🤔 > > > Currently I don't know whether we can have some "offcial" sample index. > > I am not sure examples need to be "official" -- I suspect people would be > interested in public open source examples of various types of indexes that > they could adapt to their own needs. > > Andrew > > On Wed, Jul 16, 2025 at 7:16 AM wish maple wrote: > > > Seems good. Personally I think > > > > 1. Parquet file format seems have index page [1], but I don't know who's > > using it. > > 2. Currently, Parquet only have single column bloom filter and column > > index. Maybe > > some kind of multi-column or other filter might work > > 3. Index can have different "levels", like Page Index is designed for > > "Page", and bloom > > filter / statistics for RowGroup. We can even define index for "file" > > > > Currently I don't know whether we can have some "offcial" sample index. > > Personally I > > might be interested in some "sketches" > > > > Best, > > Xuwei Fu > > > > [1] > > > > > https://github.com/apache/parquet-format/blob/master/src/main/thrift/parquet.thrift#L655 > > > > Andrew Lamb 于2025年7月16日周三 19:08写道: > > > > > I wrote a blog with Qi Zhu, Jigao Luo explaining how to embed user > > defined > > > indexes into Parquet files without needing any changes to the > format[1]. > > > > > > I am sorry for the somewhat shameless self promotion, but I think this > > > topic may be of general interest to the community in the context of > other > > > extensions to the format we have discussed recently. Techniques such as > > > this widen potential usecases of Parquet without any need for > consensus > > or > > > timeline for ecosystem adoption. > > > > > > Andrew > > > > > > [1]: > > > > > > https://datafusion.apache.org/blog/2025/07/14/user-defined-parquet-indexes/ > > > > > >
Re: Embedding User-Defined Indexes in Apache Parquet Files
> 1. Parquet file format seems have index page [1], but I don't know who's The INDEX_PAGE type a fascinating point -- I am not sure what benefit writing indexes using that annotation would be 🤔 > Currently I don't know whether we can have some "offcial" sample index. I am not sure examples need to be "official" -- I suspect people would be interested in public open source examples of various types of indexes that they could adapt to their own needs. Andrew On Wed, Jul 16, 2025 at 7:16 AM wish maple wrote: > Seems good. Personally I think > > 1. Parquet file format seems have index page [1], but I don't know who's > using it. > 2. Currently, Parquet only have single column bloom filter and column > index. Maybe > some kind of multi-column or other filter might work > 3. Index can have different "levels", like Page Index is designed for > "Page", and bloom > filter / statistics for RowGroup. We can even define index for "file" > > Currently I don't know whether we can have some "offcial" sample index. > Personally I > might be interested in some "sketches" > > Best, > Xuwei Fu > > [1] > > https://github.com/apache/parquet-format/blob/master/src/main/thrift/parquet.thrift#L655 > > Andrew Lamb 于2025年7月16日周三 19:08写道: > > > I wrote a blog with Qi Zhu, Jigao Luo explaining how to embed user > defined > > indexes into Parquet files without needing any changes to the format[1]. > > > > I am sorry for the somewhat shameless self promotion, but I think this > > topic may be of general interest to the community in the context of other > > extensions to the format we have discussed recently. Techniques such as > > this widen potential usecases of Parquet without any need for consensus > or > > timeline for ecosystem adoption. > > > > Andrew > > > > [1]: > > > https://datafusion.apache.org/blog/2025/07/14/user-defined-parquet-indexes/ > > >
Re: Embedding User-Defined Indexes in Apache Parquet Files
Thanks Adam Indeed embedding the index directly in the metadata is another alternative (and common) approach and deserves mention in the post. I made a PR[1] to add that. Another tradeoff of putting the index information directly in the key/value metadata footer is that all parquet readers must read (and ignore) all the index bytes when decoding the footer. Depending on the size of the index this may exacerbate concerns of footer decode speed and bloat. Clearly I don't know what is best for all systems, but we felt it was important to make sure the options with Parquet were clearer to help people decide for themselves. Andrew [1] https://github.com/apache/datafusion-site/pull/90 On Wed, Jul 16, 2025 at 7:54 PM Adam Reeve wrote: > This was very interesting Andrew, thanks for sharing. We've done something > quite similar at G-Research in the past but embedded the index directly in > the key value metadata. That has the advantage of not needing an extra IO > operation to read the index after you've read the footer, and it was simple > to implement, but the index needs to be stored as a UTF-8 string so will > usually be less compact than a binary representation and have more > deserialization overhead. > > Cheers, > Adam > > > On Wed, 16 Jul 2025 at 23:16, wish maple wrote: > > > Seems good. Personally I think > > > > 1. Parquet file format seems have index page [1], but I don't know who's > > using it. > > 2. Currently, Parquet only have single column bloom filter and column > > index. Maybe > > some kind of multi-column or other filter might work > > 3. Index can have different "levels", like Page Index is designed for > > "Page", and bloom > > filter / statistics for RowGroup. We can even define index for "file" > > > > Currently I don't know whether we can have some "offcial" sample index. > > Personally I > > might be interested in some "sketches" > > > > Best, > > Xuwei Fu > > > > [1] > > > > > https://github.com/apache/parquet-format/blob/master/src/main/thrift/parquet.thrift#L655 > > > > Andrew Lamb 于2025年7月16日周三 19:08写道: > > > > > I wrote a blog with Qi Zhu, Jigao Luo explaining how to embed user > > defined > > > indexes into Parquet files without needing any changes to the > format[1]. > > > > > > I am sorry for the somewhat shameless self promotion, but I think this > > > topic may be of general interest to the community in the context of > other > > > extensions to the format we have discussed recently. Techniques such as > > > this widen potential usecases of Parquet without any need for > consensus > > or > > > timeline for ecosystem adoption. > > > > > > Andrew > > > > > > [1]: > > > > > > https://datafusion.apache.org/blog/2025/07/14/user-defined-parquet-indexes/ > > > > > >
Re: Embedding User-Defined Indexes in Apache Parquet Files
This was very interesting Andrew, thanks for sharing. We've done something quite similar at G-Research in the past but embedded the index directly in the key value metadata. That has the advantage of not needing an extra IO operation to read the index after you've read the footer, and it was simple to implement, but the index needs to be stored as a UTF-8 string so will usually be less compact than a binary representation and have more deserialization overhead. Cheers, Adam On Wed, 16 Jul 2025 at 23:16, wish maple wrote: > Seems good. Personally I think > > 1. Parquet file format seems have index page [1], but I don't know who's > using it. > 2. Currently, Parquet only have single column bloom filter and column > index. Maybe > some kind of multi-column or other filter might work > 3. Index can have different "levels", like Page Index is designed for > "Page", and bloom > filter / statistics for RowGroup. We can even define index for "file" > > Currently I don't know whether we can have some "offcial" sample index. > Personally I > might be interested in some "sketches" > > Best, > Xuwei Fu > > [1] > > https://github.com/apache/parquet-format/blob/master/src/main/thrift/parquet.thrift#L655 > > Andrew Lamb 于2025年7月16日周三 19:08写道: > > > I wrote a blog with Qi Zhu, Jigao Luo explaining how to embed user > defined > > indexes into Parquet files without needing any changes to the format[1]. > > > > I am sorry for the somewhat shameless self promotion, but I think this > > topic may be of general interest to the community in the context of other > > extensions to the format we have discussed recently. Techniques such as > > this widen potential usecases of Parquet without any need for consensus > or > > timeline for ecosystem adoption. > > > > Andrew > > > > [1]: > > > https://datafusion.apache.org/blog/2025/07/14/user-defined-parquet-indexes/ > > >
Re: Embedding User-Defined Indexes in Apache Parquet Files
Seems good. Personally I think 1. Parquet file format seems have index page [1], but I don't know who's using it. 2. Currently, Parquet only have single column bloom filter and column index. Maybe some kind of multi-column or other filter might work 3. Index can have different "levels", like Page Index is designed for "Page", and bloom filter / statistics for RowGroup. We can even define index for "file" Currently I don't know whether we can have some "offcial" sample index. Personally I might be interested in some "sketches" Best, Xuwei Fu [1] https://github.com/apache/parquet-format/blob/master/src/main/thrift/parquet.thrift#L655 Andrew Lamb 于2025年7月16日周三 19:08写道: > I wrote a blog with Qi Zhu, Jigao Luo explaining how to embed user defined > indexes into Parquet files without needing any changes to the format[1]. > > I am sorry for the somewhat shameless self promotion, but I think this > topic may be of general interest to the community in the context of other > extensions to the format we have discussed recently. Techniques such as > this widen potential usecases of Parquet without any need for consensus or > timeline for ecosystem adoption. > > Andrew > > [1]: > https://datafusion.apache.org/blog/2025/07/14/user-defined-parquet-indexes/ >
