We are working on reporting tool. the purpose of the tool is simply visualising the data from underlying database. Although the basic functionality work; the challenges we face is when the query is executed multiple times it's slows down the performance.
What we plan to achieve is below
1. A Query executed from frontend reporting layer; the query will be bring the results from underlying database (postgresql)
2. The query results should be cached in memory layer (should be a open source technology)
3. When the same query is executed again the data should be fetched from result set cache rather than getting data from database
4. If the underlying data changed in database than result set should purge and new data should be fetched from database and cached in-memory; meaning the data in cache should be consistant with database
5. Should be able to filter from query results.
6. The cached results should never exceed the memory.
7. There should be a time out for every cached query so that we can clear the cache periodically.
8. We need to have an option to either cache the query or not
9. Any suggestions or improvements are welcome
What we provide is:
1) We will be providing a commons library we built for you to know the kind of object you would be receiving from the Application layer.
2) An developer environment (Amazon Workspace) with all the necessary tools installed
Please refer to the attachment