I am comparing this mentally to Redshift's sort keys. It sounds like a BRIN index might approximate that behavior for the time dimension but not for a locale dimension (integer value, low cardinality, high volume).
Do BRIN indices support compound keys? Sounds like that may not even be desirable even if they do.
With a Postgres analytic table that has a time and locale dimension it sounds like a hybrid of a BRIN index on the time dimension and then partition on the locale, which probably manifests as an inherited table.
But that would require some contortions in the ETL layer to re-write the partitions in order when they are updated, and to create and drop the new partitions as necessary.
I don’t see any reason why it couldn’t support compound keys, assuming the combined min/max definition is in index column defined order. I have no idea if the Postgres implementation does though.
You’re also correct that a compound BRIN is questionably useful. If the first column in the key isn’t already an effective filter, you’re going to have to scan the whole table anyway in most cases- and if it is, then the cost of storing the min/max of subsequent columns is going to increase the overhead of the BRIN index in a way that seems unlikely to justify the benefit. It seems to me that the indexed columns would have to be mutually correlating in sort order for that to be useful (eg: created_date, created_time)
I am comparing this mentally to Redshift's sort keys. It sounds like a BRIN index might approximate that behavior for the time dimension but not for a locale dimension (integer value, low cardinality, high volume).
Do BRIN indices support compound keys? Sounds like that may not even be desirable even if they do.
With a Postgres analytic table that has a time and locale dimension it sounds like a hybrid of a BRIN index on the time dimension and then partition on the locale, which probably manifests as an inherited table.
But that would require some contortions in the ETL layer to re-write the partitions in order when they are updated, and to create and drop the new partitions as necessary.