PostgreSQL Explained: Principles, Architecture, and Design Philosophy

Forum topic · ✨步子哥 · 2025-09-28

Summary

A comprehensive Chinese-language forum post explaining PostgreSQL's principles, architecture, and design philosophy. PostgreSQL is an open-source object-relational database system (ORDBMS) originating from UC Berkeley's POSTGRES project in 1986, renamed PostgreSQL in 1996. The post covers its client/server process architecture (postmaster, backend server processes, background workers like checkpointer, WAL writer, and autovacuum), memory structure (shared buffers, WAL buffers, lock tables, and per-backend local memory such as work_mem), and storage hierarchy (tablespaces, databases, schemas, tables, and indexes). It details core mechanisms including MVCC, where tuple visibility is governed by xmin/xmax transaction IDs and requires VACUUM to reclaim dead rows, and Write-Ahead Logging (WAL), which guarantees durability via 16MB segment files and checkpoints. It also explains the cost-based query optimizer (Seq Scan, Index Scan, Bitmap Scan; Nested Loop, Hash Join, Merge Join) and design principles of extensibility (custom types, PL languages, GiST/GIN indexes, FDW), SQL standards compliance, reliability, and community-driven development. Notes highlight PostgreSQL 18's asynchronous I/O framework, reportedly improving read-heavy query performance by 2-3x.

Key points

This post is a detailed Chinese-language overview of PostgreSQL's internals, architecture, and design philosophy.

Introduction and history

PostgreSQL is a powerful open-source object-relational DBMS (ORDBMS) with over 30 years of development, originating from the POSTGRES project at UC Berkeley led by Michael Stonebraker.
Milestones: 1986 POSTGRES project starts; 1995 Postgres95 adds a SQL interpreter; 1996 renamed PostgreSQL; 2010 NoSQL features like JSON; 2017 logical replication and partitioning; 2020 enhanced parallel queries and JIT compilation; 2025 asynchronous I/O framework.
Uses the permissive BSD license; supports most SQL standards plus MVCC, triggers, views, foreign keys, and transactional integrity.

Architecture

Process structure: Postmaster (main process that starts/stops the server and manages others), one server process per client connection, background processes (checkpointer, WAL writer, autovacuum launcher), and auxiliary processes (stats collector, archiver, logical replication launcher). Observable via ps aux | grep postgres.
Memory structure:
Shared memory: shared buffers, WAL buffers, lock table, shared catalog.
Local memory per backend: work_mem, maintenance_work_mem, temp_buffers.
Storage structure: tablespaces map to OS directories; databases contain schemas; tables and indexes are stored as heap files. Example queries use pg_tablespace and pg_database_size.

Core principles

#### MVCC

Updates create new row versions (tuples) instead of modifying in place; old versions remain until no transaction needs them.
Each tuple carries xmin (creating transaction ID), xmax (deleting/updating transaction ID), and ctid (physical location). Transaction states live in CLOG (in-progress, committed, aborted).
Visibility rule: a version is visible if its creating transaction committed, its deleting transaction did not commit (or doesn't exist), and visibility handling accounts for the current transaction's own changes.
Unlike Oracle/MySQL InnoDB (undo logs), PostgreSQL keeps old versions in the table itself; VACUUM (automated by autovacuum) reclaims dead tuples.

#### Write-Ahead Logging (WAL)

Changes are logged before data files are modified, guaranteeing durability and crash recovery via log replay.
Components: WAL records, WAL buffers, 16MB default WAL segment files, and checkpoints that flush dirty pages.
Flow: modify → WAL buffer → WAL writer flushes to disk → commit forces WAL flush → checkpoint → crash recovery replays WAL after last checkpoint.
PostgreSQL 18 introduces an asynchronous I/O subsystem supporting async reads for sequential scans, bitmap heap scans, and VACUUM; early tests show 2–3x gains for read-heavy queries.

#### Query optimizer

Cost-based optimizer (CBO): parse/rewrite → generate paths → estimate costs (I/O, CPU, memory, network) → pick cheapest plan.
Scan methods: Seq Scan, Index Scan, Bitmap Scan. Join strategies: Nested Loop, Hash Join, Merge Join. Inspectable via EXPLAIN / EXPLAIN ANALYZE.

Design philosophy

Extensibility: custom data types (composite, enum, range), functions in PL/pgSQL / PL/Python / PL/Perl, custom index access methods (GiST, SP-GiST, GIN), and Foreign Data Wrappers (FDW) for external sources. Examples include CREATE TYPE address, PL/pgSQL tax function, CREATE EXTENSION postgis and pg_stat_statements.
Standards compliance: supports most of SQL:2011 and all four isolation levels — though Read Uncommitted behaves as Read Committed (no dirty reads), Repeatable Read via MVCC prevents phantom reads, and Serializable uses true serialization or predictive locking.
Reliability and data integrity: WAL, full ACID support, primary/foreign/unique/check constraints, robust crash recovery.
Open source, community-driven: transparent development, diverse contributors, continuous innovation, direct user feedback.