Model the eQTL data in MongoDB for efficient loading and retrieval

[karatugo]

We need to design and implement an efficient data model for storing eQTL (expression Quantitative Trait Loci) data in MongoDB. The goal is to ensure that the data can be loaded quickly and retrieved efficiently to support API queries and downstream analyses. The data model should handle large datasets, optimize for query performance, and accommodate the specific structure of eQTL data.

[karatugo]

Had a couple of meetings with the DBA team. They had a few options based on the API usage patterns. So we asked Kaur if he's aware of such patterns.

[karatugo]

Next action item is to choose one of the database design options based on Kaur's feedback.

[karatugo]

In our meetings with the DBA team, we identified key API usage patterns. Additionally, thanks to Kaur's feedback, my analysis of old Kubernetes logs supports these patterns:

API Usage:

1. Type 1: Search by study.
2. Type 2: Search by specific field (e.g., gene ID, rs ID, variant) within a study.
3. Type 3: Search by specific field across all studies.

Karthick suggested a few approaches for database design, and we decided to proceed with the second approach:

Approach 2: Individual Collections per Study

• Description: Create a separate collection for each study, totaling 42 collections so far.
• Pros:
  • Optimized load performance.
  • Efficient for Type 1 (search by study) and Type 2 (search within a study) patterns.
• Cons:
  • Complex implementation for Type 3 searches (across all studies), requiring application-side aggregation.

We acknowledge the challenges of handling Type 3 searches. In the future, we may consider an asynchronous approach (e.g., process the request and notify users via email upon completion).

[karatugo]

MongoDB model implemented as discussed above.