Cloud Bigtable Schema Design for Time Series Data

Whenever you measure something, and you record the time together with the measurement, you're building a time series. Time series are all around us:

  • When you look at the plot of memory usage on your computer because it's running slow, you're looking at a time series.

  • When you look at temperature over time on a news report, you're looking at a time series.

  • If you're a foreign exchange trader, and your job involves plotting 5, 10, and 30 day moving average prices for USD/JPY, you're looking at time series.

Time series are also incredibly important:

  • Time series help us optimize resource usage, decrease energy usage, minimize environmental impact, and reduce cost.

  • Time series help us identify trends in data, letting us demonstrate concretely what happened in the past and make informed estimates about what will happen in the future.

  • Time series underpin some of the complex analysis and machine learning in fields such as financial services, retail, insurance, physics, and chemistry.

This guide provides detailed strategies and a walk through for storing and querying time-series data in Cloud Bigtable.

Time series and Cloud Bigtable

Storing time-series data in Cloud Bigtable is a natural fit. Cloud Bigtable stores data as unstructured columns in rows; each row has a row key, and row keys are sorted lexicographically.

There are two commonly used ways to retrieve data from Cloud Bigtable:

  • You can get a single row by specifying the row key.
  • You can get multiple rows by specifying a range of row keys.

These methods are ideal for querying time-series data, since you often want data for a given time range (for example, all of the market data for the day, or server CPU statistics for the last 15 minutes). As a result, Cloud Bigtable is functionally a great fit for time series.

Of course, there's always a devil in the details. For Cloud Bigtable, the devil is that the schema for your data—the columns and the row-key structure—must be designed carefully. A good schema results in excellent performance and scalability, and a bad schema can lead to a poorly performing system. However, there is no single schema design that provides the best fit for all use cases.

The remainder of this paper presents a number of patterns for schema design in Cloud Bigtable. You can use these patterns to design an ideal schema for your use case. After enumerating and explaining the patterns for schema design, you can learn from examples for the following use cases:

  • Financial market data
  • Server metrics (for example, CPU, memory, and network usage)
  • Intelligent energy meters (part of the "Internet of Things", or IoT)

Schema design patterns for time series

The schema design patterns for storing time series in Cloud Bigtable fit into three categories:

  • General patterns
  • Patterns for row key design
  • Patterns for data column design

General patterns

Keep names short but meaningful

When you transfer data from Cloud Bigtable, you're also transferring metadata, including:

  • The row key
  • The column family, an identifier that's used to group related columns
  • The column qualifier, a unique name within a column family

As a result, it's important to choose meaningful names that are also as short as possible, because the size of each name contributes to storage and RPC overhead. For example, rather than using CELLPHONE_NUMBER as a column qualifier, you might use CELL as a short but meaningful abbreviation.

Patterns for row key design

Use tall and narrow tables

A tall and narrow table has a small number of events per row, which could be just one event, whereas a short and wide table has a large number of events per row. As explained in a moment, tall and narrow tables are best suited for time-series data.

For example, suppose you take the temperature in your vegetable garden each morning. Now if you decide that, because you take the temperature each morning, one row per day is appropriate; as a result, your table is tall and narrow. Note that the timestamp isn't the first element of the row key. As explained later, using a timestamp as the first element of a row key can cause a variety of problems.

Row key Column data
... ...

In contrast, suppose you want to plot the temperature over each month, so one row per month is appropriate. The following example shows the short and wide table that you get as a result:

Row key Column data
VEGGIEGARDEN#20150301 TEMP:1:60.4 TEMP:2:61.2 TEMP:3:61.0 TEMP:4:65.1 TEMP:5:62.2 ... TEMP:31:60.4

For time series, you should generally use tall and narrow tables. This is for two reasons: Storing one event per row makes it easier to run queries against your data. Storing many events per row makes it more likely that the total row size will exceed the recommended maximum (see "Rows can be big but are not infinite").

As an optimization, you can use short and wide tables, but avoid unbounded numbers of events. For example, if you usually need to retrieve an entire month of events at once, the temperature table above is a reasonable optimization—the row is bounded in size to the number of days in a month.

Prefer rows to column versions

Cloud Bigtable allows columns to have timestamped versions. As a result, it's theoretically possible to store a time series as a set of versions of a column. For example, if you wanted to record the closing price of ZXZZT shares each day, you could have a single column with a timestamped version for each day:

