pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedbgwriter and checkpoints
3+ messages / 3 participants
[nested] [flat]
* bgwriter and checkpoints
@ 2021-09-03 20:56 Henry Francisco Garcia Cortez <garcortez@gmail.com>
2021-09-03 21:22 ` Re: bgwriter and checkpoints Alvaro Herrera <alvherre@2ndquadrant.com>
2021-09-03 22:16 ` Re: bgwriter and checkpoints David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Henry Francisco Garcia Cortez @ 2021-09-03 20:56 UTC (permalink / raw)
To: pgsql-admin@postgresql.org, Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: alvherre@2ndquadrant.com
Hi community, I was reading about background writer process and
checkpoints, but I still don't understand very well. How do they work for
instance I understand this if a row was updated,those changes will be on
shared buffers, then those changes will go to path on disk called pg_wall,
then when a checkpoint will be executed and finally, those changes will go
to data files when bgwriter process will be executed but I found a blog on
internet they say that after each search, the BGWRITER chooses some number
of modified pages, writes them to disk, and evicts those pages from the
shared buffer pool.
https://flylib.com/books/en/2.290.1/the_postgresql_bgwriter_process.html
--
Ing. Henry G. Cortez
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: bgwriter and checkpoints
2021-09-03 20:56 bgwriter and checkpoints Henry Francisco Garcia Cortez <garcortez@gmail.com>
@ 2021-09-03 21:22 ` Alvaro Herrera <alvherre@2ndquadrant.com>
1 sibling, 0 replies; 3+ messages in thread
From: Alvaro Herrera @ 2021-09-03 21:22 UTC (permalink / raw)
To: Henry Francisco Garcia Cortez <garcortez@gmail.com>; +Cc: pgsql-admin@postgresql.org, Laurenz Albe <laurenz.albe@cybertec.at>
On 2021-Sep-03, Henry Francisco Garcia Cortez wrote:
> Hi community, I was reading about background writer process and
> checkpoints, but I still don't understand very well. How do they work for
> instance I understand this if a row was updated,those changes will be on
> shared buffers, then those changes will go to path on disk called pg_wall,
> then when a checkpoint will be executed and finally, those changes will go
> to data files when bgwriter process will be executed but I found a blog on
> internet they say that after each search, the BGWRITER chooses some number
> of modified pages, writes them to disk, and evicts those pages from the
> shared buffer pool.
Everything you wrote here is correct. Row changes are logged to WAL
(which is a journal -- always written to disk), and initially remain in
shared buffers. They can be written down from shared buffers to disk
either at a checkpoint, or by bgwriter. If they're not written down by
those processes, and the system crashes, then when the system comes
back, WAL is read and replayed. This restores the change that was done
before the crash.
> https://flylib.com/books/en/2.290.1/the_postgresql_bgwriter_process.html
I'm not familiar with this book.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: bgwriter and checkpoints
2021-09-03 20:56 bgwriter and checkpoints Henry Francisco Garcia Cortez <garcortez@gmail.com>
@ 2021-09-03 22:16 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: David G. Johnston @ 2021-09-03 22:16 UTC (permalink / raw)
To: Henry Francisco Garcia Cortez <garcortez@gmail.com>; +Cc: pgsql-admin; Laurenz Albe <laurenz.albe@cybertec.at>; alvherre@2ndquadrant.com <alvherre@2ndquadrant.com>
On Friday, September 3, 2021, Henry Francisco Garcia Cortez <
garcortez@gmail.com> wrote:
> Hi community, I was reading about background writer process and
> checkpoints, but I still don't understand very well. How do they work
>
At a basic level the database is comprised of files representing its
current data (tables) and a record of all changes (wal).
For performance, the current data is kept in volatile memory. Since losing
data is not an option something (the WAL) has to be written to disk.
Now, it is not feasible for the current data to only exist in RAM, so there
are processes that save the current data to disk as well. The bgwriter and
checkpointer processes do this. Each has different goals and
configurations but they work together and the nuances are outside my scope
here.
Since we are recording the current data to disk, at some point the changes
that resulted in that particular current data state become obsolete. A
checkpoint record is added to the WAL at locations where it is known that
all previous change data is obsolete due to the writing of current data to
disk. This happens periodically while the server is running, controlled by
various configuration variables. A user may also issue a checkpoint SQL
command to have the server record all current data to disk immediately so
that a new checkpoint record can be written to the WAL.
Shared buffers are the table files (fragments) that are in volatile memory
while the heap refers to those tables files on persistent disk.
During crash recovery the current data on disk is taken as the starting
point, and then all changes since the last checkpoint are applied to bring
the system up to the current data state that was in volatile memory at the
time of the crash. Some of the post-final-checkpoint changes may have
already made it to the current data disk files, that’s ok, reapplying those
changes, so long as done in sequential order, will result in a correct
final state.
Side note: The last paragraph also means that during crash recovery one
cannot stop the change application process early - those future changes are
in the disk table files and cannot be undone, which would have to happen if
stopping at a time before they would have occurred.
David J.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2021-09-03 22:16 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-09-03 20:56 bgwriter and checkpoints Henry Francisco Garcia Cortez <garcortez@gmail.com>
2021-09-03 21:22 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2021-09-03 22:16 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox