Indexing Trade-offs: Write Performance and More

Hello! Welcome back to our course.

In our last lesson, we focused on the practical skill of creating single-column and composite indexes. You learned the CREATE INDEX syntax and, more importantly, the strategic thinking behind ordering columns to maximize query performance. We saw how a well-designed index can make a query almost instantaneous.

Today, we'll explore the other side of that coin. As with many things in computing, there's no such thing as a free lunch. Indexes are incredibly powerful, but they come with costs. The goal of this lesson is to describe the trade-offs of using indexes, including write performance overhead. Understanding these costs is just as crucial as knowing how to create an index, as it allows you to make informed decisions about when and when not to use them.

The Core Trade-Off: Read Speed vs. Write Cost

The fundamental trade-off of indexing is simple:

Reads (SELECT) get faster.
Writes (INSERT, UPDATE, DELETE) get slower.

Think of a textbook. The index at the back lets you find a topic (a read) very quickly. But imagine you need to add a new section to the middle of the book (a write). Not only do you have to add the new pages, but you must also go back to the index and meticulously update it with all the new terms and their correct page numbers. This second step is the "write overhead."

Database indexes work the same way. Every time you modify data in a table, the database must also modify every index that covers that data.

SQL indexing best practices | How to make your database FASTER!

The video 'SQL indexing best practices' from CockroachDB provides a very clear and concise explanation of this core trade-off.

Please watch from 02:26 to 02:55. The speaker directly addresses why you can't just create an index for every possible query and explains the impact on write operations.

This raises the critical question you've been keen on exploring: why do writes get slower? To answer that, we need to look under the hood at the B-Tree data structure we discussed previously.

The Low-Level "Why": B-Tree Maintenance and Write Overhead

In our first lesson on indexes, you learned that they are typically implemented as B-Trees. A B-Tree maintains a sorted structure to enable fast lookups. This self-balancing act is the source of the write overhead.

Clustered B-Tree Index Structure — A quick recap: A B-Tree organizes data hierarchically into nodes. Traversing this shallow tree is much faster than scanning an entire table.

Let's examine what happens during write operations:

1. INSERT Operations and Node Splits:
When you INSERT a new row, the database also inserts a new key into the index's B-Tree. It traverses the tree to find the correct leaf node where the new key belongs. If that node has enough free space, the key is simply added.

The overhead occurs when the leaf node is full. To maintain the B-Tree properties, the database must perform a node split:

A new leaf node is created.
About half of the keys from the full node are moved to the new node.
A reference to the new node is added to the parent node.

This process can cascade upwards: if the parent node is also full, it too must split. In the worst-case scenario, a single INSERT can cause splits all the way up to the root of the tree. Each split involves disk I/O, which is a relatively slow operation.

Index Page Split Example — This diagram illustrates the mechanics of a page split. A full page (P) is split into two (P and Q), and the parent index structure must be updated to point to both. This sequence of operations is the source of the `INSERT` overhead.

2. DELETE and UPDATE Operations:

When you DELETE a row, its corresponding key must be removed from the index. This can sometimes cause a node to become too empty, triggering a merge with a sibling node or a redistribution of keys, which are also I/O-intensive maintenance operations.
An UPDATE operation is often treated as a DELETE followed by an INSERT from the index's perspective, especially if you are updating an indexed column.

To get a deeper technical understanding of these mechanics, the following article is excellent.

B-Trees: Why Every Database Uses Them

The article 'B-Trees: Why Every Database Uses Them' provides a fantastic, low-level explanation of how B-Trees handle writes.

Please read the sections titled 'B-Tree Node Splits' and 'B-Tree Node Merges'. Then, review the tables under 'B-Tree Performance Characteristics'. Focus on understanding the disk I/O costs associated with Insert and Delete operations (O(log n) disk reads + O(log n) disk writes).

The Practical Costs: Write Amplification, Storage, and Maintenance

Now that you understand the low-level mechanism, let's look at the practical consequences.

1. Write Amplification

