Shared database anti-pattern in Microservices - System Design Exercise

Design: Microservices Architecture with Shared Database

Focus on the implications of using a shared database among microservices. Out of scope are detailed service implementations or UI design.

Functional Requirements

FR1: Multiple microservices must access and update data related to their domain.

FR2: Each microservice should be independently deployable and scalable.

FR3: Data consistency must be maintained across services.

FR4: Services should be loosely coupled to allow independent development.

Non-Functional Requirements

NFR1: Database schema is shared among all microservices.

NFR2: Latency for API responses should be under 300ms p99.

NFR3: System should handle 5,000 concurrent users.

NFR4: Availability target is 99.9% uptime.

Think Before You Design

Questions to Ask

❓ Question 1

❓ Question 2

❓ Question 3

❓ Question 4

Key Components

Microservices

Shared relational database

API Gateway or service communication

Data access layer

Caching layer

Design Patterns

Database per service pattern

Event-driven architecture

API composition

CQRS (Command Query Responsibility Segregation)

Reference Architecture

          +----------------+          
          |  API Gateway   |          
          +-------+--------+          
                  |                   
  +---------------+---------------+  
  |               |               |  
+----+         +----+          +----+
| S1 |         | S2 |          | S3 |
+----+         +----+          +----+
   |              |               |   
   +--------------+---------------+   
                  |                   
          +-------+--------+          
          | Shared Database |          
          +----------------+          

Components

Microservices (S1, S2, S3)

Any language/framework (e.g., Spring Boot, Node.js)

Implement business logic for different domains but share the same database schema.

Shared Database

Relational DB (e.g., PostgreSQL, MySQL)

Central data store accessed by all microservices.

API Gateway

Nginx, Kong, or custom gateway

Route client requests to appropriate microservices.

Data Access Layer

ORM or direct SQL

Microservices use this to read/write shared database.

Cache Layer (optional)

Redis or Memcached

Improve read performance and reduce DB load.

Request Flow

1. Client sends request to API Gateway.

2. API Gateway routes request to the appropriate microservice.

3. Microservice executes business logic and accesses shared database directly.

4. Microservice reads or writes data in shared tables.

5. Microservice returns response to API Gateway.

6. API Gateway sends response back to client.

Database Schema

Entities: User, Order, Product, Payment Relationships: - User 1:N Order - Order N:1 Product - Order 1:1 Payment All tables are shared and accessed by multiple microservices, leading to tight coupling.

Scaling Discussion

Bottlenecks

Tight coupling due to shared schema limits independent deployment.

Schema changes require coordination across teams.

Database becomes a single point of failure and performance bottleneck.

Hard to enforce service boundaries and data ownership.

Risk of data corruption due to concurrent writes from multiple services.

Solutions

Adopt database per service pattern to isolate data ownership.

Use asynchronous communication (events) to sync data between services.

Implement API composition or CQRS to separate read/write models.

Introduce service-level caching to reduce database load.

Use database replication and sharding to improve availability and scalability.

Interview Tips

Time: Spend 10 minutes understanding the shared database pattern and its drawbacks, 15 minutes discussing alternatives and improvements, 10 minutes on scaling and trade-offs.

Explain how shared database creates tight coupling and deployment challenges.

Discuss impact on team autonomy and service independence.

Highlight risks of schema changes and data consistency issues.

Present alternatives like database per service and event-driven design.

Show understanding of scaling bottlenecks and mitigation strategies.