Re: Does anyone use in ram postgres database?

On Thu, Mar 25, 2010 at 12:42 PM, Chris Barnes
<compuguruchrisbarnes(at)hotmail(dot)com> wrote:
>
>
>   We are testing in memory postgres database and have questions about
> configuring the ram mount point and whether there is great gains in setting
> it up this way? Are there any considerations for postgres?
>
>   If you have experience, can you please give us some ideas on how you have
> accomplished this?

These questions always get the first question back, what are you
trying to accomplish? Different objectives will have different
answers.

A common answer to these issues is to put some small but heavily used
tables into ram, and leave the rest on the hard drive. Easy enough:

(as pg unix user)
mkdir /dev/shm/pgram
psql postgres
create tablespace test location '/dev/shm/pgram';
set temp_tablespaces test;

And now all temp tables are in ram.

Now, if your pg_xlog directory is a problem, then you either need
bigger faster hard drives, or your data is more transient in nature
and you can recreate it and you put the whole db into RAM.

Note that the query planner wasn't designed with RAM as the storage
space for pg, so it might make some bad decisions until you adjust
postgresql.conf to stop that. and then it still might make some bad
decisions.