In memory Database for postgres

[aravind chandu <avin_friends@xxxxxxxxx>]

Hello,

               Thanks for your reply,but what I am actually looking for is database should be an in-memory database and at the same i want to store that data into disk so that data won't be lost when the system restarts or in case of power failure. Can you guys tell me the procedure how to do this? your help will he greatly appreciated.

Thanks,
Avin.

---

From: Emanuel Calvo Franco <postgres.arg@xxxxxxxxx>
To: John R Pierce <pierce@xxxxxxxxxxxx>
Cc: postgresql Forums <pgsql-general@xxxxxxxxxxxxxx>
Sent: Monday, April 13, 2009 11:54:45 AM
Subject: Re: In memory Database for postgres

2009/4/12 John R Pierce <pierce@xxxxxxxxxxxx>:
> aravind chandu wrote:
>>
>>            I created in-memory database but the problem is all the data
>> will be accessed from main memory .when ever the system is restarted the
>> entire data that is in the tables will lost.Is there any way to dump all the
>> data in to local hard disk before restarting the system or any similar
>> method to save the data in to a permanent storage.
>
> memory is volatile, disk is persistent.
>
> if you want persistent databases, I recommend storing them on disk.
>

ubuntu=# create table test_ram (i integer, name text) tablespace ram_space;
CREATE TABLE
ubuntu=# create temp table test_ram_temp (i integer, name text)
tablespace ram_space;
CREATE TABLE
ubuntu=# create temp table test_disk_temp (i integer, name text);
CREATE TABLE
ubuntu=# create table test_disk (i integer, name text);
CREATE TABLE


ubuntu=# explain analyze insert into test_ram values
(generate_series(1,1000000),random()::text);
                                        QUERY PLAN
---------------------------------------------------------------------------------------------
Result  (cost=0.00..0.02 rows=1 width=0) (actual time=0.019..9354.014
rows=1000000 loops=1)
Total runtime: 22836.532 ms
(2 rows)


ubuntu=# explain analyze insert into test_ram_temp values
(generate_series(1,1000000),random()::text);
                                        QUERY PLAN
---------------------------------------------------------------------------------------------
Result  (cost=0.00..0.02 rows=1 width=0) (actual time=0.025..7507.349
rows=1000000 loops=1)
Total runtime: 12773.371 ms
(2 rows)


ubuntu=# explain analyze insert into test_disk values
(generate_series(1,1000000),random()::text);
              QUERY PLAN
---------------------------------------------------------------------------------------------
Result  (cost=0.00..0.02 rows=1 width=0) (actual time=0.025..7948.205
rows=1000000 loops=1)
Total runtime: 16902.042 ms
(2 rows)

ubuntu=# explain analyze insert into test_disk_temp values
(generate_series(1,1000000),random()::text);
                                        QUERY PLAN
---------------------------------------------------------------------------------------------
Result  (cost=0.00..0.02 rows=1 width=0) (actual time=0.018..8135.287
rows=1000000 loops=1)
Total runtime: 13716.049 ms
(2 rows)


So, let's see in a brief:

standard table on ram: 22836.532
standard table on disk: 16902.042

temp table on ram: 12773.371
temp table on disk: 13716.049




--
      Emanuel Calvo Franco
        Sumate al ARPUG !
      (www.postgres-arg.org -
        www.arpug.com.ar)
    ArPUG / AOSUG Member
  Postgresql Support & Admin

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general