UPDATE large number of rows in time series data

Question

UPDATE myTBL
SET
columnA = columnA/1000,
columnB = columnB/1000,
columnC = columnC/1000,
columnD = columnD/1000,
columnE = columnE/1000
WHERE timestamp between '2010-01-01 00:00:00' and '2020-10-07 23:59:59'

It is a production database. Any idea how do it in a way that it updates slowly and other application queries can run side-by-side?

Answer 1

SingleStore can run that really fast but other concurrent readers can run at the same time and they won’t block. We use multi-version concurrency control to allow this. So there may not really be any problem to solve, it should just work.

If you really have a problem, you can use resource governor to run the update and put a hard CPU limit on the connection where you run the update.