袋鼠知识

主题

第九章 PostgreSQL 详解 ​

📖 ⏱️ 预计阅读时长

1. PostgreSQL 概述与特性 ​

PostgreSQL 是一个功能强大的开源对象-关系数据库系统（ORDBMS），在灵活的 BSD 许可证下发行。PostgreSQL 的发音为 "post-gress-Q-L"，其口号是"世界上最先进的开源关系型数据库"。它在 SQL 标准兼容性、数据类型丰富性、扩展能力等方面表现突出，被广泛应用于企业级应用、地理信息系统、科学计算和数据分析领域。

1.1 发展历程 ​

PostgreSQL 源自加州大学伯克利分校的 POSTGRES 项目，由 Michael Stonebraker 教授领导开发。1996 年更名为 PostgreSQL，以体现对 SQL 的支持。经过三十余年的发展，PostgreSQL 形成了活跃的开源社区，每年发布主版本，持续增强功能与性能。

1.2 核心特性 ​

  • 标准兼容：高度遵循 SQL 标准，支持窗口函数、CTE、递归查询、JSON 等现代特性。
  • 丰富的数据类型：除基本类型外，支持数组、JSON/JSONB、范围类型、几何类型、全文检索类型等。
  • 扩展机制：通过 Extension 可加载 PostGIS（地理信息）、pg_trgm（模糊匹配）等扩展。
  • MVCC 并发控制：多版本并发控制，读写不阻塞，高并发场景下表现优异。
  • 可编程性：支持存储过程、触发器、自定义函数，内置 PL/pgSQL 等过程语言。

PostgreSQL 常被很多工程师视为“更完整的关系数据库”，原因并不只是它功能多，而是它在设计取向上更强调语义表达、标准兼容和体系完整性。它通常不满足于只做一个高效的增删改查工具，而更愿意把数据库建设成一个既能严格表达关系结构、又能承载复杂查询和扩展能力的平台。理解这种取向后，再看 PostgreSQL 的数据类型、扩展机制、执行计划和治理能力，就更容易把它们连成一条主线，而不是当成零散特性去记。

1.3 ORDBMS 术语 ​

PostgreSQL 作为对象-关系数据库，在关系模型基础上增加了面向对象能力：

  • 数据库：关联对象的集合。
  • 表：数据的矩阵，对应关系模型中的关系。
  • 行：一组相关数据，对应元组或记录。
  • 列：相同类型的数据元素。
  • 主键：唯一标识行的列或列组合。
  • 外键：关联两表的引用完整性约束。
  • 索引：加速数据访问的结构。

2. PostgreSQL 安装与配置 ​

2.1 安装方式 ​

  • Linux：使用发行版包管理器（如 apt install postgresql、yum install postgresql-server）或官方源。
  • Windows：下载官方安装程序，按向导完成安装。
  • macOS：brew install postgresql 或使用 Postgres.app。
  • Docker：docker run -d -p 5432:5432 -e POSTGRES_PASSWORD=mypass postgres。

2.2 连接与基础命令 ​

bash
# 命令行连接（默认使用当前系统用户）
psql -U postgres -d postgres

# 指定主机和端口
psql -h localhost -p 5432 -U postgres -d mydb

连接后的常用命令：

sql
\l          -- 列出所有数据库
\c dbname   -- 切换数据库
\dt         -- 列出当前数据库的表
\d tablename -- 查看表结构
\q          -- 退出

psql 这类命令行工具在教材里看上去只是操作入口，实际上它也是理解 PostgreSQL 对象体系的最好窗口。通过系统命令查看数据库、模式、表、索引和函数，读者会更直观地感受到 PostgreSQL 并不是一堆分散的表，而是一个由数据库、Schema、对象权限、统计信息和系统目录共同组成的完整系统。很多概念若只停留在建表语句里，会显得抽象；放到实际命令行环境中观察，就容易建立整体感。

3. 数据库与模式 ​

3.1 创建与删除数据库 ​

