Nosql Database Selection

NoSQL Database Selection (Verbose)

Core Patterns

Access-Pattern-First Selection

Relational modelling lets you defer query design: normalize the entities, and the optimizer will find a plan for whatever you ask later. Non-relational stores remove that safety net. There is no join and often no ad-hoc filter, so the physical layout is the query plan. Design it from the queries or you will discover, in production, that a required read is impossible without a full scan.

The method, in order:

1. Enumerate every access pattern. Reads and writes, with shape, rate, and latency budget. Be exhaustive; the one you forget is the one that breaks the design.

2. Split by workload class. Patterns 5 and 6 are not OLTP. Aggregation belongs in a warehouse or a column store (see dimensional-modeling); full-text belongs in a search engine fed by change data capture. Forcing them into the operational store is the most common cause of "our NoSQL database is slow".

3. Identify the constraints that eliminate candidates. Pattern 4 needs a conditional write with a real guarantee — a store with last-write-wins eventual consistency will oversell inventory. Pattern 1's 10 ms p99 at 4k/s rules out anything needing a cross-region quorum on the read path.

4. Only now name products, and evaluate each against the surviving list.

If you are starting from an existing relational schema or an unclear domain, run data-model-discovery first — you cannot enumerate access patterns for a domain you have not mapped.

Store Families and Their Query Shapes

Two heuristics do most of the work:

• Start from what a store cannot do. Elimination is faster than comparison and far less susceptible to marketing.
• A graph database earns its place at three-plus hops of variable depth. One or two fixed hops is a join, and a relational database does joins well. "Our data is connected" describes every dataset ever.

Modelling the Queries: DynamoDB Single-Table Design

In a wide-column store you cannot join at read time, so you pre-join at write time: items that are read together are stored together, under a shared partition key, ordered by a sort key you design.

Take patterns 1, 2 and 3 above.

PK                 SK                       GSI1PK          GSI1SK        attrs
CUSTOMER#42        PROFILE                  -               -             name, email, tier
CUSTOMER#42        ORDER#2026-03-01#9912    STATUS#SHIPPED  2026-03-01    total, status
CUSTOMER#42        ORDER#2026-02-17#9744    STATUS#DELIVERED 2026-02-17   total, status
ORDER#9912         META                     -               -             total, addr, status
ORDER#9912         ITEM#001                 -               -             sku, qty, price
ORDER#9912         ITEM#002                 -               -             sku, qty, price

