Strip away the UI, the animations, and the business logic from any app — Instagram, Zomato, your bank — and what's left is a database answering questions fast. Here's how it actually works.
What's actually happening here?
A database is organised storage that can answer questions quickly. Not just store data — answer questions. The difference matters enormously. A text file can store data. A database can answer "give me all orders placed by user 4821 in the last 30 days, sorted by value, in under 10 milliseconds" — across a table with 500 million rows.
The engineering inside a database exists entirely to make that last part true at scale.
The problem this solves
Without a database, you'd store data in files. Finding something means reading the entire file top to bottom every time. With 1,000 rows that's fine. With 500 million rows, reading the whole file for every query would take minutes — and your app would be unusable. Databases solve the retrieval problem by building index structures that let them jump directly to the data you need, the same way a book's index lets you find a topic without reading every page.
How it really works (step by step)
What happens when your app queries a database:
Your app sends a query — written in SQL (for relational databases) or a specific API call (for NoSQL). Example:
SELECT * FROM orders WHERE user_id = 4821 ORDER BY created_at DESC LIMIT 10.Query parser checks syntax — the database engine reads the query, checks it's valid, and builds an internal representation called a parse tree.
Query optimiser picks an execution plan — this is the clever part. The optimiser looks at what indexes exist, how many rows are in the table, and calculates the cheapest way to find the answer. It might choose an index scan, a full table scan, or a hash join between two tables.
Storage engine fetches the data — reads pages from disk (or from the in-memory buffer pool if recently accessed). A page is typically 8KB — the unit of I/O between disk and memory.
Results filtered and sorted — the raw rows matching the WHERE clause are filtered, sorted, and limited to what the query asked for.
Response returned — the result set travels back to your application over the database connection.
The part most tutorials skip
The buffer pool is why database servers need so much RAM. Every database keeps recently accessed data pages in memory so the next query doesn't hit the disk again. A disk read takes ~5ms. A memory read takes ~100 nanoseconds — 50,000× faster. The most important performance tuning for any database is ensuring your working set (the data your queries touch most often) fits in the buffer pool. When it doesn't, the database starts thrashing disk constantly — and latency explodes. Instagram's engineers famously sized their Postgres servers so the entire hot dataset fit in RAM before considering any other optimisation.
Real company doing this right now
Figma runs their entire collaborative design product on PostgreSQL — a database that started as a university research project in the 1980s. At 4 million users, their single Postgres instance was running out of headroom. Their scaling journey is a masterclass in how companies actually grow a database: first they scaled vertically (bigger machine, more RAM), then added read replicas for read-heavy queries, then added a connection pooler (PgBouncer) to stop thousands of app connections overwhelming the database, then split tables by domain into separate databases, and finally sharded the heaviest tables across multiple database instances by row range. Each step bought them months of runway before the next step was needed.
What breaks at scale?
Connections are expensive — and most engineers don't realise it until production. Each database connection consumes memory on the database server (~5–10MB for Postgres). An app with 100 servers each opening 50 connections = 5,000 connections = 25–50GB of RAM just for connection overhead, before a single query runs. This is why every production app runs a connection pooler (PgBouncer for Postgres, ProxySQL for MySQL) in front of the database — multiplexing thousands of application connections into tens of actual database connections. Figma hit this wall and added PgBouncer as one of their critical scaling steps.
The "aha" moment
A database index is a separate data structure that the database maintains automatically — it doesn't live in your table. Every time you insert or update a row, the database also updates every index on that table. Indexes make reads faster by making writes slightly slower. Understanding this trade-off is the foundation of all database performance work.
Your practical takeaway
Add indexes on every column that appears in a WHERE clause or JOIN condition — without an index, the database reads every single row in the table for every query. With an index on a 100M row table, the same query touches fewer than 100 rows.
Run EXPLAIN on slow queries before adding more hardware — the query execution plan shows you exactly what the database is doing. Most "slow database" problems are solved in 10 minutes with EXPLAIN, not by buying a bigger server.
Size your database server so hot data fits in RAM — check your database's buffer pool hit rate. If it's below 99%, your working set doesn't fit in memory and you're paying for disk I/O on every query. More RAM is almost always cheaper than the engineering time spent optimising queries.
Lesson 06 · Stage 2 — Storage Architecture · System Design Made Easy For All