跳至正文
OpenAgentSkill
浏览已开放 · 目录记录日期 2026-11-21 · 自动更新和账户服务暂时关闭。
设计与 UI

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_writes appropriately)
  • 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
阅读完整来源 ↗