nosql-expertExpert guidance for distributed NoSQL databases (Cassandra, DynamoDB, ScyllaDB). Covers query-first modeling, partition key design, hot partition prevention, single-table design, and BASE vs ACID tradeoffs. Triggers on: 'Cassandra', 'DynamoDB', 'NoSQL', 'partition key', 'hot partition', 'single-table design', 'GSI', 'LSI', 'WCU', 'RCU', 'ScyllaDB'.
Independent GitHub snapshot · Automated classification · Original documentation governs
NEVER DESIGN A NOSQL SCHEMA WITHOUT LISTING ALL ACCESS PATTERNS FIRST — A TABLE DESIGNED FOR THE WRONG QUERIES REQUIRES A FULL MIGRATION
Load this skill when:
| Feature | SQL (Relational) | Distributed NoSQL (Cassandra/DynamoDB) |
|---|---|---|
| Data modeling | Model Entities + Relationships | Model Queries (Access Patterns) |
| Joins | CPU-intensive, resolved at read time | Pre-computed (denormalized) at write time |
| Storage cost | Expensive — minimize duplication | Cheap — duplicate data for read speed |
| Consistency | ACID (Strong) | BASE (Eventual) / Tunable |
| Scalability | Vertical (bigger machine) | Horizontal (more nodes/shards) |
| Schema changes | ALTER TABLE, migrations | Often requires new table or full migration |
| Aggregates | COUNT, SUM, GROUP BY | Pre-calculate in separate counter tables |
The golden rule: In relational design, you model entities. In distributed NoSQL, you model the queries your application will execute.
Before touching a schema tool, list every access pattern:
Access Patterns:
1. Get user by user_id
2. Get all orders for a user, sorted by created_at DESC
3. Get all orders in status PENDING for a given tenant
4. Get order by order_id
Each row in that list becomes either a primary key structure or an index. If a pattern has no corresponding key or index, it cannot be served efficiently — fix the schema before writing code.
The partition key determines which node holds the data. Bad cardinality causes hot partitions.
-- BAD: status has 3-5 values — most traffic lands on one partition
PRIMARY KEY (status, created_at)
-- GOOD: user_id has millions of unique values — traffic spreads evenly
PRIMARY KEY (user_id, created_at)
-- GOOD: bucket sharding when one natural key is too large
-- Split USER#123 data across date buckets
PRIMARY KEY ((user_id, bucket), created_at) -- bucket = YYYY-MM
Cardinality target: the partition key should have enough unique values that no single value represents more than 1-2% of total traffic.
Clustering columns define the sort order within a partition. Design them to match your range query direction.
-- Cassandra: retrieve a user's messages, newest first
CREATE TABLE messages_by_user (
user_id UUID,
sent_at TIMESTAMP,
message_id UUID,
body TEXT,
PRIMARY KEY ((user_id), sent_at, message_id)
) WITH CLUSTERING ORDER BY (sent_at DESC, message_id ASC);
-- Query maps directly to the key structure — no ALLOW FILTERING needed
SELECT * FROM messages_by_user
WHERE user_id = ? AND sent_at > ?
LIMIT 20;
In distributed NoSQL, writes are cheap. Storage is cheap. Joins are impossible at scale. Duplicate data across tables so every read is a single-partition lookup.
-- Relational: one Author table, one Book table, JOIN at read time
-- Distributed NoSQL: store author data inside each book row
-- Table 1: books_by_author — primary lookup path
books_by_author: PK=(author_id), CK=(published_at, book_id)
-> stores: title, genre, author_name, author_bio_short
-- Table 2: books_by_genre — secondary lookup path
books_by_genre: PK=(genre), CK=(published_at, book_id)
-> stores: title, author_id, author_name (duplicated)
Rule: when author_name changes, update both tables in the same application transaction. Accept that brief inconsistency is tolerable (BASE).
Store multiple entity types and their relationships in one DynamoDB table using composite key prefixes. This minimizes WCU/RCU consumption and enables single-request relationship fetches.
Table: AppData
PK (partition key) SK (sort key) Attributes
------------------ --------------- --------------------
USER#123 USER#123 name, email, created_at
USER#123 ORDER#2024-001 total, status, items
USER#123 ORDER#2024-002 total, status, items
ORDER#2024-001 ORDER#2024-001 total, status, user_id
PRODUCT#SKU-99 PRODUCT#SKU-99 name, price, stock
-- Fetch user + all their orders in ONE query (same partition)
Query: PK = "USER#123", SK begins_with "ORDER#"
PRIMARY KEY ((partition_key_columns), clustering_columns)
-- ^--- always required ^--- optional, define sort order
((col1, col2), ...).Cassandra uses an LSM (Log-Structured Merge) tree. Writes are sequential appends to a commit log and memtable — no random I/O. This makes write-heavy workloads (IoT, event streams, time-series) natural fits.
Deletes write a tombstone marker. Tombstones accumulate until compaction. High-velocity delete patterns (e.g., deleting individual items from a feed) cause tombstone buildup that degrades read performance.
Alternatives:
WITH TTL 86400) for time-bounded data — Cassandra expires rows without tombstonesstatus = 'deleted') instead of physical deletes-- This compiles but scans every partition in the cluster
SELECT * FROM orders WHERE status = 'PENDING' ALLOW FILTERING;
-- Fix: create a table keyed by the query pattern
CREATE TABLE orders_by_status (
status TEXT,
created_at TIMESTAMP,
order_id UUID,
PRIMARY KEY ((status), created_at, order_id)
);
If you see ALLOW FILTERING in production queries, the schema is wrong for that access pattern.
-- FORBIDDEN in Cassandra at scale:
SELECT COUNT(*) FROM large_table; -- full cluster scan
SELECT * FROM a JOIN b ON ...; -- not supported
-- CORRECT: pre-compute counters in a dedicated counter table
CREATE TABLE order_counts_by_user (
user_id UUID PRIMARY KEY,
total COUNTER
);
UPDATE order_counts_by_user SET total = total + 1 WHERE user_id = ?;
| Mode | When to Use | Cost Model |
|---|---|---|
| On-Demand | Unpredictable or spiky traffic | Pay per request (WCU/RCU consumed) |
| Provisioned | Steady, predictable traffic | Pay per provisioned WCU/RCU (cheaper at scale) |
Single-table design reduces the number of WCU/RCU consumed because multiple entity types share one table — fewer round trips per request.
GSI (Global Secondary Index):
LSI (Local Secondary Index):
-- Example: orders table
Base table: PK=user_id, SK=order_id -- get order by id
GSI-1: PK=user_id, SK=created_at -- get user's orders by date
GSI-2: PK=status, SK=created_at -- ops dashboard: pending orders by date
LSI-1: PK=user_id, SK=total -- get user's orders by value (within partition)
DynamoDB TTL deletes expired items without consuming WCUs and without producing Cassandra-style tombstones.
-- Set TTL attribute on item (epoch timestamp)
expires_at: 1767225600 -- items deleted automatically after this time
-- Enable TTL on the table (attribute name must match)
aws dynamodb update-time-to-live \
--table-name Sessions \
--time-to-live-specification "Enabled=true, AttributeName=expires_at"
Use TTL for: sessions, OTP codes, cache entries, soft-deleted records.
Run through every item before handing off a schema for review or implementation.
USER#123#2024-01.Querying all partitions to find matching items — equivalent to a full table scan.
-- DynamoDB: never do this in production
aws dynamodb scan --table-name Orders --filter-expression "status = :s"
-- Fix: add a GSI with status as partition key
All traffic for a time period or popular value routes to one partition.
-- BAD: day_of_week has 7 values — Monday gets crushed
PRIMARY KEY ((day_of_week), event_time, event_id)
-- GOOD: shard by (user_id % N) or use a high-cardinality natural key
PRIMARY KEY ((user_id), event_time, event_id)
Creating normalized entity tables (Users, Orders, Products) and then trying to join them at the application layer.
-- This pattern reconstructs relational joins in application code:
users = cassandra.execute("SELECT * FROM users WHERE id = ?")
orders = cassandra.execute("SELECT * FROM orders WHERE user_id = ?")
result = merge(users, orders) # application-side join — latency doubles, errors multiply
-- Fix: denormalize. Store the user fields you need inside the orders table.
Storing all records for a high-traffic entity under one partition key without time-bucketing.
-- BAD: all events for a popular user in one partition — grows without bound
PRIMARY KEY ((user_id), event_time, event_id)
-- GOOD: bucket by month — each partition stays bounded
PRIMARY KEY ((user_id, month), event_time, event_id)
-- month = '2024-03'
Silencing a Cassandra query error with ALLOW FILTERING instead of fixing the schema. See the Cassandra Specifics section above.