PostgreSQL Index Internals
pgcon.org
PostgreSQL Index Internals
1–10 of 22 posts
Re: PostgreSQL Index Internals
#2To all the upvoters, did you actually read the linked slides or are you just upvoting because it has "PostgreSQL" in the name[1]? Interesting to see this many votes with zero comments.
It is a great set of slides by the way. The btree piece would be old news to anyone familiar with database related data structures but the detail on GIN, GIST, and particularly BRIN make for a good read. Seeing the physical layout for each of those gives you a much better understanding of the cost of maintenance and what situations would they'd be useful.
Also, if you happened to follow the recent Postgres/Uber thread, this gives a lot more context as to how updates to a row would cascade to many writes for each index.
[1]: ... which isn't a bad idea anyway because PostgreSQL is awesome!
Re: PostgreSQL Index Internals
#3To all the upvoters, did you actually read the linked slides or are you just upvoting because it has "PostgreSQL" in the name[1]? Interesting to see this many votes with zero comments. It is a great set of slides by the way. The btree piece would be old news to anyone familiar with database related data structures but the detail on GIN, GIST, and particularly BRIN make for a good read. Seeing the physical layout for…
At least for me, I have two different voting style:
- Yeah, this is pretty cool. Upvote!
- I've got to save this for later when I can really read it
PostgreSQL stuff tends to be pretty reliably good, and usually with an educational bent. So I'll tend to use the upvote as a bookmark to go back to later.
I gotta say, I don't really like slides, but I've yet to be disappointed by a PostgreSQL post. And usually, a high vote/comment ratio is a nice indicator of quality.
EDIT: formatting
Re: PostgreSQL Index Internals
#4The thing is, there's not much documentation on where each index type would be applicable, what their upsides and downsides are in particular situations.
For example, the hash index is (currently) not WAL-logged, which means it's not crash safe and not replicated. It also may have some concurrency issues, the resources are sparse on this one.
The BRIN index should be ideal for situations where there is a huge storage of data (TBs) inserted in sequential order and rarely queried - a BRIN index for such a thing would be only a few MB in size. I say should because when I tried it on 9.5.0, the query planner kept ignoring the index.
Both GIN and GIST indexes can be used for full text search (tsearch2), but GIN is faster for queries while GIST is smaller and faster for insertion.
The SP-GIST index is so underdocumented that, when I wanted to try it out in a project, I could not even understand how it works and does it work correctly as a radix tree for strings.
What's also somewhat underdocumented are the "operator classes" and which are and aren't implemented for different index types. I.e. a table of which index type can be used with which data type (and what exactly does such combination produce) would be great.
Re: PostgreSQL Index Internals
#5is there a book/documents about DB internals ? I have found some books about theory but not about an actual implementation.
Re: PostgreSQL Index Internals
#6To all the upvoters, did you actually read the linked slides or are you just upvoting because it has "PostgreSQL" in the name[1]? Interesting to see this many votes with zero comments. It is a great set of slides by the way. The btree piece would be old news to anyone familiar with database related data structures but the detail on GIN, GIST, and particularly BRIN make for a good read. Seeing the physical layout for…
Let me tell you one open secret on HN. If you see high upvote article on the front page and it stays there for a somewhat longer time ,chances are it is of great quality stuff.Most folks here can relatively separate the wheat from the chaff.
Re: PostgreSQL Index Internals
#7To all the upvoters, did you actually read the linked slides or are you just upvoting because it has "PostgreSQL" in the name[1]? Interesting to see this many votes with zero comments. It is a great set of slides by the way. The btree piece would be old news to anyone familiar with database related data structures but the detail on GIN, GIST, and particularly BRIN make for a good read. Seeing the physical layout for…
I watched the video, instead of the slides.
Re: PostgreSQL Index Internals
#8To all the upvoters, did you actually read the linked slides or are you just upvoting because it has "PostgreSQL" in the name[1]? Interesting to see this many votes with zero comments. It is a great set of slides by the way. The btree piece would be old news to anyone familiar with database related data structures but the detail on GIN, GIST, and particularly BRIN make for a good read. Seeing the physical layout for…
At least for me, I have two different voting style: - Yeah, this is pretty cool. Upvote! - I've got to save this for later when I can really read it PostgreSQL stuff tends to be pretty reliably good, and usually with an educational bent. So I'll tend to use the upvote as a bookmark to go back to later. I gotta say, I don't really like slides, but I've yet to be disappointed by a PostgreSQL post. And usually, a high v…
You might be interested in the "favorite" link under each submission title.
Re: PostgreSQL Index Internals
#9To all the upvoters, did you actually read the linked slides or are you just upvoting because it has "PostgreSQL" in the name[1]? Interesting to see this many votes with zero comments. It is a great set of slides by the way. The btree piece would be old news to anyone familiar with database related data structures but the detail on GIN, GIST, and particularly BRIN make for a good read. Seeing the physical layout for…
> did you actually read the linked slides?
Valid question. Yes, I did.
I wish the final slide (which summarizes what each index type is good for) had been at the beginning also.
Re: PostgreSQL Index Internals
#10Earlier quoted context omitted.
At least for me, I have two different voting style: - Yeah, this is pretty cool. Upvote! - I've got to save this for later when I can really read it PostgreSQL stuff tends to be pretty reliably good, and usually with an educational bent. So I'll tend to use the upvote as a bookmark to go back to later. I gotta say, I don't really like slides, but I've yet to be disappointed by a PostgreSQL post. And usually, a high v…
You might be interested in the "favorite" link under each submission title.
But I think the "favorites" are publicly view-able, isn't it? Is there any way to make them private?