Re: [PERFORM] Re: [PERFORM] Re: 回复： [PERFORM] PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?

On Tue, Feb 28, 2012 at 10:38 AM, Tomas Vondra <tv(at)fuzzy(dot)cz> wrote:
> On 28 Únor 2012, 14:08, Claudio Freire wrote:
>> On Tue, Feb 28, 2012 at 5:30 AM, Stefan Keller <sfkeller(at)gmail(dot)com> wrote:
>>>
>>> But what I'm finally after is a solution, where records don't get
>>> pushed back to disk a.s.a.p. but rather got hold in memory as long as
>>> possible assuming that there is enough memory.
>>
>> fsync = off ?
>
> I don't think this is a viable idea, unless you don't care about the data.

Well, if you "keep things in memory as long as possible" (as per the
quoted message), then you don't care about memory. There's no way
memory-only DBs can provide ACID guarantees.

synchronous_commit=off goes half way there without sacrificing crash
recovery, which is another option.

> Moreover, "fsyn=off" does not mean "not writing" and writing does not mean
> "removing from shared buffers". A page written/fsynced during a checkpoint
> may stay in shared buffers.

The OS will write in the background (provided there's enough memory,
which was an assumption on the quoted message). It will not interfere
with other operations, so, in any case, writing or not, you get what
you want.

> AFAIK the pages are not removed from shared buffers without a reason. So a
> dirty buffer is written to a disk (because it needs to, to keep ACID) but
> stays in shared buffers as "clean" (unless it was written by a backend,
> which means there's not enough memory).

Just writing is not enough. ACID requires fsync. If you don't fsync
(be it with synchronous_commit=off or fsync=off), then it's not full
ACID already.
Because a crash at a bad moment can always make your data nonpersistent.

That's an unavoidable result of keeping things in memory.