Re: Database block lifecycle

> Yesterday I had an interesting discussion with my colleague about shared
> buffers size for our new server. This machine (is dedicated for db) has got
> 512GB of RAM and database size is about 80GB, so he assumes that db will
> never have to read from disk, so there is no point to adjust read ahead
> setting, because every block gonna be read from RAM. As I've red in Greg
> Smith book, once a block is changed it will be written to a disk and buffers
> page is marked as clean, which would mean than changes occur in the same
> page as before? What if dirty page doesn't have enough space for another row
> and row has to be written to another page? Is it still occurs in RAM? If
> that's true all updates of FSM occurs in RAM as well?
>
> What about buffers_clean and pg_clog then? Are those maintained completely
> in RAM as well without direct read from disk at all?

As long as they are on shared buffers, they are read from the buffer,
not from disk.

> To be precise, does the path to update and read updated row looks like a or
> b?:
> a). clean page (shared buffers) -> dirty page (shared buffers) -> to disk ->
> read from disk -> shared buffers -> query
> b). clean page (shared buffers) -> dirty page (shared buffers) -> to disk
> & dirty page (shared buffers) -> clean page (shared buffers) -> query

I'm not sure what you exactly mean by a) or b) but both look incorrect
for me. A reader can read a page from shared bufferes even if it's
dirty. So:

clean page (shared buffers) -> dirty page (shared buffers) -> query

will be closer to the reality. Note that dirty page will be written by
bgwriter process at different timing.

Also note that I completely ignore lock or
buffer replacement algorithm. Please read
src/backend/storage/buffer/README for more precise information.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp