Schema design for time series data

This page describes schema design patterns for storing time series data inBigtable. This page builds onDesigning your schema andassumes you are familiar with the concepts and recommendations described on thatpage.

A time series is a collection of data that consists of measurements and thetimes when the measurements are recorded. Examples of time series include thefollowing:

• The plot of memory usage on your computer
• Temperature over time on a news report
• Stock market prices over a period of time

A good schema results in excellent performance and scalability, and a bad schemacan lead to a poorly performing system. However, no single schema designprovides the best fit for all use cases.

The patterns described on this page provide a starting point. Your uniquedataset and the queries you plan to use are the most important things toconsider as you design a schema for your time-series data.

The basic design patterns for storing time-series data inBigtable are as follows:

• Rows are time buckets

  • New columns for new events
  • New cells for new events

• Rows represent single timestamps

  • Serialized column data
  • Unserialized column data

Data for examples

To illustrate the differences between patterns, the examples on this page assumethat you are storing data for an app that records themeasurements that weather balloons take once every minute. We useevent tomean a single request that writes one or multiple cells at thesame time. Location IDs correspond with Google Cloud regions.

Time buckets

In a time bucket pattern, each row in your table represents a "bucket" of time,such as an hour, day, or month. A row key includes a non-timestamp identifier,such asweek49, for the time period recorded in the row, along with otheridentifying data.

The size of the bucket that you use — such as minute, hour, or day —depends on the queries that you plan to use and onBigtable data size limits. For example, ifrows that contain an hour of data are bigger the recommended maximum sizeper row of 100 MB, then rows that represent a half houror a minute are probably a better choice.

Advantages of time bucket patterns include the following:

• You'll see betterperformance. For example, if you store 100measurements, Bigtable writes and reads those measurements fasterif they are in one row than if they are in 100 rows.

• Data stored in this way is compressed more efficiently than data in tall,narrow tables.

Disadvantages include the following:

• Time-bucket schema design patterns are more complicated than single-timestamppatterns and can take more time and effort to develop.

Adding new columns for new events

In this time bucket pattern, you write a new column to a row for each event,storing thedata in the column qualifier rather than as a cell value.This means that for each cell, you send the column family, column qualifier, andtimestamp, but no value.

Using this pattern for the sample weather balloon data, each row contains allthe measurements for a single metric, such aspressure, for a single weatherballoon, over the course of a week. Each row key contains the location, balloonID, metric that you are recording in the row, and a week number. Every time aballoon reports its data for a metric, you add a new column to the row. Thecolumn qualifier contains the measurement, the pressure in Pascals, for theminute identified by the cell timestamp.

In this example, after three minutes a row might look like this:

Use cases for this pattern include the following:

• You don't need to measure changes in your time series data.

• You want to save storage space byusing column qualifiers as data.

Adding new cells for new events

In this time bucket pattern, you add new cells to existing columns when youwrite a new event. This pattern lets you take advantage ofBigtable's ability to let you store multiple timestamped cells ina given row and column. It's important to specify garbage collection rules whenyou use this pattern.

Using the weather balloon data as an example, each row contains all themeasurements for a single weather balloon over the course of a week. The row keyprefix is an identifier for the week, so you can read an entire week's worth ofdata for multiple balloons with a single query. The other row key segments arethe location where the balloon operates and the ID number for the balloon. Thetable has one column family,measurements, and that column family has onecolumn for each type of measurement:pressure,temperature,humidity, andaltitude.

Every time a balloon sends its measurements, the applicationwrites new values to the row that holds the current week's data for the balloon,writing additional timestamped cells to each column. At the end of the week,each column in each row has one measurement for each minute of the week, or10,080 cells (if your garbage collection policy allows it).

Each column in each row holds a measurement for each minute of the week. In thiscase, after three minutes, the first two columns in a row might look like this:

Use cases for this pattern include the following:

• You want to be able to measure changes in measurements over time.

Single-timestamp rows

In this pattern, you create a row for each new event or measurement instead ofadding cells to columns in existing rows. The row key suffix is the timestampvalue. Tables that follow this pattern tend to betall and narrow, and eachcolumn in a row contains only one cell.

In this pattern, you store all the data for a row in a single column in aserialized format such as JSON or a protocol buffer (protobuf). This approach isdescribed in more detail onSchema design best practices.

For example, if you use this pattern to store the weather balloon data, yourtable might look like this after four minutes:

Advantages of this pattern include the following:

• Storage efficiency

• Speed

Disadvantages include the following:

• If you don't use the SQL querying features of Bigtable forJSON or protocol buffers, you are unable to retrieve only specific fieldswhen you read the data and must instead read all data for a givencell.

• The need to deserialize the data after it's read

Use cases for this pattern include the following:

• You almost always need to retrieve all measurements for a given event at thesame time.

• You prioritize storage efficiency because you almost always retrieve allmeasurements for a given event at the same time. While you can useBigtable's SQL capabilities to filter data server-side, thispattern is most beneficial when you don't need to filter frequently.

• Each event contains so many measurements that you might exceed the100 MB per-row limit if you store the data in multiplecolumns.

Single-timestamp unserialized

In this pattern, you store each event in its own row, even if you are recordingonly one measurement. The data in the columns is not serialized.

Advantages of this pattern include the following:

• It is generally easier to implement than a time-bucket pattern.

• You might spend less timerefining your schema before usingit.

Disadvantages of this pattern often outweigh the advantages:

• Bigtable is less performant with this pattern.

• Data stored this way is not as efficiently compressed as data in widercolumns.

• Even when the timestamp is at the end of the row key, this pattern can resultin hotspots.

Use cases for this pattern include the following:

• You want to always retrieve all columns but only a specified range oftimestamps, but you have a reason not to store the data in a serializedstructure.

• You want to store an unbounded number of events.

Using theweather balloon example data, the column family andcolumn qualifiers are the same as the example using time buckets and new cells.In this pattern, however, every set of reported measurements for each weatherballoon is written to anew row. The following table shows five rows that arewritten using this pattern:

Additional strategies

If you need to send multiple different queries for the same dataset, considerstoring your data in multiple tables, each with a row key designed for one ofthe queries.

You can also combine patterns in some cases. For example, you can storeserialized data in rows that representtime buckets, as long as you don't let the rows become too big.

What's next

• Review the steps involved inplanning a schema.
• Understand thebest practices for designing a schema.
• Read aboutthe performance you can expect from Bigtable.
• Explore the diagnostic capabilities ofKey Visualizer.