PostgreSQL Internals: Inside the Storage Engine

Post 2 gave you the data structures: B+Tree, B-Tree, LSM Tree - how they're shaped and the tradeoffs they do. This post traces an INSERT and a SELECT through both PostgreSQL and MongoDB, step by step, using the same orders schema throughout. PostgreSQL The architecture Before tracing any queries, map the terrain. PostgreSQL has four major components you'll encounter on every operation: shared_buffers is PostgreSQL's in-memory page cache; every read and write touches it first. The WAL is an append-only sequential log on disk; it's how PostgreSQL survives crashes. The heap file is where rows actually live, in 8KB pages, in roughly insertion order. The index files are separate B+Tree structures, also stored as 8KB pages, pointing into the heap. Writes go to the WAL first, then into shared_buffers, and eventually to the heap and index files on disk. Reads check shared_buffers first; if the page isn't there, it comes from disk. The write path: INSERT INTO orders INSERT INTO orders VALUES ('