▪️◼️⬛ How to scale a relational database

Relational DBs struggle at scale. This guide helps you choose the right strategy, from indexing to sharding, without overengineering too soon.

Jul 20, 2025

Hi!

I’ve mostly written about career growth, but there’s no way to grow to upper levels without a good technical foundation.

I’ll be writing some articles on technical concepts and case studies I’m investigating out of my personal curiosity. I want to know if this is also well-received by you.

Let me know with a huge ❤️ in this post and the comments.

Enjoy the article!

Fran

Relational databases give us power, structure, and consistency.

But when they start groaning under scale, it’s easy to make the wrong call, like over-indexing without measuring, or sharding too early.

In the past couple of years, I’ve worked on database migration infrastructure, failover testing, and performance debugging across several teams.

What I’ve learned is this: scaling is less about clever tricks and more about knowing your workload, picking the right trade-off, and understanding your system's weakest link.

⭐ In this post, you'll learn

When to use vertical scaling before diving into distributed complexity
Which low-effort improvements give high impact (like indexing and caching)
How to scale reads and writes independently with replication and sharding
Techniques for managing large datasets with partitioning and archiving
How to choose the right strategy using a practical decision framework

🏗️ #1 Introduction: why scaling relational databases is hard

Relational databases are great until they aren't. They work well out of the box for most startups, but as load increases, you hit limits fast. Especially with traditional RDBMS like PostgreSQL or MySQL, which were never built for massive scale or multi-region writes.

You can throw more hardware at the problem (vertical scaling) or split data across machines (horizontal scaling). Both options come with cost and complexity. Scaling decisions also affect your reliability, performance, and how much your team can actually maintain long term.

Say your PostgreSQL instance starts timing out during peak hours. Replication? Indexing? Sharding? This post will help you pick the right next move.

📈 #2 Vertical scaling: the obvious but limited option

Vertical scaling is simple. Add more CPU, memory, or SSDs to your database server. It usually works—until it doesn’t.

If CPU is maxed, bump the instance size. If IOPS are the problem, switch to provisioned volumes. The upside: no app changes. The downside: there's always a ceiling. And the bill is more expensive each time.

I’ve seen teams delay proper fixes for too long by relying on vertical scaling. Once, I had a PostgreSQL with queries taking too long. One could have thought having a more powerful instance would help, but the existing instance was already underutilized. The root issue was inefficient query plans and missing indexes for expensive queries.

Use vertical scaling early on. But as traffic grows or your dataset gets larger, it becomes a temporary band-aid rather than a long-term fix.

🧮 #3 Indexing: the low-hanging fruit of performance

Indexes are often the fastest way to fix performance without touching code. They work by creating shortcuts in how the DB finds data.

You’ve got options: B-tree (default), hash, GIN for full-text search, BRIN for time-series data, partial indexes to cover specific cases. I’ve had cases where adding a simple index on user_id reduced join latency by 10x. Some DBs like PostgreSQL even allow you to create functional indexes, like for example indexing a column in lower_case when the original column isn't lower_case, but some query pattern uses the LOWER() function

But there’s no free lunch. Indexes slow down writes and increase storage. They also need maintenance. On my team last year, we noticed a couple of indexes were never used. We were able to remove them after revisiting our query patterns.

Use indexes when read latency is your problem and the access pattern is predictable. Avoid blindly indexing every column—monitor query plans first.

📚 #4 Replication: scaling reads and ensuring high availability

Replication lets you offload reads and improve availability. You copy data from a primary to one or more replicas. There are different modes—async, sync, semi-sync—and they each trade consistency for performance.

Think of your primary as a master chef. The replicas are assistants duplicating their work. It helps you serve more customers, but they might get the orders a few seconds late.

It's not only about scaling, it's also about reliability. Testing instances failover last year, I found ways to cut failover from 45 minutes to 3 minutes by having a read replica ready to become the leader the moment the leader crashed. You never want your writer instance to crash... until it does and you'd rather have 3 minutes downtime instead of 45.

Replication helps in read-heavy workloads and HA setups. Just remember: writes still go to the primary, and replication lag can lead to stale reads. Design accordingly.

🧠 #5 Caching: reducing DB pressure with faster intermediaries

If replication handles read scale, caching handles read frequency. It’s about storing hot data closer to the user, often in memory with Redis, Memcached, or AWS DAX.

