Alan McKay <alan(dot)mckay(at)gmail(dot)com> wrote:
>> My guess is this is checkpoint related.
>
> I'll assume "checkpoint" is a PG term that I'm not yet familiar with
-
> will query my DBA :-)
A checkpoint flushes all dirty PostgreSQL buffers to the OS and then
tells the OS to write them to disk. The exact details of how that's
done and the timings involved vary with PostgreSQL version and
configuration.
> If this OS buffer cache, wouldn't that be cached an awfully long
> time? i.e. we're in big trouble if we get a bad crash?
Before the commit of a database transaction is completed the changes
which are involved in that are written to a write ahead log (WAL). A
checkpoint is also recorded in the WAL. On recovery from a crash,
PostgreSQL replays all activity from committed transactions after the
last checkpoint; so nothing from a committed transaction is lost.
-Kevin