2 ms·
Not an expert, but have played with BRIN indices on a much older version of Postgres. Some of my experience may be misremembered or out of date. You are correc
by haimez 5y ago
Not an expert, but have played with BRIN indices on a much older version of Postgres. Some of my experience may be misremembered or out of date.
You are correct that they can be used for analytical workloads (but not exclusively, you still have other index types available and transactions, etc). They have very little performance overhead for the write path and small memory footprint compared to B-tree indexes.
However, they are only effective for improving read performance when filtering a table with a selective predicate, and even then- only if there’s some temporal relationship between the indexed column values and the time that the rows are inserted. Auto incrementing id and created_at columns work great at this.
So to your second point, no- I don’t believe there’s any specific storage layer awareness of them. However, Postgres inserts are typically at the “tail” of the table as a consequence of the MVCC implementation (although, tables with frequent deletes and updates can be confounding to the effectiveness of BRINs).
Remember though that the correlation needn’t be perfect to be very effective. If you can limit what would otherwise be a full table scan + filter evaluation to a scan + filter of a handful of pages that “might” contain matching rows, that can be a huge performance boost with a tiny cost of maintaining the index.
- mulmen 5y agoInteresting, thanks for sharing. 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.
- haimez 5y agoI 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)
- dragonwriter 5y ago> Do BRIN indices support compound keys? Yes. https://www.postgresql.org/docs/current/indexes-multicolumn.html https://www.postgresql.org/docs/current/indexes-multicolumn.... “Currently, only the B-tree, GiST, GIN, and BRIN index types support multiple-key-column indexes.”