Case-Design-Migrate-Monolith-DB

Case Study 05 — Migrate Monolith DB → Microservices DB

“Monolith Postgres 500GB, 50 services chia 1 DB. Tiến hóa thành microservice với database-per-service. Plan + execute + don’t lose data + don’t downtime.”

Tags: case-study migration microservices cdc strangler-fig Liên quan: Tuan-08-Zero-Downtime-Migration · Tuan-Bonus-CDC-Debezium

---

1. Starting Point

graph TB
    subgraph "Monolith"
        App1[Service A] --> DB[(Single Postgres 500GB)]
        App2[Service B] --> DB
        App3[Service C] --> DB
        App4[Service D...Z] --> DB
        DB -.-> Many[100+ tables<br/>joined freely]
    end

    style DB fill:#ffccbc

Problems

• Schema migrations affect everyone (Service A migration → block Service B deploy)
• Performance: 1 bad query from any service slows all
• Tight coupling via shared tables
• Team velocity: every change goes through “DBA team”
• Single point of failure

---

2. Target State

graph TB
    subgraph "Service A"
        SA[Service A] --> DBA[(DB A - Postgres)]
    end
    subgraph "Service B"
        SB[Service B] --> DBB[(DB B - Postgres)]
    end
    subgraph "Service C"
        SC[Service C] --> DBC[(DB C - Postgres)]
    end

    DBA -.events.-> Kafka[(Kafka)]
    Kafka -.events.-> DBB
    Kafka -.events.-> DBC

    SA <-->|sync API| SB
    SB <-->|sync API| SC

Goals

• DB per service (or per bounded context)
• Sync via events (eventual consistency) where possible
• Sync API for transactional needs
• Each team owns DB schema

---

3. Pre-work: Discover

3.1 Map tables to services

-- Find which service uses which table (via pg_stat_statements)
SELECT
    application_name,
    relname,
    sum(seq_scan + idx_scan) AS accesses
FROM pg_stat_user_tables t
JOIN pg_stat_activity a ON a.application_name IS NOT NULL  -- requires tracking
GROUP BY application_name, relname
ORDER BY accesses DESC;

Or audit log via pgaudit. Or grep codebase.

Build matrix:

Table	Service A	Service B	Service C
users	RW	R	R
orders	-	RW	R
inventory	R	RW	-
…

3.2 Find cross-table joins

-- pg_stat_statements look for queries joining tables of different domains
SELECT query FROM pg_stat_statements
WHERE query ILIKE '%users%orders%' OR query ILIKE '%orders%inventory%';

Map dependencies. The hardest part of migration.

3.3 Define bounded contexts

Apply DDD: group tables that belong together.

• User domain: users, sessions, profiles, addresses
• Catalog domain: products, categories, inventory
• Order domain: orders, order_items, payments
• Review domain: reviews, ratings

Service per bounded context.

---

4. Strategy: Strangler Fig

Don’t big-bang migrate. Replace pieces gradually.

graph LR
    Old[Monolith DB] -.shrinks over time.-> Old2[Smaller monolith]
    Old2 -.shrinks.-> Old3[Tiny monolith]

    NewA[New DB A] -.grows.-> NewA2[Service A complete]
    NewB[New DB B]
    NewC[New DB C]

Each iteration: extract one domain to its own DB + service.

---

5. Extracting One Domain — Example: Order

5.1 Phase 1 — New DB, dual-write

sequenceDiagram
    Client->>OrderService: create order
    OrderService->>OldDB: INSERT order (legacy)
    OrderService->>NewDB: INSERT order (new)
    OldDB->>OrderService: ok
    NewDB->>OrderService: ok
    OrderService-->>Client: 201

Code:

async def create_order(data):
    async with old_db.transaction():
        order_id = await old_db.execute("INSERT INTO orders ...", data)
        try:
            async with new_db.transaction():
                await new_db.execute("INSERT INTO orders (id, ...) VALUES ...", order_id, ...)
        except Exception as e:
            # Log inconsistency, alert
            await alert("Dual write failure", order_id, e)
            # Don't fail request; old DB committed
    return order_id

Failure mode: dual write inconsistency. Reconciliation job:

