Schema Design Considerations with CockroachDB. What is a Hotspot Not?

CockroachDB is crafted to scale both reads and writes by adding more nodes horizontally and/or increasing per-node hardware capacity in a vertical manner. All data is sorted lexicographically by key and organised into 512mb (max) sized ranges, which are triplicated and diversified across nodes. The database automatically rebalances all data at the range level, based on per-range statistics that are gossiped between the nodes. These stats are input for five different signals for the replica placement heuristics:

• user constraints - adding constraints for performance or compliance
• space - balance disk space utilisation
• latency - move replicas close to points of access
• diversity - spread replicas across failure domains
• load - balance load evenly across the cluster

These signals ultimately determine how and when ranges and leases (authority to read and write to a range) are split, merged and moved between the nodes. It's a constantly ongoing process.

One consequence of this range-based, order-preserving model is that range hotspots can appear when specific nodes take an unproportionate amount of traffic due to either a sequential workload or small tables that prevents splitting and scattering ranges across the cluster.

As mentioned, the database uses built-in heuristics to prevent hotspots from appearing, such as load-based range splitting. Sometimes, however, manual schema tuning is needed depending on the use case. For instance, when there’s a cardinality problem with very small tables and large tables joined together. Range hotpots are typically easy to spot and eliminate if they would appear.

One method also is to do up-front prevention against range hotspots by schema design. Choosing the appropriate primary ID strategy for tables has a significant impact for instance. A good starting point explaining this is the Unique ID Best Practices page. I typically recommend using UUIDs for primary keys rather than sequential types like SEQUENCE. Generating monotonically increasing sequences (SERIAL) requires strict ordering which is expensive, as described by one of the CockroachLabs product managers (Piyush Singh) in this video.  Alternatively, a hash sharded index will also help if primary IDs are difficult to refactor in existing schemas during migrations.

CockroachDB is designed to scale linearly with sustained throughput since ranges can split/scatter almost indefinitely. Data volumes over TiB's is not an issue for CockroachDB as long as a cluster is properly provisioned in cpu, mem etc.

CockroachDB is truly a scalable and highly resilient database to consider for the modern day applications that are being developed.