Re: In memory Database for postgres

2009/4/12 John R Pierce <pierce(at)hogranch(dot)com>:
> 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