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