PostgreSQL Server Architecture, Storage, and WAL

PostgreSQL uses server processes, shared memory, files organized as relations and pages, and write-ahead logging to make committed changes recoverable. A change becomes durable through WAL rules before dirty data pages must reach their final files. Checkpoints bound crash recovery but also shape I/O, WAL retention, replication, and disk-risk behavior.

What will you be able to do?

• Explain client backends, background processes, shared buffers, pages, and relation files.
• Describe WAL, checkpoints, crash recovery, and durability at a high level.
• Inspect server activity and WAL locations safely.
• Connect disk, checkpoint, and replication symptoms to architecture.

Prerequisites and mental model

Think of data pages as the organized book and WAL as the durable change journal. PostgreSQL records enough journal information before a dirty page needs to be written, then replays after a crash.

What can you inspect?

Some settings and views require privileges. Do not change values in a learning chapter; first understand their system-wide consequences.

Tables and indexes are separate relations. Rows live in heap pages; indexes point toward tuple locations. Updates commonly create new tuple versions, which is why vacuum and bloat appear later. The visibility map and free-space map support maintenance and planning behavior.

Why does this matter in AI-native systems?

Embedding backfills and trace ingestion can generate heavy WAL and dirty many pages. Large vector indexes need disk and rebuild headroom. Long model workflows must not hold database transactions open while waiting for a remote model because they retain resources and old snapshots.

Prove it worked

Record WAL LSN, insert rollbackable test rows in a committed disposable table, then record LSN again. Observe that a logical change advances WAL. Inspect table and index sizes with pg_total_relation_size while recognizing that tiny tests do not model production I/O.

Production judgment

• Never edit files inside the data directory manually.
• fsync, synchronous commit, storage guarantees, and replication settings define durability tradeoffs; do not disable them casually.
• WAL can fill disk due to slow archiving, replication slots, replicas, backups, or write bursts.
• Checkpoint tuning depends on workload, memory, storage, recovery goals, and version.

Common mistakes

• Disk fills unexpectedly: retained WAL or index/backfill growth ignored → monitor components and headroom.
• Commit latency blamed on SQL only: WAL/storage path omitted → inspect waits and storage.
• Transaction spans model call: remote latency inside transaction → fetch/prepare outside, keep mutation transaction short.

AI Pair-Programmer Prompt

Hands-on exercise

Draw the commit path from SQL statement to durable WAL and later data-page write. Annotate where a disk-full failure, crash, and replica apply.

Acceptance criterion: your diagram correctly separates commit durability from checkpointed data files and never proposes manual WAL deletion.

Knowledge check

1. Why can commit finish before every changed data page is in its final file?
2. What bounds crash recovery work?
3. Name two reasons WAL may be retained.

---

Answers

1. WAL records are made durable first and can replay the changes.
2. Checkpoints, together with WAL and recovery state.
3. Archiving/backup needs, replicas, replication slots, or recovery retention settings.

Completion checklist

• I can explain WAL before data-page persistence.
• I inspected activity and WAL location read-only.
• I understand why remote calls do not belong inside long transactions.
• I will never manually delete data-directory WAL.

Primary references

• PostgreSQL architecture
• Database physical storage
• WAL reliability
• WAL configuration