Indexing is often the first thing that comes to mind when thinking about database optimization. Countless studies have verified the improved performance of indexing on individual queries. However, they often overlook real-world scenarios where indexing must be considered carefully. There are also downsides to adding indexes, namely write penalties caused by the added storage overhead and maintenance of those indexes. This study investigates the trade-offs of B-tree indexing in PostgreSQL, using different workload benchmarks, shining light on what circumstances causes an index to be effective. In addition to empirical findings, this study provides practical guidelines and general takeaways to support developers and database administrators in making informed decisions about when and how to use indexing effectively in real-world scenarios.

Using the benchmarking tool HammerDB, this research evaluates multiple index B-tree composite index configurations across various data sizes, using the TPC-H and TPC-C benchmark. The findings revealed counterintuitively that in both the analytical and transactional workload, adding indexes to the baseline configuration triggered a degradation in performance (even when indexes were adopted by the query planner) across most data sizes.

The TPC-C benchmark revealed that dataset size had a significant impact on the effectiveness of indexes. In the medium-sized datasets (~8-10 GB), both dropping non-essential indexes and adding indexes on read-heavy columns improved performance. This suggests that performance is not all about indexing, memory availability seem to play a vital role in determining the system’s overall performance.

This study demonstrates that indexing relational databases is a complex matter. Quality indexes clearly outperform mass-indexing, even in read-heavy environments. In some circumstances, it is reasonable to consider dropping indexes in order to improve the performance of a system.