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. (Please find attachment for the high level architecture)
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; We don't prefer REDIS or Other commercial software for Caching.
Usecase how it should:
1. USER1 runs the query for the first time from Application Layer (example select * from emp; (100 records))
2. Application layer checks in caching layer if the query data exist; if not than goes to database and cache the data in caching layer and results sent to Application layer
3. USER2 runs a query (select * from emp where dept_no=10) returns 15 records
This query should get the data from the cache rather than database : FILTERING
4. Database updates with new inserts now we have 18 records for dept 10
5. User3 runs the same query the data should be fetched from database replace the old cache with new caching data.
6. Once the data is in Cache any user running the same query should get data from Cache rather than database unless the underlying database records changes.
9 freelancer đang chào giá trung bình $252 cho công việc này
I have been doing in memory caching in my all projects for data once database call is done and using data from cache. If any database update trigger called then updating cache as well.