How to: Spring Boot application with a large database and fast access to a small subset of data

Question

This is a request for a general recommendation about how to organize a data storage in my case.

I'm developing a Spring Boot app in Java to collect and save measurements, and provide access to the saved data via REST API. I expect to have around 10 millions measurements per hour and I need to store history for recent 2-3 months. Total amount of measurements stored can reach tens of billions. The data model is not sophisticated, there will be around ten tables. No editing is planned, only cleaning obsolete data and vacuuming. I'm planning to use Postgres as a DBMS.

Being stored, the data can be retrieved as such (using temporal or spatial filters) or used to create aggregated data products. Despite performance tuning, using indexes, and optimizing queries, data retrieval can take significant time, but this is for research purposes and I understand the price of having that amount of records. Up to this point things are clear.

On the other hand, the most recent measurements (e.g. collected during the last ten minutes) must be accessible immediately. Well, as fast as possible. This data must be served by the REST API and shown in a Front-End app as graphs updated in real-time. Obviously, retrieving last-minutes-data from a table with billions of records will take time that is unacceptable for representation.

What can be a typical solution for such situation?

So far I came up with an idea of using two datasources: Postgres for history and in-memory H2 for keeping recent data ready to be served. Thus I will have a small DB duplicating recent data in memory. With this approach I expect to re-use my queries and entity classes. Does this seem OK?

Answer 1

I found a multi-datasource solution that perfectly matches my case. The author of this article is dealing with a project "where an in-memory database was needed for the high performance and a persistent database for storage".