Topic 0.1
What a Database Actually Does
In one line
A database management system stores data durably, answers queries over it, and lets many clients change it concurrently without corrupting it. Inside, a query engine turns SQL into a plan and a storage engine turns the plan into page reads and writes; relational and NoSQL systems make different choices at each layer.
Think of it like this
A library. The shelves are the storage engine (where books physically live and how they're arranged), the catalogue and librarian are the query engine (working out the fastest way to find what you asked for), and the checkout desk is the transaction manager (making sure two people can't borrow the same copy).
Key ideas
- 01
A DBMS gives you four things a file doesn't: durability (committed data survives crashes, via a write-ahead log), concurrency control (many writers without corruption, via locks or MVCC), a query language (declare what you want, not how to fetch it), and integrity (constraints the database enforces for every client).
- 02
An RDBMS (PostgreSQL, MySQL, SQL Server, Oracle) stores data in tables with a fixed schema, enforces relationships with keys and constraints, and supports ACID transactions across many rows and tables. Its strength is flexible querying: you can ask questions nobody planned for when the schema was designed.
- 03
"NoSQL" is a family, not one thing: key-value (Redis, DynamoDB), document (MongoDB), wide-column (Cassandra, Bigtable), graph (Neo4j), and search (Elasticsearch/OpenSearch). Most trade query flexibility or cross-record transactions for horizontal scale, a data model that fits a specific access pattern, or lower latency.
- 04
Architecture of a database server: a connection and protocol layer (one process per connection in PostgreSQL), a parser and planner/optimizer that builds an execution plan, an executor, and a storage engine with a buffer pool, WAL, and on-disk files (heap pages and B-tree indexes in PostgreSQL; an LSM tree in RocksDB or Cassandra).
- 05
The storage engine determines the performance shape: B-tree engines update pages in place and favour reads; LSM engines append and compact and favour writes. Phase 6 goes inside both.
Code & diagrams
-- Which PostgreSQL am I talking to, and how is it laid out?
SELECT version();
-- PostgreSQL 17.6 on x86_64-pc-linux-gnu, compiled by gcc ...
SHOW data_directory; -- /var/lib/postgresql/data
SHOW shared_buffers; -- 128MB (default, raise for real workloads)
CREATE TABLE users (id bigint PRIMARY KEY, email text NOT NULL UNIQUE);
SELECT pg_relation_filepath('users'); -- base/16384/16385 : the heap file on diskInterview problem
The problem
"Why not just store JSON files in S3?"
A startup stores each user's profile and orders as JSON files in object storage and asks why they would need a database. Explain what breaks as they grow, in terms of the four guarantees a DBMS gives.
When it breaks
Treating a cache or search index as the database
What you see
Data written only to Redis or Elasticsearch is lost on eviction, failover, or reindex; there's no way to rebuild it.
Fix & prevent
Name one source of truth per piece of data (usually the transactional database) and derive every other copy from it (Phase 12).
Explain it without notes
What is the difference between the query engine and the storage engine?
Name the four things a DBMS provides that a plain file does not.
Practice
Start PostgreSQL in Docker (docker run -e POSTGRES_PASSWORD=pw -p 5432:5432 postgres:17), create a table, and find its heap file with pg_relation_filepath.
Trade-offs
- ↔
Relational: flexible queries and strong integrity, harder to scale writes horizontally. NoSQL: scale and specialised access patterns, at the cost of ad-hoc queries or multi-record transactions.
Done when you can
I can describe the layers of a database server and what each guarantees.