DBS:- Some Notes on DB Indexes

An Index is an additional structure that is derived from the primary data in the database. Considering this many databases allow to add indexes to the database that does not affect the contents of the databases, It only affects the performance of the query.

Now since indexes are the additional structure, maintaining them causes overhead. Indexes are known to increase the cost of writes. Lets dig in how?

Consider the world's easiest database, a log file. Don’t confuse the log files which refer to the Application Logs, where an application output text that describer’s what’s happening. Rather consider it as an append-only sequence of records file.

Now, in order to write to this append-only log file, we can simply add a new tuple (DB record) at the end of file and we are good to go. But if we have an Index defined, the database engine will have to calculate and update the index of this new tuple as well. This causes overhead.

Needless to say, removing index is rather easy and don’t affect the content of database.

This an important trade-off while designing systems:- Well chosen indexes increases speed of queries but each index slows down the writes. For this exact reason, database delegates the work of defining indexes to us the mighty developers. We must choose indexes manually, using knowledge of applications typical query pattern.

This blog is part of the blog series Developer’s bedtime stories. Please check my other articles with a prefix to the article name as DBS, short for Developer’s Bedtime Stories.

A wizard with no Magic.