Assume a text file with 150m unique records.
Each record has two columns: (1) string and (2) integer.
The string is a unique label, and the integer is the label's value.
The only query will return the integer value for a given label.
We are exploring multiple architectures for exposing this text file as an API.
This text file is regenerated every 72 hours. ~90% of the data remains the same across regeneration, but this regeneration is controlled by a 3rd party. We simply get a new text file every 72 hours.
We are aiming for query performance of 100ms - 500ms per read.
- Store the text file on disk. Query the text file. Cache queries in memory.
- Pros: Simple implementation. Easy to update data.
- Cons: Inelegant. Uncached read queries are slow.
- Parse the text file into a traditional/NoSQL database, with each line treated as a database record/document. Run queries against the database.
- Pros: Seems like standard architecture.
- Cons: Updating 150m database records is slow and seems wasteful, especially since ~90% of records remain the same.
- Use Redis or in-memory database to store the 5GB text file. Run queries against in-memory database.
- Pros: Fast queries. Easy to update data.
- Cons: Expensive.
- Use ElasticSearch to query records.
- Pros: ElasticSearch is designed for search.
- Cons: ES may be overkill for such simple queries.
Should we consider other architectures, or are there pros/cons we overlooked?
This engineering challenge seems common: what is the most "standard" architecture for balancing cost/performance when trying to produce fast reads against a data store of 150m records that change?