3 ms·

Having clustered tables seems to be a prerequisite for using this kind of index. How does this work in practice? The CLUSTER command is a one-time reordering,

Having clustered tables seems to be a prerequisite for using this kind of index. How does this work in practice? 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?

cldellow 10y ago
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.
colanderman 10y ago
Either that, or an append-only (with respect to the index's sort criteria) access pattern (which will leave your data naturally clustered).
Jweb_Guru 10y ago
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.
dfsegoat 10y ago
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.

cldellow 10y ago

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.

colanderman 10y ago

Either that, or an append-only (with respect to the index's sort criteria) access pattern (which will leave your data naturally clustered).

Jweb_Guru 10y ago

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.

dfsegoat 10y ago

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.