openfoodfacts-server
openfoodfacts-server copied to clipboard
Consider moving to a relational data model, like Postgres
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