Re: How to use the BRIN index properly?

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.

On Wed, Feb 8, 2023 at 12:27 PM Ron <ronljohnsonjr(at)gmail(dot)com> wrote:

> Is the data in your tables stored in natural correlation with those
> *three* columns? I'm dubious that can even happen.
>
> BRIN is best for *range queries* on tables who's data is added in the
> same order as the key in the BRIN index (for example, a BRIN index on a
> timestamp field in a log table where new records are always being appended
> in "timestamp" order).
>
> It would also be great for history tables where you can pre-sort the data
> by, for example, customer_id, and then put the BRIN on customer_id.
>
> On 2/8/23 13:58, Siddharth Jain wrote:
>
> our insertion order is of course != index order otherwise the question
> would have been trivial.
> we use postgres 14
>
> On Wed, Feb 8, 2023 at 11:51 AM Siddharth Jain <siddhsql(at)gmail(dot)com> wrote:
>
>> Hello,
>>
>> We have large tables with billions of rows in them and want to take
>> advantage of the BRIN index on them.
>>
>> Issues we are facing:
>>
>> - as I understand, BRIN index is useful only if the data is stored in
>> index order. As an example we want to create a composite BRIN index on 3
>> columns - integers and strings (varchar). How can we tell Postgres to store
>> data in index order as new records are inserted into the database?
>> - i understand that turning on autosummarize will keep the index
>> fresh and up-to-date as new records are inserted. is this correct?
>>
>> Thanks for your help.
>>
>> S.
>>
>
> --
> Born in Arizona, moved to Babylonia.
>