Re: DB on a ramdisk (was Re: [GENERAL] Temporary, In-memory Postgres DB?)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Undo an initdb? Probably the same way you undo unlinking an SQLite database. Maybe being wrapped in my own little niche I just don't know enough about the wide world of hyperfeaturitis, but making "temporary DB" as a feature seems a little vague. It doesn't really take that long to create a new database (especially if it's scripted!), and it's even faster if you make the "temporary DB" a schema off a public database. On 11/07/07 11:27, Gauthier, Dave wrote: > I understand caching. > > Here's the reason I'm inquiring into this line of thought... > > I already have a "big" on-disk DB with lots and lots of data, and lots > of stored functions and a data model that works with DB loaders and > other code that queries out data. I also have users that want all of > that, except for the particular data content. They basically want to > load a DB with data that's in their scratch area without polluting > what's in the "main" DB. The cardinality of this "personal, scratch > data" will be orders of magnitude smaller than what's in the main (could > all fit in memory). And once loaded, they would be able to run all the > same DB load and query tools that work on the main DB, just redirect to > the small, personal DB. > > This would be a good app for SQLite, but SQLite can't do a lot of the > things that are running in the main DB (like stored procedures). > > It's become clear that PG cannot do a pure in-memory DB like SQLite. > It's why I initially called this a "longshot" and the answer to my > question is probably "no". But enabling something like a pure in-memory > (and temporary) DB for small apps that can reap all the wonderful > features of PG would make it very attractive for some users. Just > something to think about for future development. > > One question I had earlier that I don't think got answered was how to > undo an "initdb". "dropdb" drops a DB, but how do I undo an "initdb"? > > > -dave > > > > > -----Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane > Sent: Wednesday, November 07, 2007 12:05 PM > To: Ron Johnson > Cc: pgsql-general@postgresql.org > Subject: Re: DB on a ramdisk (was Re: [GENERAL] Temporary, In-memory > Postgres DB?) > > Ron Johnson <[EMAIL PROTECTED]> writes: >> On 11/07/07 09:58, Tom Lane wrote: >>> Or put it on a ramdisk filesystem. > >> But doesn't that just add more overhead and reduce the amount of >> memory that the OS can cache things in? > > It's very possibly not a win, but the kinds of people who ask this > question at all do not understand the concept of caching, so I'm > sure they'll be happier with a solution where the data demonstrably > never hits disk ;-) > > A case where it could be a win is where you are thrashing the DB with > heavy update load. Even if everything is cached there will be a pretty > serious amount of disk write traffic, which'd possibly interfere with > other system activity. - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHMgEJS9HxQb37XmcRApJ9AJ98fxi/RecoS+MUZimzGEk5zYP15QCg7Iz/ VtVm5BMgjWsV+71AFH8M88g= =uTCV -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: DB on a ramdisk (was Re: [GENERAL] Temporary, In-memory Postgres DB?)
In response to "Gauthier, Dave" <[EMAIL PROTECTED]>: > > One question I had earlier that I don't think got answered was how to > undo an "initdb". "dropdb" drops a DB, but how do I undo an "initdb"? rm -rf the directory in which you put the initdb. -- Bill Moran http://www.potentialtech.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: DB on a ramdisk (was Re: [GENERAL] Temporary, In-memory Postgres DB?)
I understand caching. Here's the reason I'm inquiring into this line of thought... I already have a "big" on-disk DB with lots and lots of data, and lots of stored functions and a data model that works with DB loaders and other code that queries out data. I also have users that want all of that, except for the particular data content. They basically want to load a DB with data that's in their scratch area without polluting what's in the "main" DB. The cardinality of this "personal, scratch data" will be orders of magnitude smaller than what's in the main (could all fit in memory). And once loaded, they would be able to run all the same DB load and query tools that work on the main DB, just redirect to the small, personal DB. This would be a good app for SQLite, but SQLite can't do a lot of the things that are running in the main DB (like stored procedures). It's become clear that PG cannot do a pure in-memory DB like SQLite. It's why I initially called this a "longshot" and the answer to my question is probably "no". But enabling something like a pure in-memory (and temporary) DB for small apps that can reap all the wonderful features of PG would make it very attractive for some users. Just something to think about for future development. One question I had earlier that I don't think got answered was how to undo an "initdb". "dropdb" drops a DB, but how do I undo an "initdb"? -dave -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane Sent: Wednesday, November 07, 2007 12:05 PM To: Ron Johnson Cc: pgsql-general@postgresql.org Subject: Re: DB on a ramdisk (was Re: [GENERAL] Temporary, In-memory Postgres DB?) Ron Johnson <[EMAIL PROTECTED]> writes: > On 11/07/07 09:58, Tom Lane wrote: >> Or put it on a ramdisk filesystem. > But doesn't that just add more overhead and reduce the amount of > memory that the OS can cache things in? It's very possibly not a win, but the kinds of people who ask this question at all do not understand the concept of caching, so I'm sure they'll be happier with a solution where the data demonstrably never hits disk ;-) A case where it could be a win is where you are thrashing the DB with heavy update load. Even if everything is cached there will be a pretty serious amount of disk write traffic, which'd possibly interfere with other system activity. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: DB on a ramdisk (was Re: [GENERAL] Temporary, In-memory Postgres DB?)
Ron Johnson <[EMAIL PROTECTED]> writes: > On 11/07/07 09:58, Tom Lane wrote: >> Or put it on a ramdisk filesystem. > But doesn't that just add more overhead and reduce the amount of > memory that the OS can cache things in? It's very possibly not a win, but the kinds of people who ask this question at all do not understand the concept of caching, so I'm sure they'll be happier with a solution where the data demonstrably never hits disk ;-) A case where it could be a win is where you are thrashing the DB with heavy update load. Even if everything is cached there will be a pretty serious amount of disk write traffic, which'd possibly interfere with other system activity. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: DB on a ramdisk (was Re: [GENERAL] Temporary, In-memory Postgres DB?)
On 11/7/07, Ron Johnson <[EMAIL PROTECTED]> wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 11/07/07 09:58, Tom Lane wrote: > > Ron Johnson <[EMAIL PROTECTED]> writes: > >> On 11/07/07 09:03, Gauthier, Dave wrote: > >>> Is there such a thing as a temporary, probably in-memory, version of a > >>> Postgres DB? > > > >> If you have enough RAM, and your database is small enough, the OS > >> will eventually cache the whole thing. > > > > Or put it on a ramdisk filesystem. > > But doesn't that just add more overhead and reduce the amount of > memory that the OS can cache things in? Didn't say it was the smart thing to do. Just that you could do it. I think if one is looking at in memory databases, PostgreSQL is NOT the first choice really. ---(end of broadcast)--- TIP 6: explain analyze is your friend
DB on a ramdisk (was Re: [GENERAL] Temporary, In-memory Postgres DB?)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/07/07 09:58, Tom Lane wrote: > Ron Johnson <[EMAIL PROTECTED]> writes: >> On 11/07/07 09:03, Gauthier, Dave wrote: >>> Is there such a thing as a temporary, probably in-memory, version of a >>> Postgres DB? > >> If you have enough RAM, and your database is small enough, the OS >> will eventually cache the whole thing. > > Or put it on a ramdisk filesystem. But doesn't that just add more overhead and reduce the amount of memory that the OS can cache things in? - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHMeykS9HxQb37XmcRArErAJ47+9oq1/fTZZ4AXrLnL2qGo6E29gCgqVhP DoZuWDTpWE4Rks3tjAWa0mQ= =fuHr -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