This is where you want to know if you have more reads or writes. If you are read-heavy, you can cache this data because it barely changes.

But caching is a trap if you don’t think through invalidation. You could end up with user-facing bugs because the TTLs weren’t tuned or updates didn’t invalidate correctly.

Use caching when the data changes slowly and read throughput matters. Be explicit about how it gets refreshed, and what happens on cache misses.

🚀 Learn even faster

Want a higher salary, more free time, and faster learning?
This newsletter is your shortcut.

👉 Join the 19,000+ engineers already increasing salaries, saving hours, and learning more in less time. Paid subscribers also get these resources and learning tracks.

🧾 #6 Denormalization: trading purity for performance

Denormalization is about duplicating data across tables so you avoid joins at read time. It’s a tradeoff: speed in exchange for data duplication.

This works well for analytics, dashboards, or anything where performance matters more than consistency. But you have to think through update paths carefully.

Only denormalize when it’s backed by clear ownership and strong write paths. Don’t do it just to “make queries easier”.

📊 #7 Materialized views: precomputing expensive queries

When you keep hitting expensive aggregations, materialized views are a win. You compute a complex query once and reuse the result.

It’s fast and predictable. But it can get stale if not refreshed correctly. You need a clear strategy for when and how to refresh, especially if data comes from many sources.

Great for dashboards, BI tools, or batch jobs. Just don’t forget it’s a snapshot, not the live state.

🪵 #8 Partitioning: breaking large tables into smaller, manageable chunks

Partitioning splits large tables into logical pieces based on a column like date or region. It’s a must for time-series data, logs, or anything that grows linearly.

Imagine having a logs table with over a billion rows. Once you partition it by month, queries drop from minutes to seconds. Archiving old data also became trivial. You can move them out of your database into a cold storage infrastructure, like AWS S3 Glacier.

But it adds schema complexity. You have to manage partition creation and avoid uneven data distribution.

Use partitioning when table size hurts query performance or cleanup. Especially when queries are scoped to recent data, and you keep old data for compliance and not for a customer's feature.

🧩 #9 Sharding: horizontal write scaling with distributed data

Sharding is the final boss of DB scaling. You split data across instances, often by a hash of a key like user_id. Each shard is like a smaller independent DB.

This scales writes and isolates load. But it comes with huge costs. No global transactions. No joins across shards. Complex migrations. You need smart query routing and automation.

Use sharding only when a single write node is the bottleneck and your data naturally segments.

👉 There are many sharding algorithms. Let me know in comments if you're interested in this kind of content to continue writing it.

🧊 #10 Archiving: keep the hot path lean

You don’t need all data online. Archiving moves cold data to slower, cheaper storage. It keeps your main DB fast and lean.

For example, archive orders older than two years to S3 or a separate database. The live DB stays fast. Archived data is still available if needed—just slower to access.

At Amazon, this comes up a lot. Teams often ignore cleanup, and databases get bloated. However, when a team configures a cold storage and some lifecycle rules to move data automatically, their bill becomes much cheaper.

Archive when you have clear retention rules and infrequent access. Build rehydration paths early to avoid surprises.

🛠️ #11 Advanced techniques (bonus)

Connection pooling is basic hygiene. Without it, your app will kill itself by opening new connections every request. Always pool.

Query optimization means using EXPLAIN oftne to identify the data access patterns, fixing bad joins, and rewriting slow queries.

Read-after-write patterns matter if you use caching or replicas. If your app reads stale data after writing, try write-through caching or wait until replicas catch up. You can't think about a database without thinking in the application using it.

Multi-region replication can reduce latency, but global coordination is hard. It’s worth it only when users span continents and latency matters.

🧭 #12 Decision framework: choosing the right scaling strategy

Checklist:

Is the bottleneck CPU, memory, or IOPS?
Are you scaling reads, writes, or both?
Do you need strong consistency or is eventual good enough?
Are queries slow because of data size, bad indexing, or complex joins?
Can you shard by user, org, or some other natural key?

🎯 Conclusion

Scale in stages. Most systems don’t need sharding or materialized views on day one. Start with simple wins like indexing or caching. Measure, then optimize.

Invest in observability to know where your database is struggling. Add pressure gradually and iterate with purpose.

Scaling is not a feature you “add.” It’s a constant negotiation between performance, complexity, and maintainability. Build what your system actually needs, not what looks impressive.