Shared Schema, Three Teams, One Outage
A logistics platform is a single Java monolith with 6 modules and 140 database tables in one PostgreSQL schema, owned jointly by three teams. Deployments go through a weekly release train; teams block each other for an average of three days per release, and last month a destructive migration dropped a column while an older version of another team's code was still running, causing a four-hour outage. The reporting team runs a query with 40 joins across nearly every table and refuses to change it. Architecture has proposed splitting into microservices with database per service within two quarters. The engineering director wants the outage to stop and asks whether the database split should be the first step.
1. Should the team start with database-per-service? Argue the first three moves and the exit criteria for extracting a service.
Read the worked answer
No. Start with ownership and seams, not with the database split. The shared schema is a symptom; the disease is unclear ownership and no enforced boundaries. Splitting the database first multiplies coordination cost and creates distributed transactions before the domain boundaries are known.
First three moves:
- Establish ownership per table and per change. Build a co-change map from version control: which tables change together and which teams change them. Tables touched by more than one team are the real coupling. Assign one owning team per table; others consume through a published interface.
- Modularize inside the monolith. Enforce module boundaries with build-time checks so that no module reaches into another's repositories. This is cheap and reversible, and it makes the seam real before it becomes a network call.
- Choose the first extraction by pain and coupling, not by fashion. The module with high change frequency and few inbound dependencies goes first. Reporting is the worst first candidate despite the pain, because it reads everything.
Diagnosis of the outage: a destructive migration was applied before the dependent code, across team boundaries, with no expand-and-contract discipline. A database split would not fix that; it would move the failure to a network contract and make it harder to see.
Exit criteria for extraction: the module owns its data, there are no synchronous cross-module reads in the transaction path, and contract tests exist. Until then, a service is a distributed monolith with added latency.
2. How should the 40-join reporting query be handled during and after the transition? Compare the realistic options.
Read the worked answer
A 40-join report is evidence that reporting needs its own read model; it is not a reason to keep a shared transactional database.
Options and trade-offs:
- Read replica of the monolith, queries unchanged. Fastest and lowest risk, with seconds of freshness. It preserves coupling but does not block service extraction. Acceptable as a bridge with a named owner and a retirement date.
- Change data capture into an analytics store, reports remodeled there. Decouples reporting from transactional schema churn, preserves history, and scales. Costs include new infrastructure, 1 to 15 minutes of freshness, and data-quality ownership; the 40 joins must be rewritten as a star schema.
- API composition across services. Wrong for this shape: 40 joins over HTTP creates latency and chatty coupling, and reporting would depend on the availability of many services.
- Shared materialized view owned by reporting. Workable short term, but it silently re-couples schema ownership and reintroduces the original problem.
Recommendation: read replica now, CDC to a warehouse within two quarters, with a published freshness SLO such as 15 minutes p95 agreed with finance. Migrate reports in order of business criticality, and keep the replica read-only with a query budget so it does not become a second production database.
Key point: reporting is a different consistency requirement, not a different database engine. Name the freshness requirement explicitly; 'real-time reporting' is the same trap as 'real-time' in a requirements document.
3. Design the schema-change process that would have prevented the four-hour outage. What should be automated and measured?
Read the worked answer
Make schema change a contract, not a deploy step.
Rules:
- Expand and contract, always. Add a nullable column, backfill, dual-write, switch reads, and remove the old column in a later release. Never drop or rename in the same release as the code change. The outage fits the classic pattern: migration applied first, old code still running, column gone.
- Backward compatible by default. Consumers must tolerate unknown fields and additive changes. A breaking change requires a new version and a deprecation window.
- Consumer-driven contract tests. Each consumer publishes expectations, and the provider's build runs them. For the database, this means testing the views and interfaces other teams use, not internal tables.
- Ownership and change window. The owning team approves changes; a cross-team change needs a named consumer contact and a rollback plan.
- Decouple deploy from release. Run migrations as a separate idempotent step, gated and observable, with feature flags controlling behavior.
Automate: a CI check that flags destructive DDL, a required rollback script, and a blocked merge when a schema change has no consumer sign-off.
Measure: cross-team schema changes per month, failed migrations, mean time to roll back, and the number of deployments blocked by another team. The goal is not zero change; it is change one team can make without a coordination meeting.