Indexing: A blessing in vertical scaling

When it comes to managing large-scale databases, vertical scaling plays a crucial role in maintaining the performance of the system. However, as the size of the database grows, it becomes increasingly challenging to retrieve data efficiently, especially when it comes to complex queries. This is where Indexing comes into the picture.

You might have heard of Btree and B+ trees in advanced data structures, at that moment you might be wondering where we are gonna use them. Indexing is one such technique that extensively uses many such data structures Here is a nice tutorial if you aren’t familiar with Btrees: Btrees.

What does Indexing do exactly? To be honest, you are better off doing in-depth indexing than even thinking about any kind of sharding or partitioning. Let’s assume you have some millions of rows, and as the table growing larger, your queries are becoming slower. Indexing helps improve data retrieval, by storing the correct locations of the exact data required rather than scanning the whole table for each query. This in turn reduces the number of times the disk is accessed. The process involves indexing or we can say storing the location of the most frequent column values, so that database knows already where to find the expected results.

One of the most common step used during optimization of time-based queries is to trade space in order to get a quicker response. In addition to actual table size there is space dedicated for indexing which is stored as Btree, B+ tree, GIN, BRIN, etc.

Indexing should be enough, to add an edge to your queries if done right. Regardless, if you are still in a situation where more performance is required you can consider sharding and partitioning.

Going forward I will be adding some examples where Indexing is used in real life and we will climb our way from vanila database to database with indexing and will compare the results. I will attach the links as when I add the articles.

Thanks for reading till here. I hope you find my writing resourceful 😄.