Database Internals

Database Internals covers: Database Internals, Fundamentals of Database Systems. Understand how databases work at the storage engine level — B-trees, LSM-trees, write-ahead logs, buffer management, concurrency control, and recovery. This is where you stop being a database user and start understanding the database as a system. Why adding an index speeds up reads but slows writes: every INSERT must update every B-tree index on the table. Why UUID primary keys cause B-tree fragmentation: B-trees store keys in sorted order, and UUIDs are random, causing page splits and wasted space (use uuid_generate_v7() for time-ordered UUIDs instead). Why VACUUM exists: PostgreSQL's MVCC creates a new tuple version for every UPDATE, and the old version becomes a "dead tuple" that must be reclaimed. Why idle_in_transaction connections are dangerous: they hold snapshots that prevent VACUUM from cleaning up dead tuples, causing table bloat. Understanding internals turns mysterious PostgreSQL behavior into logical consequences of design decisions.