Database Selection and Trade-offs Questions

Direct answer

Expect Redis to win a leaderboard-shaped write-heavy benchmark, because its sorted-set data structure is purpose-built for exactly this access pattern, with Cassandra as the fallback once the working set genuinely outgrows what fits comfortably in memory, and PostgreSQL as the control that will most likely lose on raw write throughput but establishes the transactional-safety baseline the other two are measured against. That expectation is a hypothesis, not the answer: the actual answer to "evaluate three candidates" is the benchmark design below, built so the result is decided by measurement, not by which system sounds best on paper.

Structured elaboration

Benchmark scenarios to define, each with an explicit target, not a vague description.

Failure modes to test, one per candidate plus one cross-cutting. Node or primary failure mid-write-burst: for Redis, verify whether a failover (the automatic process of promoting a standby replica to primary and redirecting traffic when the current primary goes down) during an unacknowledged asynchronous replication window can lose the most recent writes, and whether that risk is acceptable or needs to be closed with a stronger write-acknowledgment setting (accepting the latency cost of waiting for a replica to confirm). For Cassandra, verify behavior under a network partition: does it continue accepting writes per the configured consistency level, and do replicas correctly reconcile once the partition heals. For PostgreSQL, verify failover behavior with a managed or self-managed replication topology and measure how long writes are actually unavailable during the failover window. Cross-cutting: run a genuine network partition or node-kill during the sustained peak load from the writes/reads targets above, not against an idle system, since failure behavior under load is the behavior that matters.

