scylladb

ScyllaDB

Model for the queries. Partition key defines distribution and hotspots; clustering defines order within the partition. ScyllaDB is not a relational SQL with joins.

Stack and scope

Item	Role
CQL	DDL/DML
Keyspace	RF, replication strategy
Table	Partition key + clustering + payload
Materialized views / extra tables	Alternative access patterns

Go integration: use golang-scylladb. This skill covers data modeling and operations independent of language.

Non-negotiable rules

• Design the table from the access pattern (query-first), not from the ER diagram.
• Every production query must restrict the partition key (and clustering in prefix order).
• Avoid ALLOW FILTERING on hot paths.
• Denormalize and duplicate writes when you need multiple read patterns.
• Conscious consistency level: latency vs. durability/visibility trade-off.
• TTLs and deletes generate tombstones — plan compaction and partition size.

Modeling

CREATE KEYSPACE IF NOT EXISTS app
WITH replication = {'class': 'NetworkTopologyStrategy', 'dc1': 3};

CREATE TABLE app.messages_by_room (
  room_id   uuid,
  sent_at   timeuuid,
  message_id uuid,
  author_id uuid,
  body      text,
  PRIMARY KEY ((room_id), sent_at, message_id)
) WITH CLUSTERING ORDER BY (sent_at DESC, message_id ASC);

• Partition key ((room_id)): colocates data read together.
• Clustering (sent_at, message_id): ordering and pagination within the partition.
• A new access pattern → a new table (or MV only if the trade-off is accepted).
• IDs: uuid / timeuuid as needed for temporal ordering.

Partition size and hotspots

• Overly large partitions degrade latency and compaction; break by time bucket (day, hour) when the partition grows without bound.
• Hot partition: one key receiving most of the traffic — redistribute (bucketing, logical sharding).
• Prefer many moderate partitions over a few giant ones.

Consistency and writes

Typical level	Use
LOCAL_QUORUM	Common default in single-DC multi-node / NTS
LOCAL_ONE	Faster read/write, less guarantee
EACH_QUORUM / ALL	Rare; worse latency and availability

• Lightweight transactions (IF NOT EXISTS / conditional updates) only when correctness requires it — high cost.
• Idempotency: design reapplicable writes when there are retries.
• Counters: special type with limitations; avoid if an append/rollup model would serve better.

Reads and pagination

• SELECT with equality on the partition key; ranges only on clustering columns in prefix order.
• Pagination with the driver's paging state / LIMIT conscious.
• Multi-partition fan-out on the client: limit concurrency and timeout.
• Secondary indexes: use sparingly; often a denormalized table is better.

TTL, deletes, and tombstones

• TTL in columns/rows for automatic expiration.
• Frequent deletes + wide reads = risk of tombstone storms.
• Prefer append + TTL models over intensive update/delete when the domain allows it.
• Monitor tombstones and read latency in the cluster's Ops/Monitoring.

Operations (app-facing)

• Schema changes: compatible and forward-safe; avoid destructive rewrites without a plan.
• RF and DC aligned with the NetworkTopologyStrategy of the actual deployment.
• Client timeouts and retries aligned with the SLA; no infinite retry on non-idempotent writes.
• Capacity: measure p99 under the real access pattern, not just synthetic throughput.

Anti-patterns

• Modeling as 3NF and “discovering joins” in the app
• Queries without a partition key / ALLOW FILTERING on the hot path
• A single giant per-tenant partition without bucketing
• Secondary index for high cardinality / wrong high selectivity
• LWT on every write “just in case”
• Treating Scylla as a queue without tombstone/TTL control

Completion criteria

• Every production query matches a table/access pattern
• Partition and clustering keys justified
• Consistency levels documented per operation
• Partitions with a reasonable bound (or bucketing)
• Tombstones/TTL considered in the design
• No ALLOW FILTERING on critical paths