--- title: "11 — Indexes and Transaction Isolation: Speed and Correctness Under Concurrency" uid: indexes-and-isolation tags: ["sql", "roadmap:sql", "query-optimization", "indexes", "concurrency", "b-tree", "managing-indexes", "isolation-levels"] excerpt: "An index trades write speed for read speed; an isolation level trades consistency for concurrency. The dials that turn 'it works' into 'it works fast and stays correct under load.'" date: 2026-08-13T03:27:36+0000 source: https://www.aveshina.my.id/en/blog/indexes-and-isolation --- "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. ```figure ``` ## 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](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](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](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](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/](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](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/](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](https://www.sqlservercentral.com/articles/isolation-levels-in-sql-server) ```quiz Q: What is the fundamental trade-off when adding an index? - Faster writes but slower reads - Faster reads but slower writes correct: 1 explain: An index speeds up lookups (reads) but must be updated on every INSERT/UPDATE/DELETE, so each index adds write overhead. Q: A query WHERE LOWER(email) = 'x' may not use an index on email because… - wrapping the column in a function can prevent the optimizer from using a plain index on that column - email columns cannot be indexed correct: 0 explain: Transforming the indexed column with a function defeats a standard index. A functional index on LOWER(email), or storing the value pre-lowercased, restores the lookup. Q: Which isolation level prevents dirty reads but still allows non-repeatable reads? - Read Committed - Serializable correct: 0 explain: Read Committed only exposes committed values (no dirty reads) but the same row read twice can change between reads. Repeatable Read or Serializable guard against that. Q: Why avoid SELECT * in production queries? - It returns every column, including bytes you don't need, costing storage and bandwidth - It is a syntax error correct: 0 explain: Naming only the columns you need (selective projection) avoids fetching data you'll discard, improving query efficiency. Q: Serializable isolation offers the strongest correctness guarantee but… - the lowest concurrency, because transactions effectively run one at a time - no concurrency at all — only one user may connect correct: 0 explain: Serializable makes transactions behave as if executed sequentially. That's safe but reduces throughput, so it's reserved for cases where anomalies would be real bugs. ```