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,,,