第 1 章 PostgreSQL 是什么 & 为什么火

学习目标：能用一句话向产品经理解释「PostgreSQL 是什么、它和 MySQL 有什么不一样」；能向面试官讲清楚「为什么这两年大厂集体倒戈 PG」；能跑通第一段 Python 代码，连上一个真实的 PG 实例并打印出版本号。

0. 全书约定（先把规矩定下来）

为了让全教程上下一致、读起来不别扭，本教程从这一章开始遵守如下约定，请你也用一样的写法练习：

项目	约定	说明
服务端版本	PostgreSQL 16 / 17	后续所有 SQL、参数、视图都以 16/17 为准。
客户端	psql（PG 官方 CLI）	第 2 章会全方位讲它。
编程语言客户端	Python + `psycopg` v3	`psycopg2` 是上一代库，新代码推荐 v3。
SQL 风格	关键字大写，标识符小写	例：`SELECT id, name FROM users WHERE id = 1;`。
演示数据库	`learn_pg`	默认连接 `host=127.0.0.1 port=5432 dbname=learn_pg user=postgres`。
字符集	UTF-8	PG 安装时即指定，不像 MySQL 那样需要逐表指定 charset/collation。

📌 与 MySQL 的区别：MySQL 同一个实例下「库 = schema」，所以你只见过两层（库.表）。PG 是 三层结构：database → schema → table，本章会先打个招呼，第 2 章详细讲。

1.1 一段「家谱」：PostgreSQL 从哪来

1.1.1 关系型数据库简史（1 分钟版本）

1970 ─────────────────────────────────────────────────────────────────
  ▲ Edgar F. Codd 提出「关系模型」论文（IBM）
  │
1974 ─ IBM System R（第一个关系型数据库原型，孕育出 SQL）
1979 ─ Oracle v2 商用发布（业界第一款商用关系数据库）
1986 ─ POSTGRES（UC Berkeley · Michael Stonebraker）  ← 这就是 PG 的爹
1995 ─ MySQL 1.0
1996 ─ POSTGRES 改名 PostgreSQL，加入 SQL 标准支持
2010 ─ PostgreSQL 9.0 引入流复制 / 热备
2017 ─ PostgreSQL 10 引入声明式分区 / 逻辑复制
2023 ─ PostgreSQL 16
2024 ─ PostgreSQL 17（截至本教程编写时的最新稳定版）

PostgreSQL 的根，扎在加州大学伯克利分校 1986 年的 POSTGRES 项目里 —— 那一年 MySQL 都还没出生。它的设计者 Michael Stonebraker（也是 Ingres、Vertica、VoltDB 的发明人）拿过图灵奖。所以你可以理直气壮地说：

「PG 不是一个『新』数据库，它是一个有 35 年血统的老贵族，只不过最近五年被互联网大厂重新发现了而已。」

1.1.2 名字的故事

Ingres（"Interactive Graphics Retrieval System"）：Stonebraker 70 年代的项目。
POSTGRES = "Post-Ingres"，意思是「Ingres 的下一代」。
1996 年加上对 SQL 标准的支持后，改名 PostgreSQL（"Post-Gres-Q-L"）。

正确读法是 "post-gress-Q-L"，但大家都简称 PG 或 Postgres，听到任何一个都是它。

1.2 PG 的设计哲学：「最先进的开源关系数据库」

PG 官网的副标题就是 "The World's Most Advanced Open Source Relational Database"。这个「先进」体现在哪里？我们用 3 个生活类比一一拆开。

1.2.1 哲学一：可扩展性 ——「这是个能装插件的数据库」

普通数据库：一台冰箱，只能放食物。
PostgreSQL：一台冰箱 + 插槽。
            插上「酿酒模块」→ 它会酿酒；
            插上「冰激凌模块」→ 它会做冰激凌。

PG 从一开始就把内核做成可插拔的：

新数据类型 你可以注册（不只是 INT/VARCHAR，还有数组、JSONB、范围、地理坐标……）
新索引方法 你可以写（B-Tree 之外还有 Hash、GIN、GiST、BRIN、SP-GiST）
新查询语言 你可以挂（PL/pgSQL PL/Python PL/JavaScript）
新过程型扩展 你可以装（CREATE EXTENSION postgis; —— 一行命令，PG 就会做地理信息计算了）

📌 首次术语解释 · OID：在 PG 里几乎所有东西（表、列、类型、函数、操作符）都有一个 OID（Object Identifier，对象标识符），本质是一个 4 字节整数。系统目录（pg_class、pg_type 等）就是靠 OID 把这些对象串起来的。可扩展性的底层支撑，就来自这套以 OID 为中心的 catalog（系统目录）设计。

1.2.2 哲学二：对象-关系（ORDBMS）混合模型

