Explorer Database Investigation and Scalability

[victaphu]

Description

Analyse and suggest restructure of explorer database

Context

Explorer had scalability issues which caused it to use a large amount of resources. Harmony engaged with Russel to analyse the database structure and provide some recommendations to improve the scalability. The following tasks were performed:

1. Restructure the schema to direct the references using numeric keys than string. (20 hours)
2. Restructure indices as per the changes in schema (20 hours)
3. Analyse queries to avoid string searches, traverse each operations via numeric keys. Same goes with updates. (10 hours)
4. Scripts to migrate data to local, data export and import (16 hours)
5. Apply changes and analyse cost. (16 hours)
6. Review and revise the changes. (8 hours)
7. Achieved 20% gain in query speeds in a 1 million disparate datasets ( unrelated) (10 hours)

Acceptance Criteria

• [ ] Restructure the schema to direct the references using numeric keys than string. (20 hours)
• [ ] Restructure indices as per the changes in schema (20 hours)
• [ ] Analyse queries to avoid string searches, traverse each operations via numeric keys. Same goes with updates. (10 hours)
• [ ] Scripts to migrate data to local, data export and import (16 hours)
• [ ] Apply changes and analyse cost. (16 hours)
• [ ] Review and revise the changes. (8 hours)
• [ ] Achieved 20% gain in query speeds in a 1 million disparate datasets ( unrelated) (10 hours)

Reward

@sophoah as discussed here is the bounty for the work that was done by Russel. Please review

[sophoah]

can we move that under tracking repo instead, also I would involve @hypnagonia.

Right now my concern is that I didn't see any of those deliverables hopefully @hypnagonia did