Creating indexes for release 25.2.1

In Allure TestOps version 25.2.1, a one-time execution of scripts is introduced to create indexes on two of the largest tables in your database: test_result and test_result_parameters. This is necessary to improve Allure TestOps performance.

These scripts are executed during the upgrade of the Allure TestOps instance. The execution time depends on:

• size of the tables in your database;
• number of queries to these tables;
• performance of the host running your database.

To avoid downtime of your instance, we recommend running the scripts in advance, before installing version 25.2.1, during off-peak hours. In this case, the scripts will not be executed again after the upgrade.

Check available storage space in your database

Creating indexes is a resource-intensive process. Please make sure that the storage used by your database has enough free space:

1. Calculate the size of the test_result and test_result_parameters tables in your database.

   For example:

   SELECT
    pg_size_pretty(pg_total_relation_size('test_result')) AS test_result_size;
   

   SELECT
    pg_size_pretty(pg_total_relation_size('test_result_parameters')) AS test_result_parameters_size;
   

2. Calculate the amount of free space required to create the indexes:

   test_result_size * 0.22 + test_result_parameters_size * 0.8 + storage_space * 0.4

   where:

   test_result_size — size of the test_result table;

   test_result_parameters_size — size of the test_result_parameters table;

   storage_space — total size of your database storage.

3. Make sure that the required free space is available.

Run scripts

Run the following scripts to create the indexes:

CREATE INDEX CONCURRENTLY IF NOT EXISTS test_result_params_tr_id_inc_value_name_hidden_excluded_idx
  ON test_result_parameters USING btree(test_result_id)
    INCLUDE (value, name, hidden, excluded);

CREATE INDEX CONCURRENTLY IF NOT EXISTS test_result_launch_id_status_hidden_idx
   ON test_result USING btree (launch_id, status, hidden);

CREATE INDEX CONCURRENTLY IF NOT EXISTS test_result_history_key_launch_id_idx
  ON test_result USING btree (history_key, launch_id);

CREATE INDEX CONCURRENTLY IF NOT EXISTS test_result_launch_id_external_hidden_inc_tc_id_status_idx
  ON test_result USING btree (launch_id, external, hidden)
    INCLUDE (test_case_id, status);