Federation and functional partitioning

What is database federation?

Federation (functional partitioning) splits a monolithic database into multiple independent physical databases along domain or functional boundaries.

Instead of a single monolithic database housing users, products, orders, and payments, we provision:

• Users DB: user_profiles, auth_credentials, settings
• Catalog DB: products, inventory, categories
• Orders DB: orders, line_items, shipments
• Payments DB: transactions, payouts, audit_log

---

Advantages of functional federation

• Less traffic per database: Each database sees only its own reads and writes, so there is less to replicate and less replication lag.
• Smaller working sets: Smaller databases mean more of the data fits in memory (PostgreSQL shared_buffers, MySQL's InnoDB buffer pool), so more reads are cache hits.
• Parallel writes: With no single central master serializing every write, the databases can accept writes in parallel, increasing throughput.
• Independent sizing and failure isolation: A busy Orders DB can get bigger hardware than a quiet Users DB, and a runaway query on one database does not exhaust connections on another.

---

Trade-offs and architectural challenges

While federation is simpler to implement than full horizontal sharding, it introduces fundamental distributed systems constraints:

1. Cross-database joins get harder

Your application must now know which database to read from and write to. In a monolithic database:

SELECT o.id, u.email, p.title 
FROM orders o 
JOIN users u ON o.user_id = u.id 
JOIN products p ON o.product_id = p.id;

Across federated databases, a single SQL join no longer works as is: joining data from two databases needs a server link between them (more complex and slower), or the application performs the join itself with fan-out queries:

1. Fetch order from Orders DB.
2. Concurrently fetch user_id from Users DB and product_id from Catalog DB.
3. Stitch the results in application memory.

2. Loss of referential integrity (foreign keys)

Foreign key constraints cannot span physical database instances. Application services or asynchronous event streams must enforce referential consistency.

3. Uneven domain growth

Federation does not help if your schema needs one huge table or function. If most of the traffic lands on one domain (say the Orders DB), federating only buys time; when that database hits its ceiling, you need horizontal sharding. Federation also adds hardware and complexity.