Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL Index Internals

pgcon.org

Post reply on HN
Print view View original

11–20 of 22 posts

Re: PostgreSQL Index Internals

#11

Post by pgaddict »

kbradero wrote: post #5

is there a book/documents about DB internals ? I have found some books about theory but not about an actual implementation.

Because there are many different implementations, with different trade-offs and design decisions. Also, the implementation-specific documentation is typically kept fairly close to the code, as the docs need to be kept in sync.

So for example if you need to know how indexes in PostgreSQL work, look into the READMEs in the proper directory:

b-tree indexes: https://github.com/postgres/postgres/blob/master/src/backend...

gin: https://github.com/postgres/postgres/blob/master/src/backend...

brin: https://github.com/postgres/postgres/blob/master/src/backend...

etc. The READMEs also include links to related papers etc.

Re: PostgreSQL Index Internals

#12

Post by rlau26 »

koolba wrote: post #2

To 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…

Same question could be asked of anything "written in Go"

Re: PostgreSQL Index Internals

#13

Post by koolba »

rlau26 wrote: post #12
koolba wrote: post #2

To 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…

Same question could be asked of anything "written in Go"

> Same question could be asked of anything "written in Go"

Haha. I 've noticed that as well.

It does beg the question, would a Postgres driver written in Go cause the HN front page to sink into a black hole of upvotes?

Re: PostgreSQL Index Internals

#14

Post by malisper »

ivoras wrote: post #4

The 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…

> 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.

A BRIN index only keeps summary information for a range of pages. Because of this, a BRIN can only be used for a bitmap scan and cannot be used for a normal index scan. This makes the set of queries where the planner will use the BRIN index really small. It is also possible, since it is such a common mistake, that you forgot to run ANALYZE and so the planner had no clue the data was in sequential order.

Re: PostgreSQL Index Internals

#15

Post by dguaraglia »

kbradero wrote: post #5

is there a book/documents about DB internals ? I have found some books about theory but not about an actual implementation.

If what you are looking is a general book (not necessarily just for Postgres) I can't recommend the "Use The Index, Luke"[1] tutorial, and the accompanying book "SQL Performance Explained" by Markus Winand, enough.

It doesn't go into a lot of relational theory, but it does provide a somewhat low-level description on how indices and database data are stored, and why you want to use different kind of indices in different scenarios.

[1] http://use-the-index-luke.com

Re: PostgreSQL Index Internals

#16

Post by kbradero »

pgaddict wrote: post #11
kbradero wrote: post #5

is there a book/documents about DB internals ? I have found some books about theory but not about an actual implementation.

Because there are many different implementations, with different trade-offs and design decisions. Also, the implementation-specific documentation is typically kept fairly close to the code, as the docs need to be kept in sync. So for example if you need to know how indexes in PostgreSQL work, look into the READMEs in the proper directory: b-tree indexes: https://github.com/postgres/postgres/blob/master/src/backend...…

hey thanks!, tho I agree with this, I was thinking, if there are books about kernel internals there may be some about db internals.

but Thanks for your links !

Re: PostgreSQL Index Internals

#17

Post by radiowave »

ivoras wrote: post #4

The 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…

Agreed. "Somewhat underdocumented" for, in places, some very large values of somewhat.

Re: PostgreSQL Index Internals

#18

Post by neilc »

kbradero wrote: post #5

is there a book/documents about DB internals ? I have found some books about theory but not about an actual implementation.

This is a good summary paper on the database internals and major architectural decisions: http://db.cs.berkeley.edu/papers/fntdb07-architecture.pdf

Re: PostgreSQL Index Internals

#19

Post by hyperpape »

dguaraglia wrote: post #15
kbradero wrote: post #5

is there a book/documents about DB internals ? I have found some books about theory but not about an actual implementation.

If what you are looking is a general book (not necessarily just for Postgres) I can't recommend the "Use The Index, Luke"[1] tutorial, and the accompanying book "SQL Performance Explained" by Markus Winand, enough. It doesn't go into a lot of relational theory, but it does provide a somewhat low-level description on how indices and database data are stored, and why you want to use different kind of indices in differe…

It's useful (probably more useful for most of us than any kind of deep dive into internals, at least for a first read), but I don't remember it going that deep into implementation decisions.

Re: PostgreSQL Index Internals

#20

Post by amag »

koolba wrote: post #13
rlau26 wrote: post #12

Earlier quoted context omitted.

Same question could be asked of anything "written in Go"

> Same question could be asked of anything "written in Go" Haha. I 've noticed that as well. It does beg the question, would a Postgres driver written in Go cause the HN front page to sink into a black hole of upvotes?

Only if it was written by PG...but last I heard he's a die hard LISP:er so we're probably safe..
Post reply on HN