Intermediate System design concept · Distributed Systems & Data · 40 mins read
Database Scaling Techniques
Scale a relational database past one primary: split it by domain, reshape data for reads, tune the queries, and reshard without downtime.
Federation (Functional Partitioning)
Split one database into several databases by business domain so each one handles less traffic and can scale on its own.
Intuition
A single database often serves every part of the product: users, catalogue, orders, payments, analytics. Each domain competes for the same CPU, memory, buffer cache and write throughput, and a heavy query in one domain slows down all the others.
Federation is usually the first split a growing system makes, because it removes contention without changing how any single domain stores its data. It also lines up with service boundaries: each service can own its own database.
Mental Model
Draw the boundaries along domains that rarely need to be queried together. Users go to one database, products to another, orders to a third. Each database is smaller, handles fewer writes, keeps more of its working set in memory, and can be replicated, tuned or upgraded independently. Think of it like: Instead of one office handling HR, sales and accounting for the whole company, each department gets its own office. Each office is less crowded — but a question that needs all three now means visiting three offices.
Building Blocks
- Domain boundaries: Split along data that is read and written together. Users and their profiles belong together; orders and products are referenced across the boundary only by ID.
- Routing in the application: Each service or data-access layer knows which database owns which tables. There is no shared connection that can query everything.
- References by ID, not foreign keys: An order stores product_id, but the database can no longer enforce that the product exists. Integrity moves into application logic.
- Independent scaling: A read-heavy catalogue database can get replicas and caching while the write-heavy orders database gets a bigger primary or sharding.
Definitions
- Federation
-
Splitting a database into multiple databases by function or domain.
- Also called functional or vertical partitioning.
- Each database holds a complete domain, not a slice of rows.
- Cross-database join
-
Combining data that lives in two federated databases.
- The database cannot do it for you.
- The application queries both and merges, or keeps a denormalized copy.
- Database per service
-
A microservices pattern where each service exclusively owns its database.
- Federation is the data side of this pattern.
- Other services access the data only through the owning service's API.
Patterns
- Split by bounded context — When domains have clear ownership and few queries span them.
- Read-side copy for cross-domain views — When a screen needs data from several domains on every load.
Strategies
- Strangle one domain at a time When: When migrating an existing monolithic database. How: Pick the domain with the fewest cross-domain joins, copy its tables to a new database, switch reads, then switch writes, then drop the old tables. Example: Move the notifications tables out first because nothing else joins against them.
- Replace foreign keys with IDs plus checks When: Once two tables live in different databases. How: Keep the ID column, validate existence through the owning service at write time, and handle deletions with events or soft deletes. Example: Before creating an order, the Orders service asks the Catalogue service whether the product is active.
What you lose when you federate
Inside one database you get joins, foreign keys and multi-table transactions for free. Federation gives all three up at the boundary. A query like 'top customers by order value in each product category' now touches three databases and must be assembled in code, or answered from a reporting copy.
Transactions are the bigger loss. Creating an order and reserving stock used to be one ACID transaction; across two databases it becomes a distributed workflow (see Sagas in the Consistency topic). That is why the boundaries must follow real domain seams: if two tables are constantly written together, they belong in the same database.
Tradeoffs
| Decision | Upside | Downside |
|---|---|---|
| Smaller, independent databases vs cross-domain queries | Less contention, smaller working sets, independent scaling and failure isolation. | Joins, foreign keys and transactions across domains move into application code. |
| Federation vs sharding | Most queries still hit one database because each domain stays whole. | It does not help when a single domain's table outgrows one primary. |
Real World
| System | How it's used |
|---|---|
| Microservice platforms | Teams at companies like Amazon and Uber give each service its own database; this is federation enforced by organisation boundaries. |
| Monolith migrations | Companies splitting a Rails or Django monolith typically federate the database domain by domain before any sharding. |
Interview
Questions interviewers ask
- How would you split a single overloaded database?
- What breaks when users and orders live in different databases?
- When is federation not enough?
What a strong answer covers
Explain splitting by domain, the loss of joins, foreign keys and transactions at the boundary, and when a single domain still needs sharding.
Common traps
- Splitting tables that are written in the same transaction.
- Calling federation 'sharding' — it splits by domain, not by rows.
Quiz
What does federation split a database by?
- Row ranges of one table
- Business domain or function
- Read vs write traffic
- Geographic region only
Federation gives each domain (users, orders, catalogue) its own database. Splitting rows of one table is sharding.
Which capability is lost across federated databases?
- Indexes
- Replication
- Multi-database ACID transactions and joins
- Backups
Each database still has indexes, replicas and backups, but the engine can no longer join or transact across the boundary.
A single 'events' table outgrows one primary. Does federation solve it?
- Yes, always
- No — that domain needs sharding
- Yes, if you add a cache
- Only with foreign keys
Federation moves whole domains apart; it does not split a single huge table. That requires sharding.
Which tables should stay in the same database?
- Tables owned by different teams
- Tables that are written together in one transaction
- Tables with different access patterns
- Tables with different sizes
Tables written atomically together must share a database, or you turn a simple transaction into a distributed workflow.
After federation, how is 'order references a valid product' enforced?
- Foreign key across databases
- Application-level checks or events
- The load balancer
- It cannot be enforced at all
Cross-database foreign keys do not exist, so the owning services validate references and react to deletions.
Denormalization
Store redundant copies of data so hot reads avoid expensive joins, and keep those copies correct when the source changes.
This section is part of the full PRISM roadmap, with worked examples, trade-off tables, interview questions and a quiz.
Unlock the full lessonSQL Tuning at Scale
Find the queries that actually cost the most, read their execution plans, and fix them before adding hardware or splitting data.
This section is part of the full PRISM roadmap, with worked examples, trade-off tables, interview questions and a quiz.
Unlock the full lessonResharding & Cross-Shard Queries
Handle what basic sharding leaves out: queries that span shards, uneven growth, and moving data to new shards without downtime.
This section is part of the full PRISM roadmap, with worked examples, trade-off tables, interview questions and a quiz.
Unlock the full lessonPractice database scaling techniques in PRISM
Concepts stick when you watch them fail. Build an architecture that depends on database scaling techniques, push traffic through it in the PRISM simulator, and see the latency and error rates change as you adjust the design.