Details
-
Task
-
Resolution: Fixed
-
P1: Critical
-
None
-
None
-
None
Description
Currently in the postgres database I store "test_names" in the following table:
CREATE TABLE IF NOT EXISTS test_names ( test_name_n integer PRIMARY KEY NOT NULL GENERATED BY DEFAULT AS IDENTITY, test_executable text NOT NULL, test_function text, test_datatag text, UNIQUE NULLS NOT DISTINCT (test_executable, test_function, test_datatag) );
Note the "UNIQUE" constraint, which adds an index on the three components of a test_name (test_executable,test_function,test_datatag).
This is based on the facts that the combination of these components is mostly stable, does not grow.
I now see that this is not true, there are data tags that are generated randomly and the list grows a lot with each integration. Thus this table gets too long and becomes a bottleneck after the index outgrows the memory.
I also see indexing (automatically by the UNIQUE constraint) on a combination of many text fields is expensive. The size of the index has grown larger than the size of the data.
Rethink and re-design the schema to perform better.