Federation

For four lessons we've done one thing over and over: taken a single dataset and split its rows across many boxes. Choose a shard key, hash it onto a ring, reshard when a shard melts — all of it in service of making one table go faster than one server can. It's powerful, and it's genuinely hard.

But there's an older, simpler move that almost every team makes first — long before they touch a shard key. Don't split one thing into pieces. Separate different things. Pull users onto their own database, orders onto another, the product catalog onto a third. That's federation (also called functional partitioning), and on the map from Partitioning vs Sharding it's the other axis entirely: sharding is horizontal (split one table's rows); federation is vertical, across boxes — different tables, different databases.

Picture a startup with one giant filing room. HR files, Sales files, Accounting files — all in the same room, everyone rummaging through the same drawers. It works when you're small. As you grow it becomes both a bottleneck (everyone waiting on one room) and a hazard: one fire, one flood, one clumsy intern, and the entire company's records are gone at once. So each department gets its own room. Faster, safer, and each can grow on its own schedule. That's federation. And the catch — the whole point of this lesson — is the report that needs a customer's Sales record and their Accounting record: now you walk to two rooms and staple the pages together yourself.

Netflix learned the hazard the hard way. In 2008, a database corruption took their DVD shipping offline for three days — one monolithic database meant one blast radius, and a single failure took the whole business with it. That outage became a founding argument for splitting data by function so that no single database is the company's single point of failure. (You met that idea in Single Point of Failure back in Container 1 — federation is that principle applied to your data.)

In this lesson: what federation is and how it differs from sharding (isolation versus throughput), why it's usually your first scaling move, the one real bill it charges — the cross-boundary tax, which we'll measure — and how to decide where to draw the seams.

Scope: this is a data-layer split. Splitting the application itself into independent services is a later story (Architecture Styles, Container 4); here the app is still one program that simply talks to more than one database. And sharding a single domain is still Choosing a Shard Key — federation is what you do before you ever need it.

The cleanest way to hold the two apart is one line each:

Sharding splits one table across servers by a key (user_id % N) — for throughput. Federation splits the database by domain (users here, orders there) — for isolation.

They answer different questions. Sharding answers "this one table is too big or too write-heavy for a single box — how do I spread it?" Federation answers "these different parts of my system shouldn't share fate or fight for the same box — how do I separate them?" One is about size; the other is about blast radius and independence.

Because they're different axes, they compose. The standard progression is: run one database until it hurts, federate the heavy or dangerous domains onto their own boxes, and then — only if one of those domains still outgrows a single server — shard that one domain on its own. Federation first (separate the different things), sharding second (split the one thing that's still too big). You rarely start with sharding, and you almost never shard something you could have just federated.

Why reach for federation first? Three reasons, and they're exactly what the four sharding lessons cost you:

• It's far simpler. No shard key to choose, no ring, no resharding, no hot-shard triage — none of Choosing a Shard Key through Resharding & Hot Shards. You move a table to another database. That's the whole operation.
• It isolates blast radius. A runaway analytics query, or an entire domain's database falling over, can't take down checkout — each domain has its own box, its own connections, its own failure domain. This is the Netflix lesson: no single database is the whole company.
• Each domain scales independently. Different hardware, different read/write mix, different backup cadence — and each can be sharded later, alone, only if it specifically needs it. You don't take on one table's scaling problem for the whole system.

Federation sounds like a big-bang migration — spin up three database servers, move everything, hope nothing breaks. In practice the sane path is logical before physical.

The common first step is to separate the schemas by functional area while keeping them on the same server. Users tables in a users schema, orders in an orders schema, and — this is the part that matters — you make the application code stop reaching across those lines. No query joins orders to users in one statement anymore; the code fetches from each domain and combines the results itself, even though, physically, they're still one database with one backup and one set of ops you already know.

You've paid nothing yet in infrastructure, but you've done the hard part: the code no longer crosses the seam. So when a domain finally gets heavy or dangerous enough to earn its own box, you lift just that schema onto a new server and the application barely changes — it was already talking to that domain as if it were separate. Federate the schema first, the server second. The seam is a decision you make in the code long before it's a decision you make in the infrastructure, and drawing it early is what makes the physical move boring instead of terrifying.

Everything above is upside. Here's the one real cost, and it's sharper than "it's a bit more complex." The instant users and orders live on different databases, the SQL join between them doesn't get slower. It stops existing.

We measured it. Two Postgres databases, 100,000 users on one and 500,000 orders on the other, and the everyday query every app has — the 500 most-recent orders, each with the buyer's name. On the orders database, ask for the join:

orders=# SELECT o.id, u.name FROM orders o JOIN users u ON u.id = o.user_id LIMIT 1;
ERROR:  relation "users" does not exist

That's the whole cost in one line. Not a slow query — a gone query. The users table isn't in the orders database, so the planner has nothing to join to. A JOIN, a foreign key, a transaction — all three of them live inside a single database, and you just put your data in two. So three things you took for granted become your problem:

