Understanding What Databases Actually Do Under the Hood

A developer reflecting on system design realized they lacked a foundational understanding of how databases work internally, beyond just writing SQL queries. The article explores how databases handle large-scale data retrieval, explaining why full table scans become impractical at millions of rows. Indexes, such as those on a user_id column, allow databases like PostgreSQL to locate relevant rows efficiently without scanning entire tables. The piece also distinguishes between index types — including B-trees, Hash indexes, and LSM Trees — noting each suits different read and write workloads. The author concludes that a system designer's role is not to implement these structures manually, but to understand access patterns well enough to choose the right database features.