openfoodfacts-server icon indicating copy to clipboard operation
openfoodfacts-server copied to clipboard

Consider moving to a relational data model, like Postgres

Open john-gom opened this issue 1 year ago • 6 comments

Problem

Currently the OFF data is in a lot of different places (taxonomy files, MongoDB, STO files) which makes it difficult to perform queries across the data sets.

Aggregated queries against MongoDB are also very slow and the author feels these would be considerably faster against a relational model

Proposed solution

Move to a relational model

### Tasks
- [x] Create a data model and import taxonomies
- [x] Import prodcts as JSONB initially
- [x] Create GraphQL API for queries (Postgraphile)
- [x] Create a partial relational model for Products
- [ ] Consider a relational model for external data (AGRIBALYSE and CIQUAL) and import data
- [ ] Create relationships between taxonomies, products and external data
- [x] Implement full text search
- [x] Test some example aggregations for performance
- [ ] Clean up taxonomy id normalisation
- [ ] Ability to re-export as taxonomy files
- [ ] Validate existing taxonomies
- [ ] Develop a migration plan

Part of

  • #5527

john-gom avatar Jun 28 '23 08:06 john-gom