How databases work comes down to four tricks: tables that organize data, SQL queries that describe what you want, indexes that skip the search, and ACID transactions that make every change all-or-nothing. Every like, payment and login you made today hit a database. Each one came back almost instantly, and none was lost.
That isn't luck. It's engineering. This guide follows a single piece of data from a blank table to a bank transfer that can't go missing. Along the way you'll see why spreadsheets break, how an engine picks the fastest path, and the four mistakes that cause most database pain.
- A database is organized data you can store, search and update, fast and reliably.
- Tables hold rows (records) and typed columns (fields), and a primary key makes each row unique.
- SQL says what you want. The query optimizer decides how to get it.
- B-tree indexes find one row among millions in a handful of steps.
- ACID transactions make sure a crash never leaves half a change behind.
- 0:00Intro
- 0:24Why spreadsheets break at scale
- 1:11Organized data, fast and reliable
- 1:57Tables, rows and primary keys
- 2:48SQL asks, the engine decides
- 3:30Indexes skip the search
- 4:13ACID: all or nothing
- 5:01One payment, start to finish
- 5:40Three types, one invisible job
- 6:27Four ways to break a database
- 7:08Everything in one breath
- 7:32Design them well, everything flies
Why can't you just use a spreadsheet?
A spreadsheet is perfect for your own budget. Now picture a ticket sale opening at noon. Thousands of people hit buy at the same second. A file can't serve all of them at once. They queue up, or worse, they collide.
Collisions are where things get ugly. Two people save at once, and one edit silently overwrites the other. Nobody gets a warning. The laptop crashes mid-save, and half the file vanishes. A half-saved file is a broken file.
A database is built for exactly these moments. It recovers cleanly even if the power dies. It settles conflicting edits by deciding who wins every time. The short version: files break, databases don't.
- Thousands at once: files can't serve many simultaneous users
- Crashes mid-write: a half-saved file is a broken file
- Conflicting edits: one save can silently erase another
What is a database, really?
A database is organized data you can store, search and update, fast and reliably. Think of it as the memory behind every app you use. Your app never touches the files on disk directly. Everything goes through one gatekeeper: the database engine.
Follow a single like. Your phone sends it to the app server. The app server asks the database engine to save it. The engine decides how, then writes it safely to disk storage. That one engine sits between your app and the disk and guards every byte.
The job has three parts. Store means data lands somewhere permanent, so your like is still there after a reboot. Search means finding one record among millions without reading them all. Update means changing data safely while other people are reading it.
- Store: data survives app restarts and server reboots
- Search: find one record among millions
- Update: change data safely while others read
Tables, rows and primary keys explained
Everything starts with tables. A table looks like a spreadsheet, but the engine enforces strict rules on it. Three words matter here: rows, columns and keys. The example below creates a users table. The id column is an integer and the primary key, while name and email are text. Then it inserts one row: id 42, Maya, and her email.
Rows are records. Each row is one thing, such as one user, one order or one payment, with all its details kept together. Columns are fields, and each has a type the engine enforces. Try to put text in a number column and the database refuses. Bad data stops at the door.
The primary key is the quiet hero. It's a value no two rows can share, like id 42. Because it's unique, the engine can always find exactly one Maya. Every speed trick that follows depends on that guarantee.
CREATE TABLE users (
id INT PRIMARY KEY,
name TEXT,
email TEXT );
INSERT INTO users VALUES
(42, 'Maya', 'maya@example.com');How does a SQL query actually run?
This is where it gets clever. In SQL you describe what you want, not how to get it. You ask a precise question, such as which user has id 42. You don't write loops, and you never tell the engine which files to open.
Watch a query for the name where id equals 42 run. First the engine parses the SQL and confirms the syntax is valid. Next it plans the query and chooses to use the id index instead of scanning everything. Then it reads the page that holds id 42. Finally it returns the row: name equals Maya.
The planning step is the query optimizer's job. It weighs the possible plans and picks the cheapest one. That split of labor is the secret. You say what, the optimizer says how, and with a smart plan the same question can run dramatically faster.
- Parse SQL → valid syntax
- Plan query → use the id index
- Read page → the page holding id 42
- Return row → name = Maya
What is a database index and why is it fast?
So how did the engine jump straight to id 42? Without an index, it would read every row from top to bottom. That's fine for ten rows. It's a disaster for ten million.
Most databases use a sorted structure called a B-tree, and each step down cuts the search space dramatically. The root compares id 42 and points to the branch covering 1 to 99. That leads to the leaf covering 40 to 49, which lands on row 42, Maya's record. It takes a handful of steps instead of a full scan.
It works like the index at the back of a book. You don't read every page to find one word. You check the back and jump straight there. The trade-off is that writes cost more, because every insert also updates the index. So index the columns you search on, not every column.
- No index: the engine scans every row
- B-tree: each level narrows the search
- Cost: every insert also updates the index
What does ACID mean in databases?
Fast lookups are only half the story. The other half is trust. Transactions group several changes into one unit, and ACID describes how those units behave. These four letters are why your bank balance never just disappears.
Atomicity: every step happens, or none do. Consistency: the rules always hold. Isolation: people working in parallel don't see each other's half-done work. Durability: once a change is committed, it survives power loss.
Two ideas make this concrete. All-or-nothing means a failed change is rolled back completely, so half a transaction never sits in your data. Saved means saved: the engine writes to a log before it confirms, so after a crash it can replay or undo cleanly. Reliability is designed in.
- Atomicity: all steps or none
- Consistency: the rules always hold
- Isolation: no peeking at half-done work
- Durability: committed changes survive power loss
One bank transfer, start to finish
Here's ACID protecting real money. Ana sends Ben a payment. Two rows have to change: Ana's balance goes down and Ben's goes up. They have to change together, so two separate updates get wrapped into one transaction.
The bank app opens the conversation with BEGIN. It debits Ana, then credits Ben. COMMIT seals both changes at once, and the database replies that the transfer is saved and written to disk.
Now the scary version. The server dies after the debit but before the credit. Without a transaction, Ana's money would simply be gone. With one, the database rolls the whole thing back. Money is never lost in the gap.
- BEGIN → open the transaction
- Debit Ana → Credit Ben
- COMMIT → both changes saved together
- Crash midway → full rollback
Relational vs NoSQL vs in-memory databases
Banks, airlines, hospitals, games and streaming services all run on databases, but not the same kind. There are three big families, each built for a different shape of work.
Relational databases such as PostgreSQL use tables with fixed columns and excel at joins and strict money rules, which is why banks rely on them. NoSQL databases such as MongoDB store flexible documents or keys and handle huge scale and changing data, like streaming catalogs. In-memory databases such as Redis keep data in RAM for instant reads and counters, such as leaderboards.
Pick by access pattern, not hype. Ask how you'll read and write the data, then choose the family that fits. You can also mix them freely. Many apps keep orders in PostgreSQL and cache hot data in Redis, and that combination is what makes big apps feel instant.
- Relational (PostgreSQL): fixed columns, joins, strict rules
- NoSQL (MongoDB): flexible documents or keys, huge scale
- In-memory (Redis): data in RAM, instant reads
Four common database mistakes to avoid
A database is only as good as the way you use it. When an app is slow or broken, the cause usually traces back to one of four mistakes. Every one is common, and every one is avoidable.
A missing index means every query scans the whole table. No backups means one bad delete wipes the data for good. SQL injection happens when unsanitized user input gets pasted into a query and runs as code. Choosing the wrong database type means you fight your tools every day.
The fixes are quick. Never paste user input into SQL. Use parameterized queries instead. Test your backups, don't just schedule them. Index the columns you actually search. Each fix takes minutes, and skipping one can sink a company.
- Missing index → full table scans
- No backups → one bad delete is permanent
- SQL injection → user input runs as code
- Wrong DB type → constant friction
The bottom line
- Tables organize data with rows, typed columns and a unique primary key.
- SQL asks the question, and the optimizer finds the fastest path.
- Indexes keep lookups fast but make writes slightly more expensive.
- ACID transactions make changes all-or-nothing, even mid-crash.
- Choose relational, NoSQL or in-memory based on how you read and write data.
- Always index what you search, test your backups and use parameterized queries.
Quick-fire questions
What is the difference between a database and a spreadsheet?
A spreadsheet is a file built for one person. A database puts an engine between your app and the disk that enforces data types, handles thousands of simultaneous users, settles conflicting edits and recovers cleanly from crashes.
Why are database indexes so fast?
Most databases use a sorted B-tree, and each step down the tree cuts the search space dramatically. Instead of reading every row, the engine reaches the record it needs in a handful of steps, much like using the index at the back of a book.
Should I add an index to every column?
No. Every insert also has to update each index, so writes get more expensive. Index the columns you actually search on.
What happens if a database crashes in the middle of a transaction?
The database rolls the unfinished transaction back, so no half-done change is left behind. Because the engine writes to a log before confirming, it can replay or undo work cleanly after a crash.
How do I prevent SQL injection?
Never paste user input directly into a SQL string. Use parameterized queries so the database always treats input as data and never runs it as code.
Should I use SQL or NoSQL?
Decide by access pattern. Relational databases suit joins and strict rules like payments, NoSQL suits huge scale and flexible data, and in-memory stores suit instant reads. Many apps combine them, such as PostgreSQL for orders and Redis for hot data.
