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@xxxxxxxxx> 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@xxxxxxxxx> 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.