• No cross-database JOIN. orders JOIN users becomes an application-side join: query the orders, collect the user_ids, query the users database WHERE id = ANY(...), and merge the two result sets in your code. If that shape sounds familiar, it's the exact IN-batch pattern from Query Optimization in Practice — one batched lookup, not a loop.
• No cross-database foreign key. The database can no longer guarantee that every order points at a real user — that referential integrity is now your application's job, and it's a classic source of bugs when someone forgets.
• No cross-database transaction. A change that must atomically touch both domains isn't a local transaction anymore; it needs a distributed commit across two databases (two-phase commit — the machinery Two-Phase Commit exists for, in §6, and the reason teams restructure to avoid cross-domain writes rather than perform them).

Now the part people get wrong in both directions. Does federation make that query slow? Here are the measured numbers for the same result three ways:

Read those carefully, because the lesson is in the gap. Federation does not make the query slow if you replace the JOIN correctly. The app-side IN-batch join at 2.2 ms is within a hair of the in-database JOIN at 1.9 ms — one extra round trip, and you're done. What federation actually costs you is that you now write the merge the database used to do for free, and you own the second round trip and the second failure domain. But the trap is wide open: the naive translation of a join — loop the orders, look up each user one at a time — is an N+1 across a network boundary, 501 queries and twenty-nine times slower, and far worse in production where every hop is a real round trip rather than a socket on the same machine. Federation is nearly free for the reads you re-plumb correctly and a cliff for the ones you don't.

There's a second escape hatch besides the app-side join: denormalize across the boundary — copy the user's name onto the order at write time so the read never needs users at all. That's Denormalization doing exactly its job, trading a little redundancy and a little staleness for killing a cross-boundary read entirely.

If crossing a seam costs you a JOIN, then the entire skill of federation is drawing the seams where your queries don't cross. Put the boundaries through the sparse parts of your join graph, not the dense ones.

There's a rule of thumb worth internalizing:

The ~80% join rule: if two tables are joined in more than about 80% of the queries that touch either one, they belong in the same domain. Tables that are almost always used together should never be split apart.

