Database Federation

Copies share one tuning, so browsing still starves checkout

Replication ended with fencing and lag budgets deciding which copy leads and how stale readers may be, yet every replica still holds the same data under the same configuration. A single database serves logins, product browsing, and checkout. Browsing is 95% reads and explodes on weekends. Checkout is write-heavy and cannot lose a payment. Logins barely touch the disk but must never leak credentials. One machine with one configuration serves all three, so tuning the cache for browsing starves checkout of memory, while tuning durability for checkout slows browsing with extra disk confirmations.

The naive fix is a bigger shared box, and it postpones the fight without settling it. A second tempting fix is read replicas for every workload, and it fails on writes: three replicas spread the 95% browsing reads but every checkout write still funnels through one primary's write-ahead log, and the catalog surge still evicts login pages because all three workloads share one buffer pool on the primary. The workloads still share the same buffer pool, which is the memory region where the database keeps hot pages, the same write-ahead log, and the same failure domain, so a runaway catalog scan still evicts login pages from memory and a products outage still blocks payments. Vertical scaling also has a ceiling where the next box costs multiples for single-digit percentage gains.

Federation, which means splitting one database into several databases by business function, gives users, products, and orders each their own database. Think of a restaurant that splits one overwhelmed kitchen into a drinks counter, a grill, and a till, each with its own staff and pace: one analogy for the whole idea, and the rest is mechanics. This splits by what the data is for, which is the opposite axis from sharding, where sharding means splitting rows of one table across many machines.

One application talking to three specialist databases

After the split the application holds three connection pools, which are sets of reusable database connections, one per database, and each request touches only the databases it needs. A login touches the users database. A product search touches the products database. Only checkout touches two, and it does so through application code rather than a database join.

Isolation buys each workload its own ceiling and its own tuning

The mechanics of the win are concrete. Each database gets its own buffer pool and its own disk, so a weekend catalog surge fills the products memory without evicting login sessions. Each gets its own configuration: aggressive caching and relaxed durability for the catalog, synchronous replication, which means the primary waits for a replica to confirm before acknowledging, for payments. Failures also stay in their lane, because the products process crashing no longer stops the orders process from accepting writes.

Splitting kills cross-domain joins and atomic commits

The price arrives immediately. A report like all orders by users in one city used to be a single join, which is a query that combines rows across tables. Now users live in one database and orders in another, and the query language cannot reach across the boundary. Application code fetches matching users, then fetches their orders, then stitches the two in memory, which costs extra round trips and memory proportional to the larger side. Transactions, which are groups of writes that succeed or fail together, stop spanning the boundary too, so debiting inventory in products while recording payment in orders needs application-level coordination instead of a single commit.

An 800 GB database split three ways shows where the headroom comes from

Walk the sizes: suppose the shared database holds 800 GB, with 600 GB of product catalog, 150 GB of orders, and 50 GB of users. After federation the users database at 50 GB fits comfortably in memory on an ordinary server, so login lookups stop touching disk and their indexes, which previously competed with catalog pages for cache space, shrink to megabytes. The orders database at 150 GB moves to storage tuned for writes with synchronous replication, while the products database keeps its 600 GB but gains a fleet of read replicas and a large cache. The cache math explains the oft-quoted jump: when the small database's working set fits in RAM after the split, its hit rate climbs from perhaps 60% to over 95%, which removes most disk reads overnight without changing a single query.

Checkout across two databases becomes a saga with compensating steps

Checkout needs to reserve inventory in products and record payment in orders as one atomic outcome, which a single database provided with a commit. Federated, the flow becomes a saga, which is a sequence of local transactions where each step has a compensating action that undoes it. The application writes the order as pending, reserves inventory, then confirms, and each step runs through a retryable worker that can safely run twice, which is called idempotent. When the second step fails the system does not roll back, because there is no shared transaction to roll back, but instead runs the compensating action such as releasing the reservation or refunding the charge, and reconciles later. The edge case that punishes skippers is the refund arriving before the reservation confirms: money moves, inventory never did, and no single database shows the whole truth, so the team needs an outbox table, which is a durable to-do list written alongside each local update, plus a poller per service from the start.

Draw the lines where queries rarely cross and operations differ

The practical checklist keeps the seams honest. Split along domains that already have separate code owners and deploy schedules, keep cross-boundary queries under roughly 5% of traffic, and require a genuine difference in durability, compliance, or scaling needs per side. Cross-domain reads travel through service interfaces rather than cross-database queries, only immutable reference data such as country codes is duplicated, and each database gets its own migration pipeline so deploys never need to lockstep three systems at once.

Reports that span the split move downstream instead of joining live

Revenue by user city now needs users from one database and orders from another with no join available. The working answer is a downstream copy rather than live cross-database queries: stream row changes from each federated database into a warehouse, which is a separate analytical store, using change data capture, which means reading the database's own change log and forwarding each edit. Analysts join the copies there. The operational price is honest and permanent: one pipeline per source, lag measured in seconds to minutes, and a schema contract so a column rename in users does not silently break every dashboard. Production databases stay fast while reporting scales on hardware bought for scans.

Federation slices by purpose, giving users, products, and orders each their own buffer pool, durability setting, and failure domain. Purpose stops helping when the data is uniform: ten billion order rows cannot be divided into users versus products versus orders, because they are all orders hammering one primary's fsync ceiling. Uniform rows need the same schema dealt across machines by key, with routing, hotspots, and consistent hashing as the price, which is the sharding split.