[UXPROD-4513] Review and implement table key constraints and indexes in Agreements Created: 17/Oct/23  Updated: 01/Feb/24

Status: Open
Project: UX Product
Components: None
Affects versions: None
Fix versions: Quesnelia (R1 2024)

Type: New Feature Priority: TBD
Reporter: Owen Stephens Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: agreements, erm
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original estimate: Not Specified

Issue links:
Cloners
is cloned by UXPROD-4514 Review and implement table key constr... Open
Defines
is defined by ERM-3072 Identify and implement indexes that s... Closed
Release: Quesnelia (R1 2024)
Development Team: Bienenvolk
PO Rank: 0

 Description   

Current situation or problem:

Some tables in Agreements are missing indexes that could increase performance in multiple areas including local KB data ingest and searching

In scope

  • Firstly all foreign keys should have a foreign key constraint, and I think there are ones that do not. I haven't changed these because that has wider implications for existing data (we have been bitten by this recently).
  • Check for any tables missing primary keys and fix (including alternate names table)
  • All foreign keys should be indexed and should be the default index type from postgres (Btree). You can use liquibases CreateIndex
  • If a table is a resolution of a many to many and has 2 foreign keys you should add a composite index (specify both columns in the create index entry). Order of columns here can be important, and the general rule is anything used in a join or where clause more frequently should be defined first.
  • There is no need to add indexes for the columns individually as well, unless you need a free text as well as a composite match, in which case add a GIN as below for the single field as well
  • Anything you search on using the text box should have a GIN index. This requires a special kind of entry in the migrations.

 
When adding foreign key constraints need to handle the situation where people have removed a foreign reference otherwise there will be issues when the migration  to add the constraint attempts to run.
 


Generated at Fri Feb 09 00:40:29 UTC 2024 using Jira 1001.0.0-SNAPSHOT#100246-sha1:7a5c50119eb0633d306e14180817ddef5e80c75d.