# Federation: Splitting One Database Into Several by Job Area

> Federation gives each part of an app its own database, so heavy traffic in one part does not slow the others. You also take on new costs.

**In short:** Federation splits one large database into several, one for each job area of the app, so heavy traffic in one area does not slow the others.

From "System design", a free course by Venkat R. Murty: https://designervenkat.online/learn/system-design/federation

## What You Will Learn

Most small apps start with one database that holds every table. That works well at first. As the app grows, many teams start to feel the strain, because every part of the app shares the same database server. Federation is one way to ease that strain. In this lesson you will learn what federation means, how it works, what it gains you, and what it costs. You will also walk through a small example and check your understanding with two quick questions.

- **Database:** An organized store of data that a program can read and change. Think of it as a set of filing cabinets managed by software.
- **Table:** One block of rows inside a database, where each row holds one record, such as one user or one order.
- **Federation:** Splitting one large database into several smaller databases, where each one holds the data for one job area of the app.
- **Functional partitioning:** Another name for federation. The word 'functional' refers to the function, or job, that the data serves.
- **Join:** A query that combines rows from two or more tables by matching a shared value, such as a user ID.
- **Replication lag:** The short delay before a copy of the data on a second server catches up with the main copy.

## What Does Federation Mean?

Federation is a way to divide data by the job it does in the app. A shop, for example, has data about customers, data about products, and data about orders. Federation places each group in its own database. From the point of view of the app's users, the app still behaves as one system. Behind the scenes, several separate databases do the work.

Some sources describe federation as making several physical databases look like one logical database to the people using them. Others describe it as a virtual single database sitting on top of many databases, with a coordinator that knows where each piece of data lives. Both descriptions point to the same idea: one front door, several rooms behind it.

### Why Split by Function, Not by Size?

Federation divides data along lines of job area, not along lines of how much data there is. This matters because another technique, called sharding, divides data by size. Sharding cuts the rows of one table across many machines. Federation cuts whole tables, or groups of related tables, away from each other.

A simple test helps. Ask, 'Which part of the app uses this data?' If the answer is 'the user accounts part,' the data belongs in the users database. If the answer is 'the checkout part,' the data belongs in the orders database. Teams often base these lines on business concepts rather than on the org chart, because business concepts change more slowly.

## How Does Federation Work?

1. **Map the job areas:** List the main functions of the app, such as users, products, orders, or messages. Then list which tables each function reads and writes.
2. **Give each area its own database:** Move the tables for one area into one database. Each database now has a clear owner, and only that part of the app should change it directly.
3. **Route each request:** When the app needs data, it sends the request to the database that owns that data. Some setups use a coordinator layer that checks the request and forwards it to the right place, so the calling code does not have to know the layout.
4. **Combine results when needed:** If a screen needs data from two databases, the app asks each one separately and merges the answers. This step is where most of the extra work appears.

The key idea is that reads and writes are spread across several databases instead of landing on one. Each database handles a smaller share of the total work, and each can be tuned for the kind of work it does.

## What Are the Benefits of Federation?

- **Less traffic per database:** Each database receives only the reads and writes that belong to its job area. A busy product catalog no longer competes with checkout for the same server's time.
- **Less replication lag:** When a database has fewer writes, its copies on other servers usually keep up more easily. Smaller write loads can mean shorter waits before a copy shows the newest change.
- **Smaller datasets that cache well:** A small database is more likely to fit in a server's memory. When the working data fits in memory, the server can answer more requests without reading from disk.
- **Parallel writes:** Writes to different databases can happen at the same time. The writes do not all wait in one line behind a single database engine.

Teams also gain some non-speed benefits. Each database can be backed up, patched, or changed on its own schedule. A risky change to the orders tables then does not force a pause in the catalog.

> **Tip:** Federation pays off most when the job areas have very different workloads. For example, a product search may read thousands of rows per second, while checkout writes a few rows per customer. Those two patterns are easier to serve from separate databases.

