Multi-datasource Spring Boot: 3 databases, 1 transaction
How we split back-office editing, mobile API reads and analytics into three distinct PostgreSQL databases while keeping atomic transactions across them. Lessons from an accessible indoor navigation back-end (Java 17 / Spring Boot 3).
Why one database stopped being enough
The product is a mobile app for accessible indoor navigation, deployed in large public venues (airports, train stations, museums). It's powered by a Spring Boot back-office that serves two very different worlds:
on the editing side, a handful of operators update complex maps (places, levels, troubles, with a full audit trail). On the read side, thousands of mobile apps pull published maps from our APIs.
With a single database, heavy back-office writes were contending with massive mobile reads. And the joins needed for editing penalised mobile latency. We had to decouple.
3 databases, 1 application
We split into three PostgreSQL datasources side by side, all managed by the same Spring Boot application:
full audit trail
read-only · versioned
write-heavy
The mobile API only reads published_data — never working_data. The batch job (Spring Batch + Quartz) copies and denormalises at publish time.
working_data normalised · full audit trail Back-office working data — everything operators edit. Every mutation also writes to an audit table so we keep a complete trail (who did what, when, from where).
published_data read-only snapshot · denormalised · versioned Mobile API read path. Data is denormalised at publication time so production never has to join. Multiple versions can coexist via URL-prefix versioning.
segment_events analytics · write-heavy Analytics events (taps, navigation, sessions). Isolated so write spikes can't impact business tables.
Why a distributed transaction
When an operator edits a business entity, we have to write to working_data AND the audit table atomically. If audit fails, the edit rolls back, and vice versa. Local-per-database transactions aren't enough — we need a distributed transaction that spans multiple datasources.
In practice, a single @Transactional on the service method wraps both the business update on working_data AND the audit insert. The transaction manager (Atomikos JTA here) drives the 2-phase commit across datasources.
The alternative — native PostgreSQL XA — was less mature with Hibernate at the time. Atomikos integrates cleanly, but watch out: if a commit fails partially, the error can be silent in some cases. We learned to read the Atomikos logs carefully and never mix @Async with @Transactional (use a JMS message instead).
Publication, or how to move to read-only
When an operator validates a site, business data must be copied from `working_data` to `published_data` while being denormalised. It's heavy (sometimes several minutes for a large airport) and must not block the user. The pattern:
- 1
The operator clicks Publish → the API records a job request in a table and publishes a JMS message on Apache Artemis.
- 2
A Spring Batch consumer picks up the message, runs the job (reader/processor/writer). Quartz handles scheduling and clustering (only one worker grabs the job at a time).
- 3
The job copies and denormalises into
published_datawith checkpoints. If it crashes, it resumes from the last checkpoint. - 4
Status switches to
published. Mobile apps read the new version. The previous one stays in the database for fast rollback.
On the mobile API side, the version is prefixed in the URL (/mobile/api/vN/...). We can publish a new version without breaking older apps — built-in grace period.
What we also considered
Single DB + table partitioning
Fine while read/write patterns stay close. Not enough here: mobile-side denormalisation requires a different schema, not just partitioning.
Separate microservices
Would have over-engineered the need. Three databases in one Spring monolith are enough for current volume and stay simple to deploy (a single Maven artifact).
Read replicas
Great for read scaling but doesn't help with denormalisation. And replication lag would have introduced a consistency window that the publication workflow can't tolerate.
Event sourcing
Tempting for audit, but a big tax for a team our size. A dedicated audit table + a distributed transaction gives 80% of the benefits at 10% of the cost.
What we keep from it
- Three databases in one app isn't three times the complexity — it's actually less, because each database has a clear role and a schema optimised for that role.
- Atomikos is solid as long as you avoid async / transactional mixing. Testing error paths (mocking a DB going down) is mandatory.
- URL-prefix versioning is well worth the over-header approach: explicit, debuggable, and lets multiple versions coexist without shared state.
- Spring Batch + Quartz covers 100 % of long-running job needs. Kafka would have been overkill for our volume.
- Never modify a published mobile API version. Always create the next one. Mobile apps roll out over months, not days.