Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 6 Next »

Data Model

Data model for CPS Temporal is limited to one entity to store configuration and state data with its timestamp.

The store keeps a new data instance each time a new data observation is made for a given dataspace, schema set and anchor.

Timestamp is used to partition time-series data.

Implementation

PostgreSQL database is proposed to implement CPS Temporal data store.

Timescale PostgreSQL extension is proposed for time-series data implementation.

By implementing PostgreSQL and its Timescale extension, CPS Temporal benefits from:

  • Time-series data features on top of standard well know Postgres database
  • All PostgreSQL relational database features
  • CPS previous experience with PostgreSQL (default reference implementation for CPS Core)
  • PostgreSQL JSONB data type for configuration and state data


Detailed schema information:

cpstemporaldb=# \d network_data
                      Table "public.network_data"
   Column   |           Type           | Collation | Nullable | Default
------------+--------------------------+-----------+----------+---------
 timestamp  | timestamp with time zone |           | not null |
 dataspace  | character varying(255)   |           | not null |
 schema_set | character varying(255)   |           | not null |
 anchor     | character varying(255)   |           | not null |
 payload    | jsonb                    |           | not null |
 version    | timestamp with time zone |           | not null |

Indexes:
    "network_data_dataspace_schema_set_anchor_timestamp_idx" UNIQUE, btree (dataspace, schema_set, anchor, "timestamp" DESC)
    "network_data_dataspace_anchor_timestamp_idx" btree (dataspace, anchor, "timestamp" DESC)
    "network_data_dataspace_schema_set_timestamp_idx" btree (dataspace, schema_set, "timestamp" DESC)
    "network_data_payload_idx" gin (payload)
    "network_data_timestamp_idx" btree ("timestamp" DESC)


cpstemporaldb=# SELECT * FROM timescaledb_information.dimensions;
 hypertable_schema | hypertable_name | dimension_number | column_name |       column_type        | dimension_type | time_interval | integer_interval | integer_now_func | num_partitions
-------------------+-----------------+------------------+-------------+--------------------------+----------------+---------------+------------------+------------------+----------------
 public            | network_data    |                1 | timestamp   | timestamp with time zone | Time           | 1 day         |                  |                  |
(1 row)

Indexes above are designed to optimize following queries

  1. By dataspace, schema set, anchor. Order by timestamp desc.
  2. By dataspace, anchor. Order by timestamp desc.
  3. By dataspace, schema set, payload content. Order by timestamp desc.

For 3. both indexes "network_data_dataspace_schema_set_timestamp_idx" and "network_data_payload_idx" are combined to handle the query. See attached explain plan.

References




  • No labels