How to do "batch updates" in FROST-Server

[ssoper-usgs]

Hello all,

We have an upstream system that sends many updates to our FROST-Server implementation. I'm noticing a high level of commits and cpu usage occurring in our database, and one of the suggested ways of alleviating the issue is to limit the number of commits by perhaps batching them instead. If curious, the use case I'm seeing is described in this doc: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/apg-waits.xactsync.html

We currently launch a PATCH request against the SensorThings API for each changed Observation. Is it possible to launch a batched PATCH request against the SensorThings API such that a commit wouldn't take place until some number of Observations had been updated in the database?

Thanks in advance for any tips!

[hylkevds]

Yes, using the Batch Processing extension you can do batch requests that all get committed to the database in one go: https://fraunhoferiosb.github.io/FROST-Server/extensions/JsonBatchRequest.html
All requests with the same atomicityGroup will be in the same commit.

[ssoper-usgs]

@hylkevds - this solution worked well for our use-case, thanks for your guidance!

[ndevilleBE]

Dear @hylkevds ,
I noticed that when using batch request, a count sql command is performed for each operation in the batch code. Is it the intended behaviour? As the count is slow, it might be a possible improvement for performances?!
Below an example of my patch requets:

{
"requests": [
{
"id": "1",
"atomicityGroup": "Group1",
"method": "patch",
"url": "Observations(1171241930)",
"body": {
"resultQuality": 0
}
},
{
"id": "2",
"atomicityGroup": "Group1",
"method": "patch",
"url": "Observations(1171241960)",
"body": {
"resultQuality": 0
}
},
{
"id": "3",
"atomicityGroup": "Group1",
"method": "patch",
"url": "Observations(1171241984)",
"body": {
"resultQuality": 0
}
},
{
"id": "4",
"atomicityGroup": "Group1",
"method": "patch",
"url": "Observations(1171242012)",
"body": {
"resultQuality": 0
}
}
]
}

Thanks

[ndevilleBE]

ok.
But in the case of millions of patch, the small amount of time it takes adds up, that's why I though it might be an improvement.

[ndevilleBE]

Correct me if I'm wrong but the patch method does three queries:

select count(*) from "OBSERVATIONS" where "OBSERVATIONS"."ID" = $1

select "e0"."RESULT_BOOLEAN", "e0"."RESULT_QUALITY", "e0"."PHENOMENON_TIME_START", "e0"."PARAMETERS", "e0"."DATASTREAM_ID", "e0"."RESULT_STRING", "e0"."RESULT_TYPE", "e0"."VALID_TIME_END", "e0"."PHENOMENON_TIME_END", "e0"."FEATURE_ID", "e0"."ID", "e0"."RESULT_JSON", "e0"."RESULT_TIME", "e0"."RESULT_NUMBER", "e0"."VALID_TIME_START", "e0"."MULTI_DATASTREAM_ID" from "OBSERVATIONS" as "e0" where "e0"."ID" = $1 offset $2 rows fetch next $3 rows only

update "OBSERVATIONS" set "RESULT_QUALITY" = $1::json where "OBSERVATIONS"."ID" = $2

only the last one is effective.

[hylkevds]

It has to do the second one regardless, since the MQTT interface needs to (potentially) send the entire Entity, and the PATCH only contains the partial entity.
The first one I should be able to get rid of.

[ndevilleBE]

ok!
And the casting to json in the update function is automatic or my batch request is ill-formed?
"RESULT_QUALITY" = $1::json

[hylkevds]

That's automatic. The RESULT_QUALITY column is a JSONB column, so what goes in must be json, but PostgreSQL is flexible enough to also allow a single JSON-primitive (String, Number, Boolean or NULL) as a value.