Re: Postgres as In-Memory Database? - Mailing list pgsql-general

From Edson Richter
Subject Re: Postgres as In-Memory Database?
Date
Msg-id BLU0-SMTP3409477C7244D1DF8D35AC9CFE40@phx.gbl
Whole thread Raw
In response to Re: Postgres as In-Memory Database?  (Gavin Flower <GavinFlower@archidevsys.co.nz>)
Responses Re: Postgres as In-Memory Database?
Re: Postgres as In-Memory Database?
List pgsql-general
Em 17/11/2013 22:02, Gavin Flower escreveu:
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

pgsql-general by date:

Previous
From: Ken Tanzer
Date:
Subject: What does this error message mean?
Next
From: John R Pierce
Date:
Subject: Re: Postgres as In-Memory Database?