CODEWITHSUNDEEP
sqlpostgresql
meysam
meysam

Reputation: 1794

Limitation of an index entry on PostgreSQL

I was reading this section of PostgreSQL documentation. I got to this sentence and I can't understand the concept behind this:

The only limitation is that an index entry cannot exceed approximately one-third of a page (after TOAST compression, if applicable)

I want to know the underlying reason for this fault. What's the "page" mentioned above? (is this the same page on the journal on for example ext4 file systems?). Why does an index entry have this limitation?

Is there any resource to give a comprehensive understanding of these concepts?

Update: Database Internals gives some deep insights about designing a database system and obviously also answers this question.

Upvotes: 1

Views: 85

Answers (2)

jjanes
jjanes

Reputation: 44323

A page is the same thing as a database block. The size of a database block is 8kB by default. You change it at compile time, but this is seldom done.

You can see it from the line Database block size: from the pg_controldata binary. Or from within a running server by using show block_size;.

The reasoning here is that you must be able to store enough information on the block/page so that it can have a fan-out factor greater than one.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270653

The page is the page referred to that holds the index data.

Basically, each page needs to have comparison values in order for the index to be useful. This guarantees that at least two or three values are on the page.

Upvotes: 0

Related Questions