Metrics and service-level objectives (SLOs) to observe. Write latency, p50 and p99. Read latency, p50 and p99. Read-after-write staleness distribution (not just an average, the tail is what a user actually notices). Replication lag under load. Error rate during the fault-injection window. Time to return to SLO-compliant behavior after a failure resolves. And one candidate-specific early-warning metric each: for Redis, memory headroom against maxmemory and the active eviction policy (if the working set exceeds available memory, Redis evicts keys per policy, commonly least-recently-used, which can silently and incorrectly drop leaderboard entries if the eviction policy is not deliberately scoped away from leaderboard keys); for Cassandra, compaction backlog (a growing backlog under sustained high write rate is the leading indicator that read latency is about to degrade, well before it visibly does); for PostgreSQL, table and index bloat ratio from autovacuum lag (a write-heavy pattern that repeatedly updates the same rows, exactly what leaderboard score updates do, is the classic case that causes PostgreSQL's multi-version concurrency control, MVCC, to accumulate dead row versions faster than autovacuum reclaims them, degrading both read and write latency over time if unaddressed).

Why these three failure modes are not interchangeable, and why they matter more than the raw throughput numbers. A benchmark that only measures steady-state throughput will make all three candidates look reasonable; the failure modes above are each a specific, well-known weak point of a write-heavy pattern on that particular engine, and a benchmark that skips them will not catch the actual production incident each engine is prone to.

Worked example

Derive the writes/sec and reads/sec targets from a stated scenario, and check Redis's working-set memory budget against a stated player count, since memory headroom is the single most consequential capacity question for a Redis-based design.

# benchmark target derivation for a write-heavy leaderboard.
players = 5_000_000
updates_per_player_per_day = 40      # match/score events per active player per day
writes_per_day = players * updates_per_player_per_day
writes_per_sec_avg = writes_per_day / 86400
peak_multiplier = 6                  # evening/event-driven peak concentration
writes_per_sec_peak = writes_per_sec_avg * peak_multiplier
print(f"avg writes/sec = {writes_per_sec_avg:,.0f}, peak writes/sec (x{peak_multiplier}) = {writes_per_sec_peak:,.0f}")

# Redis working-set sizing for a large leaderboard.
lb_players = 20_000_000
bytes_per_entry = 200                # member id + score + sorted-set node overhead, illustrative
working_set_gb = lb_players * bytes_per_entry / 1e9
replication_factor = 2               # primary + 1 replica for HA
headroom = 1.3
total_ram_gb = working_set_gb * replication_factor * headroom
print(f"working set = {working_set_gb:.1f} GB for {lb_players:,} players")
print(f"RAM budget with {replication_factor}x replication and {headroom}x headroom = {total_ram_gb:.1f} GB")

# avg writes/sec = 2,315, peak writes/sec (x6) = 13,889
# working set = 4.0 GB for 20,000,000 players
# RAM budget with 2x replication and 1.3x headroom = 10.4 GB

Two results worth acting on. First, peak write load (about 13,900 writes per second under these stated assumptions) is well within what all three candidates can sustain in isolation; the benchmark's value is in the failure-mode and tail-latency behavior at that load, not in whether any of the three can technically keep up. Second, a 20-million-player leaderboard's working set is only about 4 gigabytes, comfortably fitting in a single modestly sized Redis instance with room to spare for replication and headroom, well under commonly available managed-instance memory tiers. The real trigger for moving off Redis toward Cassandra is not this leaderboard's size, it is a leaderboard an order of magnitude or two larger, or one that needs to retain full historical score events (not just current standings) indefinitely, which no longer fits an in-memory design economically.

Trade-offs & pitfalls

• Benchmarking only steady-state throughput. All three candidates will look acceptable; the failure modes above are where the real differentiation, and the real production risk, actually lives.
• Assuming Redis's async replication failover is safe by default. It is not, without an explicit stronger write-acknowledgment configuration, which trades some write latency for the durability a leaderboard's ranking correctness actually needs.
• Missing a Cassandra compaction backlog until read latency has already visibly degraded. Compaction backlog is a leading indicator specifically because it degrades before the symptom (slow reads) becomes obvious; alert on the backlog metric itself, not just on read latency.
• Treating PostgreSQL's bloat risk as a generic "Postgres is slower" conclusion, rather than the specific, addressable cause it is: hot-row updates outrunning autovacuum. Tuning autovacuum aggressiveness for the specific hot table, or restructuring the update pattern, is a real fix, not a reason to dismiss PostgreSQL outright for smaller-scale versions of this workload.
• Setting the read-after-write latency SLO too strictly for the actual product requirement. A live global leaderboard tolerating roughly a second of staleness is normal and expected; over-specifying "instant" consistency here adds real engineering cost for a UX improvement users are unlikely to notice.

Direct answer

For a customer-profile service that is high-read, low-write, with a requirement for global read replicas (not global writes), recommend Amazon Aurora Global Database as the default, particularly for a full-stack team already fluent in SQL and ORM tooling: the workload shape, single-region writes fanning out to low-latency reads worldwide, is exactly what Aurora Global Database was designed for, and it is the cheapest and operationally simplest of the four named options for that specific shape. Reserve DynamoDB Global Tables for a genuinely non-relational schema or a workload with real multi-region write volume; reserve Cosmos DB for an Azure-committed team or one that needs its five-level tunable consistency spectrum for different call sites in the same application; reserve Spanner for the moment writes themselves need to happen from multiple regions with linearizable global consistency, which this workload, as stated, does not need.

Structured elaboration

Why Aurora Global Database fits this specific shape. The defining characteristic here is asymmetry: many reads, spread globally, versus few writes, concentrated in one place. Aurora Global Database has exactly one writable primary region and up to ten read-only secondary regions, with cross-region replication typically completing in under a second, using dedicated replication infrastructure separate from the database engine itself, so replication does not compete with the primary region's own read and write traffic. Each secondary region can scale independently with up to sixteen read instances (more than a standard, non-global Aurora cluster's usual limit of fifteen, since a secondary cluster is read-only). For a team that already writes SQL and uses standard relational tooling and object-relational mapping (ORM) libraries, staying on a PostgreSQL- or MySQL-compatible engine avoids the new data-modeling and query-pattern discipline the other three options would introduce.

Write forwarding, and its limits. Aurora Global Database also offers write forwarding, letting an application connected to a secondary region send writes that are transparently forwarded back to the primary region and applied there. This is useful for occasional writes originating far from the primary (an infrequent settings change from a user in a distant region, for example) without requiring the application to know which region is currently primary, but it does not turn Aurora into a multi-region-write system: every write still ultimately commits in one place, and a write forwarded from a distant secondary pays that distance's round-trip latency on top of the forward itself.

Consistency models across all four. Aurora Global Database: asynchronous, eventually consistent secondary regions (typically sub-second lag), with write forwarding as described above. Cloud Spanner: external consistency (linearizable) by default, everywhere, because every region can be a writer under a consistent global ordering. DynamoDB Global Tables: two modes, multi-Region eventual consistency (the default, replication typically completing within about a second) or the newer, opt-in multi-Region strong consistency mode, which synchronously replicates before acknowledging a write. Cosmos DB: five explicit, tunable consistency levels from strongest to weakest, Strong, Bounded Staleness, Session, Consistent Prefix, and Eventual, each trading off latency and availability differently, and selectable per request, not just per account.

