So... What Actually Happens Inside the Database?

In my last article, I was trying to figure out how to approach a system design problem without immediately jumping into technologies.

The conclusion I came to was pretty simple:

I shouldn't start by asking “Should I use PostgreSQL or MongoDB?”

I should first understand:

• What data does the system need?
• How will I access it?
• How often will I access it?
• What happens when multiple things happen at the same time?

That sounded great.

Until I actually started learning database design.

Because then I realised I didn't really understand what the database was doing in the first place.

I knew how to write:

SELECT * FROM orders WHERE user_id = 123;

But what happens after I run that?

And why should I care about indexes, transactions, or isolation levels when I'm designing a system?

So that's what I started with.

Let's start with one query

Imagine our system has an orders table.

Maybe it looks something like:

orders

id
user_id
amount
status
created_at

And a user opens their order history.

The application asks:

SELECT *
FROM orders
WHERE user_id = 123;

Simple.

But now imagine the database has 10 rows.

No problem.

Now imagine it has 100 million.

The database can't just scan every row every time someone asks for their orders.

So I started asking:

How does the database find the rows it actually needs?

And that's where indexes come in.

An index is basically an answer to a question

Instead of thinking:

“A database has indexes.”

I find it easier to think:

“I'm repeatedly looking for data in a particular way. Can I build something that makes that lookup faster?”

For example, if I frequently search orders by user_id, an index on user_id can help the database find those rows without scanning the entire table.

But there was one thing I initially misunderstood.

You usually aren't implementing these indexes yourself.

If you're using something like PostgreSQL, the database already provides index types such as B-tree and Hash indexes and handles the underlying data structures for you.

You might write something as simple as:

CREATE INDEX idx_orders_user_id
ON orders(user_id);

PostgreSQL takes care of the actual index structure and maintaining it as data changes.

So as a system designer, I'm generally not sitting there implementing a B-tree from scratch.

My job is more like:

What queries are going to happen frequently, and what indexes would help those queries?

And that distinction matters.

Because the system design problem isn't:

“How do I build a B-tree?”

It's:

“This is how my application accesses the data. How should the database support that access pattern?”

But then I found out there isn't just “an index”

This is where it got confusing again.

There are things like:

• Hash indexes
• B-Trees
• LSM Trees
• SSTables

At first, this felt like another list I was supposed to memorise.

But I kept coming back to the same question:

Why do we need different ways of indexing data?

Different structures are useful for different workloads and access patterns.

If I'm doing an exact lookup:

user_id = 123

that's different from something like:

created_at > yesterday

where ordering and range queries matter.

And systems designed around heavy writes have different concerns again.

The important part for me isn't:

Hash → B-Tree → LSM → memorise.

It's:

What kind of reads and writes does this system need to handle?

And then:

What data structure or database feature supports that workload?

The database handles a lot of the implementation details for me.

I still need to understand what it's doing, though, because those details affect the decisions I make later.

Then I got to writes

Reading data was one thing.

Writing data made things more interesting.

Let's say a user places an order.

Maybe the system needs to:

Create order
      ↓
Reduce inventory
      ↓
Create payment record

Now imagine:

Create order       ✓
Reduce inventory   ✓
Create payment     ✗

We now have a problem.

The system has partially completed something that, from the user's perspective, should have been one operation.

I could technically write three separate database operations.

But what I actually need is:

Either all of these changes happen, or none of them do.

And that is where transactions started making more sense to me.

Instead of memorising:

“ACID = Atomicity, Consistency, Isolation, Durability.”

I can start with the actual problem:

I have multiple changes that need to behave like one logical operation.

That's a much easier reason to remember why transactions exist.

But what if two requests happen at the same time?

This was the part that made isolation finally click for me.

Imagine a bank account has:

₹1,000

Two withdrawal requests arrive at almost exactly the same time.

Both want to withdraw:

₹800

If both requests read:

Balance = ₹1,000

before either one updates it, both might conclude:

“There is enough money.”

And now the system has allowed something that shouldn't have happened.

So the problem isn't just:

“Can I read and write data?”

It's:

What happens when multiple transactions are reading and writing the same data at the same time?

That's where isolation comes in.

Isolation is about concurrent transactions

I started learning about things like:

Read Committed

A transaction shouldn't see data that another transaction hasn't committed yet.

That sounds straightforward.

Until you ask:

What if I read the same thing twice?

Can another transaction change it between those two reads?

Then there is:

Snapshot Isolation

Instead of seeing every change happening around you, a transaction can work with a consistent view of the data.

That solves some problems.

But again, not everything.

Then you start running into things like:

• Write skew
• Phantom writes

And honestly, this is where database concepts stopped feeling like random terminology.

They're all answering variations of the same question:

What should happen when multiple things are happening at the same time?

And this brings me back to system design

This is probably the connection I was missing before.

In my previous article, I said that when designing the database, I should ask:

“What are the important queries?”

Now I understand that this isn't just about drawing a table.

The query tells me something about how the data needs to be accessed.

And that can lead to a decision like:

“This column is queried constantly, so maybe it needs an index.”

I also said:

“Do we need transactions?”

Now I have a better idea of what that question is actually asking.

And when I ask:

“What happens under concurrency?”

I'm not asking some theoretical database question.

I'm asking what happens when real users send requests at the same time.

So my database questions are becoming more specific:

What data do we store?
        ↓
How do we read it?
        ↓
What access patterns do we have?
        ↓
Do we need indexes?
        ↓
How do we write the data?
        ↓
Do multiple writes need to succeed together?
        ↓
What happens when transactions run concurrently?
        ↓
What consistency/isolation do we need?

And that's actually starting to feel like system design.

I think this is the pattern I'm looking for

One thing I'm noticing while learning all of this is that every concept becomes easier when I stop asking:

“What is this?”

and start asking:

“What problem made someone need this?”

Why do indexes exist?

Because searching through everything gets expensive.

Why do different index structures exist?

Because different workloads have different access patterns.

Why do transactions exist?

Because multiple changes sometimes need to behave like one operation.

Why do isolation levels exist?

Because transactions can happen concurrently and interfere with each other.

And this is exactly the way I want to continue learning system design.

Not:

Learn PostgreSQL.

Learn Redis.

Learn Kafka.

Memorise when to use each.

But:

What problem am I facing?

What changes because of that problem?

What design decision follows from it?

That's the part I was trying to figure out in the first article.

I think I'm finally starting to see how the pieces connect.