Hybrid Approaches: Combining SQL Plus a Document Store

1. Why polyglot persistence is a decision, not a compromise

A hybrid architecture, also called polyglot persistence, deliberately combines several database types within one system, instead of forcing a single technology to meet all requirements at once. The idea behind it is simple: no single database model is optimal for every use case, so each domain is built with the data model that best fits its specific access patterns.

The most common mistake with a hybrid architecture is treating it as a technical compromise rather than a deliberate decision, with the result that data splitting and synchronization are not properly planned and a fragile system made of two independently grown databases emerges. Done correctly, with a clear domain boundary, defined synchronization paths, and deliberately chosen consistency guarantees per data area, polyglot persistence is instead a robust and well maintainable architecture pattern.

This article shows how to keep core processes in a relational database while a document store takes over for variable, read heavy use cases, including synchronization strategy, consistency boundaries, and the operational overhead such a hybrid architecture actually brings.

2. Basic pattern: where SQL stays and where the document store takes over

The basic pattern of a working hybrid architecture divides responsibilities by consistency needs and access pattern: orders, payments, inventory bookings, and anything with strict referential integrity stay in a relational database with full ACID transactions. Product catalogs, content structures, user profiles with variable fields, or search indexes move into a document store optimized for fast read operations over nested, heterogeneous structures.

This split follows the domain, not the technology. A typical e commerce system often keeps cart intermediate states and session data in a key value store, the product catalog in a document store, and completed orders as well as payment data in a relational database. Each domain gets exactly the consistency and query properties it needs, instead of a compromise that is optimal for none.

-- Relational database: orders with strict constraints
-- Consistency and referential integrity are the priority here
CREATE TABLE orders (
  order_id      BIGINT PRIMARY KEY,
  customer_id   BIGINT NOT NULL,
  status        VARCHAR(20) NOT NULL CHECK (status IN ('pending','paid','shipped','cancelled')),
  total_amount  NUMERIC(10,2) NOT NULL CHECK (total_amount >= 0),
  created_at    TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE payments (
  payment_id    BIGINT PRIMARY KEY,
  order_id      BIGINT NOT NULL REFERENCES orders(order_id),
  amount        NUMERIC(10,2) NOT NULL,
  captured_at   TIMESTAMP
);
-- Foreign keys and CHECK constraints enforce consistency,
-- exactly the model that is indispensable for payment data

// Document store: product catalog with variable structure
// Read optimized, no referential integrity needed
db.catalog.insertOne({
  productId: "sku-4471",
  title: "Trail running shoe model Gamma",
  category: "footwear",
  variants: [
    { size: 42, color: "black", stock: 12 },
    { size: 43, color: "black", stock: 4 }
  ],
  specs: { weightGrams: 285, waterproof: true },
  searchTags: ["trail", "running", "outdoor"]
});
// One document delivers the complete product page in a single lookup,
// without joins across variant and spec tables

3. Splitting data by domain instead of by table

The decisive planning step of a hybrid architecture is to align the split along business domain boundaries, not technical table boundaries. Instead of asking "which table do we move where", the question should be "which bounded context, in the domain driven design sense, belongs together and can have its own data model independent of other areas". This mindset prevents related data from being spread across two systems, which unnecessarily complicates synchronization.

A data field should count as the "source of truth" in exactly one hybrid architecture component, even if it is duplicated or projected in other systems. The product price, for example, lives as the authoritative source in the catalog system, but is copied as a snapshot into the new order line in PostgreSQL at the time of an order, so historical orders remain unchanged even if the current catalog price changes later. This deliberate duplication is not a design flaw but a central pattern of hybrid architectures.

4. Synchronizing between systems with change data capture

Once data exists in two systems, a hybrid architecture must define how changes are propagated. The most robust approach is change data capture, CDC for short: a tool like Debezium continuously reads the write ahead log of the relational database and publishes every change as an event on a message queue, from which the document store is updated. This approach avoids duplicate write logic in the application and guarantees that no change is lost, because the log serves as the reliable source.

The alternative, dual write directly from the application, where every write operation explicitly updates both systems, is easier to implement but more error prone: if the second write fails after the first has already committed, the systems drift apart without any visible error. CDC solves this problem structurally, because it builds on the already committed state of the relational database instead of trusting two independent write operations in application code.

# Debezium connector configuration for CDC from PostgreSQL
# Changes to the orders table are published as events
curl -X POST http://connect:8083/connectors \
  -H "Content-Type: application/json" \
  -d '{
    "name": "orders-connector",
    "config": {
      "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
      "database.hostname": "postgres-primary",
      "database.dbname": "shop",
      "table.include.list": "public.orders,public.payments",
      "topic.prefix": "shop-cdc"
    }
  }'

