Database Selection and Trade-offs Questions

Direct answer

Add a third, often-overlooked option to this comparison: PostgreSQL with read replicas, which is the right call for a mid-sized team below a real, statable throughput threshold, since it avoids adding a second datastore entirely. Above that threshold, DynamoDB is the right default for most teams, mid-sized or enterprise, because it is fully managed (no repair or compaction to operate) and has native, built-in item expiry (time-to-live, TTL) that maps directly onto an abandoned-cart use case. Reserve Cassandra for a team that already operates a Cassandra fleet at real scale, or that specifically needs active-active writes across many regions with fine-grained, per-operation consistency tuning, or that needs to avoid single-cloud lock-in.

Structured elaboration

Decision matrix.

Data modeling. All three model a cart the same conceptual way: one record per user_id (or cart_id), holding the current set of line items. DynamoDB embeds the line items as a list or map attribute on the item; Cassandra holds them as a collection type or as clustering-column rows under the same partition key; PostgreSQL stores them either as a JSONB column on the cart row or as a normalized cart_items child table, the more natural relational choice if line items ever need to be queried or joined independently (for example, for inventory reconciliation).

Consistency options. DynamoDB defaults to eventually consistent reads, with strongly consistent reads available per request, a good match for "read your own cart" if the read immediately follows the write on the same request path. Cassandra exposes consistency as a tunable per query (from ONE, a single replica, up to QUORUM, a majority of the replicas, or ALL, every replica), giving fine control at the cost of a decision the team has to make deliberately and correctly for every query pattern. PostgreSQL is trivially consistent on the single writer; its read replicas are asynchronous, so a cart view routed to a stale replica immediately after a write can show outdated data, a real and easy-to-miss bug unless the write path is designed to read its own write from the primary.

TTL and expiry, a genuine differentiator. DynamoDB and Cassandra both sweep and remove expired items automatically, in the background, at no query-time cost, a strong fit for "clear out abandoned carts after N days." PostgreSQL has no native equivalent; expiry has to be implemented as a scheduled job (a cron-triggered DELETE ... WHERE last_updated < now() - interval), which is a small but real, ongoing piece of operational surface that does not exist on the other two.

Hotspots. A common misconception is that a shopping-cart service hotspots on popular items during a flash sale. It does not, in any of the three designs above, because the partition or primary key is user_id, not item_id; cart writes are naturally spread across users, not concentrated on whatever product is trending. A real hotspot risk would only appear from a different, poor key choice, for example partitioning by store_id for a platform with one dominant retailer, which none of these three designs do here.

Repair and compaction, Cassandra's real operational cost. Cassandra requires operator-scheduled anti-entropy repair (reconciling replicas that have drifted, commonly run via nodetool repair on a regular cadence, before deleted data's tombstone markers expire past gc_grace_seconds and could resurface) and ongoing compaction-strategy tuning (choosing and tuning between strategies like size-tiered and leveled compaction for the actual write and read pattern). Both are genuine, continuous SRE work that DynamoDB and managed PostgreSQL (for example, Amazon RDS) simply do not require, since that maintenance is the vendor's responsibility in both of those options.

Worked example

Size DynamoDB's cost at a stated cart volume, and use it to reason about where the mid-sized-versus-enterprise threshold plausibly sits.

# DynamoDB on-demand monthly cost for a shopping-cart service.
carts = 2_000_000
reads_per_cart_per_day = 8
writes_per_cart_per_day = 3

monthly_reads = carts * reads_per_cart_per_day * 30
monthly_writes = carts * writes_per_cart_per_day * 30

price_per_million_read_eventual = 0.0625
price_per_million_write = 0.625

read_cost = monthly_reads / 1_000_000 * price_per_million_read_eventual
write_cost = monthly_writes / 1_000_000 * price_per_million_write
total = read_cost + write_cost

print(f"monthly reads = {monthly_reads:,}, monthly writes = {monthly_writes:,}")
print(f"DynamoDB on-demand monthly cost = ${total:,.2f} (${read_cost:,.2f} read + ${write_cost:,.2f} write)")

