TimescaleDB

TimescaleDB is a category-defining relational database for time-series data. Packaged as a PostgreSQL extension, TimescaleDB is designed to be easy to use, easy to get started, and easy to maintain.

Download this sample dataset by Timescale

Docker Compose

mkdir /tmp/prest+timescaledb
cd /tmp/prest+timescaledb
cat <<YML > docker-compose.yml
---
version: "3"
services:
  timescaledb:
    image: timescale/timescaledb:latest-pg12
    volumes:
      - "./data:/var/lib/postgresql/data"
      - "/tmp:/var/tmp"
    environment:
      - POSTGRES_USER=prest
      - POSTGRES_DB=prest
      - POSTGRES_PASSWORD=prest
    ports:
      - "5432:5432"
  prest:
    image: prest/prest:latest
    links:
      - "timescaledb:timescaledb"
    environment:
      - PREST_DEBUG=true  # remove comment for enable DEBUG mode (disable JWT)
      - PREST_PG_HOST=timescaledb
      - PREST_PG_USER=prest
      - PREST_PG_PASS=prest
      - PREST_PG_DATABASE=prest
      - PREST_PG_PORT=5432
      - PREST_JWT_DEFAULT=false  # remove if need jwt
      - PREST_SSL_MODE=disable
    depends_on:
      - timescaledb
    ports:
      - "3000:3000"
YML

Starting up the containers

docker-compose pull
docker-compose up -d

Creating database structure

curl https://timescaledata.blob.core.windows.net/datasets/devices_small.tar.gz -o /tmp/devices_small.tar.gz
tar -C /tmp -xzvf /tmp/devices_small.tar.gz
docker-compose exec -T timescaledb psql -U prest -f /var/tmp/devices.sql

Loading data

docker-compose exec -T timescaledb psql -U prest <<SQL
COPY device_info FROM '/var/tmp/devices_small_device_info.csv' WITH (FORMAT CSV);
COPY readings FROM '/var/tmp/devices_small_readings.csv' WITH (FORMAT CSV);
SQL

Simple Query

SQL execution:

docker-compose exec -T timescaledb psql -U prest <<SQL
SELECT
    time, device_id, battery_temperature
FROM
    readings
WHERE
    battery_status = 'charging'
ORDER BY
    time DESC
LIMIT
    10;
SQL

prestd execution:

curl -G http://localhost:3000/prest/public/readings \
  -d battery_status='$eq.charging' \
  -d _select=time,device_id,battery_temperature \
  -d _order=-time \
  -d _page=1 \
  -d _page_size=10

Joining tables

SQL execution:

docker-compose exec -T timescaledb psql -U prest <<SQL
SELECT
    time, readings.device_id, cpu_avg_1min,
    battery_level, battery_status, device_info.model
FROM
    readings
    JOIN device_info ON readings.device_id = device_info.device_id
WHERE
    battery_level < 33
    AND battery_status = 'discharging'
ORDER BY
    cpu_avg_1min DESC, time DESC
LIMIT
    5;
SQL

prestd execution:

curl -G http://localhost:3000/prest/public/readings \
  -d battery_level='$lt.33' \
  -d battery_status='$eq.discharging' \
  -d _select='time,readings.device_id,cpu_avg_1min,battery_level,battery_status,device_info.model' \
  -d _join='inner:device_info:readings.device_id:$eq:device_info.device_id' \
  -d _order='-cpu_avg_1min,-time' \
  -d _page=1 \
  -d _page_size=5

Using VIEWs

Creating VIEW named battery_level_by_hour:

docker-compose exec -T timescaledb psql -U prest <<SQL
CREATE VIEW battery_level_by_hour AS
SELECT
    time_bucket('1 hour', time) AS "hour",
    model,
    min(battery_level) AS min_battery_level,
    max(battery_level) AS max_battery_level
FROM
    readings
    JOIN device_info ON readings.device_id = device_info.device_id
GROUP BY
    "hour", model;
SQL

Aggregating data over battery_level_by_hour view:

docker-compose exec -T timescaledb psql -U prest <<SQL
SELECT
    hour, min(min_battery_level), max(max_battery_level)
FROM
    battery_level_by_hour
WHERE
    model IN ('pinto', 'focus')
GROUP BY
    hour
ORDER BY
    hour ASC
LIMIT
    12;
SQL

prestd execution:

curl -G http://localhost:3000/prest/public/battery_level_by_hour \
  -d model='$in.pinto,focus' \
  -d _select='hour,min:min_battery_level,max:max_battery_level' \
  -d _groupby='hour' \
  -d _order='hour'

Simple SELECT over battery_level_by_hour view:

docker-compose exec -T timescaledb psql -U prest <<SQL
SELECT
    hour, min_battery_level, max_battery_level
FROM
    battery_level_by_hour
WHERE
    model = 'mustang'
LIMIT
    5;
SQL

**prestd execution over battery_level_by_hour view:

curl -G http://localhost:3000/prest/public/battery_level_by_hour \
  -d model='$eq.mustang' \
  -d _select='hour,min_battery_level,max_battery_level' \
  -d _order='hour' \
  -d _page='1' \
  -d _page_size='5'

Batch Insert data

