Database Selection and Trade-offs Questions

Direct answer

Split ownership cleanly: a relational database as the system of record for orders, giving atomicity, consistency, isolation, and durability (ACID) across everything one order write touches, and a separate, read-optimized store, a document database or a search index, for the product catalog. Treat the catalog store as a derived, denormalized VIEW fed from the product data's own relational system of record, never a second source of truth, and never something the order service writes to directly.

Structured elaboration

Why relational for orders. An order write typically touches several related facts at once: creating the order, decrementing stock, recording the payment status, and writing each line item. That is exactly the multi-row, multi-table atomic transaction relational engines are built for. Losing atomicity here produces partial orders, phantom charges, or oversold inventory, all of which are correctness incidents, not just inconvenience.

Why not relational alone for the catalog. Catalog queries are read-dominated and highly variable in shape: filter by many different facets, free-text search, faceted navigation. Schema also varies per category, a shirt has size and color, a laptop has RAM and storage, so a single rigid relational schema ends up either sparse and wide or forced into a generic key-value-per-row pattern, both of which fight the query patterns a catalog actually needs. A document store's per-record schema flexibility, or a dedicated search index's inverted-index query model, fits the shape directly.

Data synchronization. Populate the catalog store through change data capture (CDC, streaming a database's write-ahead log to downstream consumers) or an outbox pattern (the product service writes an event to an outbox table inside the same transaction as its own business write, and a separate process publishes it). The flow is one-directional, from the product service's relational store outward to the catalog read model, never the reverse, and never touched by the order service.

Ownership. The product service owns product data (name, price, attributes) in its own relational store. The catalog read model, whether a document store or a search index, is a projection it publishes; nothing else writes to it. The order service owns order state exclusively. It may read product data at order time, but it writes an immutable snapshot of price and details into the order record rather than a live reference, so a later catalog price change never rewrites history.

Read and write patterns. Orders are write-moderate, strongly consistent, and low fan-out, one customer creating one order. The catalog is write-light and read-massive, with eventual consistency acceptable: a price change propagating to the search index a few seconds late is a minor UX issue, while the same delay in the order path would be a correctness bug.

Worked example

flowchart LR
    ProductSvc[Product service] -->|owns, ACID writes| ProductDB[(Relational DB: products)]
    ProductDB -->|CDC or outbox| CatalogIndex[(Catalog read model: document store or search index)]
    Shopper -->|browse and search| CatalogIndex
    Shopper -->|checkout| OrderSvc[Order service]
    OrderSvc -->|reads product snapshot at order time, then owns| OrderDB[(Relational DB: orders)]

At order time, the order service reads current price and availability from the product service, but writes that price into the order's own line items as a fixed value. A later price change in the product service, and its downstream propagation to the catalog index, never touches an already-placed order.

Trade-offs and pitfalls

A pitfall is letting the order service query the catalog's live document store or search index for "current" price at checkout instead of the product service's own transactional data, which risks a race if price changes mid-checkout. Always snapshot the price into the order at commit time from the authoritative source.

A second pitfall is trusting the catalog's stock number as authoritative for checkout because it is convenient to read. CDC and outbox pipelines lag by design; that lag is fine for browsing, but the actual stock decrement at checkout must go through the order service's own transactional path, re-verified against the real number, not the catalog's possibly-stale copy.

What would flip the recommendation: a catalog small enough, a few thousand SKUs with a simple, largely uniform schema, that the flexible-query need never really materializes. At that scale a single relational database with good indexing serves both orders and catalog fine, and introducing a second store is unwarranted complexity.

Direct answer

Given an OLTP (online transaction processing: a workload of many small, fast reads and writes, like inserting one order) primary already straining under mixed transactional and analytic load, the default move is not to reach for a purpose-built HTAP (hybrid transactional/analytical processing: a single database built to serve both transactional writes and analytical scans well) database (TiDB, CockroachDB, SingleStore); that's a bigger migration and operational commitment than most situations call for. Get the analytic load off the primary first, using whichever of three offload patterns fits the freshness requirement, and only adopt a dedicated HTAP engine when none of those can meet it at acceptable operational cost, most often when a single request genuinely needs a transactionally-consistent read and a large aggregation together.

Structured elaboration

Three offload options, analyzed separately (not one bundled choice)

Purpose-built HTAP engines, named honestly

The question groups TiDB, CockroachDB, and SingleStore together, but they are not equivalent on analytics, and current vendor documentation says so directly:

• TiDB pairs a row-oriented engine (TiKV) with a columnar replica (TiFlash) that replicates data in real time specifically to isolate analytical scans from the transactional path. This is a genuine HTAP design.
• SingleStore unifies row and columnar storage in one engine (its Universal Storage layer), letting the same table serve fast point operations and large scans without a separate replica. Also a genuine HTAP design.
• CockroachDB is a distributed, strongly-consistent, Postgres-wire-compatible SQL database, and a strong option for horizontally-scaled OLTP. Its own current documentation states it is not yet suitable for heavy analytics or OLAP (online analytical processing: workloads dominated by large scans and aggregations, not small transactional writes) workloads. Grouping it with the other two as an HTAP option overstates what it is built for today; it solves the "scale my transactional writes across nodes" problem, not the "run heavy aggregations without hurting my writes" problem this question is actually about.

Consistency, cost, and operational complexity, compared directly

Consistency. A read replica and a CDC-to-warehouse pipeline are both eventually consistent by construction: the analytic side lags the primary by a bounded but nonzero amount, which is fine for a dashboard and wrong for a decision that must see a just-committed write. A lambda or kappa stream can narrow that gap to sub-second for its speculative layer, but the speculative answer is provisional until the batch layer corrects it, which is a different kind of eventual consistency, not none. TiDB and SingleStore, by contrast, keep the analytic-capable path inside the same transactionally consistent system as the writes (TiFlash replicates from TiKV with a bounded, typically sub-second, delta, and SingleStore's unified storage reads the same committed rows a transactional query would), so they are the only options on this list where an analytic read can be given the same consistency guarantee as a transactional one, at the cost of everything below.

Cost. The three offload options each add infrastructure that scales somewhat independently of the primary (a replica instance, a warehouse's compute, or a streaming platform's brokers and processing layer), which is usually cheaper at moderate scale than re-platforming onto a distributed SQL engine, since the primary itself keeps its existing footprint. A purpose-built HTAP engine instead replaces the primary, so its cost has to be compared against the full cost of the current primary plus every offload piece it would remove, not against the offload pipeline alone.

Operational complexity. The offload options add a pipeline to operate (replication lag monitoring, CDC schema-drift handling, or a streaming platform), but the primary itself, the part most of the team already knows how to run, stays untouched. A purpose-built HTAP engine removes the pipeline but replaces the whole primary with a system that has its own distributed-systems failure modes (node loss, rebalancing, cross-region consensus) that most teams have not yet built operational muscle for, which is real complexity even when the architecture diagram looks simpler.

Worked example

Assume the OLTP primary currently handles about 8,000 writes per second, and a nightly reporting job has grown long enough to run into business hours, pushing write p99 latency (the 99th-percentile latency: 99% of writes finish faster than this number, so it captures the slow tail rather than the typical case) from roughly 40 ms to over 300 ms while it runs (an illustrative scenario, not a measured benchmark). First move: a read replica dedicated to the reporting job, with a statement timeout so a runaway query can't further degrade replication apply. If lag during the batch window is still unacceptable because the reporting queries are large table scans that saturate the replica's own I/O, move to CDC into a columnar warehouse, which removes analytic I/O from any row-oriented engine entirely. Only if the business then adds a requirement like "score this transaction for fraud using both the row just written and a rolling aggregate, within the same request, before it's approved" is there a genuine argument for TiDB or SingleStore: that's a single request needing strongly consistent point access and analytical aggregation together, which none of the three offload options can give you, since they are all, by design, a step removed from the primary.

Trade-offs & pitfalls

HTAP engines trade a smaller pool of operational expertise and newer failure modes for solving a problem the offload options structurally cannot. Each offload option has its own failure mode: read replicas silently grow stale under exactly the load spikes you're trying to protect against, so lag needs active alerting, not a one-time setup; CDC pipelines can drift on schema changes and need periodic reconciliation, not just a "pipeline is running" health check; lambda and kappa architectures are frequently adopted for one reporting problem and then become a permanent, expensive platform commitment because reverting is harder than the original decision to adopt one.

Direct answer

Recommend DynamoDB Global Tables for the profile-store use case as stated: writes are occasional and profile fields rarely conflict across regions at the same instant, so eventually-consistent, last-writer-wins (LWW) cross-region replication is an acceptable trade for fully managed, active-active writes with local reads under 20ms in every region.

Structured elaboration

Why every candidate needs a LOCAL copy of the data in each read region. A synchronous cross-region round trip alone typically busts a 20ms budget by an order of magnitude. The physics floor for a round trip is the distance divided by the speed of light in fiber (roughly two-thirds the speed of light in a vacuum, due to the refractive index of glass):

import math

def great_circle_km(lat1, lon1, lat2, lon2, R=6371):
    lat1, lon1, lat2, lon2 = map(math.radians, [lat1, lon1, lat2, lon2])
    dlat, dlon = lat2 - lat1, lon2 - lon1
    a = math.sin(dlat/2)**2 + math.cos(lat1)*math.cos(lat2)*math.sin(dlon/2)**2
    return R * 2 * math.asin(math.sqrt(a))

d_km = great_circle_km(39.0, -77.5, 1.35, 103.8)  # N. Virginia <-> Singapore
c_fiber_km_s = 300_000 * (2/3)
rtt_ms = (d_km / c_fiber_km_s) * 2 * 1000
print(f"great-circle distance: {d_km:,.0f} km")
print(f"physics-floor RTT (straight-line fiber, no routing overhead): {rtt_ms:.1f} ms")

great-circle distance: 15,526 km
physics-floor RTT (straight-line fiber, no routing overhead): 155.3 ms

Real internet paths are always slower than this straight-line floor (routing hops, congestion), so a design that answers every read with a synchronous cross-region call cannot meet a 20ms budget at all; every candidate must serve reads from a copy of the data that already lives in the requesting region.

"Occasional writes" does not demand low write latency from every region, only that writes eventually land everywhere without being lost.

Candidates.

• DynamoDB Global Tables: replicates via DynamoDB Streams to every region; every region can read AND write locally; conflicts are resolved last-writer-wins (LWW) by internal timestamp; propagation between regions is typically sub-second, though AWS documents no hard SLA on that figure; fully managed, billed per region's capacity plus a replicated-write-unit charge for each additional region.
• Cassandra multi-DC: NetworkTopologyStrategy places replicas per datacenter, and LOCAL_QUORUM reads/writes stay in-region for low latency while replication to other datacenters happens in the background, a similar shape to Global Tables but with tunable, per-request consistency rather than a single fixed LWW rule. This is the natural pick if the organization is not committed to a single cloud provider: Cassandra or Scylla multi-DC is the closest cross-cloud equivalent to what Global Tables gives on AWS alone, at the cost of self-managing the cluster (or paying a managed-Cassandra vendor).
• PostgreSQL with read replicas: one write-primary region, asynchronous streaming replication to per-region read replicas. Reads are local and fast; but EVERY write funnels through the one primary region regardless of where the user is, so a write from a non-primary region pays the full cross-region round trip (tolerable for genuinely occasional profile edits, a poor fit the moment writes need to feel local too). It is also a single point of write failure: if the primary region goes down, every other region loses write capability until a primary is promoted, and any replica that had not caught up loses its most recent writes on promotion.

Read routing. Route each request to the nearest region's local replica, for example with latency-based DNS routing (directing a client to whichever region answers fastest) or an edge/CDN layer that forwards to the nearest regional API, so the read never leaves the region.

flowchart LR
    U1[User: EU] --> R1[EU region: local Global Table replica]
    U2[User: US] --> R2[US region: local Global Table replica]
    U3[User: APAC] --> R3[APAC region: local Global Table replica]
    R1 <-. async replication .-> R2
    R2 <-. async replication .-> R3
    R1 <-. async replication .-> R3

Worked example

Two regions each write the same user's display_name field within the same short replication window, close enough together that both writes are in flight before either has propagated. DynamoDB Global Tables resolves the conflict by keeping whichever write has the later internal timestamp and silently discarding the other, no error, no merge. The practical implication: don't build a "who last touched this field" audit feature purely off the base table, since the losing write leaves no trace there; log profile changes to a separate append-only table or stream if that provenance genuinely matters.

Trade-offs & pitfalls

What would flip the recommendation. For a REAL-TIME game-data variant of this same shape (position, health, inventory, written far more frequently, usually from a session pinned to one region at a time), the calculus changes: Cassandra/Scylla multi-DC with LOCAL_QUORUM (tunable, and not tied to one vendor's specific LWW rule) is the better fit, especially on a multi-cloud platform. If the organization is not on AWS at all, Cassandra/Scylla multi-DC answers that constraint directly, since it runs the same way on any cloud or on-premises. Two pitfalls to name explicitly: Global Tables' replicated-write-unit cost scales with the number of regions (each additional region adds roughly another full write's worth of cost), and "no SLA on propagation" means there is no contractual recovery-point guarantee, only an empirically sub-second one; anything that truly cannot tolerate an unbounded (if rare) propagation delay needs a design that does not rely on Global Tables' default consistency model at all.

Direct answer

For a multi-tenant analytics service dominated by heavy scans, commit to option (B), a distributed column-store, for the general case: it amortizes fixed compute cost across many tenants and is the storage family built for scan-and-aggregate workloads. Reverse that choice only for a small number of large, contractually regulated tenants (for example, ones with a hard data-residency or audit requirement) where option (A), relational with dedicated read replicas per tenant, buys real physical isolation that a shared cluster cannot promise without extra work. Most real systems end up as a hybrid: a shared column-store for the long tail of small and mid-sized tenants, and dedicated infrastructure for the handful of tenants whose contracts demand it.

Structured elaboration

Isolation guarantees. Option (A) gives isolation almost for free: one database (or schema) per tenant means the blast radius of any incident, a slow query, a bug, a security issue, is one tenant, by construction. Option (B) gives a soft guarantee by default: a shared table with a tenant_id column relies on every query correctly filtering by tenant and on the engine's own resource governance to stop one tenant's heavy scan from starving another's. That governance is a real, named feature to configure, not an assumption: a column-store cluster needs per-tenant resource quotas or workload-management queues (concretely, Redshift's workload management, WLM, lets you route different tenants or tenant tiers to separate query queues with their own memory and concurrency limits, and ClickHouse supports resource quotas per user or role) to make the soft guarantee closer to a hard one.

Cost. Option (A) scales linearly with tenant count, regardless of tenant size: a tenant storing 10 gigabytes (GB) and a tenant storing 5 terabytes (TB) still both need at least one running database instance, so the fixed cost floor multiplies by every small tenant you add. Option (B) shares fixed compute cost across all tenants and scales more with total data volume and query concurrency than with tenant count, which favors a long tail of small tenants but risks a noisy-neighbor problem for the largest ones without the resource governance described above.

Per-tenant performance. Heavy scans in a row-oriented relational store need materialized views (precomputed, periodically refreshed summary tables) to stay fast at scale, which introduces a staleness trade-off: the dashboard is fast but is answering yesterday's (or last hour's) numbers until the next refresh. A column-store answers ad hoc scans reasonably fast without precomputation, but "reasonably fast" for one query still costs real compute per execution; a genuinely hot, frequently repeated query still benefits from caching or pre-aggregation even on a column-store.

Scaling. Option (A) scales reads by adding replicas per tenant but does not scale writes past a single tenant's primary, and onboarding a new tenant is new infrastructure to provision. Option (B) scales by adding nodes or shards to the shared cluster, and onboarding a new tenant is a configuration change (a new tenant_id value), not new infrastructure.

SRE operational differences: backups, restores, resource isolation. This is where option (A) has a real, often-underweighted advantage: a per-tenant database backup is naturally tenant-scoped, so "restore tenant X to yesterday at 3pm without touching anyone else" is the default backup and restore story, at the cost of running and monitoring N times as many backup jobs. A shared column-store cluster backs up (and restores) the whole cluster at once; restoring a single tenant's data to a point in time from a cluster-wide snapshot usually means a slower, more manual process (exporting that tenant's rows from a restored snapshot copy and reloading them), which is a genuine recovery-time cost to weigh against option (B)'s other advantages, not a reason to dismiss it outright.

Worked example

Sizing both options against the same illustrative tenant population shows why the crossover happens where it does.

# relational-per-tenant vs shared columnar cluster, illustrative monthly cost.
n_small, small_gb = 450, 10       # long tail of small tenants
n_large, large_gb = 50, 500       # handful of large tenants
total_gb = n_small * small_gb + n_large * large_gb

min_instance_month = 50           # smallest viable managed relational instance, illustrative
option_a_floor = (n_small + n_large) * min_instance_month

shared_cluster_compute = 8000     # illustrative fixed monthly compute, sized for total scan volume
shared_storage_per_tb = 23        # illustrative columnar storage rate, $/TB-month
option_b_storage = total_gb / 1000 * shared_storage_per_tb
option_b_total = shared_cluster_compute + option_b_storage

print(f"total tenant data = {total_gb:,} GB across {n_small+n_large} tenants")
print(f"(A) relational-per-tenant floor = ${option_a_floor:,}/mo, before the 50 large tenants scale beyond the minimum instance")
print(f"(B) shared columnar cluster = ${shared_cluster_compute:,}/mo compute + ${option_b_storage:,.0f}/mo storage = ${option_b_total:,.0f}/mo")

# total tenant data = 29,500 GB across 500 tenants
# (A) relational-per-tenant floor = $25,000/mo, before the 50 large tenants scale beyond the minimum instance
# (B) shared columnar cluster = $8,000/mo compute + $678/mo storage = $8,678/mo

Under these stated assumptions, option (A)'s per-tenant floor alone is already about three times option (B)'s all-in cost, and option (A)'s number only grows from there once the 50 large tenants are sized past the minimum instance. That gap is the concrete argument for a shared column-store as the default, with dedicated relational infrastructure reserved for the specific tenants whose contracts require it, where the extra cost is a compliance requirement, not an accident of the architecture.

Trade-offs & pitfalls

• Treating tenant_id filtering as sufficient isolation without also configuring resource governance (workload queues or quotas). A shared cluster without them is one heavy tenant's bad query away from degrading everyone else's dashboards.
• Building per-tenant infrastructure for tenants that do not need it. The cost floor in the worked example applies to every tenant equally; reserve option (A) for tenants with an actual isolation requirement, not as a default "safer" choice.
• Underestimating single-tenant restore time on a shared cluster. It is the real operational cost of option (B)'s efficiency; have a tested, documented procedure for it before a tenant asks for a point-in-time restore under pressure, not after.
• Materialized views drifting silently stale on the relational side, if the refresh job fails quietly and no one is alerting on refresh staleness. A stale dashboard that looks correct is worse than a slow one that is visibly loading.
• Ignoring data volume skew when sizing a shared cluster. A cluster sized for the median tenant's scan pattern can be badly undersized for the handful of large tenants; size for the actual data and query distribution, not an average across tenants.

Direct answer

The requirement, an item added must be visible the instant the same user fetches their cart, is a read-your-writes guarantee, not a demand for global strong consistency at all times. Read-your-writes only needs to hold for a user relative to their own prior write, so the right design is not a binary pick between one strongly consistent relational database and one globally replicated eventually consistent store. It is: route each user's cart traffic to a single home region, get strong consistency cheaply within that region, and replicate asynchronously across regions for durability and for the rare case the user changes location.

Structured elaboration

Why a single-region strong RDBMS alone falls short. It trivially satisfies read-your-writes for users near that one region, but every other user pays cross-region latency on every cart operation, and that one region becomes a single point of failure for carts everywhere, which defeats the point of "globally replicated" in the first place.

Why a globally replicated NoSQL store's default mode alone falls short. A globally replicated key-value store's default multi-region mode typically replicates asynchronously with eventual consistency across regions: a write accepted in one region is not guaranteed to be visible to a read served from a different region within any bounded time. If a user's cart read happens to land on a different regional replica than the one that accepted their add-to-cart write, for example because a load balancer failed over between edge points of presence without session affinity, they can see a cart missing the item they just added. That is the exact correctness bug the question is pointing at.

The hybrid design.

1. Home-region routing: assign each user or session a home region (nearest to their first-touch location) and pin both their cart reads and writes to it at the edge, via a routing header or cookie the load balancer inspects, rather than sending requests to whichever region happens to be least loaded.
2. Same-region strong consistency for the common path: within the home region, either a normal relational transaction, or a strongly consistent read against the home region's replica of a key-value store, gives a real, cheap read-your-writes guarantee with no cross-region hop.
3. Asynchronous cross-region replication for durability and travel: replicate cart data outward so it survives a regional outage and is still available, slightly stale, if the user changes location, at which point the system re-homes them to the new nearest region.
4. For the narrow case where a user's device genuinely switches regions mid-session and home-region routing cannot be guaranteed, such as a server-to-server checkout integration without a sticky client, a synchronously replicated, strongly consistent write mode that some managed global key-value services now offer as an opt-in configuration can guarantee any-region reads see the latest write, at the cost of a real cross-region round trip on every write. That cost is acceptable selectively, not as the default for every add-to-cart click globally.

Worked example

Contrast the two failure paths concretely. A user in Sydney adds a jacket. If the client's next read is served by whatever region a stateless load balancer happens to pick, say Frankfurt because Sydney reported a momentary health blip, and Frankfurt has not yet received the asynchronous replica of that write, the fetch returns an empty cart. That is the literal bug. With home-region routing pinned to the Asia-Pacific region plus health-based regional failover, rather than per-request round robin across all regions, that scenario now only occurs during an actual regional outage of the user's home region, a much rarer and more acceptable failure mode than every single request carrying a chance of landing on a stale replica.

Trade-offs and pitfalls

A common pitfall is treating "eventual consistency" as a database property that can be swapped out later; the routing and session-affinity layer is doing the real work here, and changing the underlying database engine alone, relational to NoSQL or the reverse, does not fix a routing bug.

Another pitfall is defaulting to a synchronous, any-region-strong write mode everywhere because it sounds like the safest option. It taxes every single cart write with a real cross-region round trip, commonly tens of milliseconds depending on geography, for a guarantee the overwhelming majority of requests do not need, since most users never switch home regions mid-session.

What would flip the recommendation: the checkout and payment step, not the cart itself, needs a stronger guarantee independent of the cart's consistency model. Preventing a double charge when two payment attempts race from two devices calls for a genuinely serializable transaction (the strictest isolation level: the database guarantees the result is the same as if every transaction ran one at a time, with none interleaved) and an idempotency key at the payment boundary, regardless of how the cart's own reads and writes are routed.