The Microservices Database Problem
Microservices promise independent deployability and technology freedom, but they create a thorny data management problem. In a monolith, a single transaction can update the users, orders, and inventory tables atomically. In microservices, each of those is a separate service, potentially running on a different database engine, with no shared transaction boundary.
This guide covers the principal patterns for managing data in microservices architectures, their trade-offs, and when to apply each.
Database-per-Service Pattern
The canonical microservices guideline is one database per service. Each service owns its data exclusively — no other service can query or write to it directly.
-- Order Service: owns its own PostgreSQL schema
CREATE TABLE orders (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL, -- Foreign key concept, but no DB-level FK
total_amount DECIMAL(10,2),
status VARCHAR(20) DEFAULT 'pending',
created_at TIMESTAMPTZ DEFAULT now()
);
-- Inventory Service: separate database, possibly different engine
CREATE TABLE inventory (
product_id UUID PRIMARY KEY,
available_qty INT NOT NULL CHECK (available_qty >= 0),
reserved_qty INT NOT NULL DEFAULT 0,
updated_at TIMESTAMPTZ DEFAULT now()
);
-- Notice: no FK to orders.id — services communicate via events, not joins
Benefits:
- Services can scale, deploy, and be replaced independently
- Each service can choose the optimal storage technology (PostgreSQL, MongoDB, Redis)
- Schema changes in one service do not break others
Costs:
- No cross-service joins — data duplication or API composition required
- Distributed transactions are complex
- Eventual consistency instead of strong consistency
The Saga Pattern for Distributed Transactions
When an operation spans multiple services, you need a way to coordinate changes and handle partial failures. The Saga pattern breaks a distributed transaction into a sequence of local transactions, each publishing an event or message to trigger the next step.
-- Order Saga state machine table (in Order Service)
CREATE TABLE order_sagas (
saga_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
order_id UUID NOT NULL REFERENCES orders(id),
current_step VARCHAR(50) NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'in_progress',
-- Steps: RESERVE_INVENTORY → CHARGE_PAYMENT → CONFIRM_ORDER
-- Compensations: RELEASE_INVENTORY, REFUND_PAYMENT
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now()
);
-- Outbox table: guarantees event delivery even if the broker goes down
CREATE TABLE outbox_events (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
aggregate_id UUID NOT NULL,
event_type VARCHAR(100) NOT NULL,
payload JSONB NOT NULL,
published BOOLEAN DEFAULT FALSE,
created_at TIMESTAMPTZ DEFAULT now()
);
-- A saga step: reserve inventory and write event atomically
BEGIN;
UPDATE inventory SET reserved_qty = reserved_qty + $qty
WHERE product_id = $product_id AND available_qty >= $qty;
INSERT INTO outbox_events (aggregate_id, event_type, payload)
VALUES ($order_id, 'INVENTORY_RESERVED', jsonb_build_object(
'order_id', $order_id,
'product_id', $product_id,
'qty', $qty
));
COMMIT;
-- A background worker reads outbox_events and publishes to the message broker
CQRS: Command Query Responsibility Segregation
CQRS separates write operations (commands) from read operations (queries). The write model is optimized for consistency and business rules; the read model is optimized for query performance.
-- Write side: normalized, transaction-safe
CREATE TABLE orders (
id UUID PRIMARY KEY,
user_id UUID NOT NULL,
status VARCHAR(20),
created_at TIMESTAMPTZ
);
CREATE TABLE order_items (
id UUID PRIMARY KEY,
order_id UUID REFERENCES orders(id),
product_id UUID,
quantity INT,
unit_price DECIMAL(10,2)
);
-- Read side: denormalized projection for dashboard queries
-- Rebuilt asynchronously from domain events
CREATE TABLE order_summary_view (
order_id UUID PRIMARY KEY,
user_email VARCHAR(255),
item_count INT,
total_amount DECIMAL(10,2),
status VARCHAR(20),
created_at TIMESTAMPTZ
);
-- Refresh projection from events
INSERT INTO order_summary_view
SELECT
o.id,
u.email,
COUNT(oi.id),
SUM(oi.quantity * oi.unit_price),
o.status,
o.created_at
FROM orders o
JOIN users_read_replica u ON u.id = o.user_id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, u.email, o.status, o.created_at
ON CONFLICT (order_id) DO UPDATE SET
total_amount = EXCLUDED.total_amount,
status = EXCLUDED.status;
API Composition for Cross-Service Queries
When a client needs data from multiple services, an API Gateway or BFF (Backend for Frontend) composes responses by calling each service individually, then merging the results in application code — avoiding direct cross-database joins entirely.
-- Each service exposes a focused query
-- User Service:
SELECT id, email, display_name FROM users WHERE id = ANY($user_ids);
-- Order Service:
SELECT user_id, COUNT(*) AS order_count, SUM(total_amount) AS lifetime_value
FROM orders
WHERE user_id = ANY($user_ids) AND status = 'completed'
GROUP BY user_id;
-- The API Gateway merges results in memory — no cross-database JOIN needed
Visualize Your Service Boundaries with Erdmates
Designing the database boundaries between microservices is one of the hardest architectural decisions. Erdmates lets you create separate ERD diagrams per service, share them with your team, and discuss boundary decisions visually before you write any code — preventing the most common mistake of coupling services through shared tables.
Conclusion
Microservices data management requires deliberate design. Database-per-service gives you independence at the cost of cross-service consistency. The Saga pattern handles distributed transactions through compensating actions. CQRS separates write and read models for flexibility and performance. Choose the patterns that match your consistency requirements, team size, and operational maturity — and document your boundaries clearly in service-level ERD diagrams.