As Rob wrote: Having a feature like an in-memory table like SQLite has [1] would make application cahces obsolete and interesting to discuss (but that was'nt exactly what I asked above).
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).