「关系型」（Relational）你熟：行 + 列 + 表。「对象型」（Object）是什么？—— PG 把「类型 / 继承 / 复合」这些面向对象的概念也搬进来了：

sql

-- 自定义复合类型：把「金额 + 币种」绑成一个类型
CREATE TYPE money_t AS (amount NUMERIC(18,2), currency CHAR(3));

CREATE TABLE orders (
    id        BIGSERIAL PRIMARY KEY,
    total     money_t,                       -- 直接用复合类型
    tags      TEXT[],                        -- 数组类型
    extra     JSONB,                         -- JSON 二进制
    period    DATERANGE                      -- 日期范围
);

INSERT INTO orders(total, tags, extra, period)
VALUES (ROW(99.50, 'USD'), ARRAY['vip','black-friday'], '{"src":"app"}', '[2026-01-01,2026-02-01)');

一行表定义里同时出现了：复合类型、数组、JSONB、范围类型 —— 这些在 MySQL 里要么不支持，要么要用字符串「凑」。这就是「对象-关系」的含义：关系结构的严谨 + 类型系统的丰富。

1.2.3 哲学三：严格遵循 SQL 标准

PG 几乎是开源数据库里对 SQL 标准遵循度最高的（社区文档明确列出对 SQL:2023 的支持矩阵）。这意味着：

你写的标准 SQL（CTE、窗口函数、FETCH FIRST n ROWS ONLY、GROUPING SETS、MERGE 等）在 PG 上直接能跑。
学会 PG 的 SQL，迁到 Oracle / DB2 几乎无痛；反之 MySQL 的方言（如 LIMIT n,m、隐式类型转换、GROUP BY 不严格）迁到别处经常翻车。

1.3 PG vs MySQL vs Oracle：定位差异速查

维度	PostgreSQL	MySQL（InnoDB）	Oracle
协议	PostgreSQL License（BSD 风格、宽松）	GPL v2（社区版）	商业
SQL 标准遵循度	高（接近 Oracle）	中等，方言多	高
存储引擎	仅一种（堆表 + 索引），可插式 TableAM	多引擎可选（InnoDB 默认）	单一
行格式	堆表 + 独立索引文件	InnoDB 聚簇索引（数据按主键存）	堆表（也支持 IOT）
并发控制	MVCC + 多版本元组（Undo 信息内嵌行内）	MVCC + Undo Log	MVCC + Undo Log
隔离级别默认	READ COMMITTED	REPEATABLE READ	READ COMMITTED
事务 DDL	支持（`CREATE TABLE` 也能回滚！）	不支持（DDL 隐式提交）	部分支持
JSON	`JSON` + `JSONB`（带索引、操作符极丰富）	`JSON` 类型（不可索引）	`JSON` 类型（12c+）
数组 / 范围 / 复合	原生支持	不支持	部分（VARRAY 等）
扩展机制	`CREATE EXTENSION`，1000+ 扩展	插件较少，靠存储引擎	商业模块
复制	物理流复制 + 逻辑复制（pub/sub）	binlog 主从（statement / row / mixed）	DataGuard
进程模型	多进程（每连接 1 fork）	多线程（每连接 1 thread）	多进程或多线程
自增主键	`GENERATED ALWAYS AS IDENTITY`（标准） / `SERIAL`	`AUTO_INCREMENT`	`IDENTITY` / 序列
大小写	标识符默认小写（`SELECT * FROM Foo` 实际查 `foo`）	取决于操作系统	默认大写

📌 三个最容易踩坑的差异：

DDL 也能回滚：在 PG 里 BEGIN; CREATE TABLE foo(...); ROLLBACK; 后表不会留下；MySQL 中 DDL 一发出就提交事务。
隔离级别默认是 READ COMMITTED：从 MySQL 迁过来的同学经常被这个咬。
空字符串 ≠ NULL：PG 严格区分 '' 和 NULL；Oracle 把 '' 当 NULL；MySQL 中两者经常混用。

1.4 为什么大厂越来越多用 PG

2015 年前   ─ MySQL 一家独大（互联网大厂、CRUD 应用）
2015~2020   ─ PG 在分析 / 地理 / OLAP 场景悄悄崛起
2020~       ─ JSONB / 向量 / 时序 / 分布式扩展爆发
                ↓
            主流公司开始迁移：
              GitLab、Notion、Apple、Reddit、Instagram、
              Robinhood、字节跳动、阿里云 PolarDB-PG、
              微软 Azure Cosmos for PG、AWS Aurora-PG、
              Supabase、Neon、CockroachDB（兼容 PG 协议）……

转折点有 4 个：

