TimescaleDB databases state history

Contents

TimescaleDB databases state history

Allows to store item states history in Timescale/PostgreSQL database. Unlike SQL databases state history this service provides a dedicated connector, optimized for speed and database size.

Note that the service can store numeric item state values only.

The service provides unified database EAPI.

Timescale Installation

See https://docs.timescale.com/self-hosted/latest/install/installation-linux/

The service creates a table for event data called state_history_events and automatically tries to convert it to hyper-table (see https://docs.timescale.com/api/latest/hypertable/create_hypertable/).

If the service is unable to init the hyper-table, a warning message is written in the node log. The problem may be e.g. the table already contains events data. In this case, connect to the database and perform initialization manually:

Warning

The operation blocks the events table and may consume a lot of time for large databases.

SELECT create_hypertable('state_history_events', 't', migrate_data => true);
ALTER TABLE state_history_events SET
    (timescaledb.compress,
    timescaledb.compress_orderby = 't DESC',
    timescaledb.compress_segmentby = 'oid_id');
SELECT add_compression_policy('state_history_events', INTERVAL '1d');

To check that the events hyper-table has been initialized properly, use the following request:

SELECT * FROM timescaledb_information.hypertables WHERE
    hypertable_name='state_history_events';

Modifying compression policy

The default compression policy is set for 1 day. To modify it, connect to the database and use the following commands (the example sets compression policy to 7 days):

SELECT remove_compression_policy('state_history_events');
SELECT add_compression_policy('state_history_events', INTERVAL '7d');

To check compression effectiveness, use the following command:

SELECT before_compression_total_bytes AS before,
     after_compression_total_bytes AS after,
     (before_compression_total_bytes::DOUBLE PRECISION
         /after_compression_total_bytes)::DECIMAL(100,2) || 'x'
     AS "compression rate"
     FROM hypertable_compression_stats('state_history_events');

Setup

Use the template EVA_DIR/share/svc-tpl/svc-tpl-db-timescale.yml:

# Event-to-TimescaleDB service
command: svc/eva-db-timescale
workers: 2
bus:
  path: var/bus.ipc
config:
  # database URI
  db: postgres://USER:PASSWORD@HOST/DB
  # event buffer time-to-live, seconds
  buf_ttl_sec: null
  # periodically submit all item states
  interval: null
  # do not submit remote disconnected items (useful for zfrepl or similar)
  # ignore real-time events
  skip_disconnected: false
  ignore_events: false
  # keep records (seconds)
  keep: 604800
  # automatically cleanup obsolete OIDs (can be slow)
  #cleanup_oids: true
  # event queue size
  queue_size: 8192
  # panic on critical errors in X seconds after happened (0 - panic immediately)
  panic_in: null
  # database pool size
  #pool_size: 2 (default = workers)
  # item OIDs / OID masks
  oids:
    - "#"
user: nobody

Create the service using eva-shell:

eva svc create eva.db.timescale1 /opt/eva4/share/svc-tpl/svc-tpl-db-timescale.yml

or using the bus CLI client:

cd /opt/eva4
cat DEPLOY.yml | ./bin/yml2mp | \
    ./sbin/bus ./var/bus.ipc rpc call eva.core svc.deploy -

(see eva.core::svc.deploy for more info)

EAPI methods

See EAPI commons for the common information about the bus, types, errors and RPC calls.

state_history

Description	Gets item state history
Parameters	required
Returns	State history payload

Parameters
Name	Type	Description	Required
i	String	Item OID	yes
t_start	f64	Beginning timestamp (default: last 24 hours)	no
t_end	f64	Ending timestamp (default: now)	no
fill	String	Fill (nS/T/H/D/W e.g. 10T for 10-minute, requires ts_extension)	no
precision	u32	Round values to digits after commma	no
limit	u32	Limit records to	no
xopts	Map<String, String>	Extra, not used for SQL databases	no
compact	bool	Pack data in arrays according to type	no

Return payload example:

[
    {
        "status": 1,
        "t": 1652059860.0424938,
        "value": 15
    },
    {
        "status": 1,
        "t": 1652059865.045223,
        "value": 15
    },
    {
        "status": 1,
        "t": 1652059870.0452943,
        "value": 15
    },
    {
        "status": 1,
        "t": 1652059875.0443518,
        "value": 15
    }
]

state_log

Description	Gets item state log
Parameters	required
Returns	State log payload (includes OIDs, as other svcs may support get-by-mask)

Parameters
Name	Type	Description	Required
i	String	Item OID, supports ending masks (e.g. sensor:group/#)	yes
t_start	f64	Beginning timestamp (default: last 24 hours)	no
t_end	f64	Ending timestamp (default: now)	no
limit	u32	Limit records to	no
xopts	Map<String, String>	Extra: offset=N for query offset	no

Return payload example:

[
    {
        "oid": "sensor:tests/temp",
        "status": 1,
        "t": 1652060175.0443184,
        "value": 15
    },
    {
        "oid": "sensor:tests/temp",
        "status": 1,
        "t": 1652060180.046056,
        "value": 15
    },
    {
        "oid": "sensor:tests/temp",
        "status": 1,
        "t": 1652060185.0454304,
        "value": 15
    }
]

state_push

Description	push item states into db, (payload: single item state or list)
Parameters	none
Returns	nothing