
Postgres indexes under the hood - rusbus
https://rcoh.me/posts/postgres-indexes-under-the-hood/
======
elchief
As an IT educator, I think it's better if people understand how indexes work
at a higher level.

Then you have something, even if you forget how an R-tree works

Think of them as being much like a book index. A book index is useful because
it's sorted, so you find something fast. It points to the pages the item is
on. Same for a db. The table isn't usually sorted, but the index is and points
to the required rows

If you're a book editor and the author adds, edits, or removes a section, you
need to update the index

Want to lookup a word by it's suffix? You're gonna need to scan the whole
book, or have an index of reversed words

Database indexes work the same way

~~~
saurik
I don't really understand the thought process. Clearly, to know how they work
at the low level you must also understand what they are (the superficial
understanding you are describing here). I don't see any harm from also knowing
how they work, and as someone who has been using PostgreSQL for something like
two decades I am extremely glad I do, so saying it is "better" to only know
what they are at a superficial level instead of also knowing how they work at
a deep level seems really wrong. Meanwhile, as this article notes in its first
sentence, most software engineers in this article's target demographic use
database indexes every day and we would be shocked if they didn't already have
the superficial level of understanding. Now, I haven't verified this is a good
article, nor is it clear to me we needed yet another article to explain this,
but you seem to be coming from the idea that this knowledge shouldn't even be
imparted?... from the perspective of an "IT educator"? :(

~~~
elchief
I'm not saying it shouldn't be imparted, but the high level knowledge is
better _first_

People seem to get lost in the details. I know devs that understand the
various index tree types, but don't understand why something is sargable or
why dropping indexes before a bulk copy is faster

~~~
Angostura
I simply assume that the article is written for people who already know what
an index is.

~~~
rusbus
OP here. That was in fact the case ;-)

------
ris
For even more detail,
[https://github.com/petergeoghegan/pg_hexedit](https://github.com/petergeoghegan/pg_hexedit)

With further blog posts [https://pgeoghegan.blogspot.co.uk/2017/11/pghexedit-
rich-hex...](https://pgeoghegan.blogspot.co.uk/2017/11/pghexedit-rich-hex-
editor-annotations.html) and
[http://pgeoghegan.blogspot.co.uk/2018/01/exploring-sp-
gist-a...](http://pgeoghegan.blogspot.co.uk/2018/01/exploring-sp-gist-and-
brin-indexes.html)

------
chaitanya
Any developer working with a database should understand how indexes work. A
good book for understanding relational db indexes is [http://sql-performance-
explained.com](http://sql-performance-explained.com). Highly recommended.

------
Walf
How I wish you Americans had just left them as "indices". Almost every time I
read it as the verb.

~~~
rusbus
OP here.

This is funny. On the rest of my blog post I use indices. For this post, I
went with indexes because that's what PG calls them in the docs.

I feel your pain.

------
swinghu
a great tutorial for distill postgresql'index,thanks

