I was going through the following blog post on bring index performance when compared to btree on temporal data.
https://info.crunchydata.com/blog/postgresql-brin-indexes-big-data-performance-with-minimal-storage
I was wondering brin index usage is limited to temporal data like timestampz or could it be used for non temporal data like defining brin index on column let's say user_id .
When to use b-tree index vs brin index ?
Any pointers would be much appreciated . Thank you.
You can use a BRIN index for any data type that supports a B-tree index, that is, a data type with a total ordering (any two values can be compared).
But you can almost never use a BRIN index. They only work if the physical order of the rows in the table is either identical or exactly opposite to the logical order of the column values you are indexing.
So, using integers, the following table would be OK:
+--------------+----------------+------
|1 4 6 7 12 14 | 17 16 29 31 44 | ...
+--------------+----------------+------
8kB block 8kB block
Observe that the ordering is not perfect: the row with the value 17 is before the row with the value 16. But it is close enough not to disturb the minimum and maximum value over the block range (128 blocks by default).
However, if there is only a single outlier value per block range, the BRIN index would become useless.
So you can only use these indexes on insert-only tables where the rows are inserted with ever-increasing (or decreasing) values for the indexed column (typical for time series) or if you can artificially rewrite the table in the proper order (a data warehouse).