JSONB（9.4 引入）：让 PG 同时是 SQL 数据库和 NoSQL 文档库，挤占了大量 MongoDB 的场景。
逻辑复制 + 声明式分区（10.0）：让 PG 进入了「在线数据中台」的视野。
MERGE / 性能优化（15-17）：补齐了与 Oracle 最后的几块短板。
AI 浪潮 + pgvector（2023）：向量数据库直接长在 PG 上，不需要再选一个独立的 Pinecone / Milvus。

一句金句送给面试官：「MySQL 让你跑得快，PG 让你跑得远。功能丰富、标准严谨、扩展强大，是 PG 在 AI 时代成为新一代基础设施的根本原因。」

1.5 PG 的进程模型 vs MySQL 的线程模型

这是面试高频题，必须用图讲清。

1.5.1 PG：「一连接一进程」

                ┌──────────────────────────────────────────────┐
                │              PostgreSQL 实例                  │
                │                                                │
                │   ┌─────────────────┐                          │
                │   │   Postmaster    │  ← 主进程，监听端口 5432  │
                │   │   (主进程)       │     来一个连接 fork 一个   │
                │   └────────┬────────┘                          │
                │            │ fork()                             │
                │   ┌────────┼────────┬────────┬────────┐         │
                │   ▼        ▼        ▼        ▼        ▼         │
                │ Backend Backend Backend Backend Backend         │
                │  (conn1)(conn2)(conn3)(conn4)(conn5)            │
                │                                                  │
                │   ┌──────────┐ ┌──────────┐ ┌──────────────┐    │
                │   │ WAL Wri- │ │ Auto-    │ │ Bg Writer /  │    │
                │   │   ter    │ │ Vacuum   │ │ Checkpointer │    │
                │   └──────────┘ └──────────┘ └──────────────┘    │
                │                                                  │
                │            共享内存 (Shared Buffers)              │
                │  ┌─────────────────────────────────────────┐    │
                │  │  ▒▒▒▒  缓冲区  ▒▒▒▒  锁表  ▒▒▒▒  ...   │    │
                │  └─────────────────────────────────────────┘    │
                └──────────────────────────────────────────────┘

关键点：

Postmaster = 看门人，负责监听端口、accept 连接、fork 子进程、回收子进程。
每个客户端连接 → 一个独立的 Backend 进程（系统级进程，OS 调度）。
多个进程通过 共享内存（Shared Buffers） 共享数据页。

📌 首次术语解释 · WAL：Write-Ahead Log（预写日志）。任何修改先写日志再写数据页，就像你转账前银行先在账本上记录一笔流水。后面第 10 章会详细讲。

📌 首次术语解释 · LSN：Log Sequence Number（日志序列号）。WAL 里每条记录的位置，单调递增。可以理解为银行流水的「流水号」。

1.5.2 MySQL：「一连接一线程」

                ┌──────────────────────────────────────────────┐
                │                MySQL 实例                     │
                │                                                │
                │   ┌─────────────────┐                          │
                │   │  mysqld 主进程   │                         │
                │   └────────┬────────┘                          │
                │            │ pthread_create()                  │
                │   ┌────────┼────────┬────────┬────────┐         │
                │   ▼        ▼        ▼        ▼        ▼         │
                │ Thread1 Thread2 Thread3 Thread4 Thread5         │
                │                                                  │
                │      所有线程共享同一个进程的地址空间             │
                └──────────────────────────────────────────────┘

1.5.3 进程 vs 线程：影响在哪？

对比点	PG（多进程）	MySQL（多线程）
创建开销	较大（fork 一个进程比建线程慢）	较小
崩溃隔离	强（一个 backend 崩了不会拖死整个实例）	弱（一个线程踩坏内存可能整个 mysqld 挂）
共享数据	走共享内存（需要显式管理）	直接共享地址空间，方便
连接成本	高 → 必须用连接池（PgBouncer）	较低，MySQL 可以「裸用」
多核利用	天然多核（每进程独立调度）	早期有大锁，5.7+ 后改善

实战建议：用 PG 必须搭一个连接池（PgBouncer / pgcat）。一个 web 应用动辄几百个进程 × 每进程 10 连接 = 几千连接，PG 直接 OOM。

1.6 生态地图：1000+ 扩展任你选

