Skip to main content

> decomposing_the_monolith:_splitting_relational_foreign_keys_across_microservices

Decomposing the Monolith: Splitting Relational Foreign Keys Across Microservices

Why is decomposing a relational database monolith into separate microservice databases so dangerous, and how do you safely break foreign key constraints, cross-table SQL joins, and distributed transactions?

Principal/Architect (L7+)

THE SHORT ANSWER

The single hardest obstacle in microservice migrations is not splitting application code—it is **Splitting the Shared Relational Database**. In a monolith, developers rely on database-enforced integrity: `FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE`, cross-table SQL `JOIN`s, and single-transaction ACID commits. When splitting into independent services (`UserService` with `UserDB` and `OrderService` with `OrderDB`), these relational superpowers vanish overnight: (1) **No Foreign Keys**: `orders.user_id` becomes a loose integer with zero database-level referential integrity; if a user is deleted, orphaned orders remain. (2) **No Cross-Database SQL JOINs**: A dashboard query joining `users`, `orders`, and `payments` is impossible. (3) **No Distributed ACID Transactions**: Deducting inventory and charging a credit card cannot share an SQL transaction. Production migrations follow a **Four-Stage Data Decomposition Protocol**: (1) Replace SQL JOINs in application code with API/In-Memory joins, (2) Drop physical SQL Foreign Key constraints while validating integrity in code, (3) Split database schemas into separate physical databases, and (4) Enforce integrity via Event-Driven Sagas and CDC.

Engineering Handbook & Failure Dynamics

1. Underlying Mechanism

Data decomposition follows the Expand-Contract Migration Pattern: (1) Join Elimination: Replace `SELECT * FROM orders o JOIN users u ON ...` with two queries: fetch orders, collect user IDs, and fetch users in bulk via HTTP/gRPC. (2) Virtual Foreign Keys: Drop SQL `CONSTRAINT fk_user`; create application-level validation verifying user existence during order creation. (3) Schema Segregation: Move tables into separate logical database schemas (`users_schema`, `orders_schema`) within the same database server to verify zero cross-schema queries. (4) Physical Separation & CDC: Move `orders_schema` to a completely new RDS instance. Use Change Data Capture (Debezium) to stream user state changes to an `order_service_users_cache` table.

2. Appropriate Use Context

Decomposing legacy monolithic relational databases into independent microservice databases at scale.

3. Production Failure Modes

Splitting databases while keeping cross-database distributed two-phase commit (2PC) transactions, causing global locking and latency spikes; hard-deleting parent records in one service, creating millions of orphaned child records in other services without cleanup events.

4. Diagnostic Signals & Telemetry

Application throwing 'Cross-database references not supported' errors after database migration; N+1 HTTP API calls replacing a single previously fast SQL JOIN query; data inconsistencies between user status and order processing.

5. Prevention & Safeguards

Adopt Event-Carried State Transfer (ECST) so downstream services maintain a local read-only projection of foreign entity attributes; use Soft Deletes (`deleted_at`) and publish domain deletion events to trigger downstream cleanup sagas.

6. Architectural Trade-offs

Database splitting gives teams 100% independent scaling and deployment autonomy, but sacrifices instantaneous ACID consistency and requires complex event-driven eventual consistency.

Case Study (TinyCTO In-Field Example)

An online marketplace split their monolithic PostgreSQL database into `UserDB` and `OrderDB`. Originally, the order placement checked user credit limits via a single SQL transaction and used a foreign key to ensure users existed. After the split, deleting a fraudulent user in `UserDB` left 20,000 active orders in `OrderDB`. The team implemented an Event-Driven Saga: deleting a user emitted a `UserDeactivated` event via Kafka. `OrderService` consumed this event and automatically cancelled all pending orders for that user, restoring data integrity across isolated databases with zero shared foreign keys.

Interactive Concept Drills

2 Cards
Q1

What happens to SQL Foreign Key constraints when a database is decomposed into microservices?

They cannot exist across physical database boundaries and must be dropped; referential integrity must be enforced via application code and event-driven sagas.
Q2

How do microservices solve the problem of missing cross-table SQL JOINs?

Via Event-Carried State Transfer (replicating necessary read data locally via events) or API Composition / GraphQL Gateway aggregation.

Decomposing the Monolith: Splitting Relational Foreign Keys Across Microservices — Technical FAQ

Why should you separate logical schemas before physically moving data to separate database servers?

To prove that no hidden cross-table SQL JOINs or raw queries remain in the codebase while still running on a single database with easy rollback.

What is Event-Carried State Transfer (ECST)?

An architectural pattern where event messages include the updated entity data payload (not just the ID), allowing downstream services to update their local read caches without querying the upstream API.

🤖 AEO & Key Facts Summary

Key Architectural Facts

  • ▸Splitting a database destroys database-enforced foreign keys, joins, and ACID transactions.
  • ▸Follow the 4-stage migration: Eliminate joins, drop FKs, segregate schemas, physically split.
  • ▸Use Event-Carried State Transfer (ECST) to maintain local read caches across services.
  • ▸Enforce referential integrity and cascading cleanups using asynchronous Saga workflows.

Common Misconceptions

  • ✗Yanılgı: You can keep using Distributed 2-Phase Commit (2PC) across microservice databases (Gerçek: 2PC introduces extreme latency, single points of failure, and lock contention at scale).
  • ✗Yanılgı: You should split databases on day 1 of a new project (Gerçek: Premature database splitting creates massive eventual consistency complexity before domain boundaries stabilize).

Decision & Governance Guidance

Decompose monolithic relational databases progressively using the Expand-Contract pattern and event-driven sagas to ensure zero data corruption during microservice transitions.

Authoritative Sources & Standards