Managing Time Series Data Using TimeScaleDB-Powered PostgreSQL

PostgreSQL extensions are great. Simply by adding an extension, one can transform an otherwise vanilla general-purpose database management system into one capable of processing data requirements in a highly optimized fashion. Some extensions, like pg_repack, simplify and enhance existing features, while other extensions, such as PostGIS and pgvector, add completely new capabilities.

I would like to introduce the PostgreSQL extension TimescaleDB. As the name implies, TimescaleDB is all about data recorded against time. In database systems, time series data is often a relatively small data point of one or more dimensions. A good example is weather data: time versus air pressure, UV index, wind speed, temperature, humidity, and related measurements.

Time series data processing can be taxing to any database system:

• Data inserts come in at a furious rate.
• Data retention requirements can reach hundreds of terabytes or even petabytes.
• Data analysis and report generation are often required for both archived data and real-time incoming data.

While PostgreSQL is quite capable of ingesting, managing, and analyzing time series data, there are other solutions, many of them proprietary, that can perform data ingestion and generate actionable insights at a faster rate.

This brings us back to TimescaleDB.

TimescaleDB was created to improve the rate of time series data ingestion into PostgreSQL by simplifying and automating many of the steps that an experienced DBA uses to optimize and maintain the health of the database over its lifecycle.

Installing and Enabling the TimescaleDB Extension

The best way to appreciate the possibilities is to install the extension and try it out.

Step One: Create the PostgreSQL File Repository Configuration

Because my Linux machine is Ubuntu, the following commands are used to install the extension:

Step Two: Get the Extension

As I am using Ubuntu Jammy, I have the following available packages:

Step Three: Install TimescaleDB Packages

The available packages include:

Step Four: Tune the Data Cluster

The CLI utility timescaledb-tune is useful because it can generate an optimized set of Postgres runtime parameters. However, it assumes that Postgres is the only major process running on the host and is not competing for RAM or CPU.

Suggested test invocation:

Here is the invocation that saves the results in postgresql.auto.conf:

The service can now be restarted:

Step Five: Create Database and Extension

Pretty standard stuff: create your database and extension.

Once created, the TimescaleDB extension installs a number of functions, views, and tables in the database:

Working with TimescaleDB

Now that everything has been set up, we are ready to explore.

Scenario One

Creating the Tables

Let’s create two sets of tables. One is the standard heap table found in PostgreSQL, while the second is TimescaleDB’s hypertable.

Creating a TimescaleDB hypertable automatically defines the partitioning rule using any column of date/time type found in the table. In this case, the key is column t_stamp, which has the timestamptz data type, or timestamp with time zone.

This is the command required to create the hypertable:

The ordinary heap table is created as follows:

Increasing or Decreasing Chunk Size

TimescaleDB partitions its tables into chunks. Although the default size is constrained to seven days, it can be varied to any desired time range.

For the purposes of this blog, and because I am using small data sets, the chunk interval for table t_timescale is set to 10 minutes:

Chunk size best practices:

• Never make the chunk larger than the PostgreSQL shared buffer and free RAM.
• When in doubt, always start with chunks known to be smaller than the shared buffer. It is easier to manage and administer tables by making smaller chunks bigger than by making larger chunks smaller to fit available RAM.

Populating the Tables

Prior to populating the tables, the psql meta-command timing is invoked:

While the standard table did not have any index at table creation, which accelerated data population, the hypertable added the index at the time of hypertable creation. Note that these numbers will vary widely according to the hardware used. Mine is relatively low-end.

Taking a closer look at the t_timescale schema definition, we see that five partitioned child tables have been created. Recalling the chunk interval setting of 10 minutes prior to data population, new partitions were created as the time interval incremented by 10 minutes:

Examination of one of the chunks confirms the partition is set at 10 minutes:

The inconsistent sizes shown below are because the number of records varied within the assigned 10-minute intervals:

You will notice the TimescaleDB indexes are significantly larger than the B-tree index created for table t_standard. Evidently, they contain more information/data:

Administering Hypertable Chunks

The following focuses solely on simple chunk administration.

Chunk General-Purpose Function Calls

These TimescaleDB functions are used to administer chunks:

Chunk Compression Function Calls

These TimescaleDB functions are used to administer table compression:

TimescaleDB Chunk Runtime Parameters

Similar to the general collection of Postgres runtime parameters, some TimescaleDB parameters operate across the data cluster while others can be more fine-tuned for specific relations and even sessions.

Scenario Two

Taking a small subset of the aforementioned functions and runtime parameters, this next scenario demonstrates how one can compress everything from individual chunks to setting a comprehensive policy for a table based on chunk age under normal production conditions.

Compressing a Chunk

Table compression is one of those features best labeled as a killer feature. What is especially useful is that one can not only reduce space consumption but also query and perform DML operations on a hypertable.

Notice how the chunk’s size has been zeroed. To get the true size of the now-compressed relation, you need to use a function call. See below for an example invocation.

This function gives a complete set of metrics describing the now-compressed chunk:

Decompressing a Chunk

It is just as easy to decompress a chunk as it is to compress one. The typical reason is to maximize performance when DML operations must be performed.

Setting a Chunk Compression Policy

As previously demonstrated, setting the chunk interval is straightforward. So is setting the compression policy. One chooses the hypertable and how long after a chunk has been created before it is compressed. Using a time-based argument for compression recognizes the need to process the most recent data in the shortest amount of time.

This compression policy compresses chunks that are older than 30 minutes:

Testing the newly set compression policy is accomplished by inserting new records:

Note the partition size differences between the old and new chunks:

Disabling the compression policy is accomplished as follows:

Caveat

As with all technologies, especially new ones, there are always limitations. TimescaleDB is no exception:

• Foreign key constraints referencing a hypertable are not supported.
• Time dimensions, or columns, used for partitioning cannot have NULL values.
• UNIQUE indexes must include all columns that are partitioning dimensions.
• UPDATE statements that move values between partitions, or chunks, are not supported.
• Horizontal scaling and multi-node support are no longer supported. Instead, it is recommended that a distributed network file system, such as Ceph, be used for scaling purposes.

References

https://docs.timescale.com/
https://docs.timescale.com/api/latest/
https://docs.timescale.com/api/latest/compression/alter_table_compression/
https://docs.timescale.com/use-timescale/latest/compression/about-compression/