Using default INSERT statement WITH returning inserted data:

curl http://localhost:3000/batch/prest/public/readings \
  -H "Content-Type: application/json" \
  -d @- << JSON
  [
    {"time": "2020-10-17T20:19:30+00:00", "device_id": "demo000000", "battery_level": 43, "battery_status": "discharging", "battery_temperature": 89.8, "bssid": "01:02:03:04:05:06", "cpu_avg_1min": 28.84, "cpu_avg_5min": 16.9047812612903, "cpu_avg_15min": 10.8993036332756, "mem_free": 420023054, "mem_used": 579976946, "rssi": -40, "ssid": "demo-net"},
    {"time": "2020-10-17T20:19:30+00:00", "device_id": "demo000001", "battery_level": 27, "battery_status": "discharging", "battery_temperature": 89.3, "bssid": "A0:B1:C5:D2:E0:F3", "cpu_avg_1min": 4.89, "cpu_avg_5min": 6.63334573320236, "cpu_avg_15min": 9.25968056754939, "mem_free": 717784757, "mem_used": 282215243, "rssi": -41, "ssid": "stealth-net"},
    {"time": "2020-10-17T20:19:30+00:00", "device_id": "demo000002", "battery_level": 29, "battery_status": "discharging", "battery_temperature": 93.7, "bssid": "A0:B1:C5:D2:E0:F3", "cpu_avg_1min": 8.29, "cpu_avg_5min": 6.78591150918263, "cpu_avg_15min": 7.37546420066158, "mem_free": 634081377, "mem_used": 365918623, "rssi": -54, "ssid": "stealth-net"},
    {"time": "2020-10-17T20:19:30+00:00", "device_id": "demo000003", "battery_level": 14, "battery_status": "discharging", "battery_temperature": 93.1, "bssid": "01:02:03:04:05:06", "cpu_avg_1min": 8.83, "cpu_avg_5min": 8.18492270691781, "cpu_avg_15min": 11.3986054360923, "mem_free": 563352328, "mem_used": 436647672, "rssi": -30, "ssid": "demo-net"},
    {"time": "2020-10-17T20:19:30+00:00", "device_id": "demo000004", "battery_level": 58, "battery_status": "discharging", "battery_temperature": 93.2, "bssid": "22:32:A2:B3:05:98", "cpu_avg_1min": 8.79, "cpu_avg_5min": 10.3900175308572, "cpu_avg_15min": 13.5103326842724, "mem_free": 642162250, "mem_used": 357837750, "rssi": -62, "ssid": "demo-5ghz"}
  ]
JSON

Using COPY statement WITHOUT returning inserted data:

curl http://localhost:3000/batch/prest/public/readings \
  -H "Content-Type: application/json" \
  -H "Prest-Batch-Method: copy" \
  -d @- << JSON
  [
    {"time": "2020-10-17T20:19:30+00:00", "device_id": "demo000000", "battery_level": 43, "battery_status": "discharging", "battery_temperature": 89.8, "bssid": "01:02:03:04:05:06", "cpu_avg_1min": 28.84, "cpu_avg_5min": 16.9047812612903, "cpu_avg_15min": 10.8993036332756, "mem_free": 420023054, "mem_used": 579976946, "rssi": -40, "ssid": "demo-net"},
    {"time": "2020-10-17T20:19:30+00:00", "device_id": "demo000001", "battery_level": 27, "battery_status": "discharging", "battery_temperature": 89.3, "bssid": "A0:B1:C5:D2:E0:F3", "cpu_avg_1min": 4.89, "cpu_avg_5min": 6.63334573320236, "cpu_avg_15min": 9.25968056754939, "mem_free": 717784757, "mem_used": 282215243, "rssi": -41, "ssid": "stealth-net"},
    {"time": "2020-10-17T20:19:30+00:00", "device_id": "demo000002", "battery_level": 29, "battery_status": "discharging", "battery_temperature": 93.7, "bssid": "A0:B1:C5:D2:E0:F3", "cpu_avg_1min": 8.29, "cpu_avg_5min": 6.78591150918263, "cpu_avg_15min": 7.37546420066158, "mem_free": 634081377, "mem_used": 365918623, "rssi": -54, "ssid": "stealth-net"},
    {"time": "2020-10-17T20:19:30+00:00", "device_id": "demo000003", "battery_level": 14, "battery_status": "discharging", "battery_temperature": 93.1, "bssid": "01:02:03:04:05:06", "cpu_avg_1min": 8.83, "cpu_avg_5min": 8.18492270691781, "cpu_avg_15min": 11.3986054360923, "mem_free": 563352328, "mem_used": 436647672, "rssi": -30, "ssid": "demo-net"},
    {"time": "2020-10-17T20:19:30+00:00", "device_id": "demo000004", "battery_level": 58, "battery_status": "discharging", "battery_temperature": 93.2, "bssid": "22:32:A2:B3:05:98", "cpu_avg_1min": 8.79, "cpu_avg_5min": 10.3900175308572, "cpu_avg_15min": 13.5103326842724, "mem_free": 642162250, "mem_used": 357837750, "rssi": -62, "ssid": "demo-5ghz"}
  ]
JSON
PreviousAmazon RedshiftNextYugabyteDB