How to write data simultaneously to MongoDB and PostgreSQL with guaranteed consistency?

Guaranteeing consistency when writing data simultaneously to MongoDB and PostgreSQL is challenging because these are two different DBMSs with different transaction models and no distributed transactions between them.

Main approaches:

1. Two-Phase Commit (2PC) — a classic protocol for distributed transactions, but it is rarely supported directly between different DBMSs, and implementation is complex and costly.

2. Sagas — a pattern that breaks a transaction into a series of local transactions with compensating operations in case of errors. For example:

   • Write to PostgreSQL.
   • Write to MongoDB.
   • If the second operation fails, roll back the first via a compensating transaction.

3. Idempotent operations and message queues:

   • Write data to one database and publish an event to a queue.
   • A separate process reads the event and writes to the second database.
   • In case of failures, operations can be retried, ensuring idempotency.

4. Using an external coordinating service (e.g., a distributed transaction manager), but this complicates the architecture.

Example with sagas (simplified):

try {
    postgreService.save(data);
    mongoService.save(data);
} catch (Exception e) {
    postgreService.compensateSave(data); // rollback the first operation
    throw e;
}

In conclusion, to guarantee consistency between MongoDB and PostgreSQL, it is better to use the saga pattern or asynchronous replication via events, as full distributed transactions are practically not applicable.