# monthly reads = 480,000,000, monthly writes = 180,000,000
# DynamoDB on-demand monthly cost = $142.50 ($30.00 read + $112.50 write)

At two million carts, DynamoDB's monthly cost is genuinely small, well within range of what a single well-provisioned PostgreSQL primary with a couple of read replicas would cost to run for the same load. This is the concrete basis for the "threshold" in the decision matrix: at this scale, the deciding factor is not cost, it is whether the team wants to operate a relational instance and its own expiry job (PostgreSQL) or hand that operational surface to a managed store (DynamoDB). The threshold shifts meaningfully once cart volume grows another order or two of magnitude, at which point a single relational primary's connection and write-throughput ceiling becomes the real constraint, not cost.

Trade-offs & pitfalls

• Assuming a NoSQL store is required "at scale" without checking whether PostgreSQL's actual ceiling has been reached. The worked example shows a mid-sized deployment can comfortably stay relational well past what feels like "real" scale.
• Believing shopping carts hotspot on popular items. They hotspot on users, not items, in a correctly keyed design; this misconception has led to unnecessary key-sharding work on systems that never needed it.
• Skipping Cassandra's repair schedule because the cluster "seems fine." Deferred repair risks deleted data resurfacing after gc_grace_seconds expires on a replica that missed a delete, a subtle and unpleasant correctness bug specific to Cassandra's eventually consistent deletion model.
• Building a custom expiry job for PostgreSQL and letting it silently fail. Without alerting on the job's own health, an abandoned-cart cleanup job that stops running is invisible until storage or query performance visibly degrades.
• Choosing Cassandra for its consistency tunability without the operational maturity to actually operate repair and compaction correctly. The flexibility is only a genuine advantage in the hands of a team equipped to run it; otherwise it is pure added operational risk.

Direct answer

Polyglot persistence means deliberately using different data stores for different parts of one system, each chosen for the access pattern it actually serves, rather than forcing a single database to be good at everything. It earns its complexity when a system has genuinely different workloads coexisting, a transactional write path alongside a search path alongside a flexible-schema read path, exactly the shape in this question, and it is overreach when adopted reflexively for a system whose workloads are actually similar enough that one well-run database would have handled all of them.

Structured elaboration

When it is valuable. Distinct access patterns with genuinely different optimal storage shapes coexisting in one product: transactional writes needing atomicity, consistency, isolation, and durability (ACID) for orders and payments; free-text or faceted search needing an inverted index for product search; flexible per-entity documents needing schema freedom for user profiles with varying optional fields. Serving all three from one relational database usually means either a mediocre search implementation bolted onto SQL, or forcing profile data into a rigid schema that fights its natural shape.

Common architecture patterns. A system of record plus derived read models, where one store owns the truth and others are kept in sync as read-optimized projections via change data capture (CDC) or an outbox pattern (writing the event to an outbox table in the same transaction as the actual data change, so a separate process can publish it reliably afterward instead of risking the change and the notification getting out of sync), is the dominant, safest pattern because it keeps a single, unambiguous source of truth. Split ownership by bounded context, where each service genuinely owns and is the sole writer of its own store, and other services reach it only through that service's API, never by reading its database directly, is the complementary organizational pattern.

Operational and developer pitfalls to avoid. Multiple sources of truth for the same fact, for example both an order service and a search index both treating themselves as authoritative for "is this in stock," which drifts apart over time; always designate exactly one owner per fact. Synchronization lag surprising a caller that expects same-store consistency it does not actually have; design explicitly for which paths tolerate propagation delay and which cannot. Operational sprawl, where N stores means N backup strategies, N monitoring setups, and N sets of on-call runbooks and access controls to maintain correctly, a real, recurring cost that should be weighed against the workload-fit benefit, not assumed free. Treating cross-store atomicity as routine, since there is no shared transaction manager spanning, say, a relational database and a search index, so the architecture must be built around synchronization that is eventual (the two stores agree after a short delay, not instantly), idempotent (applying the same update twice has the same effect as applying it once, so a duplicate message causes no harm), and replayable (past events can be reprocessed from history to rebuild or repair a store) rather than an illusion of atomic writes across stores.

