Polyglot persistence: documents for identity, SQL for money

"SQL or NoSQL?" is the wrong question. It assumes a system has a database, and reasonably large systems have parts with opposite needs.

In the ecosystem I maintain, two coexist: a document store for identity and people, and PostgreSQL for treasury. Not out of technical taste — because the access patterns of each module look nothing alike.

Two modules, two different problems#

Identity: variable shape, constant reads#

A person's profile changes shape depending on the case. Some have full contact details, others just a phone number; some belong to three groups, others to none; next year somebody will ask for a field that does not exist today.

And the access pattern is overwhelmingly read by identifier: give me this user, give me the members of this group. There is almost no analytical querying.

A relational table for this ends up one of two ways: fifty mostly-null columns, or six tables and a LEFT JOIN to rebuild what is conceptually a single object.

// What I want to read is this, in one go
{
  id: 'abc',
  name: 'María Restrepo',
  contact: { phone: '+57...', email: 'maria@...' },
  memberships: [{ groupId: 'g1', role: 'LEADER', since: '2024-03-01' }],
  preferences: { locale: 'es', notifications: { email: true, push: false } },
}

In a document store that is the document. One read, and adding a field needs no migration.

Treasury: money, and money does not do "almost"#

Here everything above inverts:

• A ledger entry applied halfway corrupts the balances.
• An expense pointing at a non-existent category is invalid data, not a flexible document.
• The queries are aggregations: totals by fund, by period, by category, across several entities.
• The balance must be exact always, not in a moment.

That is precisely what a relational engine has spent forty years solving: ACID transactions, foreign keys, constraints, and SUM ... GROUP BY you do not have to invent in the application.

-- In a document store this turns into application code,
-- and that code is where the discrepancies show up
SELECT fund_id, SUM(amount) AS total
FROM expenses
WHERE status = 'APPROVED' AND occurred_at BETWEEN $1 AND $2
GROUP BY fund_id;

Forcing treasury into a document store means reimplementing referential integrity and aggregation by hand. It can be done. And every bug there is money counted wrong.

Firestore or MongoDB: I pick Firestore, but not always rightly#

For the document side, the two options I genuinely consider are Firestore and MongoDB. I prefer Firestore, and it is worth explaining why, and when that preference would be a mistake.

Why I pick Firestore#

The security rules. They are declared outside the application and enforced before the request reaches my code. In a multi-tenant system that is the difference between "I trust no endpoint forgets to filter by tenant" and "it is impossible to read another tenant's data even if the endpoint is wrong".

Zero operations. No server to size, patch, or back up. On small teams that is not convenience: it is the difference between someone doing maintenance and someone building product.

Real time comes free. One listener and the UI updates itself. If the product uses it, that is a big advantage; if not, it adds nothing.

It fits the rest. If you already use Firebase Auth and Storage, everything shares identity and rules.

When I would pick MongoDB instead#

And here I am honest, because plenty of people prefer Mongo for good reasons:

• Complex queries over documents. Mongo's aggregation pipeline does things that in Firestore end up solved in the application or by duplicating data. If the document module needs analytics, Mongo wins outright.
• Avoiding lock-in. Firestore only exists on Google Cloud. Mongo runs on Atlas, your cloud, or your machine. If that is a requirement — and with corporate clients it sometimes is — the decision is made.
• You already have an ops team. If someone can run a database, much of Firestore's advantage disappears and its query limits remain.
• Cost at high read volume. Firestore bills per operation. A system reading millions of documents a day can cost more than a fixed-size cluster.

The rule I use: does the document side need rich queries? If yes, Mongo. If it is reads by identifier with strict security and real time, Firestore.

What having two costs you#

This is the part missing from the articles recommending polyglot persistence.

There is no JOIN between them. You cannot join a PostgreSQL expense with the Firestore user who approved it in a single query. Either you make two reads, or you denormalise.

I denormalise, with a minimal snapshot:

// On the expense record, inside PostgreSQL
{
  approvedBy: {
    personId: 'abc',
    displayName: 'María Restrepo',  // a copy, deliberately
  },
}

Storing the name looks like relational heresy. It is deliberate: last year's expense report must say who approved it under the name they had then, not the current one. The copy is not lazy duplication; it is a different, historical fact.

There are no transactions across both. If an operation touches identity and treasury, there is no joint commit. You solve it with domain events and accept eventual consistency: the source of truth updates first and emits an event; the other side reacts.

That means for a few milliseconds — sometimes seconds, if something fails — the denormalised data is stale. You must decide explicitly whether the business tolerates it. For a name shown in a report, yes. For a balance, never: which is why the balance lives entirely in PostgreSQL.

Two mental models and two backups. Everyone joining the team learns two ways to query and two ways to migrate. And a recovery plan that does not cover both is worthless.

How I keep the price from spiralling#

The key is that the domain does not know what is underneath. Each module declares a port and infrastructure implements it:

// application/ports — the use case only knows this
export interface PersonRepository {
  findById(id: string): Promise<Person | null>;
  save(person: Person): Promise<void>;
}

If identity migrates from Firestore to MongoDB tomorrow, one class changes in infrastructure/. Neither the use cases nor the domain notice. That is not a theoretical promise: it is the only reason a persistence decision stops being irreversible.

When this is over-engineering#

Almost always, at the start.

If the system fits in one database, use one. PostgreSQL with jsonb columns covers the document side surprisingly well, and a single database means real transactions, one backup, one mental model, and zero synchronisation.

Polyglot persistence starts paying off when:

1. There are modules with clearly opposite access patterns, not merely different ones.
2. The document module gains something concrete: rules at the edge, real time, or a schema that genuinely changes often.
3. Someone can maintain both.

If any of the three fails, one database is the right answer — and changing later, if the domain asks for it, is exactly what the ports are for.