zero-downtime-04-backfill-script

'use strict';
 
const Knex = require('/path/to/knex/initialization/file');
 
// Arguments that are passed through the command line
const [loopCount = 100, batchSize = 2500, timeoutDelay = 1000] = process.argv.slice(2);
 
const COLUMNS_TO_CHANGE = [
 ['table1',json_col],
 [table2,json_col],
 [table3,json_col],
 [table4,json_col],
];
 
const limit = batchSize;
 
function updateRows (Knex, table, json_col, jsonb_col) {
 
 return Knex.raw(`
   UPDATE ${table}
   SET
     ${jsonb_col} = ${json_col}::jsonb
   WHERE id IN (
     SELECT id
     FROM ${table}
     WHERE ${jsonb_col} IS NULL
     AND ${json_col} is NOT NULL
     LIMIT ${limit}
   )
 `)
 });
}
 
async function backfillJsonbColumns () {
 let count = loopCount;
 let rowCount;
 for (const tableColumnPair of COLUMNS_TO_CHANGE) {
   let response;
 
   // rowCount keeps track of number of rows changed for the batch (if rowCount = batch size, continue loop)
   rowCount = limit;
   do {
     response = await updateRows(Knex, tableColumnPair[0], tableColumnPair[1], tableColumnPair[1] + '_jsonb');
     rowCount = response.rowCount;
 
     // Set timeout for other transactions to execute
     await new Promise((resolve) => setTimeout(resolve, timeoutDelay));
     count--;
   } while (count > 0 && rowCount === limit);
 }
}
 
(async () => {
  await backfillJsonbColumns();
})();