Worked example

flowchart LR
    Client -->|checkout| OrderSvc[Order service]
    Client -->|profile edit| ProfileSvc[Profile service]
    Client -->|search| SearchSvc[Search service]
    OrderSvc -->|ACID writes| OrdersDB[(Relational DB: orders)]
    ProfileSvc -->|flexible per-user documents| ProfileDB[(Document store: profiles)]
    ProductSvc[Product service] -->|source of truth| ProductDB[(Relational DB: products)]
    ProductDB -->|CDC or outbox| SearchIndex[(Search index: product lookup)]
    SearchSvc -->|query| SearchIndex

Transactional order writes go to a relational database, ACID across order lines, payment status, and stock decrement in one commit. Flexible per-user profile data goes to a document store, each user's profile a self-contained record with optional fields that vary per user, fetched whole by identifier, with no cross-user joins needed. Fast product-lookup search goes to a dedicated search index, fed by CDC or an outbox from the product service's own relational store of record, because free-text and faceted search is exactly what an inverted-index engine is built for and a basic relational text match is not.

Trade-offs and pitfalls

Running three stores for a system this size is only worth it once product search genuinely needs facets, free text, and ranking beyond a few relational indexes, and profile data genuinely varies enough per user that a rigid schema would fight it. A smaller version of this same product, few users, simple structured profile fields, simple catalog search, is better served by one relational database with good indexing, and splitting into three stores too early is the overreach pitfall materializing in practice.

What would flip the recommendation: at a small enough scale, collapse profile and search into the same relational database as orders, profiles as a nullable-column or JSONB-augmented table, search via a basic full-text index, until a measured need, search relevance complaints, or profile schema churn causing frequent migrations, actually justifies splitting out a dedicated store.

Direct answer

For a regulated financial client, recommend an open-source database engine paired with a paid commercial support contract, rather than either a purely community-supported open-source deployment or a fully proprietary enterprise engine. A regulated environment needs a contractually-backed support SLA and a documented patch-liability chain, and that is purchasable for a mature open-source engine without taking on a proprietary engine's licensing cost and deeper lock-in.

Structured elaboration

Worked example

A hypothetical regulated client running a core-banking-adjacent reporting system: recommend PostgreSQL, encrypted at rest and in transit, with a commercial support subscription whose SLA terms (severity-1 response time, patch delivery commitment) are documented as part of the audit evidence package. Principal risks and mitigations: (1) no single vendor to hold accountable in a fully self-integrated open-source stack, mitigated by the support contract plus a documented internal runbook and a named on-call owner; (2) license drift as the ecosystem's tooling changes hands or terms change, mitigated by tracking the specific license of every component, the core engine, extensions, and backup tooling, in an inventory reviewed at least annually; (3) skills concentration in one or two engineers, mitigated by funding certification and training for a second and third engineer before the first becomes a single point of failure.

Trade-offs & pitfalls

Don't treat open source as automatically cheaper for a regulated client: the audit and compliance overhead of proving controls on a self-assembled stack can exceed a proprietary vendor's bundled compliance tooling, so an honest TCO comparison has to include the cost of building the evidence a regulator will ask for, not just license fees against support fees.

Direct answer

Build a weighted scoring rubric across the eight named criteria, weight compliance and operational burden highest for a workload that explicitly names strong compliance needs, and score each option with evidence rather than impression. For a workload needing moderate (not extreme) customization, a managed service usually wins once operational burden and compliance are weighted honestly, unless a specific compliance control the vendor cannot attest to forces a self-managed deployment.

Structured elaboration

Scoring categories and weights

The weights encode the workload's own stated priorities: compliance (25) and operational burden (20) dominate because this is described as a critical application with strong compliance needs and, implicitly, a team that is not purpose-built to run a database platform.

Worked example

