Databases

An index, usually a B-tree, lets the database find rows without scanning the whole table, like the index at the back of a book. Indexes speed up equality lookups, range queries and sorting on the indexed columns. They cost storage, and every insert, update and delete must also update each index, so write-heavy tables should carry few indexes.

A composite index on (a, b) is sorted by a first, then b: it helps queries filtering on a, or on a and b, but generally not on b alone (the leftmost-prefix rule). Put equality columns before range columns. Tools such as EXPLAIN show whether a query uses an index or falls back to a full scan.

A transaction groups operations so they succeed or fail as one unit. ACID: Atomicity (all or nothing: a crash halfway rolls everything back), Consistency (the database moves from one valid state to another, with constraints intact), Isolation (concurrent transactions do not see each other's partial work, to the degree set by the isolation level), Durability (once committed, data survives crashes). The classic example is a transfer: debit one account and credit another in one transaction, so money is never lost halfway.

Weak isolation allows anomalies: a dirty read sees uncommitted data, a non-repeatable read sees a row change within one transaction, a phantom read sees new rows appear, and a lost update happens when two transactions read the same value and the second write silently overwrites the first.

Relational (SQL) databases such as PostgreSQL and MySQL store data in tables with a schema, support joins and strong ACID transactions, and suit structured, related data such as orders, customers and payments. NoSQL covers several models with different strengths: key-value stores (Redis, DynamoDB) for very fast lookups by key; document stores (MongoDB) for flexible, nested records; wide-column stores (Cassandra) for huge write volumes spread across many nodes; graph databases (Neo4j) for relationship-heavy queries such as friends of friends. NoSQL systems often trade joins, strict schemas or strong consistency for easier horizontal scaling and flexible shapes.

Choose by access pattern and consistency needs, not by fashion, and remember that modern SQL databases also scale well and store JSON.