sql
CREATE DATABASE school
  WITH ENCODING = 'UTF8'
       LC_COLLATE = 'zh_CN.UTF-8'
       LC_CTYPE = 'zh_CN.UTF-8'
       TEMPLATE = template0;

-- 删除数据库
DROP DATABASE [IF EXISTS] school;

3.2 模式 (Schema) ​

PostgreSQL 中的模式是数据库内的命名空间，用于组织表、视图等对象。默认有一个 public 模式。

sql
-- 创建模式
CREATE SCHEMA myschema;

-- 在指定模式中建表
CREATE TABLE myschema.students (...);

-- 设置搜索路径
SET search_path TO myschema, public;

模式可用来实现多租户、逻辑分组等需求。

在 PostgreSQL 中，Schema 的意义远不止“给对象换个前缀”。它本质上是数据库内部的命名空间和治理边界，可以用来隔离不同业务模块、不同租户或不同职责对象。随着数据库规模增大，若所有表、视图、函数和类型都堆在 public 下，命名冲突和权限边界都会迅速失控。也正因为如此，学习 PostgreSQL 时应把 Schema 看作对象组织能力的一部分，它体现了 PostgreSQL 对长期结构治理的重视。

4. 数据类型与表操作 ​

4.1 常用数据类型 ​

PostgreSQL 支持丰富的数据类型：

类别类型说明
数值SMALLINT, INT, BIGINT, NUMERIC(p,s), REAL, DOUBLE PRECISIONNUMERIC 精确小数
字符CHAR(n), VARCHAR(n), TEXTTEXT 不限长度
布尔BOOLEANTRUE/FALSE/NULL
日期时间DATE, TIME, TIMESTAMP, TIMESTAMPTZ, INTERVALTIMESTAMPTZ 含时区
特殊UUID, JSON, JSONB, ARRAY, 范围类型PostgreSQL 特色

PostgreSQL 的数据类型之所以常被强调，是因为它鼓励把更多业务语义直接表达在数据库结构中，而不是一律退回到字符串或数字再由应用层解释。例如，范围类型可以自然表达时间区间和数值区间，数组可以表达固定边界内的多值属性，TIMESTAMPTZ 可以更明确地处理带时区时间。类型越贴近业务语义，数据库越容易在约束、查询和索引上给出正确支持；若把本该有清晰结构的信息全部压平为通用字符串，后续很多能力就会被迫挪到应用层手工完成。

4.2 创建表 ​