-- Find orders in old but not new
SELECT o.id FROM old_db.orders o
LEFT JOIN new_db.orders n ON n.id = o.id
WHERE n.id IS NULL AND o.created_at > now() - interval '1 day';

Better: use outbox pattern for atomic write:

-- In old_db.transaction
INSERT INTO orders (...);
INSERT INTO outbox (event_type, payload, ...) VALUES ('OrderCreated', ...);
COMMIT;
-- Debezium picks up outbox, publishes to Kafka, new service consumes, writes new DB

5.2 Phase 2 — Backfill historical

# Backfill 10M historical orders in chunks
async def backfill():
    last_id = 0
    while True:
        rows = await old_db.fetch("SELECT * FROM orders WHERE id > $1 ORDER BY id LIMIT 1000", last_id)
        if not rows: break
 
        await new_db.executemany("INSERT INTO orders (...) VALUES ...", [extract(r) for r in rows])
        last_id = rows[-1].id
 
        # Throttle
        await asyncio.sleep(0.1)

Verify counts match. Verify sample rows match.

5.3 Phase 3 — Read from new DB

App reads from new DB (writes still dual). Validate functional correctness.

async def get_order(order_id):
    new_result = await new_db.fetchone("SELECT * FROM orders WHERE id = $1", order_id)
    return new_result

Optional: shadow read — read from both, compare, log differences.

5.4 Phase 4 — Stop writing to old DB

async def create_order(data):
    async with new_db.transaction():
        order_id = await new_db.execute("INSERT INTO orders ...", data)
        await new_db.execute("INSERT INTO outbox (event_type, payload) VALUES ('OrderCreated', ...)")
    return order_id

Old DB no longer source of truth for orders. Outbox publishes to Kafka.

5.5 Phase 5 — Other services consume events

Service B reads order events from Kafka:

async for event in kafka_consumer("OrderCreated"):
    # Update local read model in Service B's DB
    await service_b_db.execute("INSERT INTO order_views (...) VALUES ...", event.data)

5.6 Phase 6 — Drop old DB table

After grace period (3 months), drop orders from old DB.

-- Old DB
DROP TABLE orders CASCADE;  -- careful!

---

6. Cross-DB Joins Problem

Old code:

SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id;

After split: users in DB A, orders in DB B. Can’t JOIN.

6.1 Solution A: API composition

Application code does join:

orders = await order_service.list_orders(user_id)
user = await user_service.get_user(user_id)
return [(user.name, o.total) for o in orders]

Pros: simple. Cons: N+1 if many users.

6.2 Solution B: Materialized read model

Order service maintains denormalized data:

-- In Order service DB
CREATE TABLE orders (
    id bigint PRIMARY KEY,
    user_id bigint NOT NULL,
    user_name_snapshot text NOT NULL,  -- denormalized
    user_email_snapshot text,
    total bigint
);

User service events → Order service updates snapshots:

async def on_user_updated(event):
    await order_db.execute(
        "UPDATE orders SET user_name_snapshot = $1 WHERE user_id = $2",
        event.new_name, event.user_id
    )

Eventually consistent. Acceptable for non-critical (display name).

6.3 Solution C: CQRS read model

Separate read service with denormalized data from many services:

graph LR
    UserDB[(User DB)] -.events.-> Kafka
    OrderDB[(Order DB)] -.events.-> Kafka
    Kafka --> ReadModel[(Read Model DB<br/>denormalized)]

    Client --> ReadModel

For complex queries spanning many services.

6.4 Solution D: Distributed SQL

If joins truly necessary across all: don’t split. Or use distributed SQL (CockroachDB, Spanner) to keep logical join while physically distributed.

---

7. Distributed Transactions

Old monolith:

BEGIN;
UPDATE inventory SET qty = qty - 1 WHERE product_id = X;
INSERT INTO orders ...;
COMMIT;

After split: inventory in Service A, orders in Service B. No 2PC across DBs.

7.1 Saga pattern (orchestrated)