PG 最强的护城河之一是它的扩展生态。一行 CREATE EXTENSION xxx; 就能给数据库装一个新「能力」。

                    ┌────────────────────────┐
                    │      PostgreSQL 内核    │
                    │  （类型系统 / Catalog） │
                    └───────────┬────────────┘
                                │ CREATE EXTENSION
        ┌──────────┬────────────┼────────────┬──────────────┐
        │          │            │            │              │
   ┌────▼────┐ ┌──▼──────┐ ┌───▼─────┐ ┌────▼─────┐ ┌──────▼──────┐
   │ PostGIS │ │pgvector │ │Timescale│ │   Citus  │ │ Apache AGE  │
   │  地理    │ │  向量   │ │  时序   │ │ 分布式    │ │   图查询     │
   └─────────┘ └─────────┘ └─────────┘ └──────────┘ └─────────────┘
        │          │            │            │              │
   ┌────▼────┐ ┌──▼──────┐ ┌───▼─────┐ ┌────▼─────┐ ┌──────▼──────┐
   │ pg_cron │ │ pg_jieba│ │pg_stat_ │ │  hstore  │ │  pg_partman │
   │ 定时任务 │ │ 中文分词│ │statements│ │ KV 类型   │ │  分区管理    │
   └─────────┘ └─────────┘ └─────────┘ └──────────┘ └─────────────┘

扩展	一句话功能	替代了谁
PostGIS	地理信息计算（最近邻、范围查询）	商业 GIS 系统
pgvector	向量相似度检索（IVF / HNSW）	Pinecone / Milvus / Chroma
TimescaleDB	时序数据自动分区 + 压缩	InfluxDB
Citus	分布式 PG（分片 + 并行）	CockroachDB / TiDB（部分场景）
Apache AGE	在 PG 上跑 Cypher 图查询	Neo4j
pg_cron	SQL 里写定时任务	crontab + 脚本
pg_stat_statements	SQL 性能审计	商业 APM

一句话：「Postgres 是一种生活方式」。不是「PG 替代 MySQL」，而是 PG 让你在一个数据库里同时拿到关系、文档、地理、时序、向量、图 6 种能力。

1.7 实操：第一次 Hello PostgreSQL

1.7.1 用 psql 命令行（30 秒）

bash

$ psql -h 127.0.0.1 -p 5432 -U postgres -d learn_pg
psql (17.0)
Type "help" for help.

learn_pg=# SELECT version();
                                              version
----------------------------------------------------------------------------------------------------
 PostgreSQL 17.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.0, 64-bit
(1 row)

learn_pg=# SELECT current_database(), current_user, now();
 current_database | current_user |              now
------------------+--------------+-------------------------------
 learn_pg         | postgres     | 2026-04-17 10:23:45.123456+08
(1 row)

learn_pg=# SHOW server_version;
 server_version
----------------
 17.0
(1 row)

learn_pg=# SHOW shared_buffers;
 shared_buffers
----------------
 128MB
(1 row)

learn_pg=# \q

📌 输入 \q 退出，输入 \? 看所有元命令（meta command）。这些都是 psql 客户端特有的，不是 SQL，不需要分号结尾。第 2 章会详细讲。

1.7.2 用 Python 客户端（psycopg v3）

完整脚本见 01_intro/code/hello_pg.py。核心片段：

python

import psycopg

with psycopg.connect("host=127.0.0.1 port=5432 dbname=learn_pg user=postgres") as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT version()")
        print(cur.fetchone()[0])
        # → PostgreSQL 17.0 on x86_64-pc-linux-gnu...

with 语法保证连接自动关闭，符合 PEP-249 + 上下文管理。

1.7.3 浏览器可视化演示

打开 01_intro/demo.html，可以点击按钮一步步看：

① PG vs MySQL 架构对比（进程模型 vs 线程模型动画演示）
② PG 进程模型示意图（Postmaster fork backend、共享内存交互）
③ 生态扩展地图（点扩展卡片看用法 + 替代了哪个独立产品）

1.8 底层原理一瞥：从「客户端 SELECT 1」到「服务端返回结果」

哪怕只是发一句 SELECT 1，PG 内部也走了一条很长的路。后面章节我们会一段一段拆，这里先给一个全景图：

这张图里的每一步，后面章节都会单独展开：

协议握手 → 第 2 章
Planner / Executor → 第 5、6 章
Shared Buffers / 数据页 → 第 9 章
WAL → 第 10 章

1.9 本章小结

┌─────────────────────────────────────────────────────────┐
│                     本章核心要点                          │
├─────────────────────────────────────────────────────────┤
│                                                          │
│  ① PG 起源于 1986 年伯克利的 POSTGRES 项目，35+ 年血统   │
│                                                          │
│  ② 设计哲学三件套：                                       │
│     • 可扩展（一切皆可插件，OID + Catalog 是底层基础）    │
│     • 对象-关系混合模型（复合类型 / 数组 / 范围 / JSONB）│
│     • 严格遵循 SQL 标准                                   │
│                                                          │
│  ③ 与 MySQL 7 个最大区别：                                │
│     • 隔离级别默认 READ COMMITTED                         │
│     • DDL 可回滚                                          │
│     • 多进程 vs 多线程                                    │
│     • JSONB 可索引                                        │
│     • MVCC 多版本元组（不靠 Undo Log）                    │
│     • 三层结构 database / schema / table                  │
│     • 标识符默认小写                                      │
│                                                          │
│  ④ 进程模型：Postmaster fork Backend，必须配连接池        │
│                                                          │
│  ⑤ 扩展生态：PostGIS / pgvector / Timescale / Citus...   │
│                                                          │
└─────────────────────────────────────────────────────────┘