sql
CREATE TABLE students (
    id         SERIAL PRIMARY KEY,
    sno        VARCHAR(9)  NOT NULL UNIQUE,
    sname      VARCHAR(50) NOT NULL,
    ssex       CHAR(1)     CHECK (ssex IN ('男', '女')),
    sage       SMALLINT    CHECK (sage >= 0 AND sage <= 150),
    sdept      VARCHAR(30),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

SERIAL 为自增整数类型，等价于 INTEGER 配合 nextval 序列。

从教材角度看，PostgreSQL 建表语句最值得学习的地方，不只是语法格式，而是它鼓励把数据合法性尽量前置到定义阶段。NOT NULL、CHECK、唯一约束和外键并不是为了增加写表时的麻烦，而是在告诉数据库哪些状态从一开始就是不允许存在的。只要这些边界在表结构层定义清楚，后续查询、统计和权限控制都会稳定得多。很多系统后来难以维护，不是查询不会写，而是建模阶段就没有把约束下沉。

4.3 修改表 ​

sql
ALTER TABLE students ADD COLUMN phone VARCHAR(20);
ALTER TABLE students ALTER COLUMN sname TYPE VARCHAR(80);
ALTER TABLE students RENAME COLUMN sdept TO department;
ALTER TABLE students DROP COLUMN phone;

5. 数据操纵 (DML) ​

5.1 插入、更新、删除 ​

sql
INSERT INTO students (sno, sname, ssex, sage, sdept)
VALUES ('2021001', '张三', '男', 20, '计算机系');

UPDATE students SET sage = sage + 1 WHERE sdept = '计算机系';

DELETE FROM students WHERE sno = '2021004';

5.2 插入冲突处理 (ON CONFLICT) ​

PostgreSQL 支持 INSERT ... ON CONFLICT 实现 upsert：

sql
INSERT INTO students (sno, sname, sage)
VALUES ('2021001', '张三', 20)
ON CONFLICT (sno) DO UPDATE SET sname = EXCLUDED.sname, sage = EXCLUDED.sage;

ON CONFLICT 的教学价值很高，因为它把“并发写入下如何稳定维护唯一事实”这个问题变得非常直观。现实系统里，重复请求、并发重试和网络抖动都可能让同一业务动作被提交多次。如果只靠应用层先查后写，往往会在并发窗口中留下漏洞；把唯一约束与冲突处理直接写进数据库语句里，边界会清晰得多。也就是说，PostgreSQL 不只是提供了一个方便语法，更是提供了一种把幂等与唯一性下沉到数据层的方式。

6. 查询进阶 ​

6.1 基本查询 ​

PostgreSQL 的 SELECT 语法与标准 SQL 一致，支持 DISTINCT、AS 别名、WHERE、ORDER BY、LIMIT、OFFSET 等。

6.2 高级特性 ​

DISTINCT ON：按指定列去重，保留每组的第一行（配合 ORDER BY 使用）：

sql
SELECT DISTINCT ON (sdept) sno, sname, sdept, sage
FROM students
ORDER BY sdept, sage DESC;

RETURNING：在 INSERT/UPDATE/DELETE 后返回受影响的行：

sql
INSERT INTO students (sno, sname) VALUES ('2021999', '新同学')
RETURNING id, sno, sname, created_at;

FILTER 子句：在聚合函数中按条件过滤：

sql
SELECT sdept,
       COUNT(*) AS total,
       COUNT(*) FILTER (WHERE sage >= 20) AS adult_count
FROM students
GROUP BY sdept;

这些语法看似只是“更方便的写法”，其实反映了 PostgreSQL 强调复杂查询表达能力的特点。DISTINCT ON、RETURNING、FILTER 这类特性，本质上都是在帮助用户更紧凑、更准确地表达查询意图。但需要注意，表达能力越强，越要求对执行计划保持敏感。真正的性能分析不能只看“有没有使用索引”，而要看统计信息、过滤强度、连接基数和排序代价是否支持当前写法。对 PostgreSQL 来说，优化器是非常重要的能力中心，很多查询能否执行得好，取决于规划器是否得到了足够准确的统计信息。

6.3 窗口函数 ​

PostgreSQL 对窗口函数支持完善：

sql
SELECT sno, sname, sdept, sage,
       ROW_NUMBER() OVER (PARTITION BY sdept ORDER BY sage DESC) AS rn,
       AVG(sage) OVER (PARTITION BY sdept) AS dept_avg_age
FROM students;

窗口函数在 PostgreSQL 中非常值得重视，因为它最能体现“复杂查询能力完整”这一特点。很多原本需要多层子查询、临时表或者应用层二次处理的分析任务，在窗口函数支持下可以直接在数据库里完成，而且语义相对清晰。学习时要始终盯住两个核心问题：比较范围是谁，也就是分区边界；顺序依据是什么，也就是排序语义。只要这两个问题清楚，排名、累计、移动统计等分析功能就会变得可解释而稳定。

6.4 CTE 与递归查询 ​

sql
WITH dept_stats AS (
    SELECT sdept, COUNT(*) AS cnt, AVG(sage) AS avg_age
    FROM students
    GROUP BY sdept
)
SELECT * FROM dept_stats WHERE cnt > 5;

-- 递归 CTE 示例：组织层级
WITH RECURSIVE org_tree AS (
    SELECT id, name, parent_id, 1 AS level
    FROM department WHERE parent_id IS NULL
    UNION ALL
    SELECT d.id, d.name, d.parent_id, o.level + 1
    FROM department d JOIN org_tree o ON d.parent_id = o.id
)
SELECT * FROM org_tree;

CTE 与递归查询共同体现了 PostgreSQL 把复杂逻辑“结构化表达”的能力。递归查询尤其适合处理组织结构、地区层级、目录树和依赖关系这类天然层次化对象。若没有递归 CTE，很多层级问题要么写成冗长且固定层数的自连接，要么被迫在应用层手工递归。PostgreSQL 在数据库层直接提供这种能力，意味着很多复杂结构并不必然需要脱离关系数据库才能表达。

7. JSON 与全文检索 ​

7.1 JSON 类型 ​

PostgreSQL 支持 JSON（存储原样）和 JSONB（二进制存储，支持索引和操作符）：

sql
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    data JSONB
);

INSERT INTO events (data) VALUES ('{"user": "张三", "action": "登录", "time": "2026-03-20"}');

-- 查询与操作
SELECT data->>'user' AS user_name FROM events;
SELECT * FROM events WHERE data->>'action' = '登录';
SELECT * FROM events WHERE data @> '{"user": "张三"}';

PostgreSQL 提供 JSON 和 JSONB，并不意味着关系结构从此不重要。更准确地说，它允许数据库在保持关系模型主体稳定的前提下，为那些变化较快、层级较深、又需要一定查询能力的附加属性提供更灵活的表达方式。若核心业务字段长期稳定、需要频繁连接和统计，仍应优先使用明确列结构；若是配置项、扩展属性、事件内容等弹性部分，JSONB 则能提供很好的补充。把 JSONB 当作结构灵活区域的承载手段，而不是“万物皆可塞进去”的通用替代品，才是更稳妥的用法。

7.2 全文检索 ​

sql
-- 创建全文检索配置与索引
ALTER TABLE articles ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (to_tsvector('simple', title || ' ' || content)) STORED;
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

-- 查询
SELECT * FROM articles WHERE search_vector @@ to_tsquery('简单', '数据库 & 设计');

全文检索能力说明 PostgreSQL 并不只关心严格结构化数据，也愿意把部分文本搜索任务吸收到数据库内部完成。但这同样需要边界意识。数据库可以高效处理一定范围内的检索需求，并不意味着所有复杂搜索都应该压在同一个实例上。真正成熟的设计会先判断检索规模、相关性需求和更新频率，再决定是用数据库内置全文检索，还是借助专门搜索系统协同完成。

8. 约束、索引与视图 ​

8.1 约束 ​

PostgreSQL 支持主键、唯一、非空、CHECK、外键等约束，且对 CHECK 的支持完善（MySQL 8.0.16 之前不支持）：

sql
CREATE TABLE sc (
    sno   VARCHAR(9),
    cno   VARCHAR(4),
    grade SMALLINT CHECK (grade >= 0 AND grade <= 100),
    PRIMARY KEY (sno, cno),
    FOREIGN KEY (sno) REFERENCES students(sno) ON DELETE CASCADE
);

8.2 索引 ​

sql
CREATE INDEX idx_students_sdept ON students(sdept);
CREATE UNIQUE INDEX idx_students_sno ON students(sno);
CREATE INDEX idx_students_sdept_sage ON students(sdept, sage);

-- 部分索引
CREATE INDEX idx_adult_students ON students(sage) WHERE sage >= 18;

-- GIN 索引（JSONB、数组、全文检索）
CREATE INDEX idx_events_data ON events USING GIN (data);

PostgreSQL 的索引能力之所以有代表性，在于它不只提供最常见的 B 树，还针对不同数据结构和访问模式提供更贴合的索引方法。部分索引适合只覆盖热点子集，GIN 适合处理 JSONB、数组与全文检索，唯一索引则直接承担业务约束责任。学习索引时，最重要的不是记住名字，而是理解它们分别在解决什么访问问题。只要把索引和数据分布、查询路径、更新成本一起思考，就不会把索引当成机械“加速开关”。

8.3 视图 ​

sql
CREATE VIEW cs_students AS
SELECT sno, sname, sage FROM students WHERE sdept = '计算机系';

