Thread: In memory Database for postgres
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.
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@gmail.com>
To: John R Pierce <pierce@hogranch.com>
Cc: postgresql Forums <pgsql-general@postgresql.org>
Sent: Monday, April 13, 2009 11:54:45 AM
Subject: Re: [GENERAL] In memory Database for postgres
2009/4/12 John R Pierce <pierce@hogranch.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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
2009/4/13 aravind chandu <avin_friends@yahoo.com>:
>
>
> 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.
>
Oh, well... there is a way but is not recomended.
So you can make the tables on memory and with a trigger update on disk...
you can call this inverted materialized views (because in general you
update the views on memory but not in the disk).
But there is a problem... if you update on memory and a shutdown
occurs and the trigger didn't start... you lost this record :( I must
say that you
That's because i prefer have a database in disk and have updatable
views in memory :)
--
Emanuel Calvo Franco
Sumate al ARPUG !
(www.postgres-arg.org -
www.arpug.com.ar)
ArPUG / AOSUG Member
Postgresql Support & Admin
On Mon, Apr 13, 2009 at 2:06 PM, aravind chandu <avin_friends@yahoo.com> wrote: > > > 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. But that's just the point people have been making. A small enough db will be cached completely in memory, and the only time you'll have to access the disks is the first read, and during writes, which can be made to happen mostly after the fact and not influence the rest of the db. You're trying to reinvent a wheel to solve a non-existent problem.
On Mon, 2009-04-13 at 17:36 -0300, Emanuel Calvo Franco wrote:
> > 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.
> >
If you want the writes to be preserved across shutdown, the writes must
go to disk. If that's too expensive, and you are only concerned about
preserving the writes after a clean shutdown, you can turn off fsync
(but then your data will be corrupt after a crash).
The reads will usually come from memory anyway (as long as you have
enough memory), even if the tables are stored on disk. So what's wrong
with just using normal tables?
> So you can make the tables on memory and with a trigger update on disk...
> you can call this inverted materialized views (because in general you
> update the views on memory but not in the disk).
How does that help? Don't you have the same number of disk writes that
way?
> But there is a problem... if you update on memory and a shutdown
> occurs and the trigger didn't start... you lost this record :( I must
> say that you
>
Triggers are transactional. Either they all fire, and all the updates
happen, or none do.
Aravind, Scott asked the most important question: what problem are you
trying to solve?
Regards,
Jeff Davis
If you need to run some database really fast, try to put only all your indexes onto ram disk. Look here... http://www.linux.com/feature/142658 They use SSD to store indexes (not data) for postgresql. A think the same conclusions should apply for ram disk too. And in wrost case (power off for RAM disk or wear out for SSD) you need only a reindex to build your indexes again Scott Marlowe napsal(a): > On Mon, Apr 13, 2009 at 2:06 PM, aravind chandu <avin_friends@yahoo.com> wrote: > >> 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. >> > > But that's just the point people have been making. A small enough db > will be cached completely in memory, and the only time you'll have to > access the disks is the first read, and during writes, which can be > made to happen mostly after the fact and not influence the rest of the > db. > > You're trying to reinvent a wheel to solve a non-existent problem. > >