Re: Postgres as In-Memory Database?

Hi Edson

As Rob wrote: Having a feature like an in-memory table like SQLite has [1] would make application cahces obsoleteand interesting to discuss (but that was'nt exactly what I asked above).

Hi, Stephan,

I don't think any feature you add to database server would bring obsolescence to app server caches: app server caches have just no lag at all:

1) Don't need network connection to database server
2) Don't need to materialize results (for instance, I have in mind a Java or .Net app server running hundred thousands of objects in memory).

IMHO, no matter how much you improve database, app server caches provides additional level of speed that cannot be achieved by database.

That said, I still can see huge improvements in database server.
Having strong in memory operation would bring substantial improvements.
For instance, if you have in-memory database (tables, indexes, etc) for all sort of queries, and just **commit** to disks, then you will have unprecedent performance.
I would get benefit from this architecture, since typical customer database has < 64Gb on size (after 2 or 3 years of data recording). So, a database server with 64Gb of memory would keep everything in memory, and just commit data to disc.

In this case, commited data would be instantly available to queries (because they are all in memory) while log (changes) is recorded in a fast disk (a SSD, perhaps) and then those changes are made persistent data, written async into slow massive disks (SCSI or SAS).

This would allow also a hybrid operation (too keep as much data pages as possible in memory, with a target of 50% or more in memory).

When database server is started, it would have lazy load (data is loaded and kept in memory as it is used) or eager load (for slower startup but faster execution).

May be I'm just wondering too much, since I don't know PostgreSQL internals...


Regards,

Edson

--Stefan

 

[1] http://www.sqlite.org/inmemorydb.html

[2] http://www.postgresql.org/docs/9.1/static/non-durability.html

2013/11/17 Edson Richter <edsonrichter@hotmail.com>

Em 17/11/2013 12:15, rob stone escreveu:

On Sun, 2013-11-17 at 12:25 +0100, Stefan Keller wrote:

How can Postgres be used and configured as an In-Memory Database?


Does anybody know of thoughts or presentations about this "NoSQL
feature" - beyond e.g. "Perspectives on NoSQL" from Gavin Roy at PGCon
2010)?


Given, say 128 GB memory or more, and (read-mostly) data that fit's
into this, what are the hints to optimize Postgres (postgresql.conf
etc.)?


-- Stefan

Not as being completely "in memory".
Back in the "good ol'days" of DMS II (written in Algol and ran on
Burroughs mainframes) and Linc II (also Burroughs) it was possible to
define certain tables as being memory resident. This was useful for low
volatile data such as salutations, street types, county or state codes,
time zones, preferred languages, etc.
It saved disk I/O twice. Firstly building your drop down lists and
secondly when the entered data hit the server and was validated.
It would be good to have a similar feature in PostgreSql.
If a table was altered by, say inserting a new street type, then the
data base engine has to refresh the cache. This is the only overhead.

Cheers,
Robert

For this purpose (building drop down lists, salutations, street types, county or state codes), I keep a permanent data cache at app server side (after all, they will be shared among all users - even on a multi tenant application). This avoids network connection, and keep database server memory available for database operations (like reporting and transactions).
But I agree there are lots of gaings having a "in memory" option for tables and so. I believe PostgreSQL already does that automatically (most used tables are kept in memory cache).

Edson.

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