🏗️ Section 17 · Question #13

What is Database per Service pattern

The Database per Service pattern is a foundational architectural principle of microservices requiring that every microservice owns and completely encapsulates its own private da...

🏗️ Microservices 📄 Source Markdown

🟢 Junior Level

The Database per Service pattern is a foundational architectural principle of microservices requiring that every microservice owns and completely encapsulates its own private database.

[Order Service] ──────► [(Private Order DB)]
      ▲                         │
      │ REST / gRPC / Kafka     │ (Direct cross-database access is STRICTLY FORBIDDEN!)
      ▼                         ▼
[User Service]  ──────► [(Private User DB)]

The Cardinal Rule of Microservices

No microservice is ever permitted to establish a direct JDBC/SQL connection to another microservice’s database, read its private tables, or execute cross-service SQL queries. All inter-service data interactions must occur exclusively via well-defined public APIs (REST, gRPC) or asynchronous events published through a message broker (Kafka, RabbitMQ).

Core Architectural Benefits

  1. Loose Coupling & Autonomous Releases: The engineering team owning User Service can rename columns, alter schemas, or run Liquibase/Flyway migrations at any time without coordinating changes with or breaking dependent teams.
  2. Failure Isolation (Blast Radius): If the Product Catalog database crashes due to a runaway query or storage exhaustion, the Order Processing and Payment databases remain completely unaffected and operational.
  3. Polyglot Persistence: Each microservice can select the storage engine best suited to its specific data access patterns (e.g., Relational for billing, Document for catalog, Graph for social networks).

[!NOTE] Apartment Kitchens Analogy: A shared monolithic database is like a communal kitchen in a crowded hostel. If one resident leaves the stove burning or locks the refrigerator door, no one else in the building can prepare food. Database per Service provides private individual apartments with dedicated private kitchens. If you need milk from your neighbor, you do not unlock their door; you knock and ask via an API request.

🟡 Middle Level

1. Three Physical Isolation Strategies

Isolation Strategy Architecture Setup Advantages Disadvantages & Risks
1. Database Server per Service A dedicated physical or cloud database instance (e.g., AWS RDS / PostgreSQL cluster) per microservice. 🟢 Maximum Isolation: 100% dedicated CPU, RAM, disk IOPS, and separate failure domains. Highest cloud hosting cost; operational overhead of administering dozens of database clusters.
2. Database Schema per Service A shared database server instance, but each service operates in its own isolated schema (orders_schema.*, users_schema.*). 🟢 Cost-Effective: Low infrastructure footprint; simplified unified database backups. 🔴 Noisy Neighbor Hazard: A slow, unindexed query in one service can exhaust server CPU/RAM and crash the entire instance.
3. Private Table Grants A shared database and schema, with table access restricted via dedicated SQL database user credentials. Easiest starting point when refactoring a legacy monolith. Fragile: High risk of developers accidentally bypassing rules or leaking cross-table permissions.

2. How to Retrieve Cross-Service Data Without SQL JOINs

Because relational JOINs across distinct microservice databases are physically impossible, distributed systems utilize three proven data retrieval patterns:

A. API Composition (Synchronous Edge Aggregation)

When a client requests a composite view (e.g., Order Details with Customer Name), an API Gateway or Backend for Frontend (BFF) issues parallel REST/gRPC calls:

  • Call 1: GET /orders/101 $\to$ returns { "orderId": 101, "userId": 42, "amount": 150.00 }
  • Call 2: GET /users/42 $\to$ returns { "userId": 42, "name": "Jane Doe", "email": "jane@example.com" } The gateway merges the JSON payloads into a unified view for the client frontend.

B. Event-Driven Data Replication (Asynchronous Denormalization)

The Order Service subscribes to a Kafka topic user-events. When a customer registers or updates their shipping address, Order Service consumes the event and saves a local read-only copy of the customer’s identity (userId, name, shippingAddress) in its local order_customers table. Benefit: Order Service can query order details locally without making synchronous network hops to User Service.

C. CQRS (Command Query Responsibility Segregation)

A dedicated Query Service subscribes to domain events emitted by multiple microservices and populates a denormalized read-optimized storage engine (Elasticsearch, ClickHouse, or MongoDB) tailored for fast full-text searching, dashboarding, and multi-entity reporting.

🔴 Senior Level

1. Polyglot Persistence in Enterprise Microservices

Decoupling databases allows architects to abandon the “one-size-fits-all” relational database trap and select purpose-built storage engines for each Bounded Context:

[User & Billing Service]    ──► PostgreSQL     (ACID transactions, relational integrity, audits)
[Product Catalog]           ──► MongoDB        (Flexible polymorphic JSON document schemas)
[Shopping Cart & Sessions]  ──► Redis Cluster  (Sub-millisecond in-memory key-value lookups with TTL)
[Product Search Service]    ──► Elasticsearch  (Faceted search, inverted indexes, typo tolerance)
[Fraud Detection Service]   ──► Neo4j          (Graph database for detecting syndicates and circular transactions)
[Real-Time Analytics]       ──► ClickHouse     (Columnar engine aggregating billions of metrics per second)

2. Data Consistency & Managing Data Drift

When data is asynchronously denormalized across services (e.g., Order Service caching a copy of the user’s shipping address), Data Drift (eventual inconsistency) inevitably emerges:

  • Network partitions or consumer processing errors can delay address updates, leaving stale records in downstream databases.
  • Architectural Safeguards:
    1. Data Ownership & Single Source of Truth: Clearly define that User Service is the authoritative owner of customer data. Downstream copies are treated strictly as immutable snapshots valid at the time the order was placed.
    2. Automated Audit & Reconciliation: Run scheduled background reconciliation batch jobs (or streaming Change Data Capture audits via Apache Flink) that compare cryptographic checksums across service boundaries and publish corrective events.

3. Required Supporting Architectural Patterns

Operating Database per Service requires adopting complementary distributed data patterns:

  • The Saga Pattern: Replaces distributed Two-Phase Commit (2PC) with a sequence of local transactions coordinated via events or an orchestrator.
  • The Transactional Outbox Pattern: Guarantees that local database updates and outgoing message broker events are committed atomically, preventing dual-write inconsistencies.
  • Anti-Corruption Layer (ACL): Translates external message contracts into internal domain entities, preventing external schema models from polluting internal database representations.

4 Tricky Questions

1. How do you implement GDPR compliance (“Right to be Forgotten” / data erasure) when customer data is replicated across 20 independent microservice databases?

Answer:

  1. Choreographed Deletion Saga: The User Service publishes a high-priority UserDeletedEvent(userId) to an event broker. All 20 microservices subscribe to this topic and execute local cleanup logic.
  2. Anonymization over Hard Deletion: Systems subject to financial and regulatory auditing (e.g., Billing and Invoicing services) are legally required to retain transaction records for 5–10 years. These services cannot perform an SQL DELETE. Instead, they anonymize the data: replacing personal identifiers with pseudonyms (name = "GDPR_REMOVED", address = "ANONYMIZED", tax_id = null).
  3. Crypto-Shredding (Cryptographic Erasure): A user’s personally identifiable information (PII) is encrypted with an individual per-user encryption key stored in a centralized Key Management Service (KMS). To legally delete the user across all 20 databases simultaneously, the application deletes the user’s encryption key from the KMS. All historical ciphertext across all databases instantly becomes mathematically irrecoverable noise without needing to touch 20 separate databases.

2. How do you prevent the $N+1$ Network Request problem during synchronous API Composition (e.g., displaying a list of 50 orders with customer names)?

