We use analytics cookies.We use cookies to analyze site traffic and improve your experience.
Loading...
System Design and AI Engineering, by Roni Das. 984 interactive lessons.
Start LearningIntermediate · Data Replication & Distribution
Splitting databases by function (users in one DB, orders in another) so each database handles only its domain. Simpler than sharding but limits cross-domain joins.
In short
Data federation is the practice of splitting a database into several smaller databases by function, so each one owns a single domain. Users live in one database, orders in another, and payments in a third, which keeps every database smaller and lets you scale, secure, and operate each domain independently.
In short
Data federation is the practice of splitting a database into several smaller databases by function, so each one owns a single domain. Users live in one database, orders in another, and payments in a third, which keeps every database smaller and lets you scale, secure, and operate each domain independently.
Learn it in the course
This page is the free part.
₹1,111 in India$25 everywhere elseonce, for the whole course
The System Design course covers Data Federation across a run of lessons, not one page. These 4 alone are about 80 minutes of step-by-step reading, every one with a quiz.
Splitting your monolithic database into multiple specialized databases by domain, reducing load and enabling independent scaling.
Principles and practices for writing code that resists attacks, from input validation to dependency management.
How identity federation lets people log in across organizations with one account. Compares SAML and OpenID Connect, and hub-and-spoke vs mesh.
The identical function body took 143.874 ms declared VOLATILE and 0.078 ms declared STABLE, which is 1,845 times. The default is VOLATILE, and nothing validates any of these claims.
all part of
System Design Masterclass
770 lessons · about 287 hours · 18 free to read
₹1,111in India, by UPI
$25everywhere else, by PayPal
It is really nice and good Please join the course
You own this course
Continue with Database Federationpay once,
yours for life
Federation is functional partitioning. Instead of one giant database holding every table, you draw a line along business boundaries and give each domain its own database. A social app might run a users database, a posts database, a messages database, and an analytics database, each on its own server or its own Postgres instance.
This is different from sharding. Sharding splits one table across many machines by a key like user_id, so each shard holds rows 0 to 1M, 1M to 2M, and so on. Federation splits by what the data is about, not by row ranges. You can run both at once: federate first into a users database, then shard that database when it gets too big.
One naming warning. The phrase data federation is also used for query federation, where a tool like Trino, Presto, or Amazon Athena queries many separate sources (Postgres, S3, MongoDB) through one SQL endpoint without moving the data. In a system design context, when someone says they federated their database, they almost always mean the functional split described here.
You identify clusters of tables that are read and written together and rarely join across the boundary. Users, profiles, and sessions belong together. Orders, line items, and shipments belong together. Each cluster moves to its own database with its own connection pool and credentials.
Application code routes each query to the right database. This is usually a thin data-access layer that maps a domain to a connection string, so the user service talks to the users database and the order service talks to the orders database. Many teams pair this with a service-per-domain layout, which is where federation and microservices meet.
The hard part is anything that used to be a single JOIN. Once orders and users live in separate databases, you cannot write SELECT ... FROM orders JOIN users in one query. You fetch the order, then fetch the user by id in a second call, or you denormalize a copy of the user's name into the orders database so the join is no longer needed. Foreign keys across databases stop being enforced by the engine, so the application has to protect that integrity.
Reach for federation when one domain is dragging down the rest. If your analytics queries are scanning huge tables and slowing down the checkout path, moving analytics to its own database isolates that load. It is also the simplest way to scale writes, because each database now handles a fraction of the total traffic, and it lets you tune each database for its workload (more memory for the cache-heavy domain, faster disks for the write-heavy one).
It is genuinely simpler than sharding because there is no shard key to choose, no resharding, and no scatter-gather queries. The downside is that cross-domain joins and cross-domain transactions become application problems. You lose ACID transactions that span databases, so a single operation touching orders and inventory now needs a saga or two-phase commit, both of which add complexity and failure modes.
The practical limit is that federation only buys you so much. A single domain can still outgrow one database, and at that point you have to shard the federated database too. So federation is often the first scaling step you take after read replicas, and sharding is the step you take when one federated piece is still too large.
Picture an e-commerce site that launched on one Postgres database with tables for users, products, orders, reviews, and event logs. Traffic grows, and the event logs table alone is now 400 GB and being written to constantly, which bloats backups and slows down vacuum for every other table.
The team federates. Event logs move to their own database tuned for high write throughput. Products and reviews move to a catalog database that is read-heavy and gets a couple of read replicas. Users and orders stay on the core database. Each database is now smaller, backs up faster, and can be scaled on its own.
The cost shows up at the product page, which used to join products, reviews, and the seller's user record in one query. Now the page makes three calls and stitches the result in the service, or the seller name is copied into the catalog database so the page only reads from one place. That trade, more application code in exchange for smaller and independently scalable databases, is the heart of federation.
Famously ran functionally separate Postgres clusters for different data domains before and alongside sharding individual clusters as each domain grew.
Amazon
Broke its early monolithic database into per-service data stores so each team owns its own database, the canonical example of federation meeting microservices.
Uber
Split data by domain such as trips, riders, and payments into separate storage systems so each team scales and operates its own data independently.
Trino and Amazon Athena
Implement the query-federation sense, presenting many separate sources like Postgres, MongoDB, and S3 through one SQL endpoint without copying the data.
Federation splits a database by function, so users, orders, and analytics each get their own database. Sharding splits a single table by a key, so rows are spread across many machines that all hold the same kind of data. Federation answers what data goes where by domain; sharding answers it by row. You often federate first, then shard a federated database once one domain outgrows a single server.
Not across databases. A JOIN works inside one database, but once two tables live in separate databases the engine cannot join them in a single query. You either make a second query to fetch the related rows by id and combine them in the application, or you denormalize a copy of the needed columns into the database that does the lookup so the join disappears.
You lose single-database ACID transactions across the split. An operation touching orders and inventory in two databases needs either a distributed transaction with two-phase commit, which is slow and fragile, or a saga that performs each step with compensating actions if a later step fails. Most teams design domains so cross-domain transactions are rare and use sagas where they are unavoidable.
No, although they share a name. The federation described here physically splits storage by domain inside your own system. A federated query engine like Trino or Amazon Athena leaves data where it is and reads many sources through one SQL interface for analytics. One is a storage layout decision, the other is a read-time virtualization tool.
Read replicas help when reads are the bottleneck and the data fits comfortably on one primary. Federate when one domain is hurting the others, such as heavy analytics slowing checkout, or when write load on a single primary is too high. Replicas scale reads of the whole database; federation isolates and scales each domain separately, including writes.
Sharding
Splitting a database into smaller pieces (shards) distributed across multiple servers. Each shard holds a subset of the data.
Microservices
An architecture where an application is split into small, independent services that communicate over the network. Each service owns its own data and can be deployed separately.
Database Partitioning
Dividing a large table into smaller, more manageable pieces while keeping them in the same database. Sharding is partitioning across servers.
Read Replica
A copy of your database that handles read queries, reducing load on the primary database. Writes still go to the primary and replicate out.
Write-Ahead Log
A technique where changes are written to a log before being applied to the database. Ensures durability and crash recovery.
Consistent Hashing
A hashing technique where adding or removing servers only moves a small fraction of keys. Used by Amazon DynamoDB and Cassandra for data distribution.
This page explains the idea. The "Database Federation" lesson builds it up step by step, with diagrams. It is part of the System Design Masterclass course, which has 770 lessons. 18 of them are free to read. You pay once and keep it for life, with no subscription.
Open the Database Federation lessoncourse 1
From absolute beginner to principal engineer, drawn step by step.
Asked about Data Federation in an interview?
Each question has why it is asked, a full answer, what the interviewer listens for, the follow-ups, the common mistakes and a diagram (268 diagrams in all). A PDF you keep, junior to staff level.
India: UPI, cards or netbanking through Razorpay. Elsewhere: card or PayPal. Download at once from My packs.
