Understanding the Tradeoff Between Reads and Writes in Databases and Why You Can’t Optimize Both at the Same Time

If you have worked with databases long enough, you have probably noticed something strange.

When you add indexes, inserts become slower.
When you design for analytics, data is loaded in batches.
When you design for fast transactions, reporting queries struggle.

Different databases. Different use cases. Same behavior.

This is not a coincidence.
This is not a limitation of a particular database.

This happens because of a very deep reason:

It is fundamentally impossible to optimize a database for both reads and writes at the same time without trade-offs.

To understand why, we need to forget modern databases for a moment and go back to first principles.

A database, at its core, does only two things:

It writes data to storage.
It reads data from storage.

Everything else — indexes, query engines, replication, caching — exists only to make these two operations efficient.

So the real question becomes:

What makes a write fast?
What makes a read fast?

And this is where the conflict begins.

Imagine you want to design the fastest possible write system in the world.

What would you do?

You would avoid reordering data.
You would avoid maintaining indexes.
You would avoid random access.
You would simply keep appending new data to the end.

This is exactly how a log file works.

Appending to the end of a file is the most efficient write operation possible for any storage device.

This gives you extremely fast writes.

But now ask:

How do you read something from this file?

You must scan the entire file until you find what you need.

Writes are fast because no organization is done.
Reads are slow because no organization exists.

Now imagine the opposite.

You want the fastest possible read system.

You would keep data sorted.
You would build multiple indexes.
You would arrange data so you can jump directly to what you need.
You would pre-organize everything.

This is what a heavily indexed SQL table looks like.

Now reads are extremely fast.

But what happens during a write?

Every insert must:

• Place data in the correct sorted position
• Update multiple indexes
• Maintain the structure

Writes become expensive because organization must be maintained.

This reveals the real reason behind the tradeoff.

Data must be organized before it can be read efficiently.

And that organization work must happen somewhere.

Either:

• During writes
  or
• Later, as background work

There is no third option.

If you skip the work during writes, reads must do it later.
If you do the work during writes, reads become easy.

You cannot avoid the work. You can only move it.

This is why different storage systems behave differently.

A log file chooses to optimize writes and ignore reads.

A traditional SQL table chooses to optimize reads and pay the price during writes.

LSM trees (used in Cassandra, RocksDB, LevelDB) do something clever:

They make writes fast now and do the organization later through a process called compaction.

But even here, the work is not removed.

It is only postponed.

You can even see this with a simple mathematical view.

If there are N records in storage:

In an append-only system:

Write cost ≈ O(1)
Read cost ≈ O(N)

In an indexed system:

Write cost ≈ O(log N) per index
Read cost ≈ O(log N)

You are trading linear read time for logarithmic read time by paying logarithmic cost on every write.

This is not an implementation detail.

This is a property of how data structures and storage work.

At this point, people often say:

“But modern databases are fast at both reads and writes.”

They are fast relative to hardware, but they still pay the cost using:

• More CPU
• More memory
• More I/O
• Background processes
• Eventual consistency
• Replication

They hide the cost. They don’t eliminate it.

This also explains many real-world observations:

Why indexes slow down inserts.
Why analytics databases load data in batches.
Why transactional databases struggle with heavy reporting queries.
Why denormalization speeds up reads but hurts writes.

Because the workload and the storage design are fighting each other.

In the end, databases are not magical systems.

They are bound by a simple rule:

To read data quickly, it must be carefully organized.
That organization requires work.
And that work must happen during writes or afterward.

You don’t get to skip the work.

You only get to choose when to do it.

And that is the fundamental reason why you cannot optimize a database for reads and writes at the same time.

Add this section after the Final Thought.

Where to Go From Here

Now that we understand why reads and writes cannot be optimized together, the natural next question is:

How do we optimize them separately based on workload?

I’ve written two follow-up articles that dive into this:

• How to Design Databases for Fast Reads [LINK]
• How to Design Databases for Fast Writes [LINK]

These two designs are very different — and now you know why.