• Pattern 1 — GetItem(PK=ORDER#9912, SK=META), or Query(PK=ORDER#9912) to get the order and all its line items in one round trip. That is the join, performed at write time.
• Pattern 2 — Query(PK=CUSTOMER#42, SK begins_with "ORDER#", ScanIndexForward=false, Limit=20). Newest first, paged, one request. The date sits in the sort key before the id precisely so lexical order equals chronological order.
• Pattern 3 — Query(GSI1, GSI1PK=STATUS#SHIPPED, GSI1SK < 2026-03-18). A secondary index is a second physical layout of the same items, maintained for you, to serve an access pattern the main key cannot.

What this makes explicit:

1. The key encodes the query. ORDER#<date>#<id> is chosen because you must list a customer's orders in date order. Sort by total instead and the key changes. This is why the design must follow the patterns.
2. Entity type lives in the key prefix, not in a separate table. One physical table, many logical entities, distinguished by PK/SK prefixes.
3. Duplication is the point. Order status appears on both the customer-scoped item and the order item. You are trading write cost and consistency work for read cost, deliberately.

And what it costs:

• New access patterns can require a migration. If someone later needs orders by warehouse, you add a GSI or backfill. A normalized relational schema would have absorbed that with a WHERE clause.
• It is hard to read. Onboarding onto a single-table design is materially slower than onto a normalized schema, and the model lives in code and docs rather than in the schema itself.
• It presumes stability. Adopt it when access patterns are known and settled — a mature, high-scale service. For a product still discovering its domain, the rigidity costs more than the read efficiency saves.

Partition key selection is the load-bearing decision. The key must spread traffic. PK = CUSTOMER#<id> is fine when no customer dominates; PK = TENANT#<id> in a system where one tenant is 60% of traffic creates a hot partition that no amount of provisioned capacity fixes, because a single partition has a hard throughput ceiling. Mitigations: add a suffix shard (TENANT#7#3 across N shards, scatter-gather on read), or key by a finer-grained entity. Equally, partitions must be bounded — a key like ORDERS#ALL grows without limit and eventually cannot be queried usefully.

"We Need NoSQL for Scale" Is Usually Wrong

For OLTP workloads below a few terabytes, a single well-tuned relational primary on modern hardware handles tens of thousands of transactions per second, with read replicas for read-heavy fan-out. Meanwhile you keep:

• Joins, so you are not writing them in application code
• Multi-row ACID transactions, so invariants hold without compensating logic
• Foreign keys, uniqueness, and check constraints enforced by the engine
• Ad-hoc query capability for the questions nobody anticipated
• Decades of tooling for backup, replication, migration, and query analysis

Nearly every "we outgrew Postgres" story that turns out to be true involves either write throughput past what one primary can absorb, or a working set far larger than one machine's memory. Most that turn out to be false involve a missing index, an N+1 query, or analytics running on the OLTP primary — none of which a different database fixes. See sql-optimization before migrating.

Real reasons to leave relational:

That last one is legitimate and under-discussed: choosing DynamoDB because nobody wants to run a database is a defensible decision, provided you know you are paying for it in modelling rigidity.

Also worth noting: modern Postgres covers several of these adjacently — JSONB for semi-structured documents, TimescaleDB for time-series, pgvector for embeddings, tsvector for modest full-text. One database you operate well often beats three you operate poorly.

Consistency Models

Concretely, what eventual consistency breaks:

• Read-your-writes. User edits a profile, the write goes to region A, the reload reads region B, and the old value appears. Users read this as a bug and they are right to. Fix by pinning a session to a replica or by reading strongly for a short window after a write.
• Counters and balances. Read-modify-write on an eventually consistent value loses updates under concurrency. Use an atomic increment or a conditional write with a version, never read-then-write.
• Inventory and uniqueness. "Check then reserve" oversells under concurrency. This needs a conditional write with a real guarantee, or a reservation record with a uniqueness constraint.
• Cross-entity invariants. "A customer may have at most one active subscription" cannot be enforced across items in an eventually consistent store without additional machinery — a single item holding the invariant, or a transaction that spans them.

Most stores let you choose per operation. Use strong reads on the few paths that need them (balance, inventory, auth) and eventual elsewhere; strong reads cost more in latency and often in price, so paying for them everywhere is waste, and paying for them nowhere is a correctness bug.

Polyglot Persistence — And Its Bill

Different patterns genuinely suit different stores: Postgres as the system of record, Redis for sessions, Elasticsearch for search, a time-series store for metrics. Each addition brings a real cost: another failure mode, another backup and restore procedure, another upgrade path, another thing on-call must understand at 3am, and — hardest — synchronisation between stores, which is a distributed systems problem in its own right.

The workable pattern is one system of record plus derived stores fed by change data capture or an outbox, where every derived store can be rebuilt from the record. If two stores are both authoritative for overlapping data, you have signed up for reconciling them forever.

Common Anti-Patterns

❌ Choosing the store before writing the access patterns. The design is then fitted to the product rather than the problem, and the missing pattern surfaces in production. ✅ Enumerate patterns with shape, rate, and latency budget first; let them eliminate candidates.

❌ "We need NoSQL for scale" with no measured throughput, data size, or profile of the slow queries. ✅ Measure first. Below a few TB of OLTP, tune the relational database — an index usually beats a migration.

❌ Using a document store as a relational database. Application code fetches documents in a loop and joins them in memory; ten reads where one join belonged. ✅ Either embed the aggregate so one read serves the query, or use a database with joins.

❌ Modelling entities instead of queries in a wide-column store. One item type per table, then a scan for every read. ✅ Design the partition and sort keys from the queries; duplicate data to serve reads.

❌ Single-table design on a domain still in flux. Access patterns change weekly; every change is a migration. ✅ Adopt it when patterns are stable and read efficiency matters. Until then, prefer a model you can re-query cheaply.

❌ A partition key with heavy skew. One tenant, or one popular entity, takes most of the traffic and throttles regardless of provisioned capacity. ✅ Choose a high-cardinality key, or shard the hot key with a suffix and scatter-gather.

❌ Unbounded partitions. All events under EVENTS#ALL, growing forever. ✅ Bound partitions by time or tenant so each stays queryable.

❌ Analytics against the operational store. Aggregation queries scan the table and starve the latency-critical path. ✅ Stream to a warehouse or column store; model it dimensionally.

❌ Eventual consistency on a correctness-critical path — balances, stock, entitlements — with read-then-write update logic. ✅ Atomic or conditional writes, and strong reads where invariants depend on it.

❌ A graph database for one-hop relationships. Foreign keys and a join already do this, faster and with better tooling. ✅ Reserve graph stores for variable-depth traversal.

❌ Polyglot persistence adopted store-by-store with no ownership plan. Six databases, one on-call engineer, no rebuild path. ✅ One system of record; derived stores rebuildable from it via CDC or an outbox.

❌ Believing schemaless means no schema. The schema moves into application code, undeclared, and every reader implements a different version of it. ✅ Enforce a schema at the boundary — validation, versioned documents, a migration strategy for old shapes.

NoSQL Selection Checklist

• Every access pattern written down with shape, rate, and latency budget
• Consistency requirement stated per pattern, not globally
• Analytical and full-text patterns routed away from the operational store
• Current data size and write rate measured, not estimated
• Relational option evaluated honestly, including tuning and indexing
• Candidates eliminated by what they cannot do before comparing what they can
• Partition key chosen for even distribution; skew analysed against real data
• Partitions bounded in growth
• Sort key encodes the ordering the read patterns actually need
• Secondary indexes justified by a named access pattern, and their cost known
• Duplication and its write-time synchronisation cost accounted for
• Correctness-critical paths use atomic or conditional writes, not read-then-write
• Read-your-writes handled for anything a user edits and immediately views
• Cross-entity invariants have an enforcement mechanism
• Hot-key and throttling behaviour tested under realistic skew
• Migration path and exit cost understood before committing
• Derived stores rebuildable from a single system of record
• Schema validation enforced somewhere explicit, even if the store is schemaless
• Backup, restore, and point-in-time recovery tested, not assumed