Architecture

PostgreSQL uses shared memory to speed things up by avoiding unnecessary disk reads/writes. Inside shared memory, there are two important parts

Shared Buffer

1. Stores frequently accessed data blocks

2. It reduces I/O

3. Supports multiple users accessing data together

WAL Buffer

1. WAL - Write ahead Log - It tracks every change before it’s actually saved to the disk

2. This buffers holds the data temporarily , after that these changes are pushed to WAL File (disc)

3. Useful for backup and recovery

Process Types

When PostgreSQL is running, it uses different types of processes, each with its own job

Postmaster Process

1. This is the first process when PostgreSQL boots up

2. Sets up shared memory, starts background workers

3. Listens for new client connections

4. Creates backend process for each client

5. Parent of all other processes

Background Process

1. Runs in background to maintain the system health

2. Some of the processes it runs are

These processes are not directly handling user queries but support the system

Backend Process

1. These are created for each client (user or app) that connects to PostgreSQL

2. Each one handles one client connection (for better fault tolerance)

3. Handles the SQL Query execution

Client Process

1. This is not part of PostgreSQL itself

2. It's the user or app that connects to PostgreSQL — e.g., you using psql, or your app connecting to the database via TCP/IP or UNIX socket

Database Structure

When you install or initialize PostgreSQL (initdb), it sets up 3 default databases — but they aren’t just random. Each has a job

1. template0 – The Clean Master Copy

• Think of it like the backup

• It’s clean, untouched, and no one can log into it

• You can use it to create a fresh, default database anytime

2. template1 – Your Custom Starter Pack

• This is your main template.

• You can customize it (add functions, extensions, etc)

• Whenever you create a new database, PostgreSQL clones this

3. postgres – Your Playground

• This is the database you log into by default

• Use it for playing around, testing queries, or trying stuff out

Tablespaces

Tablespaces are just folders on your system where PostgreSQL stores data. That’s it

1. pg_default

• Where your tables and indexes live by default.

• Path: $PGDATA/base/

2. pg_global

• Stores data that is shared across all databases, like user accounts.

• Path: $PGDATA/global/

3. Custom Tablespaces

• You can create your own storage locations

• Example: Store large tables on a separate SSD for performance