Determining the Correct Data Store Option
Determining the data store option is specific to your solution. You may have to consider the process and cost of the data store option that you choose for your solution. There are multiple data store options available:
• The internal database that ThingWorx uses, for example, PostgreSQL, MS SQL, or SAP HANA. Usually, PostgreSQL performs well up to an estimated 500 GB of data storage.
 |
H2 is only supported for non-production instances because they do not support large amount of data.
|
• Use DataStax Enterprise (DSE) when the writes per second exceeds the limitations of the internal database that ThingWorx uses. Refer to the ThingWorx sizing calculator. If you have completed sizing of your ThingWorx server, you can review it here.
|
|
As of version 8.5.0 of ThingWorx Platform, DSE is no longer for sale and will not be supported in a future release. Reference the End of Sale article for more information.
|
• Use any data storage server that provides a JDBC connector. The ThingWorx Platform can use JDBC drivers to connect to a relational database such as Oracle.
|
|
Persistence providers enable the connection to a data store and the ability to perform a CRUD operation on that data. Persistence providers are an entity type in ThingWorx. For more information about Persistence providers, see ThingWorx Model and Data Best Practices guide.
|
The following table provides recommendations for the data store to use depending on the type of data in your solution:
|
Data Store Option
|
Type of Data
|
||
|---|---|---|---|
|
Relational Database
|
• Data that uses complex SQL such as joins, cursors, temporary tables, and so on.
• For large data sets of non-time series data that has a loose relationship between the data that needs to be used and executed on the database server.
|
||
|
ThingWorx Platform data storage, such as PostgreSQL, MS SQL, or SAP HANA
|
Simple historical data, time series data, or data that does not require compounding or recursive calculations.
|
||
|
DataStax Enterprise
|
For large data sets of non-time series data that has sequential data and does not need the data to be executed on the database server.
|
Best Practices for Choosing Your Data Store Option
Use the following best practices to choose your data store option:
• Use an external data store option if you need to perform any of the following tasks:
◦ Query streams or value streams for a large amount of data
◦ Query results with millions of rows
◦ Implement database-level custom logic and calculations, for example: stored procedures, triggers, joins, relational data queries, and so on.
◦ Use ThingWorx as a data lake
◦ Integrate with third-party analytics and reporting tools
◦ Add additional indices and database infrastructure
|
|
It is recommended that you archive historical data that is not frequently accessed.
|
• Decide how much data you need to store. If data can be stored in small data sets (around 100,000 records or less), use the ThingWorx Platform storage.
• Decide what kind of data your solution has.
• How frequently you query your data affects the performance of the solution. Depending on how frequently you want to query your data, it is recommended to use either of the following options:
◦ To query data frequently, use a JDBC connector to query the database. You can use this JDBC connector with PostgreSQL or DataStax Enterprise.
|
|
As of version 8.5.0 of ThingWorx Platform, DSE is no longer for sale and will not be supported in a future release. Reference the End of Sale article for more information.
|
◦ If you do not need to retrieve historical data very frequently, you can move all data to a separate ThingWorx instance (in a data table or stream) or to a database server.