Skip site navigation (1) Skip section navigation (2)

Re: explanation of some configs

From: Matthew Wakeling <matthew(at)flymine(dot)org>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: explanation of some configs
Date: 2009-02-09 10:30:59
Message-ID: alpine.DEB.2.00.0902091021550.3995@aragorn.flymine.org (view raw or flat)
Thread:
Lists: pgsql-performance
On Sat, 7 Feb 2009, justin wrote:
> In a big databases a checkpoint could get very large before time had 
> elapsed and if server cashed all that work would be rolled back.

No. Once you commit a transaction, it is safe (unless you play with fsync 
or asynchronous commit). The size of the checkpoint is irrelevant.

You see, Postgres writes the data twice. First it writes the data to the 
end of the WAL. WAL_buffers are used to buffer this. Then Postgres calls 
fsync on the WAL when you commit the transaction. This makes the 
transaction safe, and is usually fast because it will be sequential writes 
on a disc. Once fsync returns, Postgres starts the (lower priority) task 
of copying the data from the WAL into the data tables. All the un-copied 
data in the WAL needs to be held in memory, and that is what 
checkpoint_segments is for. When that gets full, then Postgres needs to 
stop writes until the copying has freed up the checkpoint segments again.

Matthew

-- 
 Don't worry!  The world can't end today because it's already tomorrow
 in Australia.

In response to

Responses

pgsql-performance by date

Next:From: justinDate: 2009-02-09 15:44:31
Subject: Re: explanation of some configs
Previous:From: Mario SplivaloDate: 2009-02-09 10:07:43
Subject: Re: Postgres not willing to use an index?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group