## What Are the Drawbacks of Federation?

- **Joins across databases are hard:** A join combines tables in one query. When the tables sit in different databases, one query cannot combine them, so the application must do that work.
- **More machines and more work to run:** Several databases mean several servers, backups, and upgrades. A small team may find this load heavier than the problem it was trying to solve.
- **Weak fit for very large tables:** Federation moves whole groups of tables. If one function has a single table that is too big for one server, moving that table to its own database does not fix the size problem.

There is also a cost in consistency. Because data lives in several places, a single transaction that changes data in two databases cannot rely on one database engine to keep both changes in step. Teams must decide how much delay they can accept, and they must plan what happens if one of the two changes fails.

Sources also note that joins across functions become complex in this setup. The next section shows what that looks like in practice.

### Working Around Cross-Database Joins

The usual approach is to ask each database for its own rows and then combine them in the application's memory. For a page that shows an order with the customer's name, the app asks the orders database for the order, then asks the users database for the name, and then builds the page from both answers. The work is simple, but the app now owns logic that a join would have handled in one step.

Some teams avoid repeated extra calls by copying a small piece of data, such as the customer's name, into another database. That copy must be kept up to date, so it adds its own rules. You will see this trade-off again in later lessons on denormalization, which is a separate topic.

> **Tip:** When you plan a federated design, write down every screen that needs data from two databases. Those screens are where your extra code, delays, and bugs will appear first.

## Worked Example: An Online Shop

Consider a large online shop such as Amazon. This is a teaching example to show the idea. It does not describe how any real company builds its system. The shop keeps three groups of data: users, the product catalog, and orders. Each group lives in its own database.

1. **Split the data:** The users database holds accounts and addresses. The catalog database holds products, prices, and descriptions. The orders database holds each order and the items in it.
2. **Handle a flood of searches:** Thousands of shoppers search the catalog at once. Those reads hit only the catalog database. The orders and users databases are not touched, so checkout stays fast for people buying things.
3. **Place an order:** A customer who buys a lamp writes a new row to the orders database. The catalog load does not slow that write, because the two databases are separate servers.
4. **Show order history with product details:** A customer opens a page listing past orders with product names and images. The app first reads the orders from the orders database. Then it reads the product details from the catalog database and joins the two results in its own code.

The last step shows the cost. A single database could have joined orders and products in one query. Here, the app must make two requests and combine the rows itself. The sample below shows the same pattern with two small in-memory databases. The app builds a lookup table of names from the users database, and then it walks through the orders database and fills in each name.

```python
import sqlite3

users_db = sqlite3.connect(":memory:")
orders_db = sqlite3.connect(":memory:")

users_db.execute("CREATE TABLE users (id INTEGER, name TEXT)")
users_db.execute("INSERT INTO users VALUES (1, 'Ana')")
users_db.execute("INSERT INTO users VALUES (2, 'Ben')")

orders_db.execute("CREATE TABLE orders (id INTEGER, user_id INTEGER, item TEXT)")
orders_db.execute("INSERT INTO orders VALUES (10, 1, 'Lamp')")
orders_db.execute("INSERT INTO orders VALUES (11, 2, 'Desk')")
orders_db.execute("INSERT INTO orders VALUES (12, 1, 'Chair')")

# The two databases cannot be joined in one query, so the app combines the rows
names = dict(users_db.execute("SELECT id, name FROM users").fetchall())
rows = orders_db.execute("SELECT id, user_id, item FROM orders ORDER BY id").fetchall()
for order_id, user_id, item in rows:
    print(order_id, names[user_id], item)

```

Output:

```text
10 Ana Lamp
11 Ben Desk
12 Ana Chair

```

Read the output line by line. Each row came from the orders database, and the name came from the users database. The app did the matching. If the shop later moved users into a different service, this code would need to change, which is one reason teams treat cross-database reads with care.

## Federation Compared With Sharding

