Things you should know about how a database works.

When I started working as a Software Engineer in a very small team I already knew there are things waiting for me to stumble upon… a vast amount of tech and inner engineering is waiting for me to figure it out. I knew no one will be there to hold my hand and tell me where to look for what and how to navigate the traumatic and so technically verbose documentations that a college kid who is just starting out his carreer might already be questioning thier life choices.

But I keep learning and trying out new things, thanks to a very small team I got to learn to learn things by myself.

In this blog I will be jotting down all the things that I currently know about databases or data in general.

Earlier I was thinking of making this into a series but I think for now I will have to try to write it all in this single blog itself.

I will follow the following structure for avoiding making a mess of thoughts:

1. Introduction 

2. What is Data?
     Bits, bytes
     Encoding
     Real-world issues

3. Character Encoding (your experience)
     UTF-8 vs others
     Japanese encoding issues

4. How Databases Store Data
     Disk, pages, records

5. Core Concepts
     Tables
     Indexes
     Transactions
     Queries

6. Common Mistakes I Made
     Real stories

7. Resources & References

I will include all the extensive references and articles that you can read to get a better hang for all of it.

Let’s go.

What is Data?

At a very low level, everything in a computer is stored as bits (0s and 1s). These bits are grouped into bytes, and depending on how we interpret those bytes, they can represent numbers, characteds, or even entire files.

One of the first real-world problems I faced was understanding how text is stored. It sounds simple until it breaks.

Since I work in a Japanese company, I ran into encoding issues very early. I remeber My machine was set to English, and suddenly some characters would appear as unreadable symbols that were fine in my colleague’s machine. That’s when I learned that text is not just text… it depends on encoding.

We can think of encodings like UTF-8, UTF-16, and Shift-JIS define how bytes map to characters. If the encoding used to store data is different from the one used to read it, things break in very confusing ways.

Let’s take a simple character:

Character: A

• In ASCII / UTF-8:
• Stored as: 01000001 (1 byte)

Now let’s take a Japanese character:

Character: あ

• In UTF-8:
• Stored as: 11100011 10000001 10000010 (3 bytes)
• In Shift-JIS:
• Stored as: 10000010 10100000 (2 bytes)

Same character, completely different byte representation.

From now on you know that encoding is just a mapping between bytes and characters. If the reader and writer don’t agree on the mapping, your data turns into a mess.

Let’s now talk about how data is stored internally, but before that we first need to review how we interact with it at a higher level.

Tables (Logical View)

A table is the way we logically organize data in a database.

It consists of:

• Columns -> define the structure (schema)
• Rows -> actual data entries

From a developer’s perspective:

• We think in terms of tables and rows

But internally:

• Rows -> stored as records
• Records -> stored inside pages

How Databases Actually Store Data?

At a high level, a database is just a highly optimized way of storing and retrieving data from disk.

Under the hood, it’s not that different from files on our system. The difference is just that it is origanized very efficiently.

Data is Stored in Pages (Not Rows Directly)

Common misconception between begginers is that data is stored in rows one by one while this is not the case.

Instead, Databases uses fixed-size blocks of disk called pages (usually 4KB-16KB).

We can think of it like this:

• Disk -> divided into pages
• Each page -> containes multiple records (rows logically)

This helps in efficient reading and writing because disks operate better with chunks of data rather than individual records.

Inside a Page

A page typically contains:

• Metadata (page info)
• Row data (multiple Records More about records in a bit)
• Pointers (to track where rows are stored)

I don’t think you need to go too deep here… just a hint at the structure will work fine unless you are making a database yourself.

Now the question is Why this Matters?

Let’s say you run:

select * from users where id = 101;

The database:

1. Finds which page contains the data
2. Loads the entire page into memory
3. Extracts the required row

It never reads “just one row” from disk.

This is why:

• Fetching 1 row vs 10 rows from the same page -> almost same cost
• But fetching from different pages -> more expensive

Common misconceptions that I had earlier:

1. Query returns only the data I asked, so only that much is read.

Records

A record is the actual stored representation of a row inside a database.

While we usually think in terms of “rows” in a table, internally the database stores them as records inside pages.

A record typically includes:

• The actual data (column values)
• Metadata (like row size, flags)
• Sometimes pointers (for variable-length data)

Suppose we have a table of users with id and name as columns.

A row like: (1, “Abhay”)

Might be stored internally as follows:

[header][id=1][name_length=5]["Abhay"]

Always remember this:

Data is stored as records -> inside pages -> on disk

Now that we have had an idea about how our data is stored on disk. Let’s talk about how we optimize performance for data access and retrieval.

Now that we understand how data is stored in disk, the next question is: How does the databse find the required data efficiently?

Indexes

An index is a data structure that helps database quickly locate records without scanning every page.

Earlier we saw that data is stored as records inside pages.

Without an index, the database has to scan multiple pages to find a record.

Index stores:

• column value
• Stores a reference (pointer) to the location of the data, which could be a page or a record depending on the database.

Most databases uses a B-Tree structure for indexes (sorted structure, fast (log n) lookups, Nodes point to pages/records)

So… a query like

Select * from users where id = 101;

With index:

1. Traverse index (B-Tree)
2. Find pointer
3. Jump to correct page
4. Read record

I have had multiple occurences where there were slow queries just because there were no indexes or the wrong column was indexed which is of no benefit.

Also remember we can not make too many indexes it will slow down the inserts/updates.

Golden rule to remember is that: Indexes improve read performance but add overhead to writes.

Now we know how data is stored and how it is efficiently retrieved. Now the question is what happens when multiple users are reading and writing data at the same time? Or what happens if something fails in the middle of an operation?

Transactions (ACID)

A transaction is a group of operations that are executed as a single unit.

Either all of them succeed, or none of them do.

Why we need it?

Let's take a simple example:
You are transferring money from account A to account B.
Step 1: Deduct 100 from A
Step 2: Add 100 to B

Now imagine the system crashes after Step 1.

Money is deducted from A
But never added to B

this is bad very bad. Either both steps should happen, or neither happens.

1. Atomicity (All or Nothing): No partial updates allowed.
2. Consistency (Valid State): Rules like constraints, relationships should not break.
3. Isolation (No Interference): Multiple Transactions should not interfere with each other.
4. Durability (It Stays Saved): Even if the system crashes, the data is safe.

Some issues I’ve either faced or can easily happen:

• Partial updates (half data written)
• Duplicate or inconsistent data
• Race conditions when multiple users update same data
• Debugging nightmares where “data looks wrong but no error occurred”

Now we know how data is stored, how it is retrieved efficiently using indexes, and how consistency is maintained using transactions.

The next quesiton is:

How does database actually execute a query internally?

Query

Let’s take a simple example:

select * from users where id = 101;

At first glance, this looks simple. But internaly, a lot is happening.

Step 1: Query Parsing

The database first parses the SQL query.

• Checks syntax
• Validates table and column names

If something is wrong you will get an error in this phase itself.

Step 2: Query Planning

The database decides how to execute the query.

It evaluates:

• Is there an index on id?
• Should it scan the whole table?
• What is the fastest way?

This step is handled by Query Optimizer.

Step 3: Data Access

Now the database executes the plan:

• If index exists: It will traverse the index (B-Tree), Find pointer to data, and Jump to correct page.
• If no index: Scan pages one by one (full table scan)

Step 4: Read from Disk (Pages)

• The required page is loaded into memory
• Records inisde the page are scanned
• Matching row is extracted

Step 5: Transaction Check

If the query is part of a transaction:

• Database ensures consistency
• Handles locks/isolation

Step 6: Return Result

Finally:

• Data is returned to the user
• Query completes

In Short:

SQL -> Parse -> Plan -> Index/Table Scan -> Page Read -> Record Fetch -> Return Result

As you can see a simple query is not simple internally. Understanding this helps you debug performance issues much better.

Mistakes that Made me

Most of what I’ve learned about databases didn’t come from books… It came from breaking things and then figuring out why it broke.

1. Assuming the Database Reads only what I ask

I used to think that If I query one row, the database reads only that row which wasn’t really true as we have seen above.

2. Not Using Indexes

One of the most common issues I faced:

• Queries were slow
• CPU Usage was high
• Everything looked fine

Later I realised there was no index on the column which I was filtering on.

3. Using Indexes Incorrectly

• Index on a column that is rarely used in queries
• Forgetting to index columns used in JOIN
• Not indexing columns used in ORDER BY

This renders indexes useless and database still have to scan a lot of data.

4. Adding Too many indexes

At one point I thought:

“More indexes = better performance”

Reality: Every insert/update has to update indexes too, Writes became slower.

Indexes improve reads, but slows down writes.

5. Encoding Issues (This one Hurt)

One of the most painful bugs I faced was related to encoding.

I was working with a 2 million+row CSV file that was encoded in SHIFT-JIS.

But my system as scripts were using UTF-8.

At that time, I didn’t even know encoding could cause issues like this:

• Data looked fine initially
• After procesing and backup characters became corrupted
• Some data was unreadable

Resources & References

Books

• Designing Data-Intensive Applications by Martin Kleppmann (currently reading) — probably the best book on how data systems actually work at scale. Covers storage engines, replication, distributed systems, and a lot more.
• Database System Concepts by Silberschatz, Korth, Sudarshan — the classic textbook. Dense but thorough. Good for understanding the theory behind everything covered in this blog.
• Database Internals by Alex Petrov — goes deep into B-Trees, storage engines, and distributed database algorithms. Great follow-up after DDIA.

Interactive Learning & Practice

• SQLZoo — browser-based SQL practice. You write real queries against real datasets directly in the browser. Good for building intuition fast.
• Khan Academy: Intro to SQL — pairs short video explanations with in-browser coding challenges. A solid starting point if you want both visual and hands-on practice.
• SQLiteOnline — a full SQLite IDE in your browser. Create tables, upload CSV files, run queries. No setup needed.
• PostgreSQL Exercises — real PostgreSQL exercises ranging from beginner to advanced. Covers joins, aggregations, window functions, and more.

Animations & Visual Tools

These are the ones I wish I had found earlier. If you are a visual learner like me, these will click things into place faster than any textbook.

• B-Tree Visualization (USFCA) — interactive B-Tree where you can insert and delete keys and watch the tree restructure itself in real time. University of San Francisco hosts this and it is completely free.
• B+ Tree Visualizer — similar to the above but specifically for B+ Trees (which is what most databases like MySQL actually use). Clean UI, very satisfying to play with.
• PlanetScale: B-Trees and Database Indexes — not just an article. It has embedded interactive components where you can add keys and watch the tree grow, adjust node sizes, and compare sequential vs random inserts. One of the best explanations of indexes I have come across.
• VisuAlgo — covers 20+ data structures and algorithms with step-by-step animations. Includes binary search trees, hash tables, sorting algorithms. Very useful for understanding the data structures that power databases.
• explain.dalibo.com — paste in a PostgreSQL EXPLAIN ANALYZE output and it turns it into a visual query plan diagram. This is genuinely useful when debugging slow queries. You can see exactly where time is being spent.
• DSVisualizer — simple and clean animations for stack, queue, tree traversal, and other data structure operations. Good for building intuition on the fundamentals.

Articles & Deep Dives

• Use The Index, Luke! — a free web book focused entirely on how indexes work and how to use them properly in SQL. Written from a developer’s perspective, not a DBA’s. Covers why the wrong index can be worse than no index at all. This directly addresses Mistake #3 I listed above.
• ByteByteGo: Database Index Internals — a well-illustrated breakdown of the data structures behind indexes. Good visuals, concise writing.
• FreeCodeCamp: An Animated Introduction to SQL — builds through SQL concepts step by step with interactive code playbacks you can pause and rewind.

YouTube Channels

• CMU Database Group — actual university-level database course lectures from Carnegie Mellon, free on YouTube. Andy Pavlo’s lectures on storage, indexing, and query execution are excellent if you want to go deeper.
• Neso Academy: DBMS Playlist — clear and structured explanations of DBMS concepts with diagrams. Good for covering theory quickly.
• Spanning Tree: Understanding B-Trees — a short, well-animated video explaining why B-Trees exist and how they work. This is what I would show someone before pointing them at the interactive visualizers above.