1.10 面试高频题

Q1：PostgreSQL 和 MySQL 最核心的区别是什么？

考察点：是否真的对两者都有「体感」，而不是只会背参数。

标准答案（按重要性分点）：

并发控制实现机制不同：MySQL InnoDB 使用 Undo Log 来支持 MVCC，旧版本数据存在 Undo 段中；PG 使用「多版本元组」 —— 旧版本数据直接留在数据页里，由 xmin / xmax 标记可见性，因此没有 Undo 表空间，但需要 VACUUM 来回收死元组。
默认隔离级别不同：MySQL 是 REPEATABLE READ（且通过 Gap Lock 防止幻读），PG 是 READ COMMITTED，更接近标准定义；如果要可重复读，PG 提供 REPEATABLE READ 和 SERIALIZABLE（基于 SSI 算法实现的真串行化）。
进程模型 vs 线程模型：PG 每连接 1 个进程（Postmaster fork），强崩溃隔离但连接成本高，必须配连接池；MySQL 每连接 1 个线程，连接更轻量但崩溃影响面更大。
DDL 是否在事务中：PG 的 DDL 可以回滚（BEGIN; CREATE TABLE; ROLLBACK; 安全），MySQL DDL 是隐式提交。
数据组织形式：PG 是堆表 + 独立索引（包括主键索引也是独立的 B-Tree），MySQL InnoDB 是聚簇索引（数据按主键有序存放）。
类型系统丰富度：PG 原生支持数组、范围、JSONB（带 GIN 索引）、自定义复合类型、自定义操作符，MySQL 只支持基本类型 + 不可索引的 JSON。

加分项：能补充一句「PG 的标识符默认小写，MySQL 默认行为依赖文件系统大小写敏感性」。

易错点：千万别答「MySQL 是事务型，PG 是分析型」 —— 这两个都是 OLTP，结构差异是底层实现，而不是「定位」。

Q2：PG 的进程模型是什么？为什么必须用连接池？

考察点：理解 PG 与传统 web 框架（每请求 1 连接）配合时的真实生产问题。

标准答案：

PG 采用「一连接一进程」模型：主进程叫 Postmaster，监听端口；每来一个客户端连接，就 fork() 一个独立的 Backend 进程专门服务它。
优点：进程之间内存隔离，一个 backend 段错误崩溃不会拖死整个实例；多核 CPU 利用天然好。
缺点：每个 backend 至少占用 ~10 MB 私有内存（解析栈、catalog cache、临时变量）；连接数过万会直接吃光物理内存或耗尽 max_connections。
解决办法：在应用与 PG 之间放一层 连接池（PgBouncer 最常用），让数千个应用层短连接复用十几个真实的 PG 连接。模式有 session / transaction / statement 三种，生产推荐 transaction 模式。

加分项：提一下 PG 17 引入了对异步 IO 的优化和 backend 启动开销的进一步降低；同时社区有 pgcat、Odyssey 等更现代的连接池可以选。

易错点：不要把 PG 的进程模型说成「性能更差」 —— 在合理的连接池下，PG 的吞吐和 MySQL 没有本质差距，瓶颈往往在 IO 和锁，而不是进程开销。

Q3：什么是 MVCC？PG 和 MySQL 的 MVCC 有何不同？

考察点：MVCC 的本质 + 两家实现差异。这是 PG 面试的「必考题」。

标准答案：

MVCC（Multi-Version Concurrency Control，多版本并发控制） 的核心思想是：写不阻塞读、读不阻塞写。每条记录可能存在多个版本，每个事务看到的是「在它开始时刻已经提交」的那个版本。

PG 的实现：

每行（元组 tuple）有两个隐藏字段：xmin（创建该版本的事务 ID）和 xmax（删除该版本的事务 ID）。
UPDATE 不是原地修改，而是插入一条新元组 + 把旧元组的 xmax 标为当前事务 ID，旧版本继续留在页里。
一个事务能否看到某行，由 xmin/xmax 与当前事务的「快照（snapshot）」比对决定。
旧版本（死元组 dead tuple）由 VACUUM（autovacuum 定时、VACUUM FULL 手动）清理。

MySQL InnoDB 的实现：

在 Undo Log 中保存历史版本：当前数据页只存最新版本，旧版本通过 roll_pointer 指针指向 Undo Log 中的链。
没有「死元组」概念，旧版本通过 purge 线程回收 Undo。

对比点	PG	MySQL InnoDB
旧版本存放位置	数据页内	Undo Log 段
回收手段	VACUUM（autovacuum）	purge 线程
长事务后果	死元组堆积 → 表膨胀	Undo Log 暴涨
是否需要事务 ID 防冻结	是（32 位 XID 回卷问题）	无此问题

加分项：

能解释 HOT（Heap Only Tuple）更新：当 UPDATE 不修改任何索引列时，新版本可以放在同一页且不更新索引，性能大幅提升。
能说出 PG 的 SERIALIZABLE 用 SSI（Serializable Snapshot Isolation） 实现，比 InnoDB 的 next-key lock 更现代。

易错点：

不要说 PG「没有 Undo」就不用回收 —— VACUUM 本质上和 purge 线程是干一样的活，只是回收对象不同。
不要说 MySQL 的 RR 是 "真正的可重复读" —— 它实际上靠快照读 + Gap Lock 模拟，不是 SQL 标准定义的串行化。

Q4：为什么这两年 PG 越来越火？大厂为什么从 MySQL 迁到 PG？

考察点：行业视野 + 对 PG 演进的理解，体现你不是「只会写 SQL」的工具人。

标准答案（4 个驱动力）：

JSONB + GIN 索引让 PG 同时是 SQL 和 NoSQL：自 9.4 起，JSONB 支持二进制存储和 GIN 索引，可以高效查询任意嵌套字段。Notion、GitLab 等大量「半结构化文档」型业务从 MongoDB 迁回 PG。
声明式分区 + 逻辑复制让 PG 能做数据中台：PG 10 引入声明式分区（RANGE/LIST/HASH），13 起完善了分区裁剪与并行；逻辑复制（pub/sub）支持跨大版本、跨表的数据同步。
MERGE / 性能优化补齐了与 Oracle 的最后短板：PG 15 引入 MERGE，16/17 在并行查询、JIT、向量化方向继续优化。
AI 时代的向量数据库（pgvector）：2023 年起 RAG 应用爆发，开发者不愿意再为向量再单独引入一个存储，pgvector 让 SQL + 向量检索在一个事务里完成，并且和现有用户表/订单表关联查询。
云厂商的全面押注：AWS Aurora-PG、Azure Cosmos for PG、阿里云 PolarDB-PG、Supabase、Neon 全部以 PG 为内核，相当于把全球云数据库的地基都换成了 PG。

加分项：

能举出真实迁移案例：GitLab 早年放弃 MySQL 全面 PG 化、Notion 从 MongoDB 迁 PG、Apple 内部很多关键业务用 PG。
能提到 pgvector / pg_duckdb / pg_mooncake 这些把 PG 推向 AI / 分析的扩展。

易错点：不要说「PG 比 MySQL 性能更好」—— 性能要分场景：高并发短事务 MySQL 一直很猛，复杂 SQL / 分析型 / 长查询 PG 优势明显。

Q5：PostgreSQL 的「OID」「TOAST」「WAL」分别是什么，简单说一下？

考察点：术语扫盲 + 知识广度。

标准答案：

OID（Object Identifier，对象标识符）：4 字节无符号整数，用于在系统目录（catalog）中唯一标识每个对象。表、列、类型、函数、操作符、扩展，每个都有自己的 OID。系统视图如 pg_class.oid、pg_type.oid 都是它。
TOAST（The Oversized-Attribute Storage Technique）：「超大字段离线存储技术」。PG 数据页固定 8KB，单行不能跨页。如果一行某个字段（如长 TEXT、JSONB、BYTEA）超过约 2KB，PG 会自动压缩 + 切片 + 存到独立的 TOAST 表，主表里只留一个指针。对应用层完全透明。
WAL（Write-Ahead Log，预写日志）：所有数据修改先写 WAL（顺序写，速度极快）后才改数据页。崩溃恢复时重放 WAL 把数据页恢复到一致状态；流复制 / 逻辑复制也基于 WAL 实现。WAL 中每条记录的位置叫 LSN（Log Sequence Number）。

加分项：

能补一个「XID 回卷（transaction ID wraparound）」概念：PG 事务 ID 是 32 位，每 40 亿个事务会回卷一次，因此需要 VACUUM FREEZE 把老数据冻结，防止「未来时间被识别成过去」的灾难。
能说出与 MySQL 的对应：WAL ≈ InnoDB 的 redo log；TOAST ≈ MySQL 的 off-page 行外存储。

易错点：把 OID 和主键混淆 —— OID 只用于系统目录（system catalog），普通用户表的行默认没有 OID（PG 12 起完全废弃用户表 OID 列）。

Q6：PG 的扩展机制是怎么实现的？为什么这么多扩展能即插即用？

考察点：对 PG 内核可扩展性的本质理解，进阶题。

