Is it necessary to re-insert all the data in specific order, when I'm going to use the BRIN index?

All we need is an easy explanation of the problem, so here it is.

I’m going to use a BRIN index to replace the B-tree index on a date/timestamp column in Postgresql-11. I never used it before.

Since that a BRIN index can be more effective if the data is stored physically in the order that the column is being indexed.

I’m doubt about wether or not I should delete all the data, and then re-insert them in the order.
My data is being used for statical analysis, would not change.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

As with everything related to databases, the answer will be “it depends”.

BRIN indexes are efficient if the ordering of the key values follows the organization of blocks in the storage layer. In the simplest case, this could require the physical ordering of the table, which is often the creation order of the rows within it, to match the order of the key(s). Keys on generated sequence numbers or created data are best candidates for a BRIN index.

You mentioned that the index will he made in a date/timestamp column so, as long as these records are already stored sequentially in the table, you will not need to do a dump and reload. If the values are all over the place, then it would be in your best interest to export the data in the new order, drop the table, and reload.

BRIN indexes use far less storage compared to a normal B-Tree index, but they can be quite “lossy”, which will reduce their effectiveness. You may be able to tweak this by specifying a pages_per_range value like this:

CREATE INDEX idx_date_brin ON my_stats_db USING BRIN(transaction_date) WITH (pages_per_range = 32);

BRIN indexes store entries for a range of pages in a corresponding table. The larger the range of pages, the smaller the index. The smaller the index, the lossier it becomes.

Because indexes can be created and destroyed far more quickly than a complete table load (mist of the time), i would suggest playing around on a non-production system to see whether a BRIN is better than a B-tree for your use case and if your table data is sequential enough to not require a reload.

Hope this helps 👍🏻

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply