DEV Community: niyati nehal

So... What Actually Happens Inside the Database?

niyati nehal — Wed, 30 Sep 2026 11:20:35 +0000

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.

Everyone Says “Clarify the Requirements” in System Design. But How?

niyati nehal — Wed, 30 Sep 2026 10:44:21 +0000

You all might have watched a lot of YouTube videos on system design and heard almost every one of them say:

“Before you start designing, clarify the functional and non-functional requirements.”

And yes, obviously.
You’re supposed to clarify the requirements.
But how do you actually do that?
What exactly do you ask the interviewer?
How do you know whether the question you’re asking is useful?

How do you go from:

“Design a URL shortener”

to actually knowing what you’re supposed to build?
I think this is one of those things that sounds very obvious when someone explains it after the fact, but when you’re actually sitting in an interview, it’s much harder.

So I wanted to break down the five things we usually hear about:

1-Functional and non-functional requirements
2-API design
3-High-level design|
4-Database design
5-Detailed design

But not as a checklist.

More importantly, how do you arrive at each of these? What questions do you ask, and why?
First: Don’t Start Designing

The interviewer says:

“Design a URL shortener.”

My brain might immediately go:
“Okay, Redis, database, load balancer…”

But wait.
I don’t even know what I’m designing yet.
The problem statement is intentionally vague.
So the first thing I’m actually trying to do is:
remove ambiguity.

I want to turn:

“Design a URL shortener”

into something more like:

“We’re building a URL-shortening service for authenticated users, supporting URL creation and redirection, with X traffic, Y latency requirements, and Z availability requirements.”

Now I have something I can actually design.
And this is where those five things come in.

Functional Requirements: What Does the User Actually Need to Do?

The usual advice is:

“Clarify the functional requirements.”

Okay.
But I don’t want to ask the interviewer:

“What are the functional requirements?”

That’s technically correct, but it doesn’t really help me think.
I’d rather start with:
Who is using the system?

For a URL shortener:

“Are users authenticated, or can anyone create a short URL anonymously?”

That one question already changes things.
If users are authenticated, I might need:

user accounts
ownership
authorization
URL management
user-specific limits

If it’s anonymous, a lot of that disappears.
So I’m not asking questions just to collect information.
I’m asking:

“What information could actually change my design?”

Then ask: What can the user actually do?

For example:

“Is the requirement only to create and redirect short URLs, or do we also need expiration, custom aliases, analytics, deletion, etc.?”

Because “URL shortener” could mean a very simple service.
Or it could mean:

create URLs
delete URLs
custom aliases
expiration
analytics
user dashboard
authentication

I need to know where the boundary is.
And this applies to every system.
If someone says:

“Design Instagram.”

That could mean the entire Instagram product.
Obviously we’re not designing all of Instagram in an interview.
So I need to establish the scope.
Find the Main User Journey

Once I know the scope, I want to understand the most important thing the user is trying to do.

For a URL shortener:

User
↓
Submits long URL
↓
System generates short code
↓
User gets short URL
↓
Someone opens short URL
↓
System finds original URL
↓
User is redirected

That’s the core flow.
Now I have something around which I can start thinking.
And this gives me a useful rule:

Don’t try to understand every possible feature first. Find the primary user journey.

Non-Functional Requirements: How Well Does It Need to Work?

Okay, now I know what the system does.

The next question is:
How well does it need to do it?

This is where non-functional requirements come in.
And again, I don’t want to ask:

“What are the NFRs?”

I want to ask questions that reveal the constraints.
How much traffic?
Suppose the interviewer says:

“We have millions of users.”

Cool.
But that doesn’t tell me much.
Millions of users doesn’t mean millions of requests per second.
So I’d ask:

“How many daily active users are we expecting?”

Then:

“Approximately how many requests per second?”

Then:

“What’s the read-to-write ratio?”

And:

“Is that average traffic or peak traffic?”

Suppose I get:

1,000 URL creations/sec
100,000 redirects/sec

Now I know something important.
The system is very read-heavy.
That immediately makes things like caching and read scaling relevant.
Notice what happened:
I didn’t decide:

“Let’s use Redis.”

The traffic requirement led me toward that discussion.
What About Latency?

Instead of vaguely asking:

“What’s the latency requirement?”

I can ask something more concrete:

“For the redirect operation, are we okay with something like 100 ms, or do we need it to be closer to tens of milliseconds?”

Now I can start thinking about what belongs in the critical path.
If something is expensive but doesn’t need to happen before the user receives a response, maybe it can happen asynchronously.
Again:

Requirement → consequence → architecture.
What Absolutely Needs to Work?

Availability is another thing I don’t want to treat as a number in isolation.

I can ask:

“If the analytics system is temporarily unavailable, should URL redirection still work?”

If the interviewer says yes, then I’ve learned something important:

Redirection is critical. Analytics isn’t.

So maybe analytics shouldn’t be part of the synchronous redirect path.
Maybe I can process analytics asynchronously.
That’s a much more useful way of thinking about availability than simply memorizing:

“The system should have 99.99% availability.”

And Then There’s Consistency

One question I like here is:

“What happens if two users temporarily see different values?”

Because not everything needs the same consistency guarantees.
A social-media like count being slightly stale might be acceptable.
A bank balance being wrong is a very different problem.
So instead of memorizing:

“This uses eventual consistency and this uses strong consistency.”

Ask:

“What happens if the data is temporarily inconsistent?”

The answer tells me how strong my consistency requirements actually need to be.

API Design: Don’t Just Design Endpoints

Now that I understand what the system needs to do, I can think about:

How does the outside world interact with it?

Suppose I need to create a short URL.
First ask:

“What does the client need to send?”

Probably:

{
"url": "https://example.com"
}

Then:

“What does the client need back?”

Maybe:

{
"shortUrl": "https://short.ly/abc123"
}

Only now does something like:

POST /urls

start making sense.
I’m not inventing the endpoint.
I’m deriving it from the user action.
But Don’t Stop at the Happy Path

For every important API, I want to ask:
What is the action?

Create a short URL.
What information does it need?

The original URL.
What does it return?

The generated short URL.
What can go wrong?

Invalid URL
Rate limit
Duplicate request
Service failure

And then one of my favourite questions:
What happens if the client retries?

Imagine this:
Become a Medium member

The client sends a request.
The server successfully creates the short URL.
But the response gets lost.
The client doesn’t know whether the request succeeded.
So it sends the request again.
What happens?
Do we create another short URL?
Or do we return the previous result?
Now we’ve naturally arrived at:

idempotency.

This is why I don’t think API design should just be:

“Let’s design some REST endpoints.”

The API is where the system’s behaviour starts becoming explicit.

HLD: Stop Drawing Boxes Just Because You Know Them

This is probably where I’ve seen the most “system design interview diagrams.”

Something like:

Client
↓
Load Balancer
↓
API Gateway
↓
Microservices
↓
Redis
↓
Kafka
↓
Database

And then everyone nods because it looks like a system.
But the interviewer can simply ask:

“Why do you need Kafka?”

And suddenly we’re in trouble.
So I’ve started thinking about HLD differently.
Before adding a component, ask:

What problem does this component solve?

Example: Why would I need a queue?

Suppose I’m designing a video-sharing platform.
Video processing can take a long time.
I don’t want the user to sit there waiting for:

transcoding
generating different resolutions
thumbnails
metadata processing

So I might design:

Upload
↓
Object Storage
↓
Create Processing Job
↓
Queue
↓
Workers
↓
Processed Videos

Now the queue has a reason to exist.
It’s not there because:

“Kafka is something senior engineers use.”

It’s there because:

I have expensive work that doesn’t need to block the user’s request.

That’s the kind of explanation I want to be able to give.
Every Box Should Have a “Why”

If I draw Redis:

Why?

Because the data is frequently read and can be cached.
If I draw Kafka:

Why?

Because work can happen asynchronously and doesn’t need to block the request.
If I draw a separate service:

Why?

Because it has a separate responsibility, scaling requirement, or failure boundary.
If I can’t explain why a component exists, maybe I shouldn’t have drawn it.

