Comparison of ThingWorx Database Options (taken from KCS Article CS257403)

[ttielebein]

• Embedded databases come with the installation of the ThingWorx Platform
  • No additional installation or configuration is required for embedded databases
  • Read about the various benefits and pitfalls of embedded versus external below
• Database Options
  • H2
    • RDBMS (relational database management system), written in Java
    • Has a small memory footprint
    • Embedded into ThingWorx for easy installation
    • Not as robust as other database options
    • Not scalable in production environments (unless used alongside a separate, external database for stream, value stream, and other data) ​
      • See KCS Article CS243975 for further reading on the use of external databases
    • Meant to be used for quick deployments and testing environments
  • PostgreSQL
    • ORDBMS (object-relational database management system), written in C
    • PostgreSQL is the ThingWorx recommended database for production systems
    • More Robust
      • External database installed separately from ThingWorx
      • Beneficial because external databases can be specifically configured for use in production, while embedded databases cannot
      • Able to efficiently handle larger amounts of data and store more data without affecting ThingWorx system performance
    • Greater Stability
      • Recover from data corruptions more easily by accessing the database from an external application (separate from ThingWorx) using simple SQL statements
      • Easier to back-up the database in case of issues (further reading in KCS Article CS246598)
      • Less risky and simpler upgrade procedure, which occurs "in-place"
        • Instead of exporting and importing data and entities, a simple schema update allows these to automatically persist into the new version
        • If ThingworxStorage folder is accidentally deleted, entities and data are secure in the external database
    • More Secure
      • HA (High Availability) allows for multiple server instances at different locations in the network
        • Assists in time of failover, i.e. if one server fails, the other can immediately take over
        • Secures the data and prevents further data loss in the event of a failure
      • Customizable security settings and complex password requirements
      • Fewer security vulnerabilities than other databases
    • Because Postgres is an external database, it can be harder to install
      • Follow the steps in the installation guide closely
      • See KCS Articles CS235937 and CS230085 for troubleshooting and help with installation and configuration
  • Hana
    • RDBMS (relational database management system)
    • In-memory, column based data storage
    • For more information on this database, please see the Getting Started with SAP HANA Guide
  • Neo4J
    • GDBMS (graph database management system), written in Java
    • Data is not easily accessed by external applications, and CQL must be used instead of SQL, making recovery from corruptions very difficult
    • Embedded database with limited configuration options
    • Known to have issues with deadlocks
    • Deprecated in version 7.0 (related KCS Article: CS228537)
• For full installation steps for H2 and PostgreSQL, see the ThingWorx Installation Guide

[geva]

It should be noted that ThingWorx supported database options (model and data) have evolved to best fit with our customers requirements.  Going forward you should be looking at either PostgreSQL or Microsoft SQL (or Azure PaaS versions of both), and InfluxDB for high-volume/speed time-series data ingestion.