- **Federation splits by job:** Each database holds a different kind of data, such as users or orders. The databases usually have different schemas, which means different table layouts.
- **Sharding splits by rows:** Each shard holds some of the rows of the same table, and every shard has the same layout. Sharding is covered in its own lesson.
- **The two can be combined:** A large orders database can be federated away from the users database, and then the orders database can be sharded if it grows too big for one server.

Some comparison tables online describe federation as a way to integrate existing, independent databases, while sharding is described as a way to scale one logical database. Both descriptions are useful, but the core question is simple: are you splitting different jobs apart, or splitting one job's rows apart?

## When to Federate

Federation is not a first step. Some sources describe it as a later choice, made after simpler fixes such as caching, read copies, and bigger servers have run out of room. Adding more databases adds more to run, so teams usually wait until the pain is clear.

> **Tip:** Before you federate, ask three questions. Is one job area clearly slowing the others? Do different teams need to change different tables without blocking each other? Can you name the owner of every table you plan to move? If the answers are mostly no, keep one database for now.

**Quick check:** A forum app keeps users, posts, and private messages in one database. Under heavy load, the whole app slows down. A teammate suggests a separate database for each of the three. Which answer names one real benefit and one new problem?

A. Benefit: each post gets its own copy of the author's password. Problem: private messages can no longer be deleted.
B. Benefit: a flood of posts no longer competes with private messages for the same server. Problem: showing a post next to its author's profile needs the app to query two databases and combine the results.
C. Benefit: the team no longer needs any backups. Problem: the database engine cannot store long text.
D. Benefit: every user gets a faster login page. Problem: the forum can no longer show a list of posts.

**Answer:** B. Splitting by job area reduces the load that each database carries, so heavy post traffic no longer slows private messages. The cost is that a page showing a post with its author's name must now pull data from two databases and merge it in the app.

## Quick Check

Here is a second scenario. Read it and decide which change uses the idea of federation, not some other technique.

**Quick check:** A ticket-booking app keeps accounts, event listings, and payments in one database. Event pages are slow because thousands of visitors browse listings at once. Payments are fine. Which change best applies federation?

A. Add a second copy of the whole database on a backup server for emergencies.
B. Add an index to the payments table so payment lookups finish sooner.
C. Move the event listings into their own database, while accounts and payments stay in the original.
D. Shard the payments table by customer ID so that each shard holds some of the rows.

**Answer:** C. Federation divides data by job area. Moving the listings into their own database lets the busy browsing load stop competing with payments. A backup copy, an index, and sharding each solve a different problem.

## Key Takeaways

Federation splits one large database into several databases, each owned by one job area of the app, such as users, products, or orders. It spreads reads and writes across those databases, which can reduce traffic per database. The price is harder joins, more servers to run, and a poor fit for very large tables. Start simple, and federate when one job area is clearly holding back the rest.

## Sources

- [Design Gurus: Database Federation (course page)](https://www.designgurus.io/course-play/grokking-system-design-fundamentals/doc/database-federation)
- [SystemDesign by Ops4Life: Database Federation guide](https://systemdesign.ops4life.com/guides/database-federation/)
- [jguamie system-design notes: Partitioning](https://github.com/jguamie/system-design/blob/master/notes/partitioning.md)
- [Harith Beysinghe, Databases in System Design (Medium)](https://medium.com/@harithabeysinghe/databases-in-system-design-22ed44d766c9)
- [GeeksforGeeks: Database Federation vs. Database Sharding](https://www.geeksforgeeks.org/system-design/database-federation-vs-database-sharding/)
- [TutorialsPoint: Database Federation vs. Database Sharding](https://www.tutorialspoint.com/system_analysis_and_design/database_federation_vs_database_sharding.htm)
- [Linode: Database Sharding, Concepts, Examples, and Strategies](https://www.linode.com/docs/guides/sharded-database/)
- [TheCodeForge: Database Federation, When to Split Your Database](https://thecodeforge.io/system-design/database-federation/)
