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