Why Is Database Capacity Planning So Hard? A Practical Guide Using ScyllaDB

Introduction

Planning the size of a database cluster is far from trivial. Even rough estimates require careful analysis of workload, data model, and operational constraints. The article uses the open‑source NoSQL database ScyllaDB (Cassandra‑compatible) to illustrate a practical capacity‑planning workflow.

Why Capacity Planning Is Difficult

Simple formulas—dividing dataset size and required throughput by node capacity—ignore many hidden factors. Real‑world planning must consider usage patterns, replication, consistency levels, read/write ratios, hot partitions, and maintenance overhead, all of which introduce uncertainty and iterative refinement.

Step‑by‑Step Estimation Process

Make assumptions about usage patterns.

Estimate the required workload (throughput and dataset size).

Decide on high‑level database configuration (replication factor, consistency level, etc.).

Feed workload, configuration, and usage assumptions into a performance model.

Iterate and refine the model based on observed results.

Although conceptually simple, each step involves detailed analysis and trade‑offs.

Key Workload Questions

Is the throughput figure a peak or an average?

Should read‑only and write‑only queries be separated?

How many queries are expected and how large is the dataset?

What are the hot datasets?

How does the data model affect query volume, performance, and storage?

What growth is anticipated?

What are the SLOs for latency?

Answers often require Monte‑Carlo simulations or similar modeling techniques.

Query vs. Operations

CQL queries are broken down into basic read/write operations. For example: SELECT * FROM user_stats WHERE id=UUID This primary‑key lookup translates to a single read operation (or multiple reads for consistency).

SELECT * FROM user_stats WHERE username=USERNAME

This uses a secondary index, resulting in two sub‑queries (index lookup + row fetch) and potentially multiple operations.

SELECT * FROM user_stats WHERE city="New York" ALLOW FILTERING

This forces a full partition scan, leading to unpredictable performance.

UPDATE statements also differ: a simple UPSERT generates one write, while a conditional update with IF EXISTS adds a lightweight transaction (LWT) that reads all replicas before writing.

Consistency Challenges

Distributed databases replicate data across nodes. The replication factor determines how many copies exist. Consistency level 1 waits for a single replica acknowledgment, while ALL requires all replicas to respond, affecting read/write operation counts and latency.

Lightweight transactions (LWT) use the Paxos algorithm, requiring coordination across all replicas and adding extra read/write steps to the performance model.

Materialized Views, Secondary Indexes, and CDC

ScyllaDB automatically maintains secondary indexes, materialized views, and change‑data‑capture (CDC) tables. Each derived write triggers additional writes to these auxiliary tables, consuming extra disk space and I/O that must be accounted for in capacity planning.

Peak Load and Maintenance

All databases need periodic maintenance: log cleanup, snapshotting, garbage collection, and, for LSM‑based stores like Scylla, SSTable compaction and memtable flushing. Deferring these tasks to low‑load periods improves short‑term performance but still requires reserved resources.

Planning solely on throughput is insufficient; higher throughput often means higher latency. Benchmarks that run only briefly can be misleading, as they don’t capture sustained latency/throughput trade‑offs.

Choosing the Right Node Size

Scylla can scale by adding more nodes or by using larger nodes. Larger nodes improve CPU‑to‑network efficiency, while more nodes increase fault tolerance. For modest workloads, three medium‑sized nodes may suffice, but for reliability a cluster of six‑to‑nine nodes (with replication factor 3) is recommended.

Conclusion

Capacity planning and cluster scaling are complex, iterative processes that must balance safety margins, maintenance overhead, and usage patterns. Initial estimates are rough; real production data should continuously refine the model to guide accurate capacity decisions.