11 — Indexes and Transaction Isolation: Speed and Correctness Under Concurrency

"It works" stops being the bar the moment a hundred queries run at once, and that's where this cluster lives. Writing it down pinned one idea on each side: an index is a lookup structure that trades write speed for read speed, and an isolation level is the dial that trades strict consistency for concurrency. [1][5]

The framing that clicked is that both are trade-off dials, not features to maximize. Add every index and reads fly but writes crawl; remove them and writes fly but every read scans the table. Set isolation to SERIALIZABLE and every transaction is provably correct but they serialize (concurrency suffers); drop it to READ UNCOMMITTED and they run free but read garbage. Neither dial has a "best" setting — only a setting that fits the workload.

Indexes — the read/write trade-off

An index is a separate data structure that lets the engine find rows with specific column values without scanning the whole table, much like a book's index lets me find a term without flipping every page [1][2]. The default in most engines is a B-tree, which keeps values sorted for fast equality and range lookups.

The trade-off is unavoidable:

Reads get faster. WHERE email = 'x' becomes a logarithmic lookup instead of a linear scan of every row [1].
Writes get slower. Every INSERT, UPDATE, and DELETE must also update each index's structure, so each added index adds write overhead [1].

The implication: index deliberately, not greedily. An index earns its place when a column is queried often enough that the read savings outweigh the write cost. Columns used in WHERE, JOIN ... ON, and ORDER BY are the usual candidates; columns rarely filtered on are not.

Managing indexes

Index management is ongoing [3][4]:

Create indexes on columns that are frequently filtered, joined, or sorted.
Avoid over-indexing. Every index is a tax on writes; a table with ten indexes can become painful to mutate.
Prefer composite indexes when queries filter on multiple columns together — the column order in the index matters, leading with the most selective column.
Maintain them. Over time, indexes fragment. Engines provide REBUILD/REORGANIZE operations to restore efficiency as data changes [4].

The discipline is to index for the queries I actually run, review usage periodically, and drop indexes that aren't carrying their weight.

Query optimization — making indexes pay off

An index only helps if the query lets the engine use it. The levers that decide whether the optimizer picks the index or falls back to a scan [5][6]:

Filter selectively. A WHERE that matches 90% of rows gives the index nothing to prune; one that matches 1% makes it shine.
Avoid defeating the index. WHERE LOWER(email) = 'x' may bypass an index on email because the function transforms the column. A functional index on LOWER(email) — or storing emails pre-lowercased — restores the lookup.
Return only what you need. SELECT only_the_columns_i_render instead of SELECT * — selective projection cuts the bytes the engine has to assemble and ship.

The optimizer's plan is visible via EXPLAIN (covered in the performance notes), which is how I confirm an index is actually being used rather than assumed.

Transaction isolation — the consistency/concurrency dial

Once multiple transactions run at once, the question becomes how much do they see of each other's in-flight changes? The isolation level is the answer, with four standard settings from strictest to loosest [7][8]:

Read Uncommitted — a transaction can read uncommitted changes from others ("dirty reads"). Fastest, least safe.
Read Committed — only committed values are visible; the default in many engines. Prevents dirty reads but not "non-repeatable reads" (the same row read twice in one transaction can change).
Repeatable Read — once a row is read, it stays the same for the transaction's duration. Guards against non-repeatable reads but not "phantom reads" (new matching rows appearing).
Serializable — transactions behave as if they ran one at a time. Strongest guarantee, lowest concurrency — the engine must lock enough that concurrent transactions effectively serialize.

Each step down the dial trades a consistency guarantee for more concurrency. Read Committed is the pragmatic default for most app workloads; Serializable is reserved for cases — financial ledgers, inventory — where a phantom read would be a real bug, and the throughput cost is acceptable [7].

How I use this

Two habits, one per side. For indexes: I add them only when a real query needs them, lead composite indexes with the most selective column, and run EXPLAIN to confirm the optimizer actually uses what I created — an unused index is pure write overhead. For isolation: I leave the default (Read Committed) alone for ordinary CRUD and reach for Serializable (or explicit row locks) only where a concurrent anomaly would produce wrong money-like data. The discipline in both cases is the same: don't maximize the dial, fit it to the workload.

References

[1] YouTube, "SQL Indexing Best Practices," 2023. [Online]. Available: https://www.youtube.com/watch?v=BIlFTFrEFOI