Row key Column data
ZXZZT STOCK:PRICE (V1 03/01/15):558.40 STOCK:PRICE (V2 03/02/15):571.34 STOCK:PRICE (V3 03/03/15):573.64 STOCK:PRICE (V4 03/04/15):573.37 STOCK:PRICE (V5 03/05/15):575.33

However, this isn't the best way to store this data.

By default, use new rows instead of column versions. Using multiple rows, with a single version of an event in each row, is the simplest way to represent, understand, and query your data.

It is acceptable to use versions of a column where the use case is actually amending a value, and the value's history is important. For example, suppose you did a set of calculations based on the closing price of ZXZZT, and initially the data was mistakenly entered as 559.40 for the closing price instead of 558.40. In this case, it might be important to know the value's history in case the incorrect value had caused other miscalculations.

Design your row key with your queries in mind

When Cloud Bigtable stores rows, it sorts them by row key in lexicographic order. There is effectively a single index per table, which is the row key. Queries that access a single row, or a contiguous range of rows, execute quickly and efficiently. All other queries result in a full table scan, which will be far, far slower. A full table scan is exactly what it sounds like—every row of your table is examined in turn. For Cloud Bigtable, where you could be storing many petabytes of data in a single table, the performance of a full table scan will only get worse as your system grows.

For example, consider a table where players' scores from video games are stored, which might be designed as follows.

Row key Column data
LoL#20150301 GAME:PLAYER:Corrie GAME:WIN:false GAME:KDA:4.25
LoL#20150302 GAME:PLAYER:Jo GAME:WIN:true GAME:KDA:7.00
LoL#20150302 GAME:PLAYER:Sam GAME:WIN:true GAME:KDA:7.00
LoL#20150303 GAME:PLAYER:Corrie GAME:WIN:true GAME:KDA:9.50
Starcraft#20150303 GAME:PLAYER:Eriko GAME:WIN:true GAME:KDA:6.00

Suppose you want to query this data to answer the question "How many games of LoL did Corrie win in March?" With the schema shown above, you will have to scan most of the table to answer this question. In contrast, if you design the table as follows, you could complete this query by retrieving a specific range of row keys:

Row key Column data
LoL#Corrie#20150301 GAME:WIN:false GAME:KDA:4.25
LoL#Corrie#20150303 GAME:WIN:true GAME:KDA:9.50
LoL#Jo#20150302 GAME:WIN:true GAME:KDA:7.00
LoL#Sam#20150302 GAME:WIN:true GAME:KDA:7.00
Starcraft#Eriko#20150303 GAME:WIN:true GAME:KDA:6.00

Choosing a row key that facilitates common queries is of paramount importance to the overall performance of the system. Enumerate your queries, put them in order of importance, and then design row keys that work for those queries.

How do you deal with a situation where there is no perfect row key? For example, suppose the queries for all LoL games for March and all LoL games played by Corrie in March were equally important. The schema above would allow us to query for Corrie's games on LoL in March but wouldn't help us with all LoL games in March—the best you could do is query all LoL games and then filter for March. There are two ways to solve this problem:

Use two tables, each with a row key appropriate to one of the queries. This is a good solution, because it results in a robust, scalable system.
Query and filter
Stick with the schema shown above, and have one query (all LoL games in March) that underperforms because you are filtering a large number of rows. This is not normally a good solution, because it results in a less scalable system that could easily deteriorate as usage increases.

Ensure that your row key avoids hotspotting

The most common issue for time series in Cloud Bigtable is hotspotting. This issue can affect any type of row key that contains a monotonically increasing value.

In brief, when a row key for a time series includes a timestamp, all of your writes will target a single node; fill that node; and then move onto the next node in the cluster, resulting in hotspotting. For example, if you're storing a cell phone's battery status, and your row key consists of the word "BATTERY" plus a timestamp (as shown below), the row key will always increase in sequence. Because Cloud Bigtable stores adjacent row keys on the same server node, all writes will focus only on one node until that node is full, at which point writes will move to the next node in the cluster.

Row key Column data

There are a few ways to solve this problem:

  • Field promotion. Move fields from the column data into the row key to make writes non-contiguous.

  • Salting. Add an additional calculated element to the row key to artificially make writes non-contiguous.

Field promotion

In this example, you will promote USER from a column to an element of the row key. This change would solve the hotspotting issue because user identifiers will provide a more uniform distribution of row keys. As a result, writes will be split across multiple nodes in your cluster.

