# Time Series Database

This system can use PostgreSQL's TimescaleDB extension (opens new window) (Timescale, the company, has since been renamed TigerData) to store the running-state data of industrial control and IoT devices, or massive time series data in other scenarios.

The platform itself does not depend on TimescaleDB. This page describes an optional database-level configuration: after switching to a database image with the TimescaleDB extension, you use time series tables via SQL in plugins or dynamic logic.

# Target Audience

The target audience of this document is: developers who need to store large amounts of time series data with this system

# About TimescaleDB

TimescaleDB extends PostgreSQL with time series and analytics capabilities: it converts ordinary tables into "hypertables" that are automatically partitioned by time, and provides time series functions such as time_bucket as well as continuous aggregates.

# Running TimescaleDB

The default image does not include TimescaleDB

When deployed as described in Docker Deployment, the database image in the template docker-compose.yml is pgvector/pgvector:0.8.0-pg17, which does not include the TimescaleDB extension. To use time series features, change the database image to timescale/timescaledb-ha:pg17. This image also includes the vector extension and can replace the original pgvector image.

The following is an example configuration of the database service in docker-compose.yml (the database name keeps the template's application):

  database:
    image: timescale/timescaledb-ha:pg17
    restart: always
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=password
      - POSTGRES_DB=application
      - PGDATA=/home/postgres/pgdata/data
    volumes:
      # Note: the data directory of the timescaledb-ha image differs from the pgvector image, so a new empty directory is used here
      - ./runtime/timescaledb:/home/postgres/pgdata
      - ./db-seed-data/initdb:/docker-entrypoint-initdb.d/
    healthcheck:
      test: ['CMD-SHELL', 'pg_isready -U postgres']
      interval: 10s
      timeout: 5s
      retries: 5
    networks:
      - scm

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

The timescale/timescaledb-ha image runs as the postgres user with uid 1000 (the pgvector/pgvector image uses uid 999), and it does not change the owner of the data directory before starting. Create the data directory on the host first and give it to uid 1000; otherwise the container exits with mkdir: cannot create directory '/home/postgres/pgdata/data': Permission denied:

mkdir -p runtime/timescaledb
sudo chown 1000:1000 runtime/timescaledb

1
2

Back up before switching images

The data directory (PGDATA), mount location and running user of the timescale/timescaledb-ha image all differ from the pgvector/pgvector image, so the old ./runtime/database/data directory cannot be reused directly. For an environment with existing data, switch as follows:

Before switching, back up the application database with pg_dump -Fc, and back up the ./runtime/database/data directory;
Switch to the new image and a new empty data directory according to the configuration above, then start. When the new database is initialized, it runs the scripts in ./db-seed-data/initdb, loading the seed data shipped with the template;
Restore with pg_restore --clean --if-exists -d application <backup file>. --clean --if-exists first drops the objects with the same names created by the seed data, avoiding "already exists" errors during restore.

# Creating the TimescaleDB Extension

The initialization scripts shipped with the timescaledb-ha image (such as /docker-entrypoint-initdb.d/000_install_timescaledb.sh) create the timescaledb extension. But the configuration above mounts ./db-seed-data/initdb at /docker-entrypoint-initdb.d/, which hides the image's own scripts so they do not run. As a result, the new database has no timescaledb extension, and you need to create it manually:

-- Connect to the database
\c application

-- Create the timescaledb extension
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- Show installed extensions to confirm
\dx

1
2
3
4
5
6
7
8

# Creating Tables with TimescaleDB

TimescaleDB provides the function create_hypertable for converting an ordinary table into a hypertable:

-- Create the table
CREATE TABLE stocks_real_time (
  time TIMESTAMPTZ NOT NULL,
  symbol TEXT NOT NULL,
  price DOUBLE PRECISION NULL,
  day_volume INT NULL
);

-- Create a hypertable: convert stocks_real_time into a time series table, with time as the time column
SELECT create_hypertable('stocks_real_time','time');

-- If the table already contains data, add the migrate_data parameter to migrate the existing data into time partitions
-- SELECT create_hypertable('stocks_real_time','time', migrate_data => true);

-- Create an index on the symbol and time columns
CREATE INDEX ix_symbol_time ON stocks_real_time (symbol, time DESC);

-- Create another ordinary table
CREATE TABLE company (
  symbol TEXT NOT NULL,
  name TEXT NOT NULL
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

# Querying Data

TimescaleDB uses standard PostgreSQL syntax for queries, and additionally provides functions for querying time series data, such as time_bucket.

# Inserting Test Data

Download the sample data real_time_stock_data.zip (opens new window) and extract it with unzip real_time_stock_data.zip.
Import the sample data into the database: copy the files into the database container and open the psql console.

# Copy the sample data into the running database container
docker cp tutorial_sample_tick.csv <database container ID>:/tmp/tutorial_sample_tick.csv
docker cp tutorial_sample_company.csv <database container ID>:/tmp/tutorial_sample_company.csv
# Open the psql console
docker exec -it <database container ID> psql -U postgres -d application

1
2
3
4
5

-- Import the CSV files into the stocks_real_time and company tables
\COPY stocks_real_time from '/tmp/tutorial_sample_tick.csv' CSV HEADER;
\COPY company from '/tmp/tutorial_sample_company.csv' CSV HEADER;

1
2
3

# Querying Data with Raw SQL

The sample data is historical data over a fixed time range (the version downloaded in 2026-09 covers 2025-12-29 to 2026-01-29), and it may be updated upstream. After importing, first check the actual range:

SELECT min(time), max(time) FROM stocks_real_time;

In the queries below that filter by "the last N days", replace the time with the sample data's range, for example replace now() with (SELECT max(time) FROM stocks_real_time); otherwise no data is returned.

-- Select all stock data from the last four days
SELECT * FROM stocks_real_time srt
 WHERE time > now() - INTERVAL '4 days';

-- Select Amazon's 10 most recent trades in order
SELECT * FROM stocks_real_time srt
 WHERE symbol='AMZN'
 ORDER BY time DESC, day_volume desc
 LIMIT 10;

-- Calculate Apple's average trading price over the last four days
SELECT
  avg(price)
  FROM stocks_real_time srt
       JOIN company c ON c.symbol = srt.symbol
 WHERE c.name = 'Apple' AND time > now() - INTERVAL '4 days';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# Time Series Functions

first(): finds the earliest value by time within an aggregate group
last(): finds the latest value by time within an aggregate group
time_bucket(): buckets data by an arbitrary time interval and computes aggregates over those intervals

-- Calculate the daily average price of each symbol over the last week
SELECT
  time_bucket('1 day', time) AS bucket,
  symbol,
  avg(price)
  FROM stocks_real_time srt
 WHERE time > now() - INTERVAL '1 week'
 GROUP BY bucket, symbol
 ORDER BY bucket, symbol;

1
2
3
4
5
6
7
8
9

# Continuous Aggregates

TimescaleDB continuous aggregates materialize and store aggregate results, and update them incrementally in the background according to a refresh policy, so queries on aggregate data respond faster. They do not need to be computed synchronously on write, but the background refresh itself also consumes database resources.

When creating a materialized view, using the WITH (timescaledb.continuous) option creates a continuous aggregate.

# Creating a Continuous Aggregate

-- Query the daily high, low, open and close prices of stock trading data
SELECT
  time_bucket('1 day', "time") AS day,
  symbol,
  max(price) AS high,
  first(price, time) AS open,
  last(price, time) AS close,
  min(price) AS low
FROM stocks_real_time srt
GROUP BY day, symbol
ORDER BY day DESC, symbol;

-- Create a continuous aggregate from the SQL above, storing the results in the stock_candlestick_daily view
CREATE MATERIALIZED VIEW stock_candlestick_daily
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 day', "time") AS day,
  symbol,
  max(price) AS high,
  first(price, time) AS open,
  last(price, time) AS close,
  min(price) AS low
FROM stocks_real_time srt
GROUP BY day, symbol;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

# Setting Up Automatic Refresh

A continuous aggregate is computed once from the data at creation time and is not updated automatically afterwards. You need to add a refresh policy for the background to refresh it periodically:

-- Refresh once an hour, covering data from 1 month ago to 1 hour ago
SELECT add_continuous_aggregate_policy('stock_candlestick_daily',
  start_offset      => INTERVAL '1 month',
  end_offset        => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 hour');

-- You can also refresh manually at any time
CALL refresh_continuous_aggregate('stock_candlestick_daily', NULL, NULL);

1
2
3
4
5
6
7
8

In addition, since TimescaleDB 2.13 (opens new window), continuous aggregates by default return only materialized results (materialized_only = true), so new data that has not been refreshed in yet cannot be queried. To merge the latest data into query results in real time as well, run:

ALTER MATERIALIZED VIEW stock_candlestick_daily SET (timescaledb.materialized_only = false);

TIP

The behavior above was tested on timescale/timescaledb-ha:pg17 (TimescaleDB 2.28.3): after creation, materialized_only defaults to true and there is no refresh job; newly written data does not appear in the view until refresh_continuous_aggregate is run or the refresh policy runs.

# Querying from Code

To run raw SQL in plugins or dynamic logic, use QueryHelper.withSql from the platform API (see Platform API), which passes in a groovy.sql.Sql object:

import groovy.sql.GroovyRowResult
import groovy.sql.Sql
import tech.muyan.utils.QueryHelper

List<GroovyRowResult> rows = QueryHelper.withSql { Sql sql ->
  sql.rows("SELECT * FROM stock_candlestick_daily WHERE symbol = ? ORDER BY day DESC LIMIT 30", ['AAPL'])
}

1
2
3
4
5
6
7

For detailed usage of the Sql class, see the groovy.sql.Sql API documentation (opens new window).

# Further Reading

The above is only a brief introduction to TimescaleDB. For more features, see the official documentation:

← 🔌 Plugin Development