So my rule here is:

Architecture should be the consequence of requirements, not a collection of technologies I remember.

Database Design: Don’t Start With “SQL or NoSQL?”

This is another place where I think it’s easy to jump to the wrong question.

Instead of:

“Should I use PostgreSQL or MongoDB?”

Start with:

What information does the system actually need to remember?

For a URL shortener:

original URL
short code
user
creation time
expiration time

Then ask:

How do I need to access this information?

Maybe the most important query is:

shortCode → originalURL

That’s more useful to me than immediately debating databases.

Then I can ask:

How often do we run this query?
How much data will we have?
How quickly will it grow?
What relationships exist?
Do we need transactions?
What consistency do we need?

Only after answering these questions should I start thinking about the storage technology.
So the mental sequence becomes:

Data → relationships → queries → query frequency → scale → consistency → storage choice

Not:

Problem → MongoDB because it’s scalable.

Detailed Design: Where Is the Interesting Problem?

At this point, I should know:

what we’re building
who uses it
how much traffic we have
what the important APIs are
what the major components are
what data we store

Now I can zoom in.
But I don’t need to explain every single class or function.
Instead, ask:

What’s the hardest part of this system?

For a URL shortener, maybe it’s:

“How do we generate unique short codes when multiple servers are creating URLs at the same time?”

Now we can go deeper.

Request
↓
Generate unique ID
↓
Base62 encoding
↓
Short code
↓
Store mapping

And now more questions appear:
What if two servers generate the same ID?
Can IDs be generated independently?
Do we need a centralized ID generator?
What happens if the ID generator goes down?
What happens if two identical requests arrive simultaneously?
Now I’m actually doing detailed system design.
The Most Important Thing: Every Answer Should Create the Next Question

This is probably the biggest thing I’m taking away from all of this.

System design shouldn’t feel like:

Question → answer → next section.

It should feel like:

Question → answer → consequence → next question → design decision

For example:

The interviewer says:

“We expect 1 million requests per second.”

I ask:

“Are those mostly reads or writes?”

They say:

“Mostly reads.”

I ask:

“Can the data be cached?”

They say:

“Yes, it doesn’t change frequently.”

Now I think:

“Okay, caching can reduce the load on the database.”

Then I ask:

“What happens on a cache miss?”

Now I’m thinking about the database.

Then:

“Can the database handle the remaining traffic?”

If not:

“Can we use read replicas?”

Notice how the architecture is slowly appearing.
I didn’t start with:

“Let’s use Redis.”

The requirement led me there.
So What Are the Five Things Actually Doing?

Maybe this is the easiest way for me to remember them.

Functional requirements

What are we building?
Who uses it?
What can they do?
What is the main user journey?
What’s in scope?
What’s out of scope?

Non-functional requirements

How well does it need to work?
How much traffic?
How much data?
What latency?
What availability?
What consistency?
What happens during failures?

API design

How does the outside world interact with it?
What does the client send?
What does it receive?
What can go wrong?
What happens on retries?
Is it idempotent?

What pieces do I need, and why?
Cache?
Queue?
Database?
Workers?
Services?
Load balancer?
And most importantly:

Why?

Database + detailed design

How does the system actually store and process information?
What are the entities?
What are the important queries?
What happens under concurrency?
What happens when something fails?
Where is the bottleneck?
The Question I Want to Keep Beside Me

I don’t think I need to memorize a giant list of system-design questions.
I just need to keep asking:

“What do I need to know before I can make this decision?”

I need to choose a database?
First understand the data and access patterns.
I need to add Redis?
First understand whether caching solves an actual problem.
I need Kafka?
First understand whether something should be asynchronous.
I need a read replica?
First understand the read volume and database bottleneck.
I need strong consistency?
First understand what happens if users temporarily see different data.
That’s probably the biggest shift in how I want to approach system design.
Not:

“What architecture do I know for this problem?”

But:

“What questions do I need to answer so that the architecture becomes obvious?”

And honestly, that’s probably what I should be practicing when I practice system design.
Not drawing more diagrams.
Getting better at asking the next useful question