Which is the preferred method to use to avoid hotspotting in time series data in Bigtable?

Hotspotting in Bigtable with time series data occurs when row keys are based on sequential timestamps, causing all new writes to target a single node. The preferred method to mitigate this is field promotion. This schema design technique involves prepending a high-cardinality, non-sequential value from the data—such as a metricid or deviceid—to the timestamp in the row key (e.g., [metricid]#[timestamp]). This design leverages the natural diversity of the promoted field to distribute write operations across many different row key prefixes, and therefore across multiple nodes in the cluster, while still keeping data for a single metric ordered by time.

B. Randomization: This approach destroys the lexicographical ordering of the row keys, making efficient time-based range scans—a critical requirement for time series analysis—impossible.

C. Salting: While salting (adding a computed prefix) is a valid technique to prevent hotspotting, field promotion is generally preferred as it uses the natural cardinality of the data, which can result in a simpler and more intuitive schema.

D. Hashing: Hashing the entire row key has the same drawback as randomization: it breaks the natural sort order required for time-range scans.

1. Google Cloud Documentation, "Schema design for time series data": This document explicitly warns against using a raw timestamp at the beginning of a row key. It recommends, as a primary solution, prepending a value with high cardinality. The example provided is [metricname]#[timestamp], which is a direct implementation of the field promotion technique. (See section: "Metric name, then timestamp").

2. Google Cloud Documentation, "Schema design best practices": In the section "Row keys that are related to timestamps," the documentation advises against using a raw timestamp as the row key to avoid hotspotting. The recommended example, [deviceid]#[reversedtimestamp], demonstrates promoting the deviceid field to the beginning of the row key to distribute the load.

3. Designing a schema for time series data on Google Cloud Bigtable, Google Cloud Blog (2022): This article reinforces the principle, stating, "The most common cause of hotspotting with time series data is using the timestamp as the row key... The solution is to add a prefix to the row key that will ensure that your writes are distributed across multiple tablets." It then presents [metricid]#[timestamp] as the first and most common pattern.