Re: How to use the BRIN index properly?

For membership/equality predicates (also partial, for multiple columns) you
could take a look at bloom indexes: they are quite efficient in terms of
space footprint, you can even choose how long is the signature for each
entry and how is distributed among the columns.
https://www.postgresql.org/docs/14/bloom.html
g

On Wed, 8 Feb 2023 at 23:15, Siddharth Jain <siddhsql(at)gmail(dot)com> wrote:

> OK so in that case we are left with the B-Tree index.
>
> If the B-Tree index will be so large that it cannot fit in memory, then is
> it worth creating it at all? Are there any established patterns here?
>
> On Wed, Feb 8, 2023 at 1:21 PM Christophe Pettus <xof(at)thebuild(dot)com> wrote:
>
>>
>>
>> > On Feb 8, 2023, at 13:17, Siddharth Jain <siddhsql(at)gmail(dot)com> wrote:
>> >
>> > As I explained in my question that is indeed our dilemma. Our insertion
>> order will not be equal to index order. i.e., referring to your response:
>> >
>> > > who's data is added in the same order as the key in the BRIN index
>> >
>> > does NOT hold.
>>
>> A BRIN index is not a good choice in this case. You can CLUSTER the data
>> on an index, but that's a one-time operation: PostgreSQL will not maintain
>> that order after the CLUSTER. If the number of rows in the table at the
>> time of the CLUSTER is much larger than the number that are inserted
>> between CLUSTER operations, then a BRIN index might be useful, but
>> clustering a very large table is an expensive operation, and requires an
>> exclusive lock on the table while it is being done.
>
>