NoSQL & data modeling

#Why NoSQL exists and its main families

Some workloads need predictable distributed access at scale, flexible aggregates or graph traversal rather than general joins. NoSQL describes several families, not one product or a guarantee of schema-free design and superior speed.

Families overlap. pgvector is a relational PostgreSQL extension, so vector search does not require a separate NoSQL store. Cassandra's wide-column model differs from analytical columnstores. Start with queries, consistency and operations rather than product fashion.

#Model by access patterns: orders and customers

If a screen always displays an order and its lines, one document is convenient. Embedding every order inside the customer creates unbounded growth and contention.

{
  "id": "order-42",
  "tenantId": "store-7",
  "customerId": "customer-9",
  "customerSnapshot": { "name": "Ali", "shippingCity": "Cairo" },
  "status": "pending",
  "lines": [
    { "productId": "product-3", "nameAtSale": "Mug", "qty": 2,
      "unitPriceMinor": 12000, "currency": "EGP" }
  ],
  "schemaVersion": 1
}

1. List access patterns: order point lookup, time-ordered customer history and status changes. Design partition keys and indexes for them.
2. Embed lines because they are bounded, order-owned and commonly accessed together. Reference products and customers with independent lifetimes.
3. Snapshot purchase-time prices, names and shipping details; read current customer data separately when needed.
4. If current facts are copied, define their owner, propagation mechanism and acceptable staleness.

Money uses minor units to avoid floating-point ambiguity; currency scales differ, so define the policy. Use schemaVersion, validation and readers that tolerate older documents. Flexible schema still requires schema design. Review product-specific document, array and contention limits.

#Partition keys, sharding and hot partitions

One machine has finite storage and throughput. Sharding distributes data across nodes; the partition key routes reads and writes. A poor key can make a large cluster behave like one overloaded machine.

Access pattern                  Candidate key           Risk
Orders for one ordinary tenant  tenantId                One huge tenant becomes hot
Order point lookups             orderId                 Customer history fans out
Customer history                tenantId + customerId   One celebrity customer can be hot
Write-heavy event archive       tenantId + time bucket  Active bucket can still be hot
Extreme write skew              key + hash bucket       Reads must query several buckets

High cardinality helps but is insufficient: a million keys can still send most traffic to one value. Increasing range keys can concentrate writes on the newest shard. Hashing spreads writes but sacrifices range locality. Compound or hierarchical keys can balance needs where supported.

1. Measure the busiest partition, not only averages, including RU, CPU and throttling.
2. Check routing: a query without its partition key may require scatter-gather.
3. Review transaction boundaries, uniqueness scope and resharding before choosing.
4. If splitting tenants into buckets, design routing, result merging and migration.

#Replication and quorum reads and writes

A single copy is a failure point. Replication improves availability and read locality, but coordinating replicas and ordering writes has costs.

N = 3 replicas: A, B, C
W = 2: a successful write is acknowledged by A and B
R = 2: a read waits for responses from B and C
R + W = 4 > N: the read and write sets intersect at B
But: concurrent writes, version choice and failed writes still need rules.

R is the required read response count, W the write acknowledgement count, and N the replication factor. R + W > N guarantees intersection for quorums over the same fixed replica set. It does not alone guarantee linearizability. Version ordering, concurrent and partial writes, and membership changes still matter. Sloppy quorums may use fallback replicas and lose the expected intersection. Write-quorum intersection itself requires 2W > N when needed; consensus involves more than this arithmetic.

W=1 reduces write waiting but can lose acknowledged data if failure precedes replication. Increasing R or W raises coordination and reduces tolerated failures for that operation. Never infer undocumented guarantees from a tuning setting alone.

#CAP and PACELC without slogans

If the network separates replica groups, should both continue serving operations or should one wait? CAP describes a trade-off during a partition, not a permanent menu of two features.

During a partition:
  C: preserve one-copy linearizable behavior, possibly reject or delay requests
  A: every request to a non-failing node completes, possibly with stale results
PACELC:
  If Partition: Availability versus Consistency
  Else:        Latency versus Consistency

C means linearizability, not ACID consistency as valid constraints. A is formal availability for every request to a non-failing node, not ordinary uptime percentage. Network partitions must be handled rather than selecting away P. One product may make different choices by operation or configuration.

PACELC also reminds us that even healthy networks impose latency when waiting for distant replicas. Avoid labeling every feature of a product AP or CP without naming scope and configuration.

#Consistency levels through Cosmos DB

A customer updates an address and immediately reads an older value. Saying eventual alone does not define the user experience. Choose a precise guarantee and scope.

This uses Cosmos DB for NoSQL terminology; check other API mappings and partition and transaction scope. Propagate session tokens when a user moves between application instances. A reused client helps token management but does not cover every distributed flow. The account default normally bounds request-level consistency; requesting strong on an eventual account is not a magical upgrade.

#BASE, ACID and NoSQL transactions

A nonrelational model does not prohibit transactions. Scope and cost are the key differences. BASE commonly means Basically Available, Soft state and Eventual consistency; it is not a precise specification of every behavior.

