9 Postgres for time series

 

This chapter covers

  • Exploring Postgres 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

Time series is a series 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 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.

In this chapter, you learn to use Postgres for time-series data and workloads. You start by exploring the nature of time-series data and the capabilities Postgres offers for working with it. Next, you dive into the TimescaleDB extension, which makes it easy to store, analyze, and manage time-series data in Postgres. Finally, you learn how to optimize the search over time-series data with B-tree and BRIN indexes. And you do all this the developer way while working on an application for smartwatches that tracks a user’s heart rate and provides important health data.

9.1 How Postgres works with time-series data

9.2 Starting Postgres with TimescaleDB

9.3 Loading 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 aggregate

9.6.2 Refreshing aggregate

9.7 Indexing time-series data

9.7.1 Using B-tree index

9.7.2 Using BRIN index

9.8 Summary