Does ThingWorx PostgreSQL has 500K records limit?

Forum|Forum|9 years ago
December 13, 2016
18 replies
7457 views

As Thingworx Neo4j(inbuilt) has 500K data table records limitation. Does it also apply for thingworx PostgreSQL (inbuild) as well?

Install-Upgrade

P

posipova

20-Turquoise

It does not have such a limitation. It is also not true that Neo4j has such a limitation, however, the Neo database should be kept below 50GB in size because after that point querying and database backups become significantly slower.

E

esirohi

Author

1-Visitor

Thanks for the clarification, Polina.

I am not bothered much about 50 GB data size as of now because by the time this limit will be reached, Thingworx platform will be enhanced (in terms of underlying architecture) for better performance.

Another query, Do we expect the support of Neo4j (inbuilt) database in future as well and till when?

W

wposner-2

1-Visitor

I wouldn't store anything in a datatable if you know you're going to have more than 10,000 rows. Use a proper relational database and create stored procedures and sql query services to access your data. I've got extensive experience in TWX performance issues when datatable sizes increase beyond a certain threshold. The size limitation varies depending on the number of columns.

E

esirohi

Author

1-Visitor

Hi Wayne,

As you have extensive experience on performance issue due to large data size, Could you please help us in understanding what would be the criteria switching from the thingworx inbuilt database to an external database like, size of data, minimum number for columns, etc.

I believe, fetching large data from external database using multiple tables would also cause performance issues for multiple concurrent requests.How would we overcome this problem when going for external database. Please guide.

One query, I have installed and setup Postgres external database. How do I port my existing data from the thingworx inbuilt Postgres database to external Postgres database. Is there a way to access the internal database?

P

PaiChung

22-Sapphire I

10,000 rows is not 'a lot' of data.

To understand this a little better.

Thingworx is designed to store all DataTable data in a single (underlying database H2/PostGres and yes even Cassandra) table structure. It has therefor limited indexing, I'm checking this in PostGres and Streams have an ID and TimeStamp, DataTables have an ID and some sort of composite index on the JSON Blob Values field. The rest of the information is stored as a JSON Blob (also true for Streams, Wiki and Blog entries)

This means that when you query from these data structures, for a Stream it first does a datetime filter, brings back the records, reconstructs the information in an actual infotable and then applies the remainder of your query if any. Not exactly sure how the DataTable index works

I believe the DataTable indexes may be kept on the side as Solar indexes - not sure since I see an Index on Values in the schema.

However it is true that the performance of DataTables isn't that good, Streams actually perform much much better and you can get reasonable performance out of streams that have millions of records.

DataTables have their limit not at 10K rows but more so in the 250-500K range.

Depending on how much data and especially what you need to do with it, an additional Relational Database (even another PostGres) and then using your own table design and a JDBC connection can speed up performance a LOT because you'll be leveraging another Server to do database side processing of your query before it comes back to Thingworx.