scylladb-data-modeling

ScyllaDB Data Modeling

CQL data modeling patterns and anti-patterns for ScyllaDB. Bad schema is the root cause of most ScyllaDB performance issues — no amount of cluster scaling can fix a fundamentally wrong data model.

When to Apply

Reference these guidelines when:

• Designing a new ScyllaDB schema from scratch
• Migrating from SQL/relational databases, MongoDB, or Cassandra to ScyllaDB
• Reviewing existing table designs for performance issues
• Troubleshooting slow queries, timeouts, or hot nodes
• Deciding how to structure primary keys (partition key + clustering columns)
• Modeling time-series, IoT, or event data
• Seeing large partition warnings in logs
• Encountering ALLOW FILTERING in queries or code reviews
• Adding secondary indexes or materialized views

Key Principle

"Start from the queries, not from the entities."

This is ScyllaDB's core data modeling philosophy. Unlike relational databases where you normalize entities and then write queries against them, in ScyllaDB you:

1. List your application's queries — every SELECT, UPDATE, and DELETE your application will run
2. Design one table per query pattern — each table's primary key is crafted to serve a specific query efficiently
3. Accept denormalization — the same data may exist in multiple tables, each optimized for a different access pattern

In ScyllaDB, the partition key determines which node holds the data and the clustering columns determine the sort order within that partition. The primary key IS your access pattern.

Quick Reference

1. Anti-Patterns — 5 rules

• antipattern-allow-filtering — ALLOW FILTERING forces a full-scan. Consult this reference whenever you see it in a query, or when a query does not include the full partition key.
• antipattern-large-partitions — Partitions that grow without bounds cause memory pressure, slow reads, and compaction issues. Consult when designing time-series tables or any table where rows accumulate per partition key.
• antipattern-hot-partitions — Uneven partition key distribution causes some nodes/shards to be overloaded while others sit idle. Consult when choosing partition keys for high-write workloads.
• antipattern-unpaged-queries — Unpaged SELECTs cause unbounded memory growth and cluster instability. Consult when a query may return more rows than a single page, or when reviewing code that calls raw/unpaged execution methods.
• antipattern-multi-partition-batch — BATCH statements spanning multiple partitions provide no real atomicity benefit and create coordinator hotspots. Consult when reviewing code that uses BATCH to simulate a transaction, or when migrating SQL transaction logic to CQL.