Answer: If an API Gateway or BFF fetches 50 orders from Order Service and sequentially executes 50 HTTP calls to User Service (GET /users/{id}), response times degrade drastically. Architectural Mitigations:

  • Batch Endpoint: Implement a bulk retrieval API on User Service: POST /api/v1/users/batch-get with body {"userIds": [1, 2, ... 50]}, returning a map of user profiles in a single HTTP round-trip.
  • Local Denormalization: At order placement time, capture an immutable snapshot of the user’s name and email directly inside the order record. The order list can then be rendered entirely from Order Service without querying User Service.
  • GraphQL DataLoader: Use DataLoader batching and request deduplication at the gateway layer to merge individual field resolvers into a single consolidated backend request.

3. How can an early-stage startup adopt Database per Service without the budget to pay for 30 distinct cloud database instances?

Answer: Adopt the Logical Database per Service (Schema per Service) model on a single shared PostgreSQL instance:

  1. Deploy a single production-grade PostgreSQL cluster with read replicas.
  2. Provision a distinct logical database (CREATE DATABASE service_db) or schema (CREATE SCHEMA service_schema) for each microservice, accessible only via a dedicated database user credential with restricted permissions (REVOKE ALL ON ALL TABLES IN SCHEMA...).
  3. Enforce architectural boundaries in CI/CD using ArchUnit tests or SQL linters to ensure services only access their assigned schema. When the company scales and traffic justifies the cost, any individual schema can be migrated to a dedicated physical AWS RDS instance via standard dump-and-restore tools without rewriting application code.

4. Why are Shared Database Views or Stored Procedures between microservices considered a covert distributed anti-pattern?

Answer: Shared SQL Views and cross-database Stored Procedures create hidden, unversioned coupling at the database layer. When Team A modifies a column or table structure in Service A’s database, the shared view breaks unexpectedly, crashing Service B in production without Team A even being aware that Service B depended on that view. This destroys independent deployment pipelines, forces cross-team release coordination, and transforms the architecture into the worst distributed anti-pattern: a Distributed Monolith.

🎯 Interview Cheat Sheet

Must Know

  • The Golden Rule: Every microservice owns its private database; direct SQL/JDBC access from external services is strictly forbidden.
  • Communication Contract: Microservices interact solely through public network APIs (REST, gRPC) or event streams (Kafka).
  • Core Benefits: Independent team velocity, autonomous schema migrations, localized blast radius (fault isolation), and polyglot persistence.
  • Inherent Trade-Offs: Loss of SQL JOINs and single-node ACID transactions; requires eventual consistency and Saga workflows.
  • Data Access Patterns: Synchronous API Composition (Gateway/BFF), Asynchronous Event Replication (Kafka projections), and CQRS Read Models.
  • Startup Pragmatism: Start with Schema per Service on a single PostgreSQL server before spinning up dozens of cloud database instances.

Physical Isolation Options

Approach Isolation Level Cost Risk
Server per Service Physical / VM level High None
Schema per Service Logical database level Low Noisy Neighbor CPU contention
Private Tables Table grant level Very Low Accidental privilege leakage

Common Follow-up Questions

  • “How do you handle reporting across all microservices?” $\to$ Ingest domain events via Kafka into an OLAP data lake / warehouse (ClickHouse, Snowflake, BigQuery) rather than running cross-database joins on operational OLTP databases.
  • “What is Crypto-Shredding?” $\to$ Deleting a user’s encryption key from KMS, instantly rendering all replicated personal data unreadable across all databases without deleting rows.
  • “Can two services use the same database if one is read-only?” $\to$ No; it couples the read-only service to the internal database schema of the other service, preventing schema refactoring.

Red Flags (DO NOT Say)

  • ❌ “It is fine for Service A to query Service B’s database directly as long as it uses a Read-Only SQL user.” (Violates encapsulation and creates invisible schema coupling).
  • ❌ “We execute distributed cross-database SQL JOINs inside our application code.” (Transfers gigabytes of raw data over the network and crushes application memory).
  • ❌ “Database per Service requires provisioning 50 separate physical database servers on day 1.” (Logical schema separation on a shared server achieves the same architectural boundary at minimal cost).