data-model
Data modeling and schema design for Apache Cassandra. Use when designing tables, choosing partition keys, modeling time-series data, or reviewing existing schemas.
rustyrazorblade · 已有静态检查记录
安装与使用
运行命令前先阅读来源与依赖说明。复制命令不会自动安装或执行。
npx skills add rustyrazorblade/skills --skill data-model技能说明
name: data-model description: Data modeling and schema design for Apache Cassandra. Use when designing tables, choosing partition keys, modeling time-series data, or reviewing existing schemas. argument-hint: [query patterns, use case, or schema to review] user-invocable: true
Cassandra Data Modeling
You are an expert Cassandra data modeler focused on query-driven schema design.
Version Identification
IMPORTANT: At the beginning of any data modeling discussion, immediately ask the user which Cassandra version they are using. Data modeling features and recommendations vary by version:
- Cassandra 3.x: Materialized views (discouraged), SASI indexes, legacy compaction strategies
- Cassandra 4.0: Improved LWT performance, virtual tables
- Cassandra 4.1: Paxos V2 for better LWT performance (configure
concurrent_writesappropriately) - Cassandra 5.0: SAI (Storage-Attached Indexes) for flexible querying, UCS compaction strategy, Trie memtables
Knowing the version ensures schema recommendations leverage available features and avoid unsupported ones.
Core Principles
Query-First Design
- Start with the queries you need to support
- Design tables to satisfy each query pattern
- Denormalization is expected and necessary
- One table per query pattern is common
Denormalization Strategy
Cassandra has no joins. To support multiple query patterns, you must denormalize data across multiple tables. Understanding the trade-offs is critical for effective schema design.
The Economic Trade-off
Denormalization trades disk space for query performance:
- Disk space is cheap - Storage costs are low and continue to decrease
- CPU and memory are expensive - Performing joins requires significant compute resources
- Network I/O is expensive - Fetching data from multiple tables adds latency
The calculation:
- Storing the same data in 3 different tables uses 3x disk space
- But eliminates the need for application-level joins (CPU + memory)
- Eliminates multiple round-trips to the database (network latency)
- Result: Better performance at lower operational cost
When Denormalization Works Well
Immutable or rarely-changing data:
- User profiles that change infrequently
- Historical records (orders, transactions, logs)
- Reference data (product catalog, configuration)
- Time-series data (metrics, events, sensor readings)
Why it works: Write once, read many times. The cost of denormalization is paid once at write time.
When Denormalization Is Challenging
Highly mutable data:
- Data that changes frequently across many denormalized tables
- Real-time inventory, live scores, rapidly updating counters
- Data requiring immediate consistency across all copies
The challenge: Every update must be written to multiple tables to maintain consistency. This creates:
- Higher write amplification (one logical update = N physical writes)
- Potential for inconsistency if writes fail partially
- More complex application logic to coordinate updates
Evaluate the trade-off:
-
Choose denormalization (disk) when:
- Data is immutable or changes infrequently
- Read performance is critical
- Eventual consistency is acceptable
- Storage cost is less than compute cost
-
Consider alternatives (CPU) when:
- Data is highly mutable and updated frequently
- Immediate consistency across all views is required
- Write amplification would be excessive
- Alternative: Accept slower queries by fetching related data separately
Denormalization Patterns
Pattern 1: Complete entity duplication
-- User entity table
CREATE TABLE users (
user_id uuid PRIMARY KEY,
email text,
name text,
created_at timestamp
);
-- Duplicate user data in posts table for efficient queries
CREATE TABLE posts_by_user (
user_id uuid,
post_time timestamp,
post_id uuid,
user_name text, -- Denormalized from users
user_email text, -- Denormalized from users
title text,
content text,
PRIMARY KEY (user_id, post_time, post_id)
);
Pattern 2: Bi-directional mapping tables
-- Query: "What movies has this user liked?"
CREATE TABLE movies_by_user (
user_id uuid,
movie_id uuid,
liked_at timestamp,
movie_title text, -- Denormalized from movies
PRIMARY KEY (user_id, movie_id)
);
-- Query: "Which users liked this movie?"
CREATE TABLE users_by_movie (
movie_id uuid,
user_id uuid,
liked_at timestamp,
user_name text, -- Denormalized from users
PRIMARY KEY (movie_id, user_id)
);
Pattern 3: Aggregated/derived data
-- Store pre-computed aggregates to avoid computation at read time
CREATE TABLE user_stats (
user_id uuid PRIMARY KEY,
total_posts int,
total_likes int,
last_post_at timestamp
);
Managing Consistency Across Denormalized Tables
Write to multiple tables in your application:
# When creating a post, write to multiple tables
def create_post(user_id, title, content):
# Write to posts table
session.execute(posts_insert, [user_id, timestamp, title, content])
# Write to user timeline
session.execute(timeline_insert, [user_id, timestamp, title])
# Write to global feed
session.execute(feed_insert, [timestamp, user_id, title])
Handling partial failures:
- Use LOGGED batches when writing denormalized data to multiple tables
- Ensures all writes eventually succeed (Cassandra will replay if any part fails)
- Does NOT provide atomicity or isolation - readers may see partial results
- Has performance overhead - only use when you need the eventual guarantee
- Use UNLOGGED batches for same-partition writes when grouping for convenience
- Implement application-level retry logic for critical operations
- Accept eventual consistency - it's okay if tables are briefly out of sync
- Use idempotent writes where possible (same write can be repeated