PostgreSQL Fundamentals

PostgreSQL Resources

• Postgres and PostgreSQL refer to the same database management system
• malisper.me provides a comprehensive set of articles about PostgreSQL, covering:

  File Layout and Storage

  • PostgreSQL's file layout is divided into pages

  WAL (Write-Ahead Log)

  • WAL is a crucial component of PostgreSQL's architecture

  Clog and Visibility Map

  • Clog and Visibility Map are essential components of PostgreSQL's storage system

  Transaction Isolation

  • Transaction isolation is implemented by creating snapshots

  Background Tasks

  • Vacuum (clean up rows) and Checkpoint (write WAL to disk) are essential background tasks in PostgreSQL

  Optimizations

  • Helper flags in records are used for optimization, such as:
    • Marking pages as clean and visible to all transactions to skip vacuum checks
    • Marking rows with flags indicating committed transactions to avoid transaction status checks

Query Optimizations

Lateral Join

• Lateral join is a type of join optimization in PostgreSQL

Window Functions

• Window functions are used for optimization in PostgreSQL

Recursive CTE

• Recursive CTE (Common Table Expression) is a feature in PostgreSQL for optimizing queries

Internals of PostgreSQL

• Hironobu SUZUKI @ InterDB provides insights into the internals of PostgreSQL

Learn about the core concepts of PostgreSQL, including file layout, WAL, clog, visibility map, transaction isolation, and background tasks like Vacuum and Checkpoint.