This is a direct result of B-Tree maintenance. If you have a table with five indexes, a single INSERT statement doesn't result in one write operation. It results in six writes: one for the table itself, and one for each of the five indexes.

This "write amplification" has several effects:

It consumes more I/O bandwidth.
It generates more transaction log data (like WAL in PostgreSQL or the redo log in MySQL).
It can become a significant bottleneck in write-heavy applications.

Optimizing SQL Indexes in PostgreSQL and MySQL

The article 'Optimizing SQL Indexes in PostgreSQL and MySQL' offers a great real-world perspective on these costs, including a case study.

Read the section 'Trade-offs and Case Studies'. Pay close attention to the definition of 'write amplification' and the 'Case Study – Over-Indexing', which demonstrates the real-world performance impact of having too many indexes.

2. Storage Overhead

Indexes are not free in terms of space. They are data structures that live on your disk. The more indexes you have, and the larger the columns they are built on, the more disk space your database will consume. For very large tables, the space taken up by indexes can even exceed the space taken by the data itself.

3. Maintenance Overhead: Bloat and Fragmentation

Over time, after countless splits, merges, and updates, an index can become "bloated" or "fragmented." This means it contains a lot of empty space or pages that are not optimally filled. A bloated index is inefficient:

It takes up more disk space than necessary.
It requires more I/O to scan because the data is spread across more pages.
It can reduce the effectiveness of the database's memory caches.

This means indexes are not a "set-and-forget" feature. They require periodic maintenance, such as REINDEX or VACUUM in PostgreSQL and OPTIMIZE TABLE in MySQL, to rebuild them and reclaim wasted space. This maintenance itself is another operational cost to consider.

Test your understanding!

You are designing a database for a new application. You have two primary tables:

users: Stores user profile information (name, email, profile_picture_url). This table is read frequently when users view profiles but is updated infrequently (only when a user changes their details).
audit_log: Stores a log of every action a user takes in the application (user_id, action_type, timestamp). This table is written to constantly but is read very rarely, and only by administrators for troubleshooting.

For which table would you be more cautious about adding indexes? Justify your answer based on the trade-offs discussed in this lesson.

Show answer

You should be much more cautious about adding indexes to the audit_log table.

Justification:
The audit_log table is write-heavy. Every user action triggers an INSERT into this table. Each index you add would cause write amplification, meaning every single action would become a more expensive operation (one write to the table + one write to each index). Given that reads are rare, the performance benefit of an index would seldom be realized, while the performance cost of the write overhead would be paid constantly. You would only add an index here if administrators were experiencing unacceptable delays on the rare occasions they needed to query it, and even then, you'd choose the index very carefully.

Conversely, the users table is read-heavy. The cost of write overhead is paid only occasionally when a user updates their profile. The benefit of faster reads, however, is realized every time a profile is viewed. Therefore, adding indexes to columns used for lookups (like email) is likely a very good trade-off.

Conclusion: Making the Strategic Decision

Indexes are an essential tool for database performance, but they are not a silver bullet. The decision to add an index is always a cost-benefit analysis. You must weigh the significant performance gains for your SELECT queries against the clear costs in write performance, disk space, and ongoing maintenance.

Key Takeaways:

The Main Trade-Off: Indexes speed up reads (SELECT) at the cost of slowing down writes (INSERT, UPDATE, DELETE).
The Low-Level Cause: Write overhead is caused by the maintenance of the B-Tree structure, specifically node splits and merges, which require disk I/O.
The Practical Costs: Be mindful of write amplification (one logical write causing multiple physical writes), storage overhead (indexes take up disk space), and maintenance overhead (indexes can become bloated and require rebuilding).
The Strategic Guideline: Be generous with indexes on read-heavy tables and very conservative on write-heavy tables.

Preview of the Next Lesson:
Now you understand how to create indexes and what they cost. The next logical question is: "How do I know when my query actually needs an index?" In our next lesson, we will dive into the EXPLAIN command. You will learn how to use a query's execution plan to identify performance bottlenecks, such as a full table scan, which is the primary indicator that a good index is missing.

Recommended resources