The first process that is started when we start PostgreSQL is /usr/local/pgsql/bin/postgres. This process has quite a few responsibilities such as performing recovery, initializing shared data structures/memory space, and kicking off the mandatory and optional processes. These processes are also referred to as utility processes and include bgwriter, checkpointer, autovacuum launcher, log writer, stats collector process, and so on. The daemon process also listens for connection requests, receives requests for connections from clients, and spawns server processes for the client. It's obvious that the daemon itself is a mandatory process that should be running for a user to connect to the database.

Let's focus on the user connecting-issuing-commands scenario and other pieces should fall in place. The following diagram walks you through the process of how the daemon process receives a connection request and starts (forks) a backend process. The backend process...

When there are thousands of users trying to read/write data to many different tables, reading from the directories/files (which we saw getting created when we installed PostgreSQL and created a database with a couple of tables) will result in a miserably non-scalable system. The reads and writes will result in searching for many files, opening these files, using fseek() for specific data records, locking, editing, and unlocking. To make this a lot more scalable and faster, the concept of shared buffers (memory area) is introduced. Now, the backend processes are no longer reading from the files and writing to the files, but dealing with buffers or RAM, with significant improvement in performance. The amount of memory to be allocated is decided by the shared_buffers parameter in postgresql.conf. This fixed-size block of shared memory is allocated when the server is started.

It's not this memory chunk alone that is responsible for improving the response times...

Checkpoint is a mandatory process. To understand this, let's discuss blocks. PostgreSQL always reads and writes data in blocks. Consider the emp table. It has just one record. The data in this record should add up to a few bytes; we have the value 1 in the column id, and the value Newname in the column first_name. However, this table will consume 8K in the disk because PostgreSQL works with 8K blocks. A block is also referred to as a page. It is easy to verify that PostgreSQL uses blocks. Ensure that our table has just one record as follows:

SELECT * FROM emp;
 id | first_name 
----+------------
  1 | Newname
(1 row)

Then, we find the filename:

SELECT pg_relation_filepath('emp');
 pg_relation_filepath 
----------------------
 base/24741/24742
(1 row)

Now, we check the size of the file:

\! ls -l /pgdata/9.3/base/24741/24742
-rw-------. 1 postgres postgres 8192 Nov 15 11:33 /pgdata/9.3/base/24741/24742

8192 bytes = 8K. So, a table with just one record takes up 8K.

Let's try inserting some...

When we make changes to the data, the changes are not written to the data files immediately, as mentioned before (probably many times). Changes are made to the blocks in the buffer and records of these changes are written to the WAL buffer (as soon as changes to data are made). The changes are flushed to the WAL segments when the changes are committed.

In the pg_xlog directory, the WAL segments are each 16 MB in size:

[postgres@MyCentOS pg_xlog]$ pwd
/pgdata/9.3/pg_xlog
[postgres@MyCentOS pg_xlog]$ ls -alrt
total 16396
drwx------.  2 postgres postgres     4096 Oct 13 13:23 archive_status
drwx------.  3 postgres postgres     4096 Oct 13 13:23 .
drwx------. 15 postgres postgres     4096 Nov 15 20:17 ..
-rw-------.  1 postgres postgres 16777216 Nov 15 20:17 000000010000000000000001

We can find out the segment PostgreSQL is writing to now using the pg_current_xlog_location function:

[postgres@MyCentOS pg_xlog]$ psql
psql (9.3.0)
Type "help" for help.

postgres=# SELECT...

The background writer is responsible for writing to disk specific dirty buffers based on an algorithm, whereas checkpointer writes all dirty buffers. The process takes into consideration shared memory usage data as well as information about which blocks have been used/accessed recently (least recently used). The primary objective of this process is to ensure that free buffers are available for use. The relevant parameters are as follows:

#bgwriter_delay = 200ms                 # 10-10000ms between rounds
#bgwriter_lru_maxpages = 100            # 0-1000 max buffers written/round
#bgwriter_lru_multiplier = 2.0          # 0-10.0 multiplier on buffers scanned/round

As we can see, the default value for delay is 200 milliseconds. This parameter specifies how long the process should wait between successive executions. The bgwriter_lru_maxpages parameter specifies the maximum number of buffers that will be written by the process in each iteration. The third parameter is also...

This is an optional process. There is a parameter called autovacuum in postgresql.conf, with ON as default value. This process automates the execution of vacuum and analyzes commands based on a few parameters. To understand autovacuum, first, we have to understand vacuum.

Assume that we delete a few records from a table. PostgreSQL does not immediately remove the deleted tuples from the data files. These are marked as deleted. Similarly, when a record is updated, it's roughly equivalent to one delete and one insert. The previous version of the record continues to be in the data file. Each update of a database row generates a new version of the row. The reason is simple: there can be active transactions, which want to see the data as it was before. As a result of this activity, there will be a lot of unusable space in the data files. After some time, these dead records become irrelevant as there are no transactions still around to see the old data. However,...

This is an optional process and the default setting is off. We have to set the logging_collector parameter to on to start this process:

cd $PGDATA

Edit the postgresql.conf file and make a few changes:

log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
log_min_duration_statement = 0

[postgres@MyCentOS 9.3]$ pg_ctl restart
waiting for server to shut down.... done
server stopped
server starting
[postgres@MyCentOS 9.3]$ ps f -U postgres
  PID TTY      STAT   TIME COMMAND
 2581 pts/2    S      0:00 -bash
 3201 pts/2    R+     0:00  \_ ps f -U postgres
 2218 pts/1    S+     0:00 -bash
 3186 pts/2    S      0:00 /usr/local/pgsql/bin/postgres
 3187 ?        Ss     0:00  \_ postgres: logger process     
 3189 ?        Ss     0:00  \_ postgres: checkpointer process   
 3190 ?        Ss     0:00  \_ postgres: writer process     
 3191 ?        Ss     0:00  \_ postgres: wal writer process   
 3192 ?        Ss     0:00  \_ postgres: autovacuum launcher process...