Databases: query paths and storage engines

NoSQL covers multiple families, not one feature set. Key-value stores suit keyed access; documents group nested data; wide-column stores organize partitioned rows; graph databases emphasize relationships. SQL systems can also store JSON. Choose from query shape, invariants and operational needs.

A key-value store is a natural fit for simple keyed sessions. A graph model may fit multi-hop relationships. Neither label guarantees transactions, scale or speed. Measure the actual workload and verify durability and consistency requirements separately.

A full scan examines the table. A suitable B-tree index navigates to a key range, then reads matches. A rough comparison is O(N) versus O(log N + K), where K is returned entries. Index traversal and heap fetches still cost work; this picture is not a latency benchmark.

Indexes consume storage and add write work. They help only when their ordering and predicates support the query. If most rows match, sequential I/O can beat many random fetches. EXPLAIN shows the chosen plan; EXPLAIN ANALYZE executes it and measures actual work.

B-tree indexes maintain ordered pages, supporting equality and range scans. Updates modify affected pages and may cause splits; databases typically add logging and buffering. Every additional secondary index adds maintenance. Ordered lookup does not make writes free or define the transaction isolation level.

An LSM engine buffers writes and flushes sorted runs. Compaction merges those runs, rewriting data to control overlap and space. Reads may consult memory and multiple files; Bloom filters and indexes can reduce probes. LSMs trade read, write and space amplification through policy choices.

Write amplification = physical bytes written / logical bytes written. At 8?, 100 MB becomes 800 MB. Compare the same measurement boundary: device writes, engine writes and logged bytes differ. Neither B-tree nor LSM has one universal factor; workload, compaction, page size and compression matter.