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.


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 👍🏻

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.