高效Cassandra数据库设计：1分钟OHLCV金融时间序列数据检索

Awesome use case—time-series with automatic TTL and bulk range queries is exactly where Cassandra shines when configured right. Let’s walk through the setup step by step, tailored to your 1000 symbols, 1-minute OHLCV data, and 24-hour retention requirement.

Schema Design: Optimized for Your Core Query #

First, we need a table built around your primary query: retrieve all symbols' data for a specific time slice. Cassandra is query-first, so we’ll structure the schema to avoid full-table scans and leverage partition locality.

Here’s the recommended table definition:

CREATE TABLE ohlcv_minute (
    time_bucket timestamp,  -- Partition key: groups data by hour (matches compaction window)
    symbol text,            -- Clustering key 1: sorts symbols within the bucket
    timestamp timestamp,    -- Clustering key 2: sorts data chronologically per symbol
    open double,
    high double,
    low double,
    close double,
    volume bigint,
    PRIMARY KEY ((time_bucket), symbol, timestamp)
) WITH CLUSTERING ORDER BY (symbol ASC, timestamp ASC)
  AND compaction = {
      'class': 'TimeWindowCompactionStrategy',
      'compaction_window_size': 1,
      'compaction_window_unit': 'HOURS'
  }
  AND default_time_to_live = 86400;  -- Auto-delete data after 24 hours (86400 seconds)

Why this works: #

• time_bucket as partition key: Groups all 1000 symbols' data for a given hour into one partition. This means when you query a time range (e.g., 10:00-11:30), you only scan 2 partitions (10:00 and 11:00 buckets) instead of 1000 symbol-specific partitions.
• Clustering keys: Ensures data is sorted first by symbol, then by timestamp—making it trivial to iterate through results for your time slice.
• TTL: default_time_to_live automatically marks data for deletion 24 hours after insertion. Since your streaming data’s timestamp aligns closely with insertion time, this perfectly matches your retention rule.

Compaction Strategy: Keep Performance Snappy #

We’re using TimeWindowCompactionStrategy (TWCS) here because it’s purpose-built for time-series data:

• It groups SSTables by time windows (1 hour, matching our time_bucket).
• Once a window expires (after 24 hours), Cassandra automatically drops the entire SSTable—no messy manual cleanup or tombstone overhead.
• Merges only relevant time windows during compaction, keeping read/write performance consistent even as data ages.

Real-Time Streaming Write Optimization #

To handle your 1000 symbols/minute stream efficiently:

• Batch within the same partition: Since all symbols in a minute belong to the same time_bucket, batch their writes together. Use Cassandra’s BATCH statement (limit batches to ~100 entries to avoid overhead) or your streaming framework’s (Spark/Flink) Cassandra sink with batch enabled.
• Use async writes: Leverage your driver’s asynchronous API to avoid blocking on each write—this drastically improves throughput for high-volume streams.
• Tune consistency level: For real-time data, LOCAL_ONE is usually sufficient (you don’t need strict consistency for streaming OHLCV). This reduces coordination overhead between nodes.

Efficient Range Querying Example #

To fetch all symbols' data between 2024-05-20 10:00:00 and 2024-05-20 11:30:00, use this query:

SELECT * FROM ohlcv_minute
WHERE time_bucket IN ('2024-05-20 10:00:00', '2024-05-20 11:00:00')
AND timestamp >= '2024-05-20 10:00:00'
AND timestamp <= '2024-05-20 11:30:00';

Why this is fast: #

• Cassandra immediately targets the two relevant partitions (10:00 and 11:00 buckets) instead of scanning the entire table.
• The clustering order ensures results are returned sorted by symbol and timestamp, so you don’t need to reprocess data client-side.

Quick Additional Tips #

• Data precision: If you need exact financial calculations, replace double with decimal for OHLC values to avoid floating-point errors.
• Monitor partition size: Each hourly partition will hold ~60,000 entries (1000 symbols × 60 minutes), which is well under Cassandra’s recommended 100MB partition limit.
• Avoid secondary indexes: You don’t need them here—your primary query is fully covered by the partition and clustering keys.

内容的提问来源于stack exchange，提问作者jamesrogers93