Indexing Runtime Data in ThingWorx
All relational database systems such as PostgreSQL, MSSQL, and H2 provide the ability to index any field or a combination of fields in a table. Indexing is leveraged when using the FindDataTableEntries service. This improves the speed of data retrieval with query and search operations against the data. However, indexing requires additional storage space, as well as additional write operations during data ingestion, since the DB engine has to maintain additional data structures for each ingested row of data. ThingWorx uses Data Shapes and base types within the application layer. Therefore, the format that’s used to store the data is based on Data Shapes and base types rather than the data type of the relational tables. This allows ThingWorx to change the data types dynamically without changing the database schema and results in saving the data as JSON or Text blobs. The default indexing scheme used in ThingWorx is a result of attempting to balance write/read performance while taking the factors above into account.
Persistent Thing Properties Data
The data is stored in the property_vtq table in the database. Each property is stored in its own row in the table. In other words, the Thing properties are mapped to table rows rather than columns. This structure makes this table unsuitable to store historical data. The indexed fields are:
• id – ThingName, Thing
• name – Name of the property
Logged Thing Properties Data in Value Streams
The data is stored in the value_stream table in the database. Each property is stored in its own row in the table to map the Thing properties to table rows instead of columns.
The indexed fields are:
• entity_id – Value stream name
• source_id – Thing name
• property_name – Property name
• time – Timestamp of the entry
Data Table Data
The data is stored in the data_table table in the database. All field values are stored in a single row in the table, resulting in the fields defined in the associated data shape are mapped to a single column for each entry as a JSON string.
{"DataShape_Field1": "aaa", "DataShape_Field2": 2}
There are several indexes:
• entity_key - Primary key defined in the data shape associated with the data table.
• entity_key, entity_id - Data shape key, data table name.
• field_values – Indexing the JSON string.
Stream Data
The data is stored in the stream table in the database. All the field values are stored in a single row in the table. The fields defined in the associated data shape are mapped to a single column for each entry as a JSON string. The indexed fields are:
• entity_id –Stream name
• source_id – Thing name
• source_type – Entity type of the source (user, Thing etc.)
• time – Timestamp of the entry