arrow_backBack to Phase 2
table_chart

Database
Internals

The database is not a black box. It's just a file system with opinions. Understanding B-Trees and LSM Trees tells you why Postgres is good for reads and Cassandra is good for writes.

The Heart: Storage Engines

When you run INSERT INTO users..., the database has to physically write bytes to the disk.How it organizes those bytes determines everything.

There are two dominant families of storage engines:

  • Page-Oriented (B-Trees): Modifies data in place.
  • Log-Structured (LSM Trees): Always appends new data.
hard_drive
The Disk

B-Trees (Read Optimized)

Used by: PostgreSQL, MySQL (InnoDB), Oracle, SQLite.

Organizes data into fixed-size pages (usually 8KB or 16KB). It's a wide, balanced tree.

The Trade-off

  • Pros: Fast Reads (LogN). Scanning ranges is efficient.
  • Cons: Writes are slower. Random writes cause "Page Splitting" and disk fragmentation.
Structure
Root
Node
Node
Leaf
Leaf
Leaf
Leaf

LSM Trees (Write Optimized)

Structure
Memtable (RAM)
arrow_downward
SSTable L1 (Disk)
SSTable L2 (Disk)

Used by: Cassandra, RocksDB, LevelDB, DynamoDB.

Log-Structured Merge-Tree. Writes go to memory buffer (Memtable). When full, flushed to disk as immutable file (SSTable). Background process merges files (Compaction).

The Trade-off

  • Pros: Blazing fast writes (Sequential I/O). High throughput.
  • Cons: Reads can be slower (might check multiple files). Compaction uses CPU.

SQL vs NoSQL

The decision is rarely about "SQL vs NoSQL" anymore. It's about access patterns and scalability needs.

table_rows

Relational (SQL)

  • • Strict Schema: Data must fit the mold.
  • • ACID: Strong consistency guarantees.
  • • Joins: Powerful query capability.
  • • Scaling: Vertical (bigger machine) is easy. Horizontal (sharding) is hard.
Use when: Data is structured, relationships matter (E-commerce, Banking).
data_object

Non-Relational (NoSQL)

  • • Flexible Schema: JSON, Key-Value, Graph.
  • • BASE: Eventual consistency (usually).
  • • No Joins: Data is denormalized.
  • • Scaling: Horizontal (add nodes) is built-in.
Use when: Scale > Consistency, unstructured data (Logs, Feed, Metadata).

Row vs Columnar

Row Oriented

Postgres, MySQL

[ID:1, Name:Alice, Age:30]
[ID:2, Name:Bob, Age:25]

Great for OLTP (Transaction Processing). Fetching one user's entire profile is one seek.

Column Oriented

Redshift, BigQuery, Cassandra

[1, 2, 3...] (IDs)
[Alice, Bob...] (Names)
[30, 25...] (Ages)

Great for OLAP (Analytics). "Calculate average Age" only reads the Age block. Compressible.