Hi, I have read all information about checkpoints in PostgreSQL I have found. I think that current implementation of checkpoints is not good for huge shared buffer cache and for many WAL segments. If there is more buffers and if buffers can be written rarely more updates of buffers can be combined so total number of writes to disk will be significantly less. I think that incremental checkpoints can achieve this goal (maybe more) and price is additional memory (about 1/1000 of size of buffer cache).
My main source of information is http://wiki.postgresql.org/wiki/User:Gsmith#How_do_checkpoints_happen_inside_the_PostgreSQL_backend.3F I see that some data are required to be written into WAL in 3) and 6). I will use CD to denote that data and P1, P2... to denote pages that are dirty and has to be written to disk in 4). In incremental checkpoint when WAL segment has written we will not start writing but we will add to queue pages P1, P2 ... and CD. If meanwhile background writer has to clean some page that page is removed from queue. When checkpoint_segments are written in the transaction log we have in queue: P1, P2 ... CD, Pi ... CD, Pj ... CD ... Here we have to make checkpoint in order to free first WAL segment. Only pages before first CD have to be written and fsync’d. I suppose that this task can be done in background writer. So first we can make some number of writes per round both lru and checkpoint. There is no deadline for each incremental checkpoint but if WAL is growing total number of writes have to increase. Also it is not required to do checkpoint for each WAL segment. It is possible to write N pages from queue and to combine several potential checkpoint in one. I hope I have explained the general idea. I am not C programmer so it is hard to me to give more details. Jordan Ivanov -- Sent via pgsql-hackers mailing list (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers