4 ms·
> In the early years you had the standard format of index b-tree that most database engines leveraged. Then quietly but confidently you started adding more. The
by pmontra 9y ago
> In the early years you had the standard format of index b-tree that most database engines leveraged. Then quietly but confidently you started adding more. Then came K-nearest neighbor, generalized inverted indexes (GIN), and generalized search-tree (GiST), only to be followed by space partitioned GiST and block range indexes (BRIN). Now the only question is which do I use?
I also wonder which index is appropriate for which case. I googled out this [1] and obviously all the chapters from 62 to 65 at [2]. I'm putting them on my reading list and posting them here just in case somebody else is asking the same question.
[1] https://devcenter.heroku.com/articles/postgresql-indexes https://devcenter.heroku.com/articles/postgresql-indexes
[2] https://www.postgresql.org/docs/current/static/internals.html https://www.postgresql.org/docs/current/static/internals.htm...
- Alex3917 9y agoB-tree for most ‘normal’ data. You mostly only need the other indexes for cases where your data isn’t just simple values, like if your column contains stemmed search terms or arrays or JSON. If you’re using ORM, then your ORM documentation should be telling you which index is the most appropriate if it isn’t a b-tree. There are also some corner cases where you can see a speed up if you have tons of data that’s being written in an append-only manner, but for the most part b-trees are the way to go for chars, ints, bools, etc.
- rjeli 9y agoI haven't found an explanation of when to use BRIN indexes that works with my brain. anyone have a good link?
- Alex3917 9y agoFrom Wikipedia, it sounds like the time to use it is when: - You have enough a huge amount of data (hundreds of millions or more rows) - You want to index a column whose values are never changed, and are monotonically increasing (e.g. a created_at datetime field).
- _wmd 9y agoI wrote http://pythonsweetness.tumblr.com/post/119568339102/block-range-brin-indexes-in-postgresql-95 http://pythonsweetness.tumblr.com/post/119568339102/block-ra... because I had the same problem, maybe see if it works for you
- rjeli 9y agoAwesome, thank you