Operational complexity, ordered for this workload. Aurora is lowest for a SQL-fluent team, since it is the least new to learn. DynamoDB and Cosmos DB are next, both fully managed but requiring new data-modeling discipline (partition-key design for DynamoDB, partition-key and consistency-level design for Cosmos DB). Spanner is the highest of the four here specifically because reasoning correctly about a globally distributed SQL system with TrueTime-based consistency is a genuinely new mental model, even for an already-SQL-fluent team, and it is the most operational and conceptual overhead to take on for a workload that does not need multi-region writes.

Cost, ordered for this specific read-heavy, low-write shape. Aurora Global Database is the cheapest of the four here: the team pays primarily for read-replica instances, storage, and the (typically low, dedicated-infrastructure) replication cost, without paying a global-write consensus tax it does not need. DynamoDB Global Tables is next: pay-per-request pricing is attractive, but every write is replicated to every region in the table, multiplying write cost by region count even though this workload has few writes. Cosmos DB and Spanner are priced around continuously provisioning for strong, multi-region guarantees, generally the most expensive of the four for an equivalent scale, a cost that buys a guarantee (multi-region write consistency) this workload does not need.

Worked example

Suppose this customer-profile service is later given a hard 99.99% availability target, and the question is whether Aurora Global Database's failover (the process of promoting a standby region to take over write duties when the primary region fails) mechanics can support it. Work the annual downtime budget and weigh it against what actually consumes it.

# 99.99% availability budget against Aurora Global Database's switchover mechanics.
minutes_per_year = 365 * 24 * 60
budget_9999_min = minutes_per_year * (1 - 0.9999)
print(f"99.99% annual downtime budget = {budget_9999_min:.1f} minutes/year")

planned_switchovers_per_year = 4     # quarterly maintenance-window switchovers, a reasonable illustrative cadence
assumed_writer_unavailable_min = 1   # illustrative, conservative upper bound per planned switchover
consumed = planned_switchovers_per_year * assumed_writer_unavailable_min
print(f"if {planned_switchovers_per_year} planned switchovers/year cost ~{assumed_writer_unavailable_min} min writer "
      f"unavailability each: consumes {consumed} of {budget_9999_min:.1f} min budget ({consumed/budget_9999_min:.1%})")

# 99.99% annual downtime budget = 52.6 minutes/year
# if 4 planned switchovers/year cost ~1 min writer unavailability each: consumes 4 of 52.6 min budget (7.6%)

Planned, well-tested switchovers are not the real threat to a 99.99% target; under these stated assumptions they consume well under a tenth of the annual budget. The real driver of whether this profile service hits 99.99% is how rarely an unplanned failover has to be invoked at all. AWS's current published figures for Aurora Global Database (aws.amazon.com/rds/aurora/global-database) do state a specific number here, not an open-ended design goal: an effective recovery point objective (RPO) of 1 second and a recovery time objective (RTO) of less than 1 minute for promoting a secondary Region to primary during a complete Regional outage. That stated RTO covers the automated promotion step itself; it is not the same as the total client-observed outage window, since detecting the primary Region's failure, updating DNS or connection endpoints, and application-side reconnection all add time on top of it, and none of that is bounded by AWS's stated figure. The actionable takeaway is to budget conservatively for planned operations (as above), treat the vendor's sub-1-minute promotion RTO as a floor on one step of recovery rather than a ceiling on total incident duration, and invest separately in monitoring and automation that keeps unplanned failovers rare and detected quickly, rather than treating the failover mechanism's advertised speed alone as the thing that earns the four nines.

Trade-offs & pitfalls

