Search Postgresql Archives

Re: How to use the BRIN index properly?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux