What is the shared database anti-pattern and how do you recognize it?
The shared database anti-pattern is what you get when several independently deployed services read and write the same database schema. Each service looks separate in your deployment diagram, but they are coupled through tables, columns, and implicit assumptions about how the data behaves.
Teams rarely choose this on purpose. One database is the fastest way to start. There is a single connection string, one backup policy, and transactions that span any rows you like. Joins are cheap and correct by construction. When a new service needs customer data, querying the customers table directly beats waiting for another team to expose an endpoint. The cost shows up later, when the schema has become a public API that no one designed and no one owns.
Symptoms to look for
- Multiple writers to the same tables. If two services update
orders.status, neither can enforce its own invariants, and bugs turn into arguments about who changed what. - Coordinated deploys for schema changes. Renaming or dropping a column requires a cross-team release plan because you cannot see every reader.
- Joins across module boundaries. A billing query that joins
invoicestoshipmentsanduser_preferencesties three domains to one physical layout. - Orphaned columns. Fields nobody can explain or safely remove, kept because "something might read it."
Who this guide is for
This guide is for backend and platform engineers whose services already share a schema and who need to untangle it without a rewrite. If your team is small or your domain is stable, a modular monolith that assigns ownership at the schema level may give you most of the benefit at a much lower operational cost.
How should you assign data ownership between services?
Start with one rule: every entity has exactly one owning service, and only that service writes it. Other services can hold copies for lookups, caches, or search indexes. Treat those copies as read-only and possibly stale. If a second service needs to change an order's status, it asks the order service through an API or an event. It does not run an UPDATE against the orders table. This rule turns "who broke this column?" into a question with one answer.
Draw ownership along business capabilities, not along the current table layout. Shared schemas often contain tables that grew together for convenience. One customers table might mix billing addresses, marketing preferences, and support tier. Those fields change for different reasons and are requested by different teams, so they often belong to different services.
Invariants pull the other way. Some rules must hold inside a single transaction, such as "stock never goes negative" or "a ledger's debits equal its credits." Splitting that data across services replaces one atomic write with a distributed protocol and its failure modes. When a rule like this spans several entities, keep them in one service, even if the domain map suggests splitting them.
| Signal | Leans toward |
|---|---|
| Fields change for different business reasons | Separate owners |
| Different teams request schema changes | Separate owners |
| Strict invariant across entities (inventory, balances) | Single owner |
| Writes must be atomic together | Single owner |
Clear ownership also saves engineering time. A schema change becomes one team's decision, released in one deploy. You avoid a negotiation across three teams and a release that has to be scheduled with all of them.
How do you inventory cross-service joins before changing anything?
Before touching any query, build a list of every statement that reads tables owned by more than one service. Don't rely on memory or code search alone. ORM-generated SQL, reporting jobs, and ad-hoc scripts rarely show up in a grep. Combine three sources: a search of application code for queries, the database's own statement logs or query statistics captured over a representative period, and a list of the scheduled jobs and reports that connect directly. Tag each table with its owner from the previous step, and flag any query that touches two or more owners.
For each flagged query, record four facts:
- Call frequency, which tells you whether extra network hops matter.
- Result size, which tells you whether fetching and merging in memory is feasible.
- Freshness requirement, which tells you whether a replicated copy is acceptable.
- Whether filters or sorts span owners, which is the strongest predictor of trouble later.
| Query | Owners | Calls/min | Typical rows | Freshness | Cross-owner filter/sort |
|---|---|---|---|---|---|
| Order detail page | Orders, Customers | 400 | 1 | Seconds | No |
| Admin order search | Orders, Customers | 2 | 50 per page | Minutes | Yes (customer region) |
| Monthly revenue report | Orders, Billing | Batch | 2M | Hours | Yes |
All entries are illustrative.
This table drives the per-query decision in the sections that follow. It also keeps migration cost bounded. Queries that already stay inside one owner's tables need no change. Each cross-owner query gets only the treatment its numbers justify, so the team avoids redesigning everything at once.
How does API composition replace a cross-service join?
An API composer handles a query that used to be a SQL join. It calls each owning service through its public API and joins the results in memory, usually as a hash lookup keyed on the foreign ID. Take an order history page. The composer fetches orders from the order service, collects the distinct customer and product IDs, asks each owner for those records, and stitches the rows together.
The example below uses four techniques:
- Batch endpoints.
GET /customers?ids=...returns many records in one round trip. Fetching per row would turn a 50-order page into 50 network calls, which is the N+1 problem moved onto the network. - Parallel calls. The customer and product lookups don't depend on each other, so they run concurrently. Latency then tracks the slowest call instead of the sum of all calls.
- Per-call timeouts. Each dependency gets its own deadline. A degraded service can then use only its share of the request's latency budget, not all of it.
- Partial results. If the product service fails, the composer still returns orders, marks product details as unavailable, and lets the client decide how to render the gap.
The cost is availability. Each synchronous dependency is another way for the request to fail, and the risks multiply. As an illustrative estimate, assume each of three services is independently available 99.9% of the time. A request that needs all three succeeds 0.999³ ≈ 99.7% of the time. Over a 30-day month, that is about 130 minutes of failed requests instead of about 43. Partial results reduce this impact, but only for data the page can do without.
type Order = { id: string; customerId: string; productId: string };
type Rec = { id: string; name: string };
async function getJson<T>(url: string, ms: number): Promise<T> {
// Per-call timeout: a slow dependency only uses its own share of the budget
const res = await fetch(url, { signal: AbortSignal.timeout(ms) });
if (!res.ok) throw new Error(`${url} -> HTTP ${res.status}`);
return (await res.json()) as T;
}
export async function orderHistory(userId: string) {
// Orders are required: if this call fails, the whole request fails
const orders = await getJson<Order[]>(
`)}`, 300);
if (orders.length === 0) return { orders: [], unavailable: [] };
const ids = (f: (o: Order) => string) => encodeURIComponent([...new Set(orders.map(f))].join(","));
// Batch endpoints (one call per owner, not per row) issued in parallel.
// allSettled lets one dependency fail without discarding the other's result.
const [customers, products] = await Promise.allSettled([
getJson<Rec[]>(` => o.customerId)}`, 200),
getJson<Rec[]>(` => o.productId)}`, 200),
]);
// In-memory join: hash map keyed on the foreign ID, or null if the call failed
const index = (r: PromiseSettledResult<Rec[]>) =>
r.status === "fulfilled" ? new Map(r.value.map(x => [x.id, x])) : null;
const byCustomer = index(customers), byProduct = index(products);
return {
orders: orders.map(o => ({
...o,
customer: byCustomer?.get(o.customerId) ?? null,
product: byProduct?.get(o.productId) ?? null,
})),
// Partial results: tell the client which sections are missing so it can render the gap
unavailable: [byCustomer ? null : "customers", byProduct ? null : "products"].filter(Boolean),
};
}
A TypeScript API composer that fetches orders, then calls the customer and product batch endpoints in parallel with per-call timeouts, joins the results in memory with hash maps, and returns partial results when a dependency fails.
When does API composition stop working, and what are the alternatives?
Latency and availability are not the only limits. Some queries can't be composed efficiently at all. Take "top 20 orders by customer region." The Orders service owns totals and dates, and the Customers service owns region, so neither service can filter and sort alone. The composer has two options:
- Fetch every customer ID in the region, then ask Orders to rank orders across thousands of IDs.
- Page through orders by total and discard non-matching regions until 20 remain. For a small region, this can mean scanning most of the table.
Pagination makes both options worse. Page 3 repeats the work of pages 1 and 2, and results shift between calls. A correct answer requires either fetching a large result set or keeping a precomputed view.
Local read models
A local read model, also called a replicated projection, is a denormalized copy owned by the consumer and fed by events or change data capture. Orders subscribes to customer changes and stores region next to each order, so the query becomes one indexed statement. The consumer never writes back to the source.
Delivery is typically at-least-once and can arrive out of order. To handle this, apply each change as an upsert keyed on entity ID that succeeds only when the incoming version is newer than the stored one, as shown in the example below. With this rule, duplicates and stale events become no-ops. Keep a tested path to rebuild the projection from scratch.
| Composition | Read model | |
|---|---|---|
| Query path | Fan out to services, merge in memory | Local indexed query |
| Freshness | Current | Lags the source |
| Runtime dependencies | All owners | None |
Costs
- Eventual consistency. Reads can be stale. Monitor replication lag as the age of the last applied change.
- Backfills. New fields and new consumers both require backfills.
- Schema coupling. Event schemas can become as hard to change as the shared tables were.
- Reporting. For reporting across many services, a separate analytical store is usually a better fit than more projections.
-- Local read model owned by the Orders service (PostgreSQL).
-- Fed only by customer change events; Orders never writes back to Customers.
CREATE TABLE customer_projection (
customer_id uuid PRIMARY KEY,
region text NOT NULL,
version bigint NOT NULL, -- monotonically increasing per customer at the source
changed_at timestamptz NOT NULL, -- when the source made the change (for lag monitoring)
applied_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ON customer_projection (region);
-- Apply one change event. Parameters come from the event payload.
-- Duplicates and stale (out-of-order) events match no row in the WHERE clause,
-- so they become no-ops instead of overwriting newer data.
INSERT INTO customer_projection (customer_id, region, version, changed_at)
VALUES ($1, $2, $3, $4)
ON CONFLICT (customer_id) DO UPDATE
SET region = EXCLUDED.region,
version = EXCLUDED.version,
changed_at = EXCLUDED.changed_at,
applied_at = now()
WHERE customer_projection.version < EXCLUDED.version;
-- The cross-service query is now a single local statement.
SELECT o.order_id, o.total, o.created_at
FROM orders o
JOIN customer_projection c ON c.customer_id = o.customer_id
WHERE c.region = 'eu-west'
ORDER BY o.total DESC
LIMIT 20;
-- Replication lag: age of the most recently applied source change.
SELECT now() - max(changed_at) AS replication_lag
FROM customer_projection;
A PostgreSQL customer projection inside the Orders service, showing a version-guarded upsert that ignores duplicate and out-of-order events, the resulting single-query 'top 20 orders by region', and a simple replication-lag check.
How do you migrate off a shared database incrementally?
Migrate in a fixed order. Each step reduces what other services can touch, and each one is reversible until the last.
Step by step
- Put the owner's API in front of the tables. At first it can be a thin layer over the existing schema. The goal is a contract that other services can call.
- Move reads. For a while, read from both the API and the old query. Compare the results and log every disagreement. Mismatches show you where the old joins depended on undocumented behavior, such as null handling, soft-deleted rows or timezone conversions.
- Move writes. Writes come after reads because a bad write corrupts data, while a bad read only returns a wrong answer.
- Revoke access. Remove the other services' grants, for example
REVOKE SELECT, INSERT, UPDATE, DELETE ON orders FROM billing_service;. Until you do this, nothing enforces the boundary.
Cross-service transactions
For each invariant that used to rely on a single database transaction, ask whether it really needs atomicity. Most do not. Replace those with compensating actions or a saga. Write down which intermediate states users can see, such as "payment captured, order pending," and design the UI to show them.
The cost
Plan for a long project. While both read paths exist, they hide bugs. Constraints that the database used to enforce move into tests and reconciliation jobs, and some of them will drift without anyone noticing. Teams often skip the revocation step to avoid an argument with the teams losing access. A "temporary" read grant then stays in place, and the shared database comes back one query at a time.
-- Final migration step: billing_service now reads and writes orders
-- only through the orders API, so remove its direct table access.
-- Run this as the orders owner after the parallel-read logs are clean
-- and billing's write path has fully moved to the API.
REVOKE SELECT, INSERT, UPDATE, DELETE ON orders FROM billing_service;
REVOKE SELECT, INSERT, UPDATE, DELETE ON order_lines FROM billing_service;
-- Remove the 'temporary' read grant that the reporting job was given
-- during the migration. Grants like this one bring the shared database back.
REVOKE SELECT ON orders FROM reporting_job;
-- Grants to PUBLIC apply to every role, so check for them too.
REVOKE ALL PRIVILEGES ON orders FROM PUBLIC;
REVOKE ALL PRIVILEGES ON order_lines FROM PUBLIC;
-- Verify the result. Only the owning service should appear here.
-- Note: this view lists direct grants only. A role can still get access
-- through membership in another role, so review role memberships as well.
SELECT grantee, table_name, privilege_type
FROM information_schema.table_privileges
WHERE table_name IN ('orders', 'order_lines')
ORDER BY grantee, table_name, privilege_type;
Standard SQL that revokes other services' table privileges on the orders tables, including a leftover temporary read grant and any PUBLIC grants, and then lists the remaining direct grants to confirm that only the owner still has access.
Which approach should you choose for each query?
Pick per query, using the latency and freshness numbers you recorded in the inventory. Whether a design looks like "proper microservices" should not decide it. A query that tolerates a minute of staleness does not need a synchronous call chain. A query that must reflect a write made a moment ago cannot be served from a nightly copy.
| Query characteristic (from inventory) | Recommended approach | Main cost |
|---|---|---|
| User-facing, needs current data, few rows from each service | Synchronous API composition | Latency and availability depend on every service called |
| User-facing, high volume, tolerates seconds of staleness | Local read model fed by events | Duplicated data, event handling, replay and backfill logic |
| Analytical, wide scans, tolerates minutes to hours of lag | Dedicated reporting store | Pipeline upkeep and schema mapping from each owner |
| Blocked by a migration dependency, no viable alternative yet | Shared read-only view, documented, with a named owner and expiry date | Ongoing coupling. If nobody enforces the expiry, the view becomes permanent |
The read-only view is the only option that keeps the coupling in place. That is why it needs an owner who answers for it and a date on which it is reviewed or removed.
Sometimes the right move is not to split at all. Keep the tables together when two "services" always change together, deploy together, or are owned by the same team. In that case the join reflects a real boundary. Leave a join alone when it runs rarely, has no latency pressure, and replacing it would add a pipeline that nobody has time to maintain. Splitting that data would bring the costs of a distributed system with no gain in team independence.
How OneEnv can help
Disclosure: OneEnv is the product behind this blog.
OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.
Applied to shared database anti-pattern, this is where it helps:
Saves time
- Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
- One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
- Changing a route's URL or method automatically notifies the services that consume it — no hunting through repos for callers.
Saves cost
- Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
- Less duplicated configuration to maintain: one shared definition inherited by many services.
- Consumers learn about a breaking change at review time rather than from production errors.
- OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.
These are qualitative benefits, not measured results — how much you save depends on how many services share the value.
Want to try it? Start at oneenv.io.