标准答案：

PG 在内核里把「类型 / 函数 / 操作符 / 索引访问方法 / 过程语言」全部做成了通过系统目录（catalog）注册的对象。比如 CREATE TYPE 实质上就是往 pg_type 写一行。
扩展机制的核心是 CREATE EXTENSION：本质上是执行一段 SQL 脚本（通常包含 CREATE FUNCTION ... LANGUAGE C AS '...so', 'symbol_name'），把 C 编写的动态库（.so）注册成 PG 内的函数 / 类型 / 操作符。
PG 暴露了索引访问方法接口（IndexAM）：扩展可以注册新的索引方法，比如 pgvector 的 ivfflat 和 hnsw、PostGIS 的 R-Tree on GiST。
PG 还暴露了 FDW（Foreign Data Wrapper） 接口和 Hook 钩子（Planner Hook、Executor Hook、Login Hook），让扩展能改变查询行为甚至接管整段执行。

加分项：

能列出常见扩展用了什么机制：
- PostGIS：新类型 + 新操作符 + GiST 索引
- pgvector：新类型 + 新距离操作符 + 新索引方法（IVFFlat / HNSW）
- pg_stat_statements：Planner Hook + 共享内存
- Citus：Planner Hook 重写为分布式 plan
能解释「logical replication 的 output plugin」也是一种扩展点。

易错点：不要把扩展和「插件式存储引擎」混同 —— MySQL 的存储引擎是替换 InnoDB 整层，PG 的扩展是叠加新能力到现有引擎之上。

📌 下一章预告：第 2 章我们正式动手 —— 学 psql 的所有元命令、理解 database / schema / table 三层结构、写出第一段完整的 DDL+DML+SELECT。

🎬 可视化演示

演示加载缓慢或样式异常？点此在新标签页打开 ↗

💻 示例代码

hello_pg.py

python

"""hello_pg.py —— 第 1 章配套代码

用途
    第一次连接本地 PostgreSQL，打印版本号、当前数据库、当前用户，
    以及若干关键服务端参数（shared_buffers / max_connections 等）。
    用于验证你本地的 PG 环境是否就绪，并初步认识 psycopg v3 的连接 API。

运行方法
    1. 确保本地已经有可用的 PostgreSQL（默认 host=127.0.0.1 port=5432）。
    2. 安装依赖：
           pip install "psycopg[binary]>=3.1"
    3. 创建演示库（首次运行时执行一次即可）：
           psql -h 127.0.0.1 -U postgres -c "CREATE DATABASE learn_pg;"
    4. 直接运行：
           python hello_pg.py

预期输出
    ============================================================
     Hello PostgreSQL!
    ============================================================
     server_version          : 17.0 (...)
     current_database        : learn_pg
     current_user            : postgres
     ...
"""

from __future__ import annotations

import sys

try:
    import psycopg
except ImportError:
    sys.exit('请先安装 psycopg v3：pip install "psycopg[binary]>=3.1"')


CONN_INFO = "host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"


KEY_PARAMS = [
    "server_version",
    "shared_buffers",
    "max_connections",
    "work_mem",
    "effective_cache_size",
    "wal_level",
    "max_wal_size",
    "default_transaction_isolation",
    "client_encoding",
    "server_encoding",
    "TimeZone",
]


def banner(text: str) -> None:
    print("=" * 60)
    print(f" {text}")
    print("=" * 60)


def main() -> None:
    banner("Hello PostgreSQL!")

    try:
        conn = psycopg.connect(CONN_INFO, autocommit=True)
    except psycopg.OperationalError as exc:
        sys.exit(
            "连接 PostgreSQL 失败：\n"
            f"  {exc}\n"
            "请检查：\n"
            "  · PG 服务是否已启动（默认端口 5432）；\n"
            "  · 数据库 learn_pg 是否已创建（CREATE DATABASE learn_pg;）；\n"
            "  · pg_hba.conf 是否允许 postgres 用户本地连接。"
        )

    with conn:
        with conn.cursor() as cur:
            cur.execute("SELECT version()")
            print(f" {'version':<24}: {cur.fetchone()[0]}")

            cur.execute("SELECT current_database(), current_user, now()")
            db, usr, now = cur.fetchone()
            print(f" {'current_database':<24}: {db}")
            print(f" {'current_user':<24}: {usr}")
            print(f" {'now()':<24}: {now}")

            print()
            banner("关键服务端参数（SHOW xxx）")
            for param in KEY_PARAMS:
                cur.execute(f"SHOW {param}")
                value = cur.fetchone()[0]
                print(f" {param:<32}: {value}")

            print()
            banner("活跃连接（pg_stat_activity）")
            cur.execute(
                """
                SELECT pid, usename, application_name, state, query
                FROM pg_stat_activity
                WHERE datname = current_database()
                ORDER BY pid
                """
            )
            for row in cur.fetchall():
                pid, usr, app, state, query = row
                query_short = (query or "").replace("\n", " ").strip()[:50]
                print(
                    f"  pid={pid:>6}  user={usr or '-':<10}  app={app or '-':<18}"
                    f"  state={state or '-':<10}  query={query_short!r}"
                )

            print()
            banner("已安装扩展（pg_extension）")
            cur.execute(
                "SELECT extname, extversion FROM pg_extension ORDER BY extname"
            )
            for name, ver in cur.fetchall():
                print(f"  {name:<24} v{ver}")

    print()
    print("✅ Done. 已成功连接到本地 PostgreSQL。")


