Re: Postgres as In-Memory Database?

On 18/11/13 12:53, Stefan Keller wrote:

Hi Martijn

2013/11/17 Martijn van Oosterhout <kleptog@svana.org> wrote:

> If your dataset fits in memory then the problem is trivial: any decent
> programming language provides you with all the necessary tools to deal
> with data purely in memory.  

What about Atomicity, Concurrency and about SQL query language and the extension mechanisms of Postgres? To me, that's not trivial.

> There are also quite a lot of databases that cover this area.

Agreed. That's what partially triggered my question, It's notably Oracle TimesTen, MS SQL Server 2014 (project Hekaton), (distributed) "MySQL Cluster", SAP HANA or SQLite >3. To me this rather confirms that an architecture and/or configuration for in-memory could be an issue also in Postgres. 

The actual architecture of Postgres assumes that memory resources are expensive and optimizes avoiding disk I/O. Having more memory available affects database design e.g. that it can optimize for a working set to be stored entirely in main memory. 

--Stefan

[...]

It would allow optimised indexes that store memory pointers of individual records, rather than to a block & then search for the record - as well as other optimisations that only make sense when data is known to be in RAM (and RAM is plentiful).  As already big severs can have a TerraByte or more of RAM, that will become more & more common place.  I have 32GB on my development box.


Cheers,
Gavin

Yes, those optimizations I was talking about: having database server store transaction log in high speed solid state disks and consider it done while background thread will update data in slower disks...

There is no reason to wait for fsync in slow disks to guarantee consistency... If database server crashes, then it just need to "redo" log transactions from fast disk into slower data storage and database server is ready to go (I think this is Sybase/MS SQL strategy for years).

Also, consider to have lazy loading (current?) or eager loading (perhaps, I just learned a bit about pg_warmcache).

And, of course, indexes that would point to pages in disk to memory areas when in RAM - as you just mentioned.


Regards,

Edson