// MongoDB.Driver; reuse client. Replica set or supported sharded deployment required.
using var session = await client.StartSessionAsync(cancellationToken: ct);
await session.WithTransactionAsync(async (s, token) =>    // (1)
{
    var filter = Builders<StockDocument>.Filter.And(
        Builders<StockDocument>.Filter.Eq(p => p.Id, productId),
        Builders<StockDocument>.Filter.Gte(p => p.Available, quantity));
    var update = Builders<StockDocument>.Update.Inc(p => p.Available, -quantity);
    var result = await stock.UpdateOneAsync(s, filter, update,
        cancellationToken: token);                        // (2)
    if (result.ModifiedCount != 1)
        throw new InvalidOperationException("Insufficient stock");
    await orders.InsertOneAsync(s, order, cancellationToken: token);
    return order.Id;                                      // (3)
}, cancellationToken: ct);

ACID transactions can coexist with eventual replica reads. Money and stock usually need conditional writes, version checks or explicit transactions rather than last-write-wins. Never assume every NoSQL transaction has identical relational isolation semantics.

#MongoDB indexing and query plans

A document model does not eliminate collection scans. Index actual filters and sorting rather than letting flexible schemas hide a bottleneck.

using MongoDB.Driver;

var keys = Builders<OrderDocument>.IndexKeys
    .Ascending(o => o.TenantId)
    .Ascending(o => o.CustomerId)
    .Descending(o => o.CreatedAt);                        // (1)
await orders.Indexes.CreateOneAsync(
    new CreateIndexModel<OrderDocument>(keys),
    cancellationToken: ct);                              // (2)

MongoDB supports compound, unique, multikey array, partial and TTL indexes. Multikey indexes have compound-array and coverage restrictions; do not assume relational equivalence. TTL removal is asynchronous, not an exact expiry deadline, so query or authorization logic must still check validity. Unique indexes on sharded collections have shard-key restrictions. Every index adds storage, memory and write costs.

#Redis data structures beyond caching

Some workloads need membership, ranking or an event log rather than JSON under a key. Redis provides operations that avoid application-level read-modify-write.

HSET cart:{customer-9} product-3 2
SADD product:3:interested customer-9
ZINCRBY products:popular 1 product-3
XADD orders:events * orderId order-42 status paid
XGROUP CREATE orders:events fulfilment 0 MKSTREAM
XREADGROUP GROUP fulfilment worker-1 COUNT 10 STREAMS orders:events >
# After durable processing: XACK orders:events fulfilment <message-id>

These are illustrative redis-cli commands; create the group once and handle an existing group. Stream consumers must recover abandoned pending messages and process idempotently after crashes. Pub/Sub differs because disconnected subscribers have no stored backlog. Lua or transactions can group server operations atomically under cluster key constraints; Redis does not provide relational rollback for every runtime command error. Persistence, replication and failover configuration determine durability.

#Using MongoDB and Cosmos DB from .NET

Use native SDKs for explicit control over database operations, or an EF provider when its abstraction and translation limits fit. Switching providers does not make a document database relational.

// MongoDB.Driver; register and reuse MongoClient for its connection pools.
using MongoDB.Bson.Serialization.Attributes;
using MongoDB.Driver;

var client = new MongoClient(mongoConnectionString);
var orders = client.GetDatabase("store")
    .GetCollection<OrderDocument>("orders");
var filter = Builders<OrderDocument>.Filter.And(
    Builders<OrderDocument>.Filter.Eq(o => o.TenantId, tenantId),
    Builders<OrderDocument>.Filter.Eq(o => o.Id, orderId)); // (1)
var order = await orders.Find(filter).FirstOrDefaultAsync(ct);

public sealed class OrderDocument
{
    [BsonId] public string Id { get; set; } = "";
    public string TenantId { get; set; } = "";
    public string CustomerId { get; set; } = "";
    public DateTime CreatedAt { get; set; }
}

// Microsoft.Azure.Cosmos; reuse CosmosClient. Existing /tenantId container assumed.
using Microsoft.Azure.Cosmos;

var cosmos = new CosmosClient(cosmosConnectionString);
var container = cosmos.GetContainer("store", "orders");
try
{
    var response = await container.ReadItemAsync<CosmosOrder>(
        id: orderId, partitionKey: new PartitionKey(tenantId),
        cancellationToken: ct);                          // (2)
    Console.WriteLine(response.RequestCharge);
}
catch (CosmosException ex) when (ex.StatusCode == System.Net.HttpStatusCode.NotFound)
{
    Console.WriteLine("Order not found in the authorized partition");
}
public sealed record CosmosOrder(string id, string tenantId, string status);

Use secret storage or managed identity where supported rather than source credentials. These snippets assume provisioned databases and collections and .NET 10 with compatible packages. Reuse clients instead of creating them per request.

#Polyglot persistence and SQL versus NoSQL decisions

Different stores can improve particular workloads while multiplying backups, monitoring, skills and consistency work. Polyglot persistence is a cost decision, not a goal itself.

-- PostgreSQL: stable relational facts plus variable product attributes.
CREATE TABLE products (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    price numeric(18,2) NOT NULL CHECK (price >= 0),
    attributes jsonb NOT NULL DEFAULT '{}'::jsonb         -- (1)
);
CREATE INDEX ix_products_attributes ON products USING gin (attributes);
SELECT id, name FROM products
WHERE attributes @> '{"color":"blue"}'::jsonb;           -- (2)

JSONB does not automatically shard data, and large updates and indexing still cost work. When SQL owns truth and search is derived, use an outbox or CDC, idempotent consumers and reconciliation rather than naïve dual writes. Support index rebuilds, deletes and schema versions. Search results must honor authorization even if the index is stale.