Module2_System_architecture Flashcards
what is the first process that starts when you start postgresql?
postmaster and is a superviser for all other postgres processes
what are the functions of postmaster process?
- it starts all other postgresql processes and restarts them if they die.
- it listens to new connections and setups up a new dedicated process per user connection
what 3 items sit in shared memory
shared buffers, wal buffer and process array
what is background writer?
ensure enough buffer supply available in the memory and sync from time to time dirty data blocks to data files
what does the checkpointer?
checkpointer writes the buffers by default every 5 min (checkpoint timeout parameter) to data files or
when WAL is full Parameter (MAX_WAL_SIZE)
What does the WAL Writer?
ensuring data wall buffers are synced with disk at commit.
what does the logger processes do?
Routes log messages to syslog, eventlog or log files
what does archiver proces do?
Copy the switched wal file to an archived wall destination. not started by default.
what does autovacuum LAUNCHER process do?
autolauncher proces manages and starts autovacuum processes that do maintaince tasks like vacuming and analyse.
what does stats collector proces do?
collect stats and store them in the DB
what does logical replication process do?
Launces the processes to perform logical replication based on the data he retrieved from WAL segments.
what does the autovacuum WORKER process
recover free space for reuse.
How is shared buffer used for READING:
If a new user connection asks for data that is present in the shared buffer the user will get the data fast because it already lookup once before. If the query is not executed before it will be fetched from disk and loaded into shared data (slow) and results can be given to the user. The result of that query will stay in memory so the next request will be fast as well.
How is shared buffer used for WRITING:
When user alters data and does a commit it will be written in the shared buffer ONLY! after a checkpoint (because of time default 5 min or when WAL is full) data will be written to disk by checkpointer proces.
What is background writer cleaning scan
BGWriter performing cleaning scans to ensure there is enough supply of clean buffers.
what is Write Ahead Logging (WAL)
WAL is used to track transactions. when user commits data it will be written from wal buffer inside shared memory to WAL file on disk by the WAL Writer. It will only give the user the commit succeeded after it’s written in transaction log (WAL file).
Transaction log archiving.
Archiver process starts process to copy the switched WAL (PG_WAL log files) to a archiver destination for PIR. The archiver runs the ARCHIVE_COMMAND on the OS the copy the files.
where is the altered data before commit?
uncommited updates are in memory
where is the altered data after commit?
WAL buffers are writen to disk (wal file) and shared buffers are markerd as commited.
where is the altered data after checkpoint?
altered data pages are written from shared memory to data files.
what process acts as the listener?
postmaster and listens on 1 port only
what happens after Postmaster listenens to a new connection
it will start a new dedicated processes voor that connection and perform authentication. after autentication is succes Postmaster will handover that new proceses to the requesting user.
statement proces has 3 functions
Parsing the query, optimizing (find plan with lowest cost) and executed based on the optimized plan .
what is a cluster?
collection of databases managed by one instance with a seperate data directory, TCP Port and set of processes.
