We have a project where we intend to log large amounts of data from a PLC. In the most extreme scenario we would be logging 175.000 data points every 3 seconds (which translates into 105GB of data / week).
Did anyone of you try to log this amount of data using Thingworx Streams? If so, what persistence provider is suitable?
Based on other comments I think we can rule Postgres out which leaves us with DSE or MS SQL. There may be a benefit for us to use MS SQL since the IT department has experience with those servers.
Please if you have tried something similar or have comments / ideas then I would be very interested in seeing your comments! Specially if there are some fundamental constraints that we should be aware of.
Re: MS SQL vs DSE storage for massive data collection
One of my client used PostgreSQL with same request before, once in a while they need to Vacuum the database to reassign some certain internal index to increase the performance. Comparatively, My SQL and DSE may be able to store more information, but on the long-run they still need to face the similar problem.
1. The way TWX store the data are the same, it create only one table for all the data coming to Stream\ Value Stream\ Datatable, My SQL and DSE might be able to store more and search faster, but there's still a upper limit.
2. How often do you need to retrieve and manipulate the data? Persistence Provider only help store the data, and data filtering is done by ThingWorx/Tomcat, performance is not much affected
For these problems, I suggest user consider [Additional Persistence Provider] and [External Database]
With [Additional Persistence Provider], new VS, Stream and Datatable can be assigned to it and expand the storage space.
With [External Database], you can keep a large amount of data, do the SQL queries in the Database side and send the result to TWX directly, save the effort of Tomcat.