Index structure and storage

[Anu Padki]

Hello,

I have a question on index structure and storage.
What is the structure of the index, does it store the entire  record for each index entry?
When does it allocate the space for the index?
Regards
- Anu

[Thomas Mueller]

Hi,

> I have a question on index structure and storage.

You probably mean the b-tree index (the default index, persistent).
There are other indexes as well (in-memory,...).

The b-tree index structure is:

- There is one BtreeHead that points to the root page. The head always
stays where it is.

- There is a number of BtreePages. Each page is eighter BtreeNode or a
BtreeLeaf.

- A node page links to other leaf pages or to node pages. Leaf pages
don't point to other pages (but may have a parent).

- The uppermost page is the root page. If nodes are added or deleted,
the root page may change.

> What is the structure of the index, does it store the entire record for
> each index entry?

Only the data of the indexed columns are stored in the index.

> When does it allocate the space for the index?

Do you mean the disk space? When creating new index pages.

What do you need this information for?

Regards,
Thomas

[Anu Padki]

I was just curious, during the load tests we found that the index files most of the times are as big as the data files and at time bigger.
That lead to the question.
Thanks for the info.
- Anu

[Thomas Mueller]

> I was just curious, during the load tests we found that the index files most
> of the times are as big as the data files and at time bigger.
> That lead to the question.

Why didn't you ask this question then? ;-) It can have many reasons,
for example there is usually a lot of empty space in b-tree indexes
(see also general b-tree documentation). Or there are many indexes.
Also the files (both data and index) don't grow in linear steps.

Regards,
Thomas