CODEWITHSUNDEEP
databasedata-structuresindexingb-tree
neverMind
neverMind

Reputation: 1784

Databases: More questions about (B-Tree) indexes

I've been studying indexes and there are some questions that pother me and which I think important.

If you can help or refer to sources, please feel free to do it.

Q1: B-tree indexes can favor a fast access to specific rows on a table. Considering an OLTP system, with many accesses, both Read and Write, simultaneously, do you think it can be a disadvantage having many B-tree indexes on this system? Why?

Q2: Why are B-Tree indexes not fully occupied (typically only 75% occupied, if I'm not mistaken)?

Upvotes: 0

Views: 346

Answers (1)

Kaganar
Kaganar

Reputation: 6570

Q1: I've no administration experience with large indexing systems in practice, but the typical multiprocessing environment drawbacks apply to having multiple B-tree indexes on a system -- cost of context switching, cache invalidation and flushing, poor IO scheduling, and the list goes up. On the other hand, IO is something that inherently ought to be non-blocking for maximal use of resources, and it's hard to do that without some sort of concurrency, even if done in a cooperative manner. (For example, some people recommend event-based systems.) Also, you're going to need multiple index structures for many practical applications, especially if you're looking at OLTP. The biggest thing here is good IO scheduling, access patterns, and data caching depending on said access patterns.

Q2: Because splitting and re-balancing nodes is expensive. The naive methodology for speed is "only split with they're full." Given this, there's two extremes -- a node was just split and is half full, or a node is full so it will be next time. The 'average' between the cases (50% and 100%) is 75%. Yes, it's somewhat bad logic from a mathematics perspective, but it exposes the underlying reason as to why the 75% figure appears.

Upvotes: 1

Related Questions