01Model the invariants first
List entities, relationships, uniqueness constraints, and state transitions. If money, ownership, or inventory must change atomically, relational transactions are a powerful default. Denormalize only for a measured access pattern.
Start with a sentence that must remain true: available inventory never becomes negative, a reservation ID names only one reservation, or a tenant cannot read another tenant's rows. Then list operations and their access patterns. Reserve-by-product, confirm-by-reservation, and list-by-customer require different keys or indexes. A database label such as “document store” does not tell you whether those operations will be correct or efficient.
Imagine two requests reading stock = 1 before either writes. Both decide to decrement, so ordinary read-then-write code can oversell. An atomic conditional statement—decrement WHERE remaining > 0, then inspect the affected-row count—places the decision where concurrency is controlled. Store the reservation and its idempotency record in the same transaction. An appropriate lock or serializable transaction is another option, with conflict handling and retries.
An index is a second maintained access path. An index on customer_id and created_at can make recent-order queries cheap, but every insert now updates more structures and consumes more storage. Explain the query it serves and inspect its plan. A transaction ensures a defined group of changes commits together; its isolation level determines which concurrent observations are possible. “Use transactions” is incomplete without the invariant and statement behavior.
- Indexes accelerate reads but tax writes and storage
- Isolation controls anomalies, not just speed
- Joins are useful; cross-shard joins are the warning
02Partition keys encode your future
A good key spreads throughput and co-locates common operations. Hash keys balance load but weaken range scans. Range keys help scans but invite hotspots. Hybrid keys—tenant plus time bucket, for example—often balance both.
Partitioning assigns each record to an owner. Hashing tenant_id can keep a tenant's queries local, but a single huge tenant may dominate one shard. Hashing product_id spreads products but makes a customer's multi-product order span owners. A time-based key supports retention scans yet can send all current writes to one partition. Choose by listing the critical queries and the expected skew.
Distinguish a partition key from a secondary lookup. If records are owned by tenant_id but a lookup arrives with only reservation_id, you need a directory, an ID that encodes routing, or a fan-out query. That extra step changes latency and availability. Also define how a record moves during rebalancing: readers and writers need a consistent owner version or a forwarding path while placement changes.
Before adding shards, measure the bottleneck. A poor index, oversized transaction, or hot row may dominate even when aggregate storage is small. Sharding does not make one contended inventory row accept concurrent conflicting updates. You may need a reservation queue, per-product ownership, or a different product contract. State which limitation your partition plan solves and which remains.
- Estimate hottest key, not only average key
- Plan rebalancing and key evolution
- Avoid unbounded partitions
03Replication is a consistency decision
Leader/follower simplifies ordered writes but creates lag. Multi-leader improves regional write availability while creating conflicts. Leaderless systems trade coordination for quorum math and repair. State the read guarantee users need.
A replica copies state; the replication protocol determines when a write is acknowledged and what readers can observe. With asynchronous replication, an acknowledged primary write may not yet exist on a follower. A user who creates an order and immediately reads a follower can see “not found.” Read the primary for that session, wait for a replication position, or explicitly permit temporary lag.
Synchronous acknowledgement can reduce some loss windows but spends latency and availability waiting for other nodes. Define the failure model: one machine, one zone, or an entire region. Two copies in the same failed zone do not meet a regional recovery promise. Backups protect against different problems, such as accidental deletion, and require a tested restore path; replicas can faithfully copy that deletion.
When comparing stores, use a small decision table: required invariants, query shapes, growth, acceptable staleness, recovery behavior, and operational familiarity. A relational store is a strong default when constraints and transactions dominate. Other layouts can win for particular access patterns or scale. The defensible answer names those conditions instead of making a universal SQL-versus-NoSQL claim.
- Read-your-writes can be session scoped
- Eventual consistency is incomplete without convergence behavior
- Use outbox + idempotent consumer to connect DB state and events