# A consumer service reads the topic shop-cdc.public.orders
# and updates the matching projection in the document store

5. Consistency boundaries: saga instead of distributed transaction

A true distributed transaction across a relational database and a document store, with two phase commit, is rarely sensible in practice because it increases latency and tightly couples both systems. The established pattern for hybrid architectures is instead the saga pattern: a business operation is broken down into several local transactions, each in its own system, with defined compensating steps in case a later step fails.

When a customer orders a product, a first step reserves stock in the document store, a second step creates the order in the relational database. If the second step fails, a compensating step releases the stock reservation in the first system again. This compensation logic must be explicitly implemented in application code or in an orchestration layer, but it is considerably more robust and scalable than a distributed transaction across system boundaries.

-- Pseudocode for the saga flow (orchestration outside the database)
-- Step 1: reserve stock in the document store (separate, see above)
-- Step 2: create the order in the relational database
BEGIN;
INSERT INTO orders (order_id, customer_id, status, total_amount)
VALUES (8842, 501, 'pending', 149.90);
COMMIT;

-- Compensating step if step 2 fails:
-- release the stock reservation in the document store again
-- (call made from the orchestration layer, not from SQL)
-- releaseReservation(productId: "sku-4471", quantity: 1)

6. The API layer as an abstraction over both data sources

So that client applications do not need to know which field comes from which database, a hybrid architecture should always have an API layer that merges both data sources into a unified response format. A GraphQL resolver or a REST aggregation endpoint can combine product data from the document store with price and availability data from the relational database, without the client having to make two separate requests.

This abstraction layer is also the right place to cushion the failure of individual systems: if the document store is briefly unreachable, the API layer can react with a fallback to cached catalog data instead of letting the entire request fail. This resilience logic conceptually belongs to the hybrid architecture and should be planned from the start, not added as a later patch.

7. Practical example: catalog in the document store, orders in SQL

A concrete example illustrates the hybrid architecture in practice: a mid sized online shop keeps its product catalog with strongly varying attributes, image galleries, and search metadata in MongoDB, because new product categories frequently bring new, unpredictable fields. Orders, payments, returns, and customer data live in PostgreSQL, because referential integrity, transactions, and legal proof requirements take priority here.

At checkout, the application reads the current price and availability from MongoDB, copies both values as a snapshot into the new order line in PostgreSQL, and then starts the payment process exclusively within the relational database. The shop's search function works exclusively against the document store, while reporting for accounting and controlling runs exclusively against the relational database. Each system serves exactly the requests it was built for.

# Consistency check: comparing a sample between both systems
# Goal: the price snapshot in orders must not differ from the current
# catalog price at order time (except through a legitimate price change)
psql -d shop -c "SELECT order_id, product_sku, price_snapshot FROM order_items WHERE order_id = 8842;"

mongosh --eval 'db.catalog.findOne({productId: "sku-4471"}, {price: 1})'

# An automated reconciliation script runs both queries periodically
# and reports discrepancies outside expected price changes

8. Additional operational effort: two systems instead of one

The most honest disadvantage of a hybrid architecture is the additional operational effort: two database types mean two backup strategies, two monitoring dashboards, two sets of operational know how, and two potential failure sources. A team must master both relational and document based databases, including their respective failure modes, backup procedures, and scaling characteristics.

This extra effort is justified when the two domains actually have fundamentally different requirements that no single database would cover well. It is not justified when a hybrid architecture is introduced only because a team member prefers working with MongoDB, without a real business reason behind it. The decision for polyglot persistence should always be a deliberate cost benefit trade off, not a matter of taste.

9. Hybrid architecture versus single store compared

The following table compares a hybrid architecture to a pure single store solution and shows the central trade offs.

The choice falls in favor of the hybrid architecture once the domains actually have different requirements and the additional operational effort is clearly outweighed by the better fit per area. For smaller systems with homogeneous requirements, a single store usually remains the simpler and more robust choice.

10. Summary

A hybrid architecture combines a relational database for transactional core processes with a document store for variable, read heavy use cases, aligned to business domain boundaries rather than technical table boundaries. Change data capture synchronizes changes more reliably than dual write, the saga pattern replaces distributed transactions across system boundaries, and an API layer abstracts both data sources for client applications.

The price of this flexibility is real additional operational effort: two systems, two operational concepts, a synchronization pipeline that must be actively monitored. Whoever deliberately weighs these costs against the benefit of a better fit per domain, instead of introducing polyglot persistence out of trend or personal preference, gets a robust, well maintainable system with a hybrid architecture that actually uses the strengths of both database worlds.

11. FAQ: Hybrid Approaches, SQL Plus a Document Store