@slangley ，
The uploaded XML is just a simplified data table to demo the concurrent situation, the real case is the following :
My customer is a factory with about 50 production lines. Each production line has an average of 14 stations, each station has one device.
Each production line has 3 production shifts per day. Before each start of production, it is necessary to generate a start order. This start order includes the check items that each station needs to check before starting production. There are 8 check items for each station, which may be device parameters or manual checks. Once get data from device or the operator mark the check box of the check items, the result of the check item need to updated to Pass or Fail.
Therefore, the annual data size is as follows:
50 production lines * 14 stations * 8 inspection items * 3 shifts per day * 365 = 6 million
However, the official document shows that the data size of the Data Table is 300,000 to 500,000, so the Data Table is designed to be the start order level instead of the check item level:
| Field |
Data Type |
Primary Key |
| LineNumber |
STRING |
Yes |
| ShiftNumber |
STRING |
Yes |
| StationCheckItems |
JSON |
No |
The annual data records would be only:
50 production lines * 3 shifts per day * 365 = 54,750
The format of StationCheckItems is(for simplicity, only the main fields are listed.):
{
"Stations":[
{
"StationName": "",
"StationNumber": "",
"CheckItems": [
{
"CheckItemName": "",
"ExpectedFrom": "",
"ExpectedTo": "",
"ActualValue": ""
}
]
}
]
}
So the field StationCheckItems will be updated concurrently as it has multiple stations and check items. The easiest way is to add lock on update to mute each other , although this way affects the throughput somehow, it's not a problem in my case.
I also considered other approaches:
- Creating a separated Data Table for each production Line, I'm not sure if this way can mitigate performance pressure of data table since all of the data tables are in a same PostgreSQL table actually.
- Use separated persistent provider for each Line to reduce the amount of data in Data Table.
- Use JDBC (Database ThingTemplate) to update the field StationCheckItems directly(something like SELECT .... FOR UPDATE)
- Invent a lock method in Java service to serialize the update.
But all of these approaches are complex I think.
