What DBMS will provide the minimum access time to unheated data?

Question

PostgreSQL

Good afternoon!
I want to ask for advice on this situation. There is data in the amount of approximately 40-50 million rows (the size on the disk with indexes is about 170 GB). The data is rather static (growth - 1-10 thousand new records per week). Initially, I loaded this data into PostgreSQL, but there is a warm-up problem here. The first request is executed up to 30-40 seconds, the second about 5 seconds, and then 150-300 ms.
It is unrealistic to warm up the cache before work. Periodically, new clients come in who run various online demos from the site. And here it is important to make a first impression - the first request should already be processed quickly, well, or at least in 1-2 seconds.
Partitioning tried - fundamentally the situation does not change. Shared Buffers set to 6 GB - also did not help. The question is what to do next? I have the following options in my head:
1. Create a copy of the data on the disk, structure it into folders and simply read the recorded "selections" from the disk. I basically did this, and in certain cases - this is the coolest and fastest solution. But it does not allow you to make selections by conditions.
2. Use something like Elsaticsearch. So far, everything I've read about her has been satisfactory. It's even nice that there is a search by geodata (find objects no further than 25 km from such and such a point). But I heard that it also has a warm-up, but I did not find how fast it is.
3. Apply in-memory databases, such as Redis. But this increases the cost of the solution, since you have to buy a second server. And the problem is that I do not have a problem of service growth, but a problem of a small number of users for a huge array of data.
4. Apply Sphinx. There I never read about the problem of warming up, but maybe I just didn’t find it.
What are the standard solutions for this situation?

Answer 1

if the selection is simple and the index is more of a disk access problem, put the database on the ssd

Answer 2

Create an additional partition with tmpfs and every time you start the server, synchronize the database to this partition. But the entire database won't fit, so select a partition that will fit into the partition. And read from this section. Of course, this is also a warm-up, but it only happens when the server is restarted.