Right, it is a prerequisite, and there are some complexities in practice.
You're right -- CLUSTER is a seriously expensive operation and locks the entire table, to boot. So we didn't really do that. Instead, we used pg_repack which is a great tool to do online clustering without the locks.
Additionally, we use pg_partman (another great tool!) to partition our data by month. Once a given month is finished, we can ensure the partition table for that month is ordered optimally and then never worry about it again.
This particular table is mostly appended to, so it's not so bad. If you were updating or deleting records, you'd have to run pg_repack more aggressively (or take similar actions) to get good performance.
A lot of data are naturally clustered for various reasons (particularly data with ids and timestamps). Also, note that BRIN indexes don't have to use ranges in the summary--for instance, for enums or other domains of small cardinality, they could use bitmaps or bloom filters. I believe that BRIN indexes will become much more useful when those alternatives are implemented.
We are using these on materialized views with an explicit order by to do that for us. Have found these most helpful for those types of scenarios (summary / reporting data where we can control refresh/population) - but obviously that is our use case and it may not be very efficient for others.
Re:BRIN Indexes in Postgres 9.5
by colanderman on Friday September 16, 2016 @04:22PM
(#12517056)
Either that, or an append-only (with respect to the index's sort criteria) access pattern (which will leave your data naturally clustered).
The CLUSTER command is a one-time reordering, and I assume a seriously expensive operation. But it doesn't affect any later insertions. So would one just run a CLUSTER periodically, or are there better ways to do this?