What is Database Federation and When Do You Use It?
Understand database federation, how it differs from sharding, and the trade-offs for cross-domain queries and transactions.
Expected Interview Answer
Database federation splits a single monolithic database into multiple smaller databases organized by function or domain (such as one database per service or business capability), rather than by splitting rows of the same table across shards, so each database serves a distinct, independently scalable slice of the system.
Where sharding splits one logical table’s rows across many nodes using a partition key, federation splits by function: a users database, an orders database, a catalog database, each owned by a different service or team and each free to use the schema and storage engine that best fits its workload. This removes a single database server as a bottleneck and lets each domain scale, tune, and evolve its schema independently, which pairs naturally with a microservices architecture where each service owns its own data store. The cost is that queries spanning multiple domains — like joining a user’s profile with their orders — can no longer be a single SQL join; they require application-level joins across service calls, an aggregation layer, or a separately maintained read model that denormalizes data from multiple federated databases. Federation also means giving up cross-database transactions, so operations spanning domains typically use sagas or eventual consistency instead of a single ACID transaction.
- Removes a single database as the bottleneck by splitting load across independently scaled stores
- Lets each domain choose the schema, indexing, and storage engine best suited to its workload
- Aligns data ownership with service boundaries in a microservices architecture
- Limits the blast radius of a database outage to one functional domain instead of the whole system
AI Mentor Explanation
Database federation is like a cricket board splitting one giant records office into separate offices for player registrations, match results, and ticketing, each run independently instead of one office handling everything. Each office can organize its own filing system best suited to its data — the ticketing office optimizes for fast lookups, the match-results office optimizes for historical queries. The cost is that answering a question spanning offices, like a player’s ticket sales history, now requires visiting multiple offices and combining the answers instead of one query. That functional split, with cross-domain questions requiring extra coordination, is exactly what database federation does for a system.
Step-by-Step Explanation
Step 1
Identify functional domains
Group data by business capability (users, orders, catalog) rather than by a single shared schema.
Step 2
Give each domain its own database
Split the monolithic database into separate stores, each owned by the team or service responsible for that domain.
Step 3
Handle cross-domain reads at the application layer
Use service calls, an API gateway aggregation step, or a dedicated read model instead of cross-database SQL joins.
Step 4
Replace cross-domain transactions with sagas
Operations spanning federated databases use compensating actions or eventual consistency instead of a single ACID transaction.
What Interviewer Expects
- Distinguishes federation (split by function/domain) from sharding (split by row/key within one logical table)
- Explains that federation aligns with microservices, each service owning its own database
- Recognizes the loss of cross-database joins and transactions as the main cost
- Mentions sagas, application-level joins, or read models as mitigations
Common Mistakes
- Confusing database federation with sharding a single table
- Assuming cross-domain queries stay as simple SQL joins after federation
- Forgetting that cross-database transactions require sagas or eventual consistency instead of ACID
- Federating domains that are too tightly coupled, creating chatty cross-service calls
Best Answer (HR Friendly)
“Database federation means splitting one big database into several smaller databases organized by business area, like one for users and one for orders, instead of one shared database handling everything. This lets each area scale and evolve independently, but it means you lose the ability to do a single join across areas, so combining data from two domains has to happen in the application instead.”
Code Example
async function getUserOrderSummary(userId) {
// users and orders live in two independently owned databases
const [user, orders] = await Promise.all([
usersDb.query('SELECT id, name, email FROM users WHERE id = $1', [userId]),
ordersDb.query('SELECT id, total, status FROM orders WHERE user_id = $1', [userId]),
])
// no cross-database SQL join is possible, so combine in application code
return {
id: user.id,
name: user.name,
email: user.email,
orders: orders,
totalSpent: orders.reduce((sum, o) => sum + o.total, 0),
}
}Follow-up Questions
- How is database federation different from sharding a single table?
- How would you implement a saga to keep data consistent across two federated databases?
- When would you build a dedicated read model instead of joining across services at request time?
- What are the risks of federating domains that are too tightly coupled?
MCQ Practice
1. What distinguishes database federation from sharding?
Federation organizes data into separate databases by business capability, while sharding partitions rows within one logical table across nodes.
2. What is the main cost of adopting database federation?
Once data is split across independent databases, queries and transactions spanning domains require application-level coordination instead of native SQL joins or transactions.
3. What pattern commonly replaces a single ACID transaction when an operation spans multiple federated databases?
Sagas break a cross-database operation into a sequence of local transactions with compensating actions for rollback, since a single distributed ACID transaction is not available.
Flash Cards
What is database federation? — Splitting one database into multiple smaller databases organized by function or domain, each independently owned and scaled.
Federation vs sharding? — Federation splits by business function; sharding splits rows of one logical table across nodes by a partition key.
Main cost of federation? — Loss of cross-database joins and ACID transactions; cross-domain operations need application-level joins or sagas.
What replaces cross-database transactions? — Sagas with compensating actions, or accepting eventual consistency across domains.