Database Capacity Planning and Scaling with ScyllaDB

Planning database capacity is notoriously difficult; this article explains why and proposes a systematic approach using the open‑source NoSQL database ScyllaDB.

The process consists of five steps: assume usage patterns, estimate workload, decide high‑level configuration, feed these into a performance model, and calculate required resources.

Key workload questions include peak throughput vs. average, read/write separation, dataset size, hot partitions, data model impact, and service‑level objectives.

ScyllaDB’s CQL queries are broken down into basic operations; examples show how primary‑key lookups, secondary‑index queries, and full‑table scans differ in the number of underlying reads and writes.

Example CQL queries:

SELECT * FROM user_stats WHERE id=UUID;

SELECT * FROM user_stats WHERE username=USERNAME;

SELECT * FROM user_stats WHERE city='New York' ALLOW FILTERING;

UPDATE user_stats SET username=USERNAME, rank=231, score=3432 WHERE id=UUID;

UPDATE user_stats SET username=USERNAME, rand=231, score=3432 WHERE id=UUID IF EXISTS;

Consistency levels affect the number of replica reads/writes, and lightweight transactions (LWT) introduce additional Paxos‑based coordination costs.

Maintenance tasks such as SSTable compaction, memtable flushing, and handling of materialized views, secondary indexes, and CDC must be accounted for in capacity calculations.

When scaling, choosing between more nodes or larger nodes involves trade‑offs between efficiency and fault tolerance; a typical small‑to‑medium workload benefits from three medium‑sized nodes, with six‑to‑nine nodes recommended for high reliability.

Ultimately, capacity planning is an iterative process: initial estimates guide deployment, real‑world metrics refine the model, and ongoing monitoring ensures the cluster remains within performance and cost targets.