Re: Embedding User-Defined Indexes in Apache Parquet Files

2025-09-25 Thread Andrew Lamb
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

2025-07-17 Thread Gang Wu
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

2025-07-17 Thread Felipe Oliveira Carvalho
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

2025-07-17 Thread wish maple
> 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

2025-07-17 Thread Andrew Lamb
> 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

2025-07-17 Thread Andrew Lamb
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

2025-07-16 Thread Adam Reeve
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

2025-07-16 Thread wish maple
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/
>