Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm a bit confused with checkpoint_segments definition in PostgreSQL documentation. What happens when checkpoint_segments reached?

  1. The dirty pages in shared_buffers written to WAL. Then what is the usage of background writer?
  2. WAL is merged to actual data files.
share|improve this question

1 Answer 1

up vote 3 down vote accepted

Combing through the docs, I see the following (accidental incorrect details will hopefully (or surely) be pointed out by others):

There is a separate server process called the background writer, whose function is to issue writes of "dirty" (new or modified) shared buffers.

BW does its work according to its config parameters. It writes at most bgwriter_lru_maxpages dirty shared buffers, then has a bgwriter_delay millisecond rest. Now, when it comes to checkpoint_segments log segments (or checkpoint_timeout seconds, if it comes first), it flushes all (remaining) dirty segments to data files. Now the database data has a consistent state on disk, with a corresponding entry in the log as well. It is a good starting point for a subsequent REDO if necessary.

And who does make the checkpoints?

The server's background writer process automatically performs a checkpoint [...]

As for your 2nd point, WAL is WAL (stored in log segment files) and data files are data files. The two will be consistent with each other but not merged.

share|improve this answer
    
So bgwriter writes updates from shared bufferes to WAL and when checkpoint_segments reaches it flushes WAL files to main data files? right? –  Majid Azimi May 29 '12 at 10:37
    
@MajidAzimi - Briefly, WAL's central concept is that changes to data files (where tables and indexes reside) must be written only after those changes have been logged, that is, after log records describing the changes have been flushed to permanent storage. (source) In other words, WAL shows what data files should contain, but never the affected rows themselves. –  dezso May 29 '12 at 11:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.