-- 物化视图（存储实际数据，需手动 REFRESH）
CREATE MATERIALIZED VIEW mv_dept_stats AS
SELECT sdept, COUNT(*) AS cnt, AVG(sage) AS avg_age
FROM students GROUP BY sdept;
REFRESH MATERIALIZED VIEW mv_dept_stats;

9. 触发器与存储过程 ​

9.1 触发器 ​

sql
CREATE OR REPLACE FUNCTION audit_trigger_func()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (table_name, action, old_data, new_data, changed_at)
    VALUES (TG_TABLE_NAME, TG_OP, row_to_json(OLD), row_to_json(NEW), NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_students_audit
AFTER INSERT OR UPDATE OR DELETE ON students
FOR EACH ROW EXECUTE FUNCTION audit_trigger_func();

9.2 存储过程与函数 ​

sql
-- 函数
CREATE OR REPLACE FUNCTION get_dept_count()
RETURNS INT AS $$
BEGIN
    RETURN (SELECT COUNT(DISTINCT sdept) FROM students);
END;
$$ LANGUAGE plpgsql;

-- 存储过程（支持事务控制）
CREATE OR REPLACE PROCEDURE transfer_credits(
    p_sno VARCHAR, p_from VARCHAR, p_to VARCHAR, p_amount INT
) AS $$
BEGIN
    UPDATE credits SET amount = amount - p_amount WHERE sno = p_sno AND course = p_from;
    UPDATE credits SET amount = amount + p_amount WHERE sno = p_sno AND course = p_to;
    COMMIT;
END;
$$ LANGUAGE plpgsql;

PostgreSQL 的函数和过程之所以强大，是因为它们不仅能封装逻辑，还能和权限、事务与对象体系结合起来形成更稳固的数据边界。对某些需要靠近数据执行的规则、统计和校验任务，把逻辑写成函数往往比在应用层分散实现更清晰；但若逻辑高度依赖外部服务和频繁迭代，继续留在应用层通常更合适。也就是说，PostgreSQL 提供了把部分业务规则下沉的能力，但是否下沉，仍然要看边界是否清晰、治理是否跟得上。

10. 事务、锁与并发控制 ​

10.1 事务 ​

PostgreSQL 默认自动提交，可显式使用事务：

sql
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- 或 ROLLBACK;

10.2 隔离级别 ​

支持 READ UNCOMMITTED、READ COMMITTED（默认）、REPEATABLE READ、SERIALIZABLE。通过 MVCC 实现读写不阻塞。

理解 PostgreSQL 的并发控制，关键不在于记住默认级别是什么，而在于把 MVCC 真正看成版本化存储机制。更新一行数据时，系统通常并不是在原位置直接覆盖，而是形成新版本；不同事务根据自己的快照规则看到不同的合法版本。这样做换来了读写互不阻塞的高并发能力，但也带来了旧版本回收的问题。也正因为如此，MVCC 从来不是“更先进的锁”这么简单，而是一种空间换时间、版本换并发的设计取舍。

10.3 锁 ​

sql
-- 行级锁
SELECT * FROM students WHERE sno = '2021001' FOR UPDATE;

-- 咨询锁（应用层协调）
SELECT pg_advisory_lock(12345);
-- ... 临界区 ...
SELECT pg_advisory_unlock(12345);

锁与 MVCC 结合起来理解，才能真正看清 PostgreSQL 的并发模型。MVCC 负责让大多数读写互不阻塞，锁则在必须明确协调访问顺序时发挥作用，例如防止同一行被并发修改、保证某些关键资源在临界区内独占。也正因为有了这两层配合，PostgreSQL 才能同时兼顾高并发下的读取效率和关键写操作的一致性。教学中若把 MVCC 和锁割裂开看，就会误以为 PostgreSQL 只靠“读不加锁”运行，这是不完整的理解。

11. 权限与安全 ​

11.1 角色与用户 ​

在 PostgreSQL 中，角色 (Role) 可表示用户或用户组：

sql
CREATE ROLE app_user WITH LOGIN PASSWORD 'secret';
GRANT CONNECT ON DATABASE school TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE ON students TO app_user;
REVOKE UPDATE ON students FROM app_user;

11.2 行级安全 (RLS) ​

PostgreSQL 支持行级安全策略，实现行级访问控制：

sql
ALTER TABLE students ENABLE ROW LEVEL SECURITY;

CREATE POLICY student_policy ON students
FOR ALL TO app_user
USING (sdept = current_setting('app.current_dept'));

RLS 的意义非常值得在教材里掰开讲。很多系统的权限控制并不只是“某个用户能不能访问某张表”，而是“能访问这张表里的哪一部分数据”。例如同样是学生表，不同院系管理员看到的行范围就不同。若把这种规则全部放在应用层手写过滤条件，随着系统增长很容易失控；把它下沉到数据库层，就能让访问边界更统一、更难绕过。PostgreSQL 提供 RLS，正体现了它把权限治理看成数据库核心能力的一部分，而不是附属特性。

12. 备份、恢复与维护 ​

12.1 逻辑备份 ​

bash
# 导出
pg_dump -U postgres school > school_backup.sql

# 导入
psql -U postgres school < school_backup.sql

# 压缩格式
pg_dump -Fc -U postgres school > school.dump
pg_restore -d school school.dump

12.2 维护 ​

  • VACUUM：回收死元组占用的空间。
  • ANALYZE：更新统计信息，供查询优化器使用。
  • REINDEX：重建索引。
  • pg_stat_activity：查看当前连接与查询。

VACUUM 之所以在 PostgreSQL 中如此重要，正是因为 MVCC 会不断留下旧版本。若这些死元组长期得不到回收，表和索引都会膨胀，缓存命中下降，查询性能也会逐渐恶化。因此，VACUUM 不是一个可有可无的“保洁命令”，而是维持版本化存储体系长期健康运行的关键环节。对应地，ANALYZE 也不只是更新元数据，它直接影响优化器的基数估计和执行计划选择。很多 PostgreSQL 性能问题，最终都能追溯到版本回收、统计信息和长事务治理这三条线索上。

备份与恢复同样不能只停留在 pg_dump 的命令记忆上。逻辑备份适合结构迁移和对象级恢复，但在更大规模生产场景中，往往还需要结合物理备份、WAL 归档和时间点恢复方案。真正成熟的 PostgreSQL 运维，会明确恢复点目标和恢复时间目标，并通过演练确认“出问题后到底能恢复到哪里、需要多久恢复”。数据库是否可靠，不能只看平时能不能跑，还要看故障后能不能稳妥救回来。

13. PostgreSQL 与 MySQL 的选型对比 ​

特性PostgreSQLMySQL
标准兼容更严格遵循 SQL 标准部分方言
复杂查询窗口函数、CTE、递归等支持完善近年逐步增强
JSONJSONB 原生支持，可索引JSON 类型，功能较简
扩展性丰富的 Extension 生态相对有限
复制与集群流复制、逻辑复制主从、组复制
生态与工具企业、GIS、分析场景常见Web 应用、互联网常见
学习曲线稍陡相对平缓

PostgreSQL 适合对 SQL 标准、数据类型、扩展能力要求较高的场景；MySQL 在 Web 生态、运维熟悉度方面仍有优势。二者均为优秀的开源数据库，可根据项目需求选择。

从更具体的场景看，PostgreSQL 的优势通常体现在结构复杂、规则严格、分析要求高的业务中。它不是单靠某一个功能胜出，而是依靠复杂查询能力、丰富数据类型、扩展生态、MVCC 与恢复体系、权限治理等多方面能力组合形成整体优势。也正因为它功能更完整，使用时更需要系统观：不能只看单条 SQL 或单个扩展，而要把查询、存储、并发、恢复和治理放到同一个框架里理解。学会 PostgreSQL，某种意义上也是在训练这种从整体看数据库系统的能力。

🔒 会员专属内容

检查登录状态中...