snoodleboot-io Managed Database Selection (Verbose)
Core Patterns
The Access Pattern Comes First
The most expensive database mistakes are made before a single row is written, by choosing the engine from familiarity or hype and then discovering the data does not fit. Invert it. Write down the access pattern first:
- Read/write ratio, and whether either is bursty
- Query shapes: point lookup by key, range scan, join across entities, aggregate
- Item/row size and total data volume
- Cardinality and how the working set grows
- Consistency requirement: read-after-write? cross-region? eventual acceptable?
- Latency budget and throughput ceiling
Only now does the choice become mechanical. The access pattern selects the family; the specific managed engine is a follow-on decision about operations and cost.
Matching Pattern to Family
| Family | Access pattern it serves | Costs / constraints | Managed examples |
|---|---|---|---|
| Relational | Joins, multi-row transactions, ad-hoc and evolving queries, referential integrity | Vertical scaling first; sharding is manual effort | RDS, Aurora, Cloud SQL, AlloyDB, Azure SQL |
| Document | Self-contained aggregates of variable shape, read/written whole | Cross-document joins are weak; easy to denormalize into anomalies | DocumentDB, MongoDB Atlas, Firestore, Cosmos DB |
| Key-value | Point lookups by known key, extreme throughput, simple values | No secondary query without extra indexes; partition key is the design | DynamoDB, Bigtable, Memorystore, Cosmos DB |
| Wide-column | Massive write volume, queries always by partition key, time-bucketed rows | Query flexibility is near zero off the key; tombstone/compaction care | Cassandra, Keyspaces, Bigtable, Scylla |
| Time-series | Append-only events/metrics, time-range queries, downsampling and retention | Not for mutable relational data; late-arriving data needs handling | Timestream, TimescaleDB, InfluxDB |
nosql-database-selection goes deeper on the non-relational branch; this skill is about picking among managed offerings once the family is known.
Relational as the Default
Relational databases earn their default status through a specific property: they let you defer query design. Normalize the entities and the engine will answer queries you did not anticipate, because the query planner composes joins and indexes at read time. Non-relational stores trade this away. In a key-value or wide-column store the partition key is the schema — you must know the queries before you model, and a new access pattern often means a new table or a full migration.
So the rule is: start relational, and move off it only when a concrete pattern forces the move — write volume beyond what one primary sustains, data too large to fit the vertical-scaling ceiling, a shape (documents, wide time-bucketed rows) that relational serves awkwardly, or a latency/throughput target a single primary cannot hit. "We might need to scale someday" is not that force.
Operational Model Within a Family
Choosing relational does not end the decision — the operational shape still varies:
Instance-based (RDS, Cloud SQL)
you choose instance class; storage attached; you plan capacity
predictable cost, hands-on scaling, well understood
Cloud-native distributed (Aurora, AlloyDB, Spanner)
storage decoupled from compute; read replicas scale reads cheaply
higher price floor; some (Spanner) add horizontal write scale + global consistency
Serverless (Aurora Serverless, DynamoDB on-demand)
capacity tracks load automatically
ideal for spiky/unpredictable or dev workloads; watch cost under sustained load
Match the model to the traffic curve, not the peak. A steady, predictable workload is cheapest on a right-sized instance. A spiky or unpredictable one wastes money on a provisioned peak and belongs on serverless or on-demand capacity.
Replication, HA, and DR Are Three Different Things
These get conflated, and the conflation causes data-loss incidents.
| Mechanism | Purpose | What it is NOT |
|---|---|---|
| Read replica | Offload read traffic; may lag | Not a backup; not automatic failover |
| Multi-AZ standby | Automatic failover for HA | Not a read scaler (often can't be read) |
| Cross-region replica | Disaster recovery; read locality | Not synchronous; expect lag and possible loss |
| Snapshot / PITR | Backup and point-in-time restore | Not high availability |
Two failure modes follow directly. First, treating a read replica as a backup: a
bad DELETE replicates to it just as fast as to the primary. Second, reading your
own write from an async replica and getting a stale result — because replication
lag is real and unbounded under load. If a flow needs read-after-write, route it to
the primary or use a session-consistency guarantee, do not assume the replica has
caught up.
Consistency and Locality Set the Ceiling
Consistency is the axis that most narrows the field, so decide it explicitly.
- Strong, single-region — any relational primary gives this within a region.
- Strong, global — a small club (Spanner-class systems using synchronized clocks / consensus). You pay for it in write latency, because a write must reach a quorum across regions before it commits.
- Eventual — opens the widest, cheapest set of options, and is correct for plenty of workloads (feeds, catalogs, analytics) that do not need to read the latest write immediately.
Locality interacts with this: putting data close to users lowers read latency but, if you also want strong global consistency, raises write latency. You rarely get low-latency writes and strong global consistency and multi-region — pick two, knowingly. See cloud-provider-tradeoffs for how each provider's flagship offering resolves this, and distributed-caching-design for absorbing read load without changing the database's consistency model.
Common Anti-Patterns
❌ Choosing the engine before writing down the access pattern. The data ends up fighting the store. ✅ Document read/write ratio, query shapes, and consistency needs first; let them select the family.
❌ Reaching for a NoSQL store "to scale" and then running relational queries on it — ad-hoc filters, joins, aggregates it cannot serve without full scans. ✅ Use a non-relational store only when the access pattern is genuinely key/partition oriented; otherwise stay relational.
❌ Forcing relational data into key-value and hand-rolling transactions and integrity the engine used to provide. ✅ Keep transactional, relationship-heavy data in a relational engine.
❌ Treating a read replica as a backup or as failover. It faithfully replicates your mistakes and does not promote itself. ✅ Separate read replicas, multi-AZ standby, cross-region DR, and snapshots — provision each for its own job.
❌ Reading your own write from an async replica and being surprised by staleness. ✅ Route read-after-write to the primary, or use an explicit consistency guarantee.
❌ Provisioning for peak on a spiky workload. You pay for idle capacity all day. ✅ Use serverless/on-demand for spiky traffic; reserve right-sized instances for steady load.
❌ Assuming strong global consistency is free. It costs cross-region write latency and a narrow engine choice. ✅ Decide the consistency requirement up front and accept its latency and cost.
Database Selection Checklist
- Access pattern written down before any engine is named
- Data-model family chosen from the pattern (relational / document / key-value / wide-column / time-series)
- Relational used unless a concrete pattern forces otherwise
- Operational model (instance / distributed / serverless) matched to the traffic curve
- Consistency requirement (strong single-region / strong global / eventual) stated explicitly
- Read replicas, multi-AZ HA, cross-region DR, and backups each provisioned for their own purpose
- Read-after-write paths routed to the primary or given a consistency guarantee
- Backup and point-in-time-restore configured and test-restored
- Cost modeled against the traffic curve, not the peak
- Cross-reference: nosql-database-selection, distributed-caching-design, cloud-provider-tradeoffs