• Reaching for DynamoDB Global Tables or Cosmos DB out of a general instinct toward "NoSQL scales better," when the actual workload (relational, low write volume, SQL-fluent team) does not need either system's differentiators and would pay their higher cost and steeper learning curve for nothing.
• Treating write forwarding as equivalent to multi-region write support. It is a convenience for occasional distant writes, not a horizontal-write-scaling or multi-region-consistency feature; a workload with real multi-region write volume needs Spanner or DynamoDB's strong-consistency mode instead.
• Assuming a documented "sub-second" replication lag is a hard guarantee rather than a typical figure. Build monitoring for actual replication lag rather than assuming it never exceeds the typical case, especially during a regional network degradation.
• Sizing an availability target purely around the failover mechanism's speed, rather than around how rarely a failover is triggered in the first place, which the worked example shows is the dominant factor for whether a 99.99% budget actually holds up over a year.
• Choosing Cosmos DB's per-request tunable consistency without a clear policy for which call sites use which level. The flexibility is a real strength, but it is also a real way to introduce inconsistent behavior across an application if left to individual developers' ad hoc choices.

Direct answer

As an engineering manager, this decision isn't purely technical: team skills and operational cost carry real weight alongside data-model fit. For a team that already knows relational modeling and SQL, default to PostgreSQL unless the data is genuinely document-shaped (deeply nested, schema varies per record) or the access pattern is dominated by simple key-based lookups at very large horizontal scale, in which case MongoDB's document model and native sharding are the better technical fit and worth the ramp-up cost.

Structured elaboration

Worked example

Favors PostgreSQL: a subscription billing system, invoices, line items, payments, and customers that must never drift out of sync, an invoice total must always match the sum of its line items, enforced by foreign keys and a single transaction, where the business also regularly needs ad hoc relational reporting across customers, invoices, and payments.

Favors MongoDB: a product catalog where each category has a wildly different, evolving set of attributes, a laptop has RAM and CPU fields, a t-shirt has size and color fields, a new category ships next quarter with fields nobody has defined yet, read almost entirely as "fetch this one product's full detail." Forcing every category into a fixed relational schema means constant migrations or a sparse, mostly-null wide table, while a per-product document naturally holds exactly the fields that product has.

Trade-offs & pitfalls

The most common bad decision here isn't picking the wrong engine, it's picking on hype: choosing MongoDB because "it scales" for a team that will never approach a single well-tuned Postgres instance's ceiling, or choosing Postgres because it's the safe default for a workload that's genuinely document-shaped and will fight the relational model for years. As EM, weigh team skills honestly: a team's first production sharding incident is a real cost, and "we'll learn it" is a training-budget-and-timeline decision, not a footnote in the proposal.

Direct answer

A key-value (KV) store maps one opaque key to one opaque value: the engine has no idea what is inside the value, so it cannot query or index into it. A document store maps a key to a structured, typically JSON, document whose internal fields the engine CAN see, index, and query. A login session, read and written whole by session id and never queried by its internal fields, fits a key-value store. A product catalog with nested attributes (color, size, a spec sheet, a list of variants), which shoppers filter and sort BY, needs a document store's ability to index and query inside that structure.

Structured elaboration

The core distinction. Both kinds of store answer "get by key" in roughly constant time. The difference is what happens after that: a KV store treats the value as opaque bytes, with no query language and no awareness of structure inside it. A document store additionally indexes and queries fields INSIDE the document, for example WHERE variants[].size = 'M', at the cost of the engine having to parse and index document structure on every write.

Session store: key-value. A session record is always read and written whole, by session id; nothing in the hot path ever needs to ask "find all sessions where role = admin." Add expiry (a TTL, or time-to-live, an attribute or command telling the store when to remove the record automatically) and this is exactly what a KV store like Redis or DynamoDB is built for. Indexing and query implications: none beyond the primary key, which is precisely why a document store's indexing machinery buys nothing here.

Product catalog: document store. Shoppers filter and sort by nested attributes constantly, and a product record is naturally nested (a product has variants, each variant has its own attributes). A document store's multikey and array indexes let it answer "products where any variant has size = M and color = blue" directly. Forcing this into a pure KV store means either flattening every filterable attribute into the key (unworkable past one or two filter dimensions) or exporting to a separate search index just to answer catalog-browse queries, which is the extra system avoided entirely if the store already chosen can index nested fields.

Why this distinction is worth making to a non-technical stakeholder. Paying for indexing power nobody uses is paying for a feature nobody asked for. Conversely, saving money by putting genuinely filterable, browsable data into a plain lookup table means engineering will need to bolt on a second system later to support "let customers filter by color," which costs more over time than choosing correctly up front. The database choice here is really a bet on what the business will ask users to DO with the data: look it up by a known key, or search and filter it.

