Warming up the neural circuits...
By the end of this chapter you will:
Every application you've ever used — Instagram, Spotify, your bank — stores something and retrieves it later. The database is how. If you don't understand what happens inside the database, you will write code that works with 100 rows and collapses at 1 million.
Databases are not black boxes. They are carefully engineered systems that have been refined over 50 years. The difference between a developer who "uses a database" and one who understands the database is the difference between a 2-second page load and a 50-millisecond one.
Think of a database as a massive library with billions of books. Your query is like asking the librarian "find me every book about penguins published after 2015."
The database's storage engine is the librarian. Its indexes are the card catalog. The tables are the shelves. Every concept in this chapter is about helping you think like that good librarian.
Every database has a hierarchy. It's the same across PostgreSQL, MySQL, SQL Server, even MongoDB (with slightly different names):
Cluster (the server process, one machine or VM)
└─ Database (a named container, e.g. "quickbite_prod")
└─ Schema (a namespace inside the DB, e.g. "public", "billing")
└─ Table (a collection of rows with the same columns, e.g. "users")
└─ Row (one record, e.g. user 42)
└─ Column (one field in that row, e.g. "email")quickbite_dev, quickbite_prod).public. Large apps use schemas to organize: public.users, billing.invoices, audit.logs.users, orders, products.email, price, created_at.One database = one application. One table = one noun (users, orders, products). One row = one instance of that noun. One column = one property. If you're confused, reduce everything to nouns and properties.
A DBMS (Database Management System) is the program that sits between you and the disk. Every DBMS does four fundamental things:
When you call await prisma.user.create({ data: { email: "a@b.com" } }), here is what actually happens:
Your code (Node.js)
→ Prisma ORM (generates SQL)
→ PostgreSQL wire protocol (TCP, port 5432)
→ Query parser (validates syntax)
→ Query planner (figures out the fastest way)
→ Executor (does the work)
→ Buffer pool (in-memory cache of disk pages)
→ Disk (SSD, spinning rust, or cloud block storage)Every layer matters. When something is slow, you need to know which layer is the bottleneck.
Data is not stored as individual rows scattered across the disk. It's organized into pages.
-- This row is ~40 bytes. ~200 fit per 8 KB page. Fast.
CREATE TABLE narrow (id INT, name VARCHAR(20), score INT);
-- This row is ~500 bytes. ~16 fit per 8 KB page. 12x slower to scan.
CREATE TABLE wide (
id INT, name VARCHAR(100), bio TEXT, avatar_url VARCHAR(500),
settings JSONB, metadata JSONB, address
Every column you add to a table makes every full-table scan proportionally slower. A SELECT * on a table with 80 columns is 4–8x slower than the same table with 10 columns. Be selective — only SELECT the columns you actually need.
Every database has a storage engine — the low-level code that decides how rows are physically organized on disk. Three families dominate:
| Engine | Database | How it works | Best for |
|---|---|---|---|
| B-tree | PostgreSQL, MySQL/InnoDB | Rows stored in a balanced tree sorted by primary key. Point queries and range scans are O(log n). | General-purpose OLTP (your typical web app) |
| LSM (Log-Structured Merge Tree) | RocksDB, LevelDB, Cassandra | Writes go to an in-memory memtable, then flushed to sorted files on disk. Compaction merges files. | Write-heavy workloads (logging, time-series, IoT) |
| Heap | PostgreSQL (default for tables without clustered index) | Rows go wherever there's space. No inherent order. | Simple storage; rely on indexes for ordering |
B-tree deep dive (this is what PostgreSQL uses for indexes and InnoDB uses for everything):
A B-tree is a self-balancing tree where every node is one disk page. The root node contains pointers to child nodes. Leaf nodes contain the actual rows (in a clustered index) or pointers to rows (in a secondary index).
[Root page: keys 1–500]
/ \
[Page: 1–250] [Page: 251–500]
/ \ / \
[1-125] [126-250] [251-375] [376-500]Stripe runs one of the largest PostgreSQL deployments in the world. Their core database stores every charge, refund, customer, and card — trillions of rows across thousands of tables. A few things they do that every backend engineer should understand:
No for core writes. Stripe writes raw SQL for payment-critical paths. The ORM (Ruby's ActiveRecord) is fine for internal tools, but when a charge is being created, they need exact control over locking, isolation, and query shape.
Logical replication for read scaling. The primary Postgres instance handles all writes. Multiple read replicas handle reporting, analytics, and internal dashboards. This pattern — write to one, read from many — is the most common scaling strategy for PostgreSQL.
Idempotency keys in the database. Every Stripe call accepts an Idempotency-Key header. Stripe stores that key in a database table. If the same key comes in again, they return the previous result instead of re-executing. This prevents double-charges even when the network drops mid-request.
-- Stripe's idempotency pattern, simplified:
INSERT INTO idempotency_keys (key, response, created_at)
VALUES ($1, $2, NOW())
ON CONFLICT (key) DO NOTHING
RETURNING response;If the row already exists, the INSERT does nothing and returns the cached response. This is a database-level safety net that no amount of application code can replicate.
| Mistake | Why it's wrong | What to do instead |
|---|---|---|
Using SELECT * everywhere | Reads every column, wastes memory, breaks if columns change | List columns explicitly: SELECT id, email, name FROM users |
| No primary key on a table | PostgreSQL stores rows unordered; every query becomes a full scan | Every table gets a UUID or BIGSERIAL primary key |
| Storing everything in one table | Wide rows, slow scans, impossible to index properly | Normalize to 3NF by default; denormalize only with a benchmark |
| Ignoring the query planner | Slow queries get blamed on "the database" instead of the query | Use EXPLAIN ANALYZE — always |
| Using VARCHAR(255) for everything | Wastes space, no , Postgres still scans the full width | Use the narrowest type: VARCHAR(n), TEXT, or a domain |
| Not understanding connection pooling | Opening a new connection per request = 20–50ms overhead per query | Use PgBouncer or a built-in pool (Prisma's connection_limit) |
| Choosing MongoDB because "it's faster" | Every DB is fast with good schema design. Every DB is slow with bad design. | Choose based on data shape, not speed myths. Relational data → Postgres. Unstructured documents → MongoDB. |
statement_timeout. A runaway query with no timeout will hold locks and block everything else. In PostgreSQL: SET statement_timeout = '30s' or configure it per-role. In your connection string or pool config, set a query timeout.ALTER TABLE manually at 3 AM, nobody else knows the schema changed.log_min_duration_statement writes every query slower than N milliseconds to the log. Set it. Read it. Fix the slowest query every week.EXPLAIN (ANALYZE, BUFFERS) is your best friend. It shows you exactly how many pages were read, from where (disk vs. ), and how long each step took. Learn to read it.EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 42;
-- Look for:
-- "Buffers: shared hit=5" → from cache (fast)
-- "Buffers: shared read=120" → from disk (slow — consider an index)CREATE ROLE app_user WITH LOGIN PASSWORD '...'. Grant only SELECT, INSERT, UPDATE, DELETE on the tables it needs.DATABASE_URL in .env. Rotate passwords on a schedule.$1, ?) are non-negotiable. Never concatenate user into SQL strings. Prisma and Drizzle do this automatically — raw SQL via $queryRaw is where you must be careful.library, create a books table with columns id, title, author, published_year, and created_at. Insert 3 rows. Query them with SELECT id, title FROM books WHERE published_year > 2000;.EXPLAIN SELECT * FROM books; and note whether it says "Seq Scan". Explain what that means.SELECT * query. Now create a second table with 3 columns and the same data. Time the same query. Measure the difference.EXPLAIN (ANALYZE, BUFFERS) on a query with and without an index. Note the difference in "Buffers: shared read".src/include/storage/bufpage.h in the PostgreSQL source). Summarize in 3 paragraphs how a page header, item pointers, and tuples are laid out.What is the difference between a database and a table? A database is a container that holds schemas, tables, indexes, and users. A table is one collection of rows with a defined set of columns. One database typically contains many tables.
Why does SELECT * cause performance problems? It reads every column from disk, even columns you don't need. Wide rows mean fewer rows per 8 KB page, so the database must read more pages to scan the same number of rows.
What is a primary key? A column (or set of columns) that uniquely identifies each row in a table. PostgreSQL uses it to physically organize data and enforce uniqueness.
Explain how a B-tree index works at the page level. A B-tree is a balanced tree where each node is one disk page. Internal nodes store key ranges and pointers to child pages. Leaf nodes store the actual indexed values and row pointers. A lookup traverses the tree from root to leaf in O(log n) page reads. Range scans walk the leaf nodes sequentially.
When would you choose an LSM-based database over a B-tree database? When write throughput dominates read throughput — logging pipelines, time-series ingestion, IoT sensor data. LSM engines batch writes in memory and flush sequentially, achieving much higher write throughput at the cost of read amplification during compaction.
A query that used to take 5ms now takes 500ms. What do you check? First: EXPLAIN (ANALYZE, BUFFERS) to see if the plan changed (different index, seq scan). Check pg_stat_user_tables for dead tuples (needs VACUUM). Check pg_stat_activity for blocking locks. Check disk I/O metrics. If the plan changed, check if ANALYZE was run recently.
Databases are not magic. They store rows in pages on disk, organize them with B-trees or LSM trees, and expose them through SQL. Understanding the hierarchy (cluster → database → schema → table → row → column), the page layout, and the storage engine is what separates backend engineers who debug performance problems from those who guess.
Every decision — column width, index choice, connection pooling — has a direct line to disk I/O. The database is fast when you work with its internals and slow when you fight them.
SELECT * in production. Never skip EXPLAIN ANALYZE on a slow query.EXPLAIN ANALYZE show that EXPLAIN does not? Actual execution time and row counts, not just estimates.pg_stat_activity shows you. Active queries, their duration, whether they're waiting for a lock, the connected user, the application name.