Database updates for upgrade to or through BSS 21.14

Original Publishing Date:

2023-07-28

The upgrade to BSS 21.14 brings several changes related to the OrderLog table in the BSS database:

a new column to the table,
a new view,
a new periodic task that will populate the new column: "OrderLog Bigint migration task". You can use this task to check the number of processed rows and rows to process.
Note: We recommend to monitor that this task does not fail after the upgrade.

After the upgrade to 21.14 or through this version, for example, from 21.12 to 21.15, you need to perform manual actions to create indexes.

Depending on whether you do this during a maintenance window or not, the exact steps will be different. Index creation can take significant time to complete depending on the OrderLog table size, the infrastructure, the current database load, and the number of long running operations.. You can use a test query to learn approximate time required for your installation.

Calculating approximate time required for index creation

Note: The estimations are only valid for creating indexes within a maintenance window.

To measure the time needed for the index creation procedure, complete these steps:

Download the index_test.sql script to a workstation with access to the BSS database and psql utility installed.
Execute the script.
It will create a test table OrderLog_test, populate it with 20 million rows from the original OrderLog table and create indexes for them. It will also display how much time the operation took and the predictions for time required to create indexes in the OrderLog table.
After that, the script drops the test table.
If something went wrong, use the index_test_cleanup.sql that will just drop the test table.

Creating indexes within a maintenance window

To create indexes within a maintenance window after the upgrade to 21.14 or through that version, complete the steps below:

Download the create_indexes_NON_CONCURRENTLY.zip file and unpack its content to a workstation with access to the BSS database and psql utility installed.
Update the unpacked create_indexes_NON_CONCURRENTLY.sh file with the credentials to the database.
Note: During execution of this script, the OrderLog table will be locked for for insertions and updates. Make sure to run this script within a maintenance window to avoid data loss.
Execute create_indexes.sh.

The script will create indexes in the newly added view. It can run for several hours, depending on the OrderLog table size and the infrastructure.

Creating indexes outside of a maintenance window

Note: The estimations provided by the index_test.sql script are not valid for this index creation type.

To create indexes outside of a maintenance window after the upgrade to 21.14 or through that version, complete the steps below:

Download the create_indexes.zip file and unpack its content to a workstation with access to the BSS database and psql utility installed.
Update the unpacked create_indexes.sh file with the credentials to the database.
Execute create_indexes.sh.

The script will create indexes in the newly added view. It can run for several hours, depending on the OrderLog table size, the infrastructure, the current database load, and the number of long running operations.

Monitoring migration status

You can check the process of migration by checking the status and the output of the last task execution of the OrderLog Bigint migration task.

These are the metrics that are available there:

rowsUpdated. The number of migrated rows.
rowsRest. The number of rows yet to be migrated.
percentUpdated. Migration progress percentage.
isFinished. If true, this means the migration has completed.
pkIndexStatus. Indicates the status of the PK index. After indexes are created using the attached script, this metric value must be: "#2 Exists, OK, not PK".
viewIndexStatus. Indicates the status of the temporary view index. After indexes are created using the attached script, this metric value must be: "#2 Exists, OK".
Note: If the pkIndexStatus or viewIndexStatus metric have values other than "#2 Exists ...", or if they contain "INVALID", you need to create indexes by completing the above instructions one more time.

During the next major CloudBlue update, the view will be deleted and necessary column renaming will be done. The newly added records will be added using the new view with the BigInt index column.