Skip to main content

Postgresql architecture

PostgreSQL is a powerful, open-source object-relational database system. To understand how PostgreSQL manages data and supports concurrent users, it's essential to understand its architecture. This article is designed to make you aware of the PostgreSQL architecture in an easy to understand manner.

The PostgreSQL System Architecture​

The PostgreSQL system architecture consists of several components working together to store data and provide the database service. These components include:

  • Processes - PostgreSQL uses a multi-process model where each connection is handled by a separate process.
  • Memory - PostgreSQL uses shared memory for caching data and supporting its processes.
  • Storage - PostgreSQL stores data in files on the hard disk.

Processes​

The primary processes in PostgreSQL architecture are:

  1. Postmaster Process - This is the parent process that starts when you start PostgreSQL. It is responsible for managing all other PostgreSQL processes.

  2. Backend Process - A backend process is created for every client connection by the postmaster process. Queries from the client are executed in this backend process.

  3. Autovacuum Process - This process automatically frees up space from deleted rows and updates statistics of the tables.

  4. Background Writer Process - This process writes dirty pages (data in memory that has been modified but not yet written to disk) to disk.

  5. Checkpointer Process - This process writes all dirty pages to disk at a checkpoint.

  6. Archiver Process - This process archives the transaction log files to another location for safekeeping.

  7. Stats Collector Process - This process collects statistics about the database activity.

  8. Wal Writer Process - This process writes the write-ahead log (WAL) to disk.

Memory​

The memory architecture of PostgreSQL consists of:

  1. Shared Buffers - This is a cache of disk pages kept in memory for faster access.

  2. Local Buffers - These are used to cache backend local data.

  3. Write-Ahead Log (WAL) Buffers - These buffers are used to cache transaction logs.

  4. Work Memory - This is used for operations like sorting.

  5. Maintenance Work Memory - This is used for maintenance operations like creating indexes.

Storage​

The storage architecture of PostgreSQL consists of:

  1. Tablespaces - These are locations on disk where PostgreSQL stores data files.

  2. Data Files - These are the files that actually store data, indexes, etc.

  3. Transaction Logs (Write-Ahead Logs) - These are the logs that keep track of all changes made to the database.

Conclusion​

The architecture of PostgreSQL is designed to ensure data integrity, support concurrent users, and provide a high-performance database service. Understanding the architecture will help you manage PostgreSQL more effectively. In the next parts, we will dive deeper into each component and discuss them in detail.