orders and order_items are the textbook example — you practically never load one without the other, so opening an order joins them on nearly every request. Put them on different databases and you've turned your single busiest query into a cross-boundary join, forever. That's the worst seam you can draw: straight through your hottest join. By contrast, users and products are rarely joined directly — a seam between identity and catalog is cheap, because few queries ever cross it, and the ones that do (a review's author name) are rare, small IN-batch lookups.

So the honest counter-question is: when should you not federate? When everything joins everything. A small, tightly-connected app whose every page stitches five tables together should stay one database — federating it makes every query a cross-database join, and you'd be paying the tax for isolation you never needed. Federation trades join-ability for isolation; if you don't need the isolation, you've bought nothing and paid for it. Federate a domain for a reason — it's heavy (wants to scale on its own), it's dangerous (keep analytics away from checkout), or it's separately owned (a different team runs it). Federating a cohesive schema for tidiness is the same premature-optimization trap as sharding too early. The seam matters more than the act: a good seam makes federation nearly free; a bad one taxes every request. Analyze the actual query patterns first, then cut.

Enough words — go draw some seams and watch the bill. Below, six tables from a real store — users, sessions, products, reviews, orders, order_items — and three databases to place them on. Every query that joins two tables lights up green when both live on the same database (a free in-DB JOIN) or amber when they're split apart (an app-side join across a boundary). A live traffic tape at the bottom fires real queries weighted by how often each runs, so you can see your seam tax as a colour.

Try all three presets. Monolith — everything on one database — every join is free and the tape is solid green, but you have exactly one blast radius. Federate by domain — identity, catalog, commerce — buys you three independent failure domains while only the cheap lookups (a buyer's name, a product) ever cross a seam. Then the instructive mistake: Bad split pulls order_items off onto its own database, cutting straight through "open an order" — your single hottest query — and watch the seam tax spike and the tape redden. Then tap individual tables to move them and find your own best seams.

You can reproduce the single most important fact of this lesson — a cross-database join is not slow, it's impossible — in about twenty lines of Python with zero dependencies, because two separate SQLite files are, quite literally, two federated databases: one file's connection cannot see the other's tables.

import sqlite3, random
random.seed(1)

# FEDERATED: users live in one database file, orders in another.
users  = sqlite3.connect('users.db')
orders = sqlite3.connect('orders.db')
users.executescript('CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT)')
orders.executescript('CREATE TABLE orders(id INTEGER PRIMARY KEY, user_id INT, total REAL)')
users.executemany('INSERT INTO users VALUES(?,?)',
                  [(i, f'user-{i}') for i in range(1, 2001)])
orders.executemany('INSERT INTO orders(user_id,total) VALUES(?,?)',
                   [(random.randint(1, 2000), random.random()*400) for _ in range(20_000)])

# Try the join the monolith gave you for free:
try:
    orders.execute('SELECT o.id, u.name FROM orders o JOIN users u ON u.id=o.user_id').fetchall()
except sqlite3.OperationalError as e:
    print('cross-DB JOIN ->', e)      # the whole cost, in one line

Predict first: what does that JOIN do? Run it:

cross-DB JOIN -> no such table: users

The orders database has never heard of users. There is no join to make slow. Now do it the two honest ways — the batched app-side join, and the per-row loop that looks innocent and isn't:

def in_batch():                       # the RIGHT way: 2 queries total
    rows = orders.execute('SELECT id,total,user_id FROM orders LIMIT 500').fetchall()
    ids  = list({r[2] for r in rows})
    ph   = ','.join('?' * len(ids))
    names = dict(users.execute(f'SELECT id,name FROM users WHERE id IN ({ph})', ids))
    return [(r[0], r[1], names.get(r[2])) for r in rows]

def n_plus_1():                       # the TRAP: 1 + 500 queries
    rows = orders.execute('SELECT id,total,user_id FROM orders LIMIT 500').fetchall()
    return [(r[0], r[1],
             users.execute('SELECT name FROM users WHERE id=?', (r[2],)).fetchone()[0])
            for r in rows]

Count the queries each one runs (SQLite's set_trace_callback will tally them) and you get:

app-side IN-batch join -> 2 queries
app-side N+1 join      -> 501 queries   (7x slower, in-process)

Two queries versus five hundred and one — for the identical result. In this in-process toy the N+1 is only ~7× slower because there's no network; against the real Postgres databases we measured, where every one of those 501 queries is a round trip, the same trap was 66 ms versus 2.2 ms — 29× slower. The join didn't disappear when you federated. You became responsible for it, and there's a right way and a very wrong way to do it.

Put it together as the decision it actually is:

• One database, and it's fine? Leave it. Federation is a cost you pay for isolation you can name — don't pay it early.
• A domain is heavy, dangerous, or separately owned? Federate it onto its own database — draw the seam through the sparse part of your join graph (the ~80% rule), keep tightly-joined tables together, and re-plumb the few crossing queries as batched app-side joins or denormalized copies. Never as per-row loops.
• A federated domain still outgrows one box? Now shard that one domain, with everything from Choosing a Shard Key onward. Federation bought you the right to solve that problem for one domain instead of all of them.

What you'd say under follow-up: "I'd federate before I shard — it's simpler and it isolates blast radius, which is usually the real pain before raw size is. I'd draw the seams where queries rarely cross, keeping tables that are almost always joined in the same database. Every crossing query becomes an app-side IN-batch join or a denormalized copy — never an N+1 — and I'd accept that I've given up cross-database foreign keys and transactions, so integrity and any atomic cross-domain write are now the application's responsibility. And I'd only shard a domain that specifically outgrew its own box, not the whole system."

• "Federation and sharding are the same thing." Different axes. Sharding splits one table's rows across boxes for throughput; federation puts different tables on different boxes for isolation. They compose — federate first, shard a domain later if it outgrows a box.
• "Federation is just microservices." No — it's a data-layer split you can do inside one plain application that simply holds two database connections. Splitting the application into services is a separate, later decision (Architecture Styles, Container 4).
• "Once federated, I can still JOIN across the databases." You can't. A SQL join and a foreign key both live inside a single database (we watched it error: relation "users" does not exist). Cross-domain becomes an app-side join or a denormalized copy.
• "Federate everything for cleanliness." Only federate a domain that earns it — heavy, dangerous, or separately owned. Federating tightly-joined tables (>80% joined together) taxes every query for isolation you didn't need. Premature federation is premature optimization, just like sharding too early.
• "The database still guarantees my data is consistent." Across two databases it can't. There is no cross-database foreign key (integrity is your code's job now) and no cross-database transaction (that needs two-phase commit).
• "Federation is a fancy proxy that makes many databases look like one." A federation layer can do that, but most teams simply have the application talk to several databases directly. Don't assume magic middleware is doing the joins for you — it usually isn't there.
• "Draw the seams anywhere." The seam is the decision. Put it where the join graph is sparse (the ~80% rule), never through two tables that are always joined.

• Federation splits the database by function (users here, orders there) for isolation — the simpler, earlier sibling of sharding, which splits one table by key for throughput. Different axes; they compose. Federate first, shard a domain second.
• It's usually your first scaling move: no shard key, no ring, no resharding; it isolates blast radius (Netflix's 2008 three-day outage is the cautionary tale) and lets each domain scale on its own. Do it logically first — separate schemas, stop the code crossing the seam — then move boxes.
• The one real bill is the cross-boundary tax: the moment two tables are on different databases, the JOIN between them is gone (we measured the literal error), along with the foreign key and the transaction. A crossing query becomes an app-side IN-batch join (2.2 ms, nearly as fast as the in-DB 1.9 ms) or a denormalized copy — but written as a per-row loop it's an N+1 across the network, 66 ms, 29× slower.
• The whole skill is where you draw the seam — through the sparse part of your join graph (the ~80% rule). Keep tables that are always joined together; federate a domain only when it's heavy, dangerous, or separately owned; and if everything joins everything, don't federate at all.
• Next — Distributed ID Generation: once your data lives on many independent databases, a single auto-increment column can't hand out unique IDs anymore. How do you generate identifiers that are unique across every box at once?