For a workload needing moderate customization and strong compliance, one honest scoring pass (1 to 5 per option per criterion):

$\text{Total}_{\text{option}} = \sum_{i=1}^{8} w_i \times s_i$

Managed total: (10x4) + (10x3) + (20x5) + (25x4) + (15x5) + (10x5) + (5x3) + (5x2) = 40 + 30 + 100 + 100 + 75 + 50 + 15 + 10 = 420.
Self-managed total: (10x4) + (10x5) + (20x2) + (25x3) + (15x3) + (10x3) + (5x3) + (5x5) = 40 + 50 + 40 + 75 + 45 + 30 + 15 + 25 = 320.

Out of a maximum possible 500 (weights sum to 100, times a max score of 5), managed scores 420, 84%, and self-managed scores 320, 64%. Managed wins here specifically because compliance and operational burden, the two heaviest weights, are also where managed scores highest and self-managed scores lowest; the result would flip if compliance had to score low for managed (say, a control genuinely unavailable from any vendor) or if operational burden scored high for self-managed (an existing, funded DBA team).

Trade-offs & pitfalls

A scoring model is only as honest as its inputs: scoring every criterion from memory rather than evidence (an actual compliance attestation checked against the specific regime, an actual measured operational-hours estimate) turns the exercise into a way of pre-justifying a decision already made rather than making one. Weights matter more than most people expect: two reviewers scoring identically but weighting compliance at 10 instead of 25 will reach opposite recommendations, so the weights themselves need to be agreed and written down before scoring starts, not adjusted afterward to match a preferred answer.

Direct answer

For scenario A, ad hoc analytics and complex queries, choose PostgreSQL: its SQL vocabulary for joins, aggregates, and window functions, plus general-purpose secondary indexing, directly serves "ask a new question of the data without redesigning the schema." For scenario B, extremely high write throughput with simple, known access patterns, choose DynamoDB: its partition-based architecture scales write throughput close to linearly by spreading load across partitions, without the single-primary ceiling a relational database eventually hits, and "simple access patterns" is precisely the assumption DynamoDB's key design is built around.

Structured elaboration

Recommendation with rationale. Scenario A needs query flexibility more than raw write scale. Analysts asking new questions cannot design a key-value schema for a question that does not exist yet, so PostgreSQL, ideally with a dedicated read replica so ad hoc queries do not compete with production writes, fits directly; if analytics volume outgrows what that replica comfortably serves, the next step is exporting into a columnar warehouse, not replacing the log store itself.

Scenario B needs write scaling more than query flexibility. A well-chosen partition key, for example the identifier the events are naturally grouped by, gives near-linear write scaling without a team manually sharding a relational primary, and "simple access patterns" is the exact condition DynamoDB's design assumes rather than fights.

Worked example

Suppose scenario B's target is 50,000 writes per second spread across 100,000 distinct users, a reasonable partition key candidate. That averages 50,000 / 100,000 = 0.5 writes per second per user, far below a single partition's verified ceiling of roughly 1,000 write operations per second for a small item, so a naive per-user partition key absorbs this comfortably. The same 50,000 writes per second against a single relational primary would need careful connection pooling, batched commits, and eventually table partitioning, none of which DynamoDB requires the team to build by hand.

Trade-offs and pitfalls

A pitfall is choosing DynamoDB for scenario A because "it scales." Write scale was never the bottleneck in an ad hoc analytics scenario; query expressiveness was, and a partition-and-key-value model does not fix that.

A pitfall for scenario B is choosing PostgreSQL and assuming sharding can be bolted on later without a real migration project. Relational horizontal write scaling is genuine engineering work, not a configuration flag.

What would flip either recommendation: if scenario A's "ad hoc" queries stabilize into a small, known set, for example always "events for this user in this date range," DynamoDB with a well-chosen sort key handles that fine and more cheaply at scale than PostgreSQL. If scenario B's throughput target turns out to be moderate, a few thousand writes per second, a well-tuned PostgreSQL primary handles it without adopting DynamoDB's operational model at all.