if __name__ == "__main__":
    main()

hello_pg.py ↗

第 1 章 PostgreSQL 是什么 & 为什么火 ​

0. 全书约定（先把规矩定下来） ​

1.1 一段「家谱」：PostgreSQL 从哪来 ​

1.1.1 关系型数据库简史（1 分钟版本） ​

1.1.2 名字的故事 ​

1.2 PG 的设计哲学：「最先进的开源关系数据库」 ​

1.2.1 哲学一：可扩展性 ——「这是个能装插件的数据库」 ​

1.2.2 哲学二：对象-关系（ORDBMS）混合模型 ​

1.2.3 哲学三：严格遵循 SQL 标准 ​

1.3 PG vs MySQL vs Oracle：定位差异速查 ​

1.4 为什么大厂越来越多用 PG ​

1.5 PG 的进程模型 vs MySQL 的线程模型 ​

1.5.1 PG：「一连接一进程」 ​

1.5.2 MySQL：「一连接一线程」 ​

1.5.3 进程 vs 线程：影响在哪？ ​

1.6 生态地图：1000+ 扩展任你选 ​

1.7 实操：第一次 Hello PostgreSQL ​

1.7.1 用 psql 命令行（30 秒） ​

1.7.2 用 Python 客户端（psycopg v3） ​

1.7.3 浏览器可视化演示 ​

1.8 底层原理一瞥：从「客户端 SELECT 1」到「服务端返回结果」 ​

1.9 本章小结 ​

1.10 面试高频题 ​

Q1：PostgreSQL 和 MySQL 最核心的区别是什么？ ​

Q2：PG 的进程模型是什么？为什么必须用连接池？ ​

Q3：什么是 MVCC？PG 和 MySQL 的 MVCC 有何不同？ ​

Q4：为什么这两年 PG 越来越火？大厂为什么从 MySQL 迁到 PG？ ​

Q5：PostgreSQL 的「OID」「TOAST」「WAL」分别是什么，简单说一下？ ​

Q6：PG 的扩展机制是怎么实现的？为什么这么多扩展能即插即用？ ​

🎬 可视化演示 ​

💻 示例代码 ​

第 1 章 PostgreSQL 是什么 & 为什么火

0. 全书约定（先把规矩定下来）

1.1 一段「家谱」：PostgreSQL 从哪来

1.1.1 关系型数据库简史（1 分钟版本）

1.1.2 名字的故事

1.2 PG 的设计哲学：「最先进的开源关系数据库」

1.2.1 哲学一：可扩展性 ——「这是个能装插件的数据库」

1.2.2 哲学二：对象-关系（ORDBMS）混合模型

1.2.3 哲学三：严格遵循 SQL 标准

1.3 PG vs MySQL vs Oracle：定位差异速查

1.4 为什么大厂越来越多用 PG

1.5 PG 的进程模型 vs MySQL 的线程模型

1.5.1 PG：「一连接一进程」

1.5.2 MySQL：「一连接一线程」

1.5.3 进程 vs 线程：影响在哪？

1.6 生态地图：1000+ 扩展任你选

1.7 实操：第一次 Hello PostgreSQL

1.7.1 用 psql 命令行（30 秒）

1.7.2 用 Python 客户端（psycopg v3）

1.7.3 浏览器可视化演示

1.8 底层原理一瞥：从「客户端 SELECT 1」到「服务端返回结果」

1.9 本章小结

1.10 面试高频题

Q1：PostgreSQL 和 MySQL 最核心的区别是什么？

Q2：PG 的进程模型是什么？为什么必须用连接池？

Q3：什么是 MVCC？PG 和 MySQL 的 MVCC 有何不同？

Q4：为什么这两年 PG 越来越火？大厂为什么从 MySQL 迁到 PG？

Q5：PostgreSQL 的「OID」「TOAST」「WAL」分别是什么，简单说一下？

Q6：PG 的扩展机制是怎么实现的？为什么这么多扩展能即插即用？

🎬 可视化演示

💻 示例代码