Need to load the whole postgreSQL database into the RAM.

Question

How do I put my whole PostgreSql database into the RAM for a faster access?? I have 8GB memory and I want to dedicate 2 GB for the DB. I have read about the shared buffers settings but it just caches the most accessed fragment of the database. I needed a solution where the whole DB is put into the RAM and any read would happen from the RAM DB and any write operation would first write into the RAM DB and then the DB on the hard drive.(some thing like the default fsync = on with shared buffers in postgresql configuration settings).

Answer 1

Perhaps something like a Tangosol Coherence cache if you're using Java.

Answer 2

Hi,

I have asked myself the same question for a while. One of the disadvantages of PostgreSQL is that it does not seem to support an IN MEMORY storage engines as MySQL does...

Anyway I ran in to an article couple of weeks ago describing how this could be done; although it only seems to work on Linux. I really can't vouch for it for I have not tried it myself, but it does seem to make sense since a PostgreSQL tablespace is indeed assigned a mounted repository.

However, even with this approach, I am not sure you could put your index(s) into RAM as well; I do not think MySQL forces HASH index use with its IN MEMORY table for nothing...

I also wanted to do a similar thing to improve performance for I am also working with huge data sets. I am using python; they have dictionary data types which are basically hash tables in the form of {key: value} pairs. Using these is very efficient and effective. Basically, to get my PostgreSQL table into RAM, I load it into such a python dictionary, work with it, and persist it into db once in a while; its worth it if it is used well.

If you are not using python, I am pretty sure their is a similar dictionary-mapping data structure in your language.

Hope this helps!

Answer 3

instead of using 8 GB of memory (is it ECC-capable?), buy an SSD instead, your OS will become faster too, i think it will become cheaper this year or next year

Answer 4

With only an 8GB database, if you've already optimized all the SQL activity and you're ready solve query problems with hardware, I suggest you're in trouble. This is just not a scalable solution in the long term. Are you sure there is nothing you can do to make substantial differences on the software and database design side?

Answer 5

if you are pulling data by id, use memcached - http://www.danga.com/memcached/ + postgresql.

Answer 6

Set up an old-fashioned RAMdisk and tell pg to store its data there.

Be sure you back it up well though.