Why should you not index all your database columns?

If you are aware of what a B-tree is, then this post will not help you much but if you don't you're on the right place.

In short, a B-tree is a data structure used to organize and store data in a database. When you create an index on a column in a database table, the database system creates a B-tree index for that column. This index allows the database to quickly find specific rows in the table based on the values in the indexed column.

While indexing can improve the performance of data retrieval operations, it's important to note that indexing all database columns is not always necessary or beneficial. In fact, indexing all columns can lead to a number of problems, including increased storage requirements, slower write operations, query optimizer issues, and maintenance problems.

To add, the slower performance when writing indexed data is that it goes back to the B-tree data structure which is actually not a binary tree but a balanced tree. A balanced tree should always be balanced ( hence the name ) that means that the deepest leaf node should always be on the same level or depth. It is to make sure that when a search goes through, the maximum time to search them is identical. By extension when writing a new nested data, the B-tree of the indices are being rebalanced too.

To avoid these issues, it's important to carefully consider which columns to index based on the specific needs of your application. For example, columns that contain low-cardinality data may not benefit from indexing, while columns that are frequently used in queries may benefit from indexing. By taking a strategic approach to indexing, you can improve the performance of your database without introducing unnecessary overhead.