The advantage of field promotion is that it often makes your queries more efficient as well, making this strategy a clear winner. The (slight) disadvantage is that your queries are constrained by your promoted fields, leading to rework if you don't promote the right fields.

Row key Column data
BATTERY#Corrie#20150301124501001 METRIC:PERCENTAGE:98
BATTERY#Corrie#20150301124501003 METRIC:PERCENTAGE:96
BATTERY#Jo#20150301124501002 METRIC:PERCENTAGE:54
BATTERY#Sam#20150301124501004 METRIC:PERCENTAGE:43
BATTERY#Sam#20150301124501005 METRIC:PERCENTAGE:38


In this example, you will take a hash of the timestamp and divide it by 3; take the remainder of this calculation; and add the remainder to the row key. Why 3? This is an estimate of the number of nodes in the cluster in this case and would provide a good division of activity across those nodes.

The advantage of salting is its simplicity—it's essentially a simple hashing function. One disadvantage is that when you query for time ranges, you'll have to do multiple scans—one scan per salt value—and combine the results in your own code. Another disadvantage is that it's difficult to choose a salt value that both distributes activity across nodes and operates well as you scale your system up or down. Because of these disadvantages, and because it's best to use human-readable row keys, avoid salting unless you can find no other way to prevent hotspotting.

Row key Column data
BATTERY#2#20150301124501002 METRIC:USER: Corrie METRIC:PERCENTAGE:54

By default, prefer field promotion. Field promotion avoids hotspotting in almost all cases, and it tends to make it easier to design a row key that facilitates queries.

Use salting only where field promotion does not resolve hotspotting. In the rare case where you apply a salting function, be careful not to make too many assumptions about the underlying size of the cluster. The example above uses a salting function that assumes there are three nodes in the cluster; this assumption is safe because it would scale to the limited number of nodes that can exist in a Cloud Bigtable cluster. If you could create clusters with hundreds of nodes, you would want to use a different salting function.

Reverse timestamps only when necessary

You can reverse timestamps by subtracting the timestamp from your programming language's maximum value for long integers (such as Java's java.lang.Long.MAX_VALUE). By reversing the timestamp, you can design a row key where the most recent event appears at the start of the table instead of the end. As a result, you can get the N most recent events simply by retrieving the first N rows of the table.

Prefer reverse timestamps only where your most common query is for the latest values. This is because reversing timestamps makes every other query more complex and complicates the overall schema.

Patterns for data column design

Rows can be big but are not infinite

Rows in Cloud Bigtable can contain ~100 column families and millions of columns, with a 100 MB limit on each value stored in a column. These generous limits provide great flexibility. However, you shouldn't assume that big rows are the right way to store data, and that you should therefore fill each row with as much data as possible. Always keep in mind that retrieving large values takes extra time and memory.

In general, keep row sizes below approximately 100 MB. This is more of a guideline than a rule--rows can be larger than 100 MB. However, if you have many rows larger than this, you should expect performance issues.

In general, keep column values below approximately 10 MB. Again, this is more of a guideline than a rule--you can store some values that are larger than 10 MB, but they are likely to cause performance issues.

To reiterate, if you often rely on large rows, or large individual values, expect performance issues in your system.

Cloud Bigtable is a key/value store, not a relational store. It does not support joins, nor does it support transactions except within a single row. As a result, it's best to access data in individual rows or in a set of contiguous rows.

One result of this pattern is fairly obvious: In the vast majority of cases, time-series queries are accessing a given dataset for a given time period. Therefore, make sure that all of the data for a given time period is stored in contiguous rows, unless doing so would cause hotspotting.

Another result is that when you read data for a row, or a range of rows, that data should be useful on its own—you should not need to combine it with other data. For example, suppose you are storing user activity on a shopping website, and you often need to retrieve the last five actions performed by the user so you can display them in a sidebar. In this case, you should consider denormalizing your data and including some user and product details in the table of recent actions. In contrast, with a relational database, you would likely store the user ID and product ID in one table, then join that table with separate user and product tables in your SQL query.

That said, you don't need to include every piece of data about an entity in every single row. For example, if you're displaying information about a user's recent actions, you don't need to store the user's phone number, or the address of a product's manufacturer, because you aren't going to display that information in a sidebar.

Look for opportunities to denormalize data to satisfy