Databases & Indexing

"SQL or NoSQL" is the wrong first question. The useful first question is what access patterns must be fast, because that determines the data model, and the data model determines the store.

Relational databases give you joins, multi-row transactions, and a schema the database enforces. Reach for one by default: most applications have relational data, and being wrong about that later is cheaper than being wrong about consistency. Document stores fit data that is genuinely read and written as one blob. Wide-column stores are built for enormous write volume with known query patterns. Key-value stores are for lookups by exact key, and nothing else.

Indexes are the single biggest lever on read latency. A B-tree index turns a table scan into a logarithmic seek. The cost is real: every index slows writes and consumes storage, so an unused index is pure overhead. A composite index on (a, b) can serve queries filtering on a, or on a and b, but not on b alone — leftmost-prefix. A covering index contains every column the query needs, letting the database answer without touching the table at all.

Transactions and isolation. Read committed prevents dirty reads but allows a value to change between two reads in one transaction. Repeatable read stops that, and still permits phantom rows in some engines. Serializable is the only level that behaves the way people assume all of them do, and it costs the most. Most production defaults sit at read committed, which is worth knowing before you assume otherwise.