Q:

Use in-memory database or search engine for read only to make website faster

I have an application which require doing a lot of queries for every single page which make it slower.
I have tried to use cache systems (Redis) but implementation are little bit complicated and will take time as every query depend on a lot of dependencies.
I'm think about have another clone of database in memory which should be read only but every time database update or insert happened should update the memory rows? do you think this will be effective and practical and if so is there are any current way to achieve this as i'm using postgres.
My second idea was to use something like sphinx search engine for all queries and in this case i will need to use real-time indexes.
Is this working or there are a better solution.
Notice: database size is about 50GB and i can get memory for it all.
Regards,,,

A:

Generally it is not a smart idea. See, databases are written by smart people and use as much memory for caching as possible. If you hit the disc too often, then you have a memory problem - and keeping ANOTHER copy in the same limited memory will not make things better.
What someone should do is baseline analysis. What is the bottleneck? Fix it. More memory, SSD are typical steps here. What are the most expensive queries? Experience tells me you likely have some hammers in there that do a lot of damage, and the problem is not "too many queries" but "what the heck is an index".
But an in memory database normally should not make a difference because a database worth anything already uses as much memory as it can as cache. That is - naturally - unless some admin puts in a ridiculously low limit to "save the memory" and then has performance problems.
And then you really should start with caching.