async def place_order(items):
    saga = SagaCoordinator()
 
    # Step 1: reserve inventory
    res = await inventory_service.reserve(items)
    saga.add_compensation(lambda: inventory_service.release(res.reservation_id))
 
    try:
        # Step 2: create order
        order = await order_service.create(items, res.reservation_id)
        saga.add_compensation(lambda: order_service.cancel(order.id))
 
        # Step 3: payment
        payment = await payment_service.charge(order.total)
        saga.add_compensation(lambda: payment_service.refund(payment.id))
 
        # Step 4: confirm
        await inventory_service.commit(res.reservation_id)
        await order_service.confirm(order.id)
 
    except Exception:
        await saga.run_compensations()
        raise

7.2 Saga pattern (choreographed)

Each service responds to events independently:

• Order placed event → Inventory: reserve, emit InventoryReserved
• InventoryReserved → Payment: charge, emit PaymentConfirmed
• PaymentConfirmed → Order: confirm, emit OrderConfirmed
• Any fail → emit Compensate event

More decentralized but harder to trace.

7.3 Tools

• Temporal.io — durable workflow engine
• Camunda — BPM
• AWS Step Functions — managed
• DIY — orchestration in app code

---

8. Cutover Risks

8.1 Data loss

Reconciliation jobs MANDATORY:

• Daily: count rows in old vs new
• Sample: deep diff random records
• Alert on mismatch

8.2 Performance regression

Add monitoring before/after migration. Compare P99 latency.

8.3 Schema drift

Old DB schema gets out of sync with new. Document freeze of old schema once new is source of truth.

8.4 Dependency surprises

Some forgotten service still queries old table. Audit before drop:

SELECT application_name, count(*) FROM pg_stat_activity
WHERE query ILIKE '%orders%' GROUP BY application_name;

Grace period before drop: 1-3 months.

---

9. Timeline Estimate

Realistic for 500GB DB, 5 domains, 10-person team:

gantt
    title Migration timeline (12-18 months)
    section Preparation
    Discovery + bounded contexts  : p1, 0, 6w
    CDC infrastructure           : p2, after p1, 4w

    section Migration per domain
    User domain (low risk)        : d1, after p2, 12w
    Catalog domain                : d2, after d1, 12w
    Order domain (high risk)      : d3, after d2, 16w
    Payment domain                : d4, after d3, 12w
    Review domain (low risk)      : d5, after d4, 8w

    section Cleanup
    Drop old tables               : c1, after d5, 4w
    Decommission old DB           : c2, after c1, 4w

Risk-ordered: start low-risk to learn, hardest in middle, easy last.

---

10. When NOT to Microservice

flowchart TD
    A[Considering split?] --> B{Team size?}
    B -->|<10 engineers| C[Don't split - modular monolith better]
    B -->|>20| D{Clear bounded contexts?}
    D -->|No| C
    D -->|Yes| E{Pain from coupling real?}
    E -->|No| C
    E -->|Yes| F[Strangle fig - one domain at a time]

    style C fill:#fff9c4
    style F fill:#c8e6c9

Cautionary tale: Many companies regret going microservices too early. Modular monolith with internal interfaces often gets 80% of benefits with 20% of complexity.

---

11. Lessons (From Real Migrations)

• Communicate — Slack channel per migration domain, weekly updates
• Don’t rush — extra month of dual-write OK
• Reconciliation is mandatory — assume something will diverge
• Monitor everything — both old and new during transition
• Plan rollback — ability to revert each phase
• Document — runbook + decision log
• Train team — distributed system different mental model

---

12. Anti-patterns

Pattern	Why bad	Fix
Big-bang migration	Disaster risk	Strangler fig
Drop old DB immediately	No rollback	Grace period 3+ months
Skip reconciliation	Silent data loss	Daily audit
Forced sync API everywhere	Latency stack-up	Events + eventual consistency
Distributed transactions for everything	Complexity, fragile	Saga, accept eventual
Each service own DB tech	Operational explosion	Default Postgres, justify exceptions

---

13. Outcome

Endgame:

• Each team owns DB
• Independent deployment
• Smaller schemas, smaller blast radius
• Some operational overhead increase
• Net win when team scaled, painful early

---

Cập nhật: 2026-05-16

Khóa Database Mastery hoàn thành. 16 weekly + 8 bonus + 5 case study.

MOC-Database-Mastery | Roadmap