Worked example

Session store: SET session:<id> <blob> EX 1800 (Redis, a 30-minute expiry) or a DynamoDB item with a TTL attribute, a single get-by-id read path, and no secondary index ever created for it. Product catalog: a document shaped like

{ "sku": "SHIRT-42", "variants": [
    {"size": "M", "color": "blue", "price": 24.99, "stock": 12},
    {"size": "L", "color": "blue", "price": 24.99, "stock": 3}
]}

with a compound multikey index on variants.size and variants.color, supporting a query like "find products where a variant has size M and color blue" directly against the index.

Trade-offs & pitfalls

The common mistake is picking a document store "to be safe" for something that is really pure key-value access (session data, feature flags, rate-limit counters), paying indexing and storage overhead for structure nobody queries. The opposite mistake, picking a KV store for genuinely queryable data and discovering months later that an internal field needs filtering, is the more expensive one to unwind, since it usually means a live data migration under production load.

Direct answer

Use InnoDB, not MyISAM, for this workload. MyISAM's table-level locking means every write blocks every other read and write against that table, which is disqualifying for a workload described as mostly heavy writes, while InnoDB's row-level locking plus MVCC (multi-version concurrency control: readers see a consistent snapshot of the data without blocking writers, and writers don't block readers) is exactly the property that lets an occasional analytical query run without stalling the write path.

Structured elaboration

Comparing the two engines directly, per current MySQL documentation:

Given this, InnoDB is the only reasonable choice for a heavy-write workload. MyISAM's remaining legitimate cases, per current documentation, don't describe this one.

Indexing approach

Keep the number of secondary indexes on the write-heavy tables deliberately small, since InnoDB maintains every secondary index synchronously on every insert and update, so each additional index is real write amplification (extra physical writes the database performs beyond the one logical row change you made). Add a covering index (one that includes every column the analytical query needs) specifically shaped for the analytical query only if it's a small, stable, repeated query, so it can be served from the index without visiting the full row, rather than adding broad indexes speculatively.

Provisioning so analytics doesn't interfere with the write path

InnoDB solves the locking problem but not the I/O and cache contention problem: a large aggregation still consumes buffer pool cache (InnoDB's in-memory cache of data pages, kept to avoid re-reading from disk) and disk I/O that writes also need. The concrete move is to run analytical queries against a replica, an InnoDB replica receiving the same writes via replication, rather than the primary, so the analytical query's I/O and cache pressure lands on a separate instance. Size the replica's InnoDB buffer pool independently, for the analytical working set, which is often close to the whole table, unlike the primary's buffer pool, which mainly needs the recently written "hot" rows. Set a statement timeout on the analytical connection so a runaway ad hoc query on the replica can't grow unbounded and starve the replication apply thread, which would otherwise let the replica's data go stale for everyone.

Worked example

-- InnoDB is the MySQL 8.4 default, but be explicit
CREATE TABLE events (
  event_id    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  device_id   BIGINT UNSIGNED NOT NULL,
  event_type  VARCHAR(32) NOT NULL,
  payload     JSON NOT NULL,
  created_at  DATETIME NOT NULL
) ENGINE = InnoDB;

-- One narrow secondary index actually used by the write-time lookup pattern
CREATE INDEX idx_events_device_created ON events (device_id, created_at);

On the read replica used for analytics: SET SESSION max_execution_time = 30000; kills any single query running past 30 seconds, and the replica's innodb_buffer_pool_size is provisioned larger relative to instance memory than the primary's, because the primary mostly needs to cache the current write-hot region while the replica needs to cache whatever the analytical queries scan, often a much larger working set.

Trade-offs & pitfalls

Routing analytics to a replica introduces replication lag, so analytical numbers are always slightly behind the primary; this needs to be an accepted, stated freshness bound, not a surprise discovered later. If the "occasional" analytical queries grow into a regular reporting workload with dozens of dashboards, a single InnoDB replica stops being the right tool, and it's time to reconsider this same separation decision with a genuinely columnar system rather than continuing to add replicas as the only lever. Don't assume InnoDB alone fixes everything: a write-heavy table carrying eight redundant secondary indexes for old query patterns nobody uses anymore will still have a slow write path. InnoDB's row-level locking removes MyISAM's specific problem, it doesn't remove general write amplification from over-indexing.