Before the order service accepts an order, it needs one fact: is there enough stock for every line? Stock belongs to the inventory service. On my laptop, both services' databases ran in the same Postgres container, milliseconds apart. Had they been two schemas in one database, the answer would have been one query:
-- The query I didn't write
SELECT item.stock_item_id
FROM orders.order_items AS item
JOIN inventory.stock_items AS stock ON stock.id = item.stock_item_id
WHERE item.order_id = $1
AND stock.on_hand - stock.reserved < item.quantity;It would have been fast, and it would quietly have welded two services into one. Here's why that query doesn't exist, what replaced it and what that costs, on a platform of thirteen NestJS services whose thirteen databases share one Postgres cluster.
Why not just JOIN across services?
The JOIN works today. You pay for it later, in three ways.
The schema becomes a contract. The day inventory renames on_hand, the order service breaks at runtime, not at compile time. Every inventory migration now needs sign-off from the order side, so a schema change has become a coordinated release of two services.
The answer is only true for an instant. Between the read and the commit, another order can reserve the same units. Keeping the answer true would mean locking inventory's rows from inside the order service's transaction, with one service holding locks on another's data.
Nobody owns the rule. Available stock (on_hand - reserved) must never go negative. When two services touch those rows, each assumes the other enforces that.
A read across a service boundary isn't really a read. It's a dependency with no version number.
What does database per service actually mean?
Each service owns one logical database: its schema, its migrations and its credentials. Other services reach that data only through the owner's API (gRPC) or the owner's events (Kafka), never through its tables.
Draw the boundaries around rules that must hold, not around nouns. Facts that must change together belong in one database: "on hand" and "reserved" must always agree, so inventory owns both. An order and the stock reserved for it live in different services, and that gap is the rest of this article.
In the repository, ownership is something a machine checks. One catalog file lists thirteen rows: service, database, login role, connection-string variable and migration config. A script refuses to load it if any row breaks the pattern (order must map to order_db, order_app and ORDER_DATABASE_URL).
Can thirteen databases share one Postgres cluster?
Yes. "Database per service" never meant "server per service". The architecture decision record (ADR) calls a shared physical cluster an operational choice, not a relaxation of ownership. Locally, one Postgres container holds all thirteen databases. The infrastructure code describes one shared managed instance too, though nothing proves it has been built.
Why separate databases instead of separate schemas?
In Postgres, a connection belongs to one database: the documentation on schemas says a connection can only reach data in the database it connected to. Crossing over takes dblink or postgres_fdw, installed on purpose. Two schemas in one database are one GRANT away from the query above. I wanted that shortcut to be hard to take, not just against the rules.
How do you stop a service from connecting to another service's database?
Roles live at the cluster level, and each database has a CONNECT privilege. That's enough for a wall. Here is one service's bootstrap, simplified from the local init script (:'order_password' is a psql variable):
CREATE ROLE order_app WITH LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE
NOINHERIT NOREPLICATION NOBYPASSRLS PASSWORD :'order_password';
CREATE DATABASE order_db OWNER order_app;
REVOKE CONNECT ON DATABASE order_db FROM PUBLIC;
REVOKE CONNECT ON DATABASE order_db FROM inventory_app, engagement_app; -- …and the other ten
GRANT CONNECT ON DATABASE order_db TO order_app;People tend to forget the PUBLIC line. Postgres grants CONNECT and TEMPORARY on every new database to PUBLIC by default, so without that revoke every login role can open every database. With it, a wrong connection fails the way you want:
$ psql -U inventory_app -d order_db
psql: error: FATAL: permission denied for database "order_db"
DETAIL: User does not have CONNECT privilege.The cloud bootstrap goes further. It reads each access list back and revokes CONNECT from everyone but the owner, refuses roles with superuser or RLS-bypass attributes, and rejects a connection URL whose user or database doesn't match the catalog. One caveat: a superuser skips privilege checks entirely, so the admin credential belongs only to the bootstrap job.
What does a shared cluster keep, and what does it give up?
It keeps the logical walls and gives up the physical ones:
| Concern | One cluster, many databases | One cluster per service |
|---|---|---|
| Schema, migrations, credentials | Isolated | Isolated |
| Cross-service JOIN or foreign key | Impossible on a normal connection | Impossible |
CPU, memory, disk I/O, max_connections |
Shared: one noisy service slows all | Separate |
| Crash, restart, failover | All thirteen together | Independent |
| Major version upgrade | One window for everyone | Per service |
| Point-in-time recovery | Whole cluster only | Per service |
The last row surprised me. The cluster has one write-ahead log, and a physical backup plus WAL replay restores a whole cluster, never a subset. pg_dump works per database but only captures the moment it ran. To rewind order_db to 14:02, you restore the entire cluster elsewhere, then copy order_db back. Rehearse that before you need it.
The escape hatch: since nobody reaches into another service's database, moving a busy service to its own server is a dump, a restore and one new connection string.
What replaces foreign keys between services?
An order can't have a foreign key to a customer or a tenant, because those rows live elsewhere. It stores the reference as a value:
model Order {
id BigInt @id @default(autoincrement())
publicId String @unique @db.Uuid
tenantPublicId String @db.Uuid // another service owns it: indexed, never a foreign key
customerPublicId String? @db.Uuid // another service owns it
status OrderStatus @default(DRAFT)
version Int @default(1)
createdAt DateTime @default(now()) @db.Timestamptz(3)
items OrderItem[] // same database: a real relation
@@index([customerPublicId, createdAt(sort: Desc), publicId(sort: Desc)])
}Those are UUIDv7 public ids; joins inside a database use BIGINT keys that never leave the service, as the piece on UUIDv7 and bigint keys explains. Three habits do the foreign key's job.
Ask the owner, with a deadline. On one order path the caller needs an immediate yes or no, so the order service asks inventory over gRPC whether it can cover the quantities. The call has a 2-second deadline, and the reply carries an asOf timestamp. If inventory doesn't answer in time, the order is refused with a 503 rather than guessed. Treat the answer as a hint, because it's stale on arrival. The guarantee comes later, from the reservation. (Choosing between REST, gRPC and Kafka has its own article.)
Snapshot what you'll need. Each order line copies the name, SKU, unit price, tax rate and discount when the order is placed. Later catalog edits never rewrite history, and reading an old order calls nobody.
Expect dangling references. An order can point at a customer whose profile was later pseudonymized. Reads must tolerate that, and drift is caught by reconciling from events, not prevented by a constraint.
How does a saga work?
Now for writes. Placing an order can touch three other owners: inventory reserves stock, engagement holds the coupon from the price quote, and delivery creates a job if the order ships. In a monolith, that's one transaction. Here it's up to four databases, and the rule is firm: writes across services use a saga, never a distributed database transaction.
I think of it as moving house through three separate firms: packers, a van company and a storage unit. No button cancels "the move". If the van falls through after the packers have boxed everything, there's no rollback. You call the packers and ask them to unpack, maybe for a fee. What you do have is a notebook: who's booked, who confirmed, who still owes an answer. A saga is that notebook plus a plan for every call that goes wrong.
Garcia-Molina and Salem named the idea in their 1987 paper Sagas. A long transaction is split into local transactions that each commit on their own. If a later one fails, compensating transactions undo the effects of the earlier ones.
The diagram compresses the states. In code, it runs as four kinds of local transaction.
1. Open the notebook. One order-database transaction inserts the order in PENDING_INVENTORY, its lines and its history rows. It also inserts a saga row (type CREATE, status STARTED, a deadline 15 minutes out) and one step per participant: RESERVE_INVENTORY always, HOLD_ENGAGEMENT if there's a coupon quote, CREATE_DELIVERY if the order ships. An order.created event and the first stock-reservation request go into the outbox in the same commit.
2. Send commands. The other steps, and every retry, become command rows in the same outbox. A relay publishes them to Kafka, keyed by order id so one order's messages stay in sequence. Each command carries the saga id, order version, participant, action, attempt number and an idempotency key <sagaId>:<action>.
3. Each participant acts locally. In one transaction, a participant claims the command in its inbox, applies the effect and writes an outcome to its own outbox. Inventory reads the stock and rejects the request with INSUFFICIENT_STOCK if it's short. Then it writes with an update that re-checks the same rule:
WITH requested(id, quantity, expected_version) AS (VALUES ($1::bigint, $2::numeric, $3::int))
UPDATE stock_items AS stock
SET reserved = stock.reserved + requested.quantity,
version = stock.version + 1
FROM requested
WHERE stock.id = requested.id
AND stock.version = requested.expected_version
AND stock.reserved <= stock.on_hand - requested.quantity
RETURNING stock.id;If another reservation lands between the read and the write, this matches no rows and nothing is oversold. A real shortage is committed as a fact: succeeded: false, retryable: false.
4. Read the replies. Each outcome gets its own order-database transaction: deduplicate, mark the step, decide what's next. When every step has succeeded, the order moves to PENDING_PAYMENT and the saga to SUCCEEDED, with a history row and an order.updated event in the same commit. When a step fails for good, compensation starts.
The diagram draws choreography: each service reacts to the previous event. The code uses orchestration: the order service sends commands and records replies. With three participants, retries, a deadline and compensation, I want one row that says what an order is waiting for.
Is a compensating transaction just an undo?
No. A rollback erases. A compensation adds a new fact that cancels an earlier one. When the packers unpack, that's a second job, and the invoice lists both.
Every step that succeeded gets a matching compensation: RELEASE_INVENTORY for a reservation, REVERSE_ENGAGEMENT for a hold, CANCEL_DELIVERY for a delivery job. Each is a new step with its own idempotency key, sent like any other command. Only when all of them succeed does the order become FAILED and the saga COMPENSATED, in a transaction that also returns the order's slot in the tenant's active-order limit.
The data model follows the same rule. Points, stock and payments are append-only ledgers, and a correction is a reversal entry linked to the original. When an edit saga fails halfway, the order isn't rewound. The service writes a new revision whose contents match the old one, so the version goes up, never back.
Holds make compensation cheap. A coupon isn't spent at quote time, only held, and the hold has its own expiry. If a saga dies and nobody compensates, the hold lapses on its own, like a storage booking that expires when you don't turn up.
Some effects can't be compensated. You can't unsend a notification or undeliver a parcel. Decide where the point of no return sits: put irreversible steps last, or behind an explicit privileged path.
Where does the saga's state live?
In two state machines, both stored as rows in the order database. Every order transition is a conditional update:
const moved = await tx.order.updateMany({
where: { id: saga.orderId, version: saga.orderVersion, status: 'PENDING_INVENTORY' },
data: { status: 'PENDING_PAYMENT', version: { increment: 1 } },
});
if (moved.count !== 1) throw new SagaOutcomeConflictError();
await tx.orderStatusHistory.create({
data: {
orderId: saga.orderId,
fromStatus: 'PENDING_INVENTORY',
toStatus: 'PENDING_PAYMENT',
reasonCode: 'SAGA_SUCCEEDED',
correlationId: outcome.correlationId,
},
});There's no read-then-write. The WHERE clause is the state machine. If the order was cancelled meanwhile, or a duplicate message already moved it, nothing matches and the transaction aborts. History commits in the same transaction, so the audit trail can't disagree with the state. Giving a retried request its earlier answer instead of a conflict is a separate problem, covered in idempotency keys and optimistic locking.
The saga has its own states: STARTED, WAITING, COMPENSATING, SUCCEEDED, COMPENSATED, FAILED and MANUAL_RECOVERY, plus a deadline. Steps are unique on saga, participant and action, so a duplicate can't create a second "reserve stock" step.
What happens when a participant never answers?
Back to the notebook: the van company doesn't pick up. You try again in a minute, then five, then an hour, and eventually you hand it to a person. The code does the same. Every 5 seconds a scheduler looks for due steps and passed deadlines. Before resending, it claims the step: a conditional update bumps the attempt count and pushes the next attempt 90 seconds out as a lease, so two replicas never send the same command. Retryable failures back off at 1 s, 5 s, 30 s, 2 min and 10 min, with ±20% jitter. After six attempts, the step goes to MANUAL_RECOVERY and a dead-letter event is written.
The saga as a whole has a 15-minute deadline. If it passes with nothing in flight, compensation starts. If the saga was already compensating, automation has run out of moves, and the saga goes to MANUAL_RECOVERY.
That's a real state, not a log line. The only way out is a replay, which requires a dedicated permission, the service-owner role and a written reason. It locks the saga row, resets the unfinished steps, grants a fresh 15 minutes and records who did it and why.
What if messages arrive twice, late or early?
Kafka delivers at least once, so every outcome goes through the inbox in the same transaction as its effect:
- Seen before (same event id): skipped. Same id with a different payload: a conflict.
- Too early (unknown saga, or a newer order version): parked and retried later. After five retries it's rejected and dead-lettered.
- Too late (a finished saga, or a mismatched order): recorded as rejected, never applied.
What it costs
- Users see in-between states. An order can sit in
PENDING_INVENTORYfor seconds or minutes, and the interface has to say so. - There are many more tables. Saga, steps, outbox, inbox, history and revisions are state you can't drop without breaking replay.
- Failure handling dominates. The file that drives the order saga runs to about 1,400 lines, and most of it handles something going wrong.
- Reporting can't JOIN thirteen databases. It builds read models from events and returns
asOfwith every answer. - The shared cluster couples failure. One service's runaway query or connection leak is everyone's problem.
When should you not split the database?
When one team ships one deployable, a modular monolith gives you real transactions for free. Data that must change together belongs to one owner; wanting a distributed transaction usually means the boundary is wrong. And if you can't afford compensation and recovery for every step, you can't afford the split.
What I'd do differently
Keep the decision record in sync with the catalog. ADR-0005 still says "11 logical databases"; the catalog has listed thirteen since two services were added. A machine checks the catalog, and nothing checks the prose. I'd have the ADR link to the catalog instead of stating a number.
Cap each role. Postgres can limit connections per role (ALTER ROLE order_app CONNECTION LIMIT 20) and set a per-role statement_timeout. Neither is set today. On a shared cluster, they're the cheapest defence against a noisy neighbour.
Rehearse a one-service restore. The ADR withholds acceptance until topology, backups, a restore drill, migrations and access controls are recorded. None of that is verified yet.
Write the failure table first. For each step, record what compensates it, whether that can fail and who gets paged. Leases, parking and manual recovery are easier to design up front than to bolt onto a working happy path.
Key takeaways
- A JOIN across services turns one service's schema into another's contract. Go through the owner's API or events.
- Separate databases on one cluster keep the logical walls, but only if you revoke
CONNECTfromPUBLIC. - A shared cluster gives up physical isolation: shared resources, one failure domain, whole-cluster point-in-time recovery.
- Cross-service references are values plus snapshots. Synchronous checks get a deadline and count only as hints.
- A saga is local transactions plus planned compensations, and each compensation is a new fact.
- Put state machines in the
WHEREclause, and makeMANUAL_RECOVERYa real, auditable state.
Every arrow in this saga is a message that must leave one database and land in another, never lost and never applied twice. The transactional outbox and inbox are how.