chapter nine

9 Postgres for time series

 

This chapter covers

  • Exploring Postgres’s capabilities for time-series workloads
  • Using the TimescaleDB extension to manage and process time-series data
  • Analyzing time-series data with specialized functions and aggregates
  • Optimizing queries with B-tree and BRIN indexes

A time series is a sequence of data points collected over time, with each point representing the state of a system or object at a specific moment. By capturing time-series data over a given period, we can observe how the system has evolved or changed, which helps us identify trends, take proactive actions, or make future predictions. For instance, we work with time-series data when reviewing CPU and memory usage from a server over the past week, exploring currency exchange rate fluctuations over the last three months, or analyzing a patient's vital signs collected over the past year.

Let’s learn how to use Postgres for time-series data and workloads as we build a smartwatch application that tracks user heart rate and records measurements for further analysis. We’ll learn how to efficiently store and analyze heart rate data in Postgres, keeping users informed of important health trends and changes.

9.1 How Postgres works with time-series data

9.2 Starting Postgres with TimescaleDB

9.3 Loading the time-series data

9.4 Exploring TimescaleDB hypertables

9.5 Analyzing time-series data

9.5.1 Using the time_bucket function

9.5.2 Using the time_bucket_gapfill function

9.6 Using continuous aggregates

9.6.1 Creating and using aggregates

9.6.2 Refreshing aggregates

9.7 Indexing time-series data

9.7.1 Using a B-tree index

9.7.2 Using BRIN indexes

9.8 Summary