[2] Stack Overflow, "What is an index in SQL?," stackoverflow.com, 2024. [Online]. Available: https://stackoverflow.com/questions/2955459/what-is-an-index-in-sql

[3] SQLServerCentral, "Introduction to Indexes," sqlservercentral.com, 2024. [Online]. Available: https://www.sqlservercentral.com/articles/introduction-to-indexes

[4] Microsoft Learn, "Reorganize and rebuild indexes," learn.microsoft.com, 2024. [Online]. Available: https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver16

[5] DeveloperNation, "12 Ways to Optimize SQL Queries," developernation.net, 2024. [Online]. Available: https://www.developernation.net/blog/12-ways-to-optimize-sql-queries-in-database-management/

[6] YouTube, "SQL Query Optimization," 2023. [Online]. Available: https://www.youtube.com/watch?v=GA8SaXDLdsY

[7] Cockroach Labs, "Everything you always wanted to know about SQL isolation levels," cockroachlabs.com, 2024. [Online]. Available: https://www.cockroachlabs.com/blog/sql-isolation-levels-explained/

[8] SQLServerCentral, "Isolation Levels in SQL Server," sqlservercentral.com, 2024. [Online]. Available: https://www.sqlservercentral.com/articles/isolation-levels-in-sql-server

Knowledge check · Question 1 of 5

What is the fundamental trade-off when adding an index?

Indexes — the read/write trade-off

The trade-off is unavoidable:

Reads get faster. WHERE email = 'x' becomes a logarithmic lookup instead of a linear scan of every row [1].

Writes get slower. Every INSERT, UPDATE, and DELETE must also update each index's structure, so each added index adds write overhead [1].

Managing indexes

Index management is ongoing [3][4]:

Create indexes on columns that are frequently filtered, joined, or sorted.

Avoid over-indexing. Every index is a tax on writes; a table with ten indexes can become painful to mutate.

Prefer composite indexes when queries filter on multiple columns together — the column order in the index matters, leading with the most selective column.

Maintain them. Over time, indexes fragment. Engines provide REBUILD/REORGANIZE operations to restore efficiency as data changes [4].

The discipline is to index for the queries I actually run, review usage periodically, and drop indexes that aren't carrying their weight.

Query optimization — making indexes pay off

An index only helps if the query lets the engine use it. The levers that decide whether the optimizer picks the index or falls back to a scan [5][6]:

Filter selectively. A WHERE that matches 90% of rows gives the index nothing to prune; one that matches 1% makes it shine.

Avoid defeating the index. WHERE LOWER(email) = 'x' may bypass an index on email because the function transforms the column. A functional index on LOWER(email) — or storing emails pre-lowercased — restores the lookup.

Return only what you need. SELECT only_the_columns_i_render instead of SELECT * — selective projection cuts the bytes the engine has to assemble and ship.

The optimizer's plan is visible via EXPLAIN (covered in the performance notes), which is how I confirm an index is actually being used rather than assumed.

Transaction isolation — the consistency/concurrency dial

Read Uncommitted — a transaction can read uncommitted changes from others ("dirty reads"). Fastest, least safe.

Read Committed — only committed values are visible; the default in many engines. Prevents dirty reads but not "non-repeatable reads" (the same row read twice in one transaction can change).

Repeatable Read — once a row is read, it stays the same for the transaction's duration. Guards against non-repeatable reads but not "phantom reads" (new matching rows appearing).

Serializable — transactions behave as if they ran one at a time. Strongest guarantee, lowest concurrency — the engine must lock enough that concurrent transactions effectively serialize.

How I use this

11 — Indexes and Transaction Isolation: Speed and Correctness Under Concurrency

Indexes — the read/write trade-off

Managing indexes

Query optimization — making indexes pay off

Transaction isolation — the consistency/concurrency dial

How I use this

References

What is the fundamental trade-off when adding an index?

Comments

Leave a Comment

0 Comments

11 — Indexes and Transaction Isolation: Speed and Correctness Under Concurrency

Indexes — the read/write trade-off

Managing indexes

Query optimization — making indexes pay off

Transaction isolation — the consistency/concurrency dial

How I use this

References

What is the fundamental trade-off when adding an index?

Comments

Leave a Comment

0 Comments