PostgreSQL 系统学习文档

适合人群：已经了解基础后端开发，希望系统掌握关系型数据库、SQL、事务、索引、查询优化和生产实践的学习者。
学习目标：从“数据库是什么”开始，逐步掌握 PostgreSQL 的 SQL 基础、表结构设计、约束、查询、事务、索引、执行计划、权限、安全、备份和项目接入。
核对时间：2026-06-25。PostgreSQL 官方当前文档指向 PostgreSQL 18 文档，本文以官方 current 文档为主整理。
官方资料：
PostgreSQL 文档首页：https://www.postgresql.org/docs/current/
PostgreSQL SQL 教程：https://www.postgresql.org/docs/current/tutorial.html
PostgreSQL 索引章节：https://www.postgresql.org/docs/current/indexes.html
PostgreSQL 事务教程：https://www.postgresql.org/docs/current/tutorial-transactions.html
PostgreSQL 事务隔离：https://www.postgresql.org/docs/current/transaction-iso.html

第一篇：数据库基础

1. PostgreSQL 是什么

PostgreSQL 是一个开源的对象关系型数据库系统。它通常用于存储结构化业务数据，例如用户、订单、文章、评论、库存、支付记录等。

它的特点：

支持标准 SQL，并有大量扩展能力。
支持事务、外键、视图、触发器、存储过程。
支持丰富数据类型，包括数组、JSONB、UUID、时间、范围类型。
支持强大的索引能力，例如 B-tree、GIN、GiST、BRIN。
在可靠性、数据一致性和复杂查询方面表现优秀。

如果 MySQL 更常见于大量互联网业务入门场景，那么 PostgreSQL 往往更受重视数据一致性、复杂查询、地理信息、JSON 查询和工程规范的项目欢迎。

2. 关系型数据库核心概念

关系型数据库把数据组织成表。表由行和列组成。

用户表示例：

id	email	name	created_at
1	a@example.com	Alice	2026-06-25
2	b@example.com	Bob	2026-06-25

核心概念：

表：同一类实体的集合，例如 users。
行：一条具体记录。
列：记录的某个属性。
主键：唯一标识一行数据。
外键：表达两个表之间的关系。
SQL：操作关系型数据库的语言。

学习 PostgreSQL 的关键不是背命令，而是理解“如何把业务关系建模成表结构”，以及“如何用 SQL 准确、高效地取出数据”。

3. 数据库、模式、表、行、列

PostgreSQL 的层级可以简化理解为：

text

PostgreSQL 实例
└── database 数据库
    └── schema 模式
        └── table 表
            ├── row 行
            └── column 列

常见默认 schema 是 public。

数据库和 schema 的区别：

database 更像一个独立数据库空间，不同 database 之间隔离更强。
schema 是同一个 database 内的命名空间，可以用来组织表。

实际项目里：

小项目通常一个 database，一个 public schema。
中大型项目可能按业务模块拆 schema。
多租户项目可能使用 schema 隔离租户，但要慎重评估运维成本。

4. 安装与连接

常见安装方式：

本机安装 PostgreSQL。
使用 Docker 启动 PostgreSQL。
使用云数据库服务。

Docker 示例：

bash

docker run --name postgres-demo \
  -e POSTGRES_USER=app \
  -e POSTGRES_PASSWORD=secret \
  -e POSTGRES_DB=appdb \
  -p 5432:5432 \
  -d postgres:18

连接字符串通常长这样：

text

postgresql://app:secret@localhost:5432/appdb

含义：

app：用户名
secret：密码
localhost：主机
5432：端口
appdb：数据库名

5. psql 基础命令

psql 是 PostgreSQL 官方命令行客户端。

连接：

bash

psql "postgresql://app:secret@localhost:5432/appdb"

常用命令：

sql

\l        -- 查看数据库
\c appdb  -- 切换数据库
\dt       -- 查看当前 schema 下的表
\d users  -- 查看 users 表结构
\dn       -- 查看 schema
\du       -- 查看角色
\q        -- 退出

注意：以反斜杠开头的是 psql 元命令，不是 SQL。真正的 SQL 一般需要以分号结尾。

第二篇：SQL 基础

6. 数据类型

常用类型：

sql

id              bigint
name            text
email           varchar(255)
age             integer
price           numeric(10, 2)
is_active       boolean
created_at      timestamptz
profile         jsonb
tags            text[]

类型选择建议：

主键可用 bigint generated always as identity 或 uuid。
金额不要用浮点数，优先用 numeric 或用整数存最小单位。
时间优先使用 timestamptz，避免时区混乱。
长文本用 text，不必过度纠结 varchar。
半结构化数据可用 jsonb，但不要滥用 JSON 替代表关系。

时间类型重点：

timestamp 不带时区。
timestamptz 带时区语义，通常更适合业务系统。

7. 创建表

用户表：

sql

create table users (
  id bigint generated always as identity primary key,
  email text not null unique,
  name text not null,
  created_at timestamptz not null default now()
);

文章表：

sql

create table posts (
  id bigint generated always as identity primary key,
  author_id bigint not null references users(id),
  title text not null,
  content text not null,
  published boolean not null default false,
  created_at timestamptz not null default now()
);

解释：

primary key 保证唯一且非空。
not null 保证字段必须有值。
unique 保证值不能重复。
default now() 在插入时自动使用当前时间。
references users(id) 建立外键关系。

8. 插入、查询、更新、删除

插入：

sql

insert into users (email, name)
values ('alice@example.com', 'Alice');

查询：

sql

select id, email, name
from users;

更新：

sql

update users
set name = 'Alice Chen'
where id = 1;

删除：

sql

delete from users
where id = 1;

最重要的安全习惯：更新和删除必须认真写 where。没有 where 的 update 或 delete 会影响整张表。

9. 条件、排序、分页

条件：

sql

select *
from posts
where published = true
  and created_at >= now() - interval '7 days';

模糊匹配：

sql

select *
from posts
where title ilike '%postgresql%';

排序：

sql

select *
from posts
order by created_at desc;

分页：

sql

select *
from posts
order by id desc
limit 20 offset 40;

limit offset 简单易懂，但大偏移分页性能会变差。高性能场景常用游标分页：

sql

select *
from posts
where id < 1000
order by id desc
limit 20;

10. 聚合和分组

统计用户数量：

sql

select count(*) from users;

按作者统计文章数：

sql

select author_id, count(*) as post_count
from posts
group by author_id;

筛选聚合结果：

sql

select author_id, count(*) as post_count
from posts
group by author_id
having count(*) >= 10;

区别：

where 在分组前过滤行。
having 在分组后过滤聚合结果。

11. 多表关联

内连接：

sql

select posts.id, posts.title, users.name as author_name
from posts
join users on users.id = posts.author_id;

左连接：

sql

select users.id, users.name, posts.title
from users
left join posts on posts.author_id = users.id;

理解：

join 只返回匹配上的记录。
left join 保留左表所有记录，即使右表没有匹配。

多表查询的重点是先搞清楚业务问题：

你要以哪张表为主？
缺失关联数据时是否仍要保留主表记录？
结果是一行一个实体，还是一行一个关联关系？

第三篇：表设计与数据建模

12. 主键、外键和唯一约束

主键：

sql

id bigint generated always as identity primary key

唯一约束：

sql

email text not null unique

外键：

sql

author_id bigint not null references users(id)

外键的价值：

保证引用的数据真实存在。
防止出现“孤儿数据”。
让数据关系更明确。

外键删除策略：

sql

author_id bigint references users(id) on delete cascade

常见策略：

restrict 或默认行为：被引用数据不能随便删。
cascade：删除父记录时自动删除子记录。
set null：删除父记录时把外键置空。

不要盲目使用 cascade。例如删除用户是否应该删除订单，通常要根据业务合规和审计需求判断。

13. NOT NULL、CHECK 和 DEFAULT

not null：

sql

name text not null

default：

sql

created_at timestamptz not null default now()

check：

sql

price numeric(10, 2) not null check (price >= 0)

状态字段：

sql

status text not null check (status in ('pending', 'paid', 'cancelled'))

约束的意义是把业务底线放到数据库层。应用层校验可以提升用户体验，但数据库约束负责最终兜底。

14. 一对一、一对多、多对多

一对多：一个用户有多篇文章。

sql

create table posts (
  id bigint generated always as identity primary key,
  author_id bigint not null references users(id),
  title text not null
);

多对多：文章和标签。

sql

create table tags (
  id bigint generated always as identity primary key,
  name text not null unique
);

create table post_tags (
  post_id bigint not null references posts(id) on delete cascade,
  tag_id bigint not null references tags(id) on delete cascade,
  primary key (post_id, tag_id)
);

多对多通常通过中间表实现。中间表可以只有两个外键，也可以包含额外字段，例如添加时间、排序权重、创建人。

一对一：用户和用户资料。

sql

create table user_profiles (
  user_id bigint primary key references users(id) on delete cascade,
  bio text,
  avatar_url text
);

一对一的关键是外键列同时具有唯一性，常见写法是直接让外键成为主键。

15. 范式和反范式

范式的目标是减少重复和异常。

例如不要在订单表里重复存用户邮箱和用户名称作为主数据：

text

orders
- id
- user_id
- user_email
- user_name

更规范：

text

users
- id
- email
- name

orders
- id
- user_id

但是反范式也有合理场景。订单快照就可能需要保留下单时的收货地址、商品名称和价格，因为这些历史信息不应该随用户或商品表后续变化而变化。

判断标准：

当前字段是实时引用，还是历史快照？
重复数据是否会产生更新不一致？
查询性能是否真的需要冗余？
冗余字段是否有明确同步机制？

16. 命名规范和字段设计

建议：

表名使用复数或单数都可以，但项目内必须统一。
字段使用 snake_case。
主键统一命名为 id。
外键使用 实体_id，例如 user_id。
时间字段常用 created_at, updated_at, deleted_at。
布尔字段使用清晰语义，例如 is_active, published。

软删除：

sql

deleted_at timestamptz

优点是可以恢复和审计；缺点是所有查询都要注意过滤 deleted_at is null，唯一约束也可能更复杂。

第四篇：进阶查询

17. 子查询

查询文章数超过 10 的用户：

sql

select *
from users
where id in (
  select author_id
  from posts
  group by author_id
  having count(*) > 10
);

子查询适合把一个查询结果作为另一个查询的条件。复杂场景下，CTE 通常更易读。

18. CTE

CTE 使用 with 定义临时结果集。

sql

with post_counts as (
  select author_id, count(*) as count
  from posts
  group by author_id
)
select users.id, users.name, post_counts.count
from users
join post_counts on post_counts.author_id = users.id
where post_counts.count > 10;

优点：

拆分复杂 SQL。
提升可读性。
便于逐步调试。

19. 窗口函数

窗口函数用于“在保留原始行的同时进行分组计算”。

例如给每个作者的文章按发布时间排名：

sql

select
  id,
  author_id,
  title,
  created_at,
  row_number() over (
    partition by author_id
    order by created_at desc
  ) as author_post_rank
from posts;

常见窗口函数：

row_number()
rank()
dense_rank()
lag()
lead()
sum() over (...)

适合场景：

排名
同比环比
获取上一条/下一条
分组内 Top N
累计求和

20. JSON/JSONB

PostgreSQL 支持 json 和 jsonb。业务中更常用 jsonb，因为它更适合查询和索引。

示例：

sql

create table events (
  id bigint generated always as identity primary key,
  type text not null,
  payload jsonb not null,
  created_at timestamptz not null default now()
);

插入：

sql

insert into events (type, payload)
values (
  'user_login',
  '{"ip": "127.0.0.1", "device": "desktop"}'
);

查询：

sql

select *
from events
where payload ->> 'device' = 'desktop';

使用建议：

稳定、核心、需要关联的字段应建成普通列。
扩展属性、事件载荷、第三方回调原文适合 JSONB。
高频 JSONB 查询要考虑 GIN 索引。

21. 视图和物化视图

视图是保存好的查询：

sql

create view published_posts as
select *
from posts
where published = true;

物化视图会保存查询结果：

sql

create materialized view author_stats as
select author_id, count(*) as post_count
from posts
group by author_id;

刷新：

sql

refresh materialized view author_stats;

区别：

视图不保存数据，本质是查询封装。
物化视图保存结果，查询快但需要刷新。

第五篇：事务、索引与性能

22. 事务 ACID

事务是一组要么全部成功、要么全部失败的操作。

sql

begin;

update accounts set balance = balance - 100 where id = 1;
update accounts set balance = balance + 100 where id = 2;

commit;

如果中途失败：

sql

rollback;

ACID：

Atomicity 原子性：事务内操作不可分割。
Consistency 一致性：事务前后数据满足约束。
Isolation 隔离性：并发事务之间互不混乱。
Durability 持久性：提交后数据可靠保存。

转账、库存扣减、订单支付等场景必须认真使用事务。

23. 隔离级别和并发问题

常见并发问题：

脏读：读到其他事务尚未提交的数据。
不可重复读：同一事务内两次读取同一行结果不同。
幻读：同一事务内两次查询范围结果数量不同。
更新丢失：两个事务同时改同一数据，后提交覆盖先提交。

SQL 标准隔离级别：

Read Uncommitted
Read Committed
Repeatable Read
Serializable

PostgreSQL 默认通常是 Read Committed。官方文档说明 PostgreSQL 可以请求四种标准隔离级别，但内部实现并不是四种完全不同机制。

设置事务隔离级别：

sql

begin transaction isolation level repeatable read;
...
commit;

实践建议：

普通 CRUD 多数使用默认隔离级别。
金额、库存、唯一业务动作要结合事务、锁和唯一约束。
不要只依赖应用层判断“看起来还有库存”，必须让数据库保证最终一致。

24. MVCC 基础

MVCC 是多版本并发控制。它的核心思想是：读操作通常不必阻塞写操作，写操作也不一定阻塞读操作，数据库通过记录版本让不同事务看到不同的数据快照。

你可以这样理解：

每次更新不是简单覆盖旧行，而是产生新版本。
不同事务根据自己的可见性规则看到不同版本。
已经无用的旧版本之后由清理机制回收。

MVCC 带来的好处：

提升并发读写能力。
降低读写互相阻塞。

需要关注的问题：

长事务会让旧版本无法及时清理。
表膨胀会影响性能。
需要理解 autovacuum 的作用。

25. 索引原理和类型

索引类似书的目录。没有索引时，数据库可能需要扫描大量行；有合适索引时，可以更快定位数据。

创建索引：

sql

create index idx_posts_author_id on posts(author_id);

复合索引：

sql

create index idx_posts_author_created on posts(author_id, created_at desc);

唯一索引：

sql

create unique index idx_users_email on users(email);

部分索引：

sql

create index idx_posts_published_created
on posts(created_at desc)
where published = true;

常见索引类型：

B-tree：默认索引，适合等值、范围、排序。
GIN：适合 JSONB、数组、全文搜索等。
GiST：适合几何、范围、全文等扩展场景。
BRIN：适合超大表且数据天然按物理顺序分布的场景。

索引不是越多越好：

索引会占磁盘空间。
写入、更新、删除时索引也要维护。
不合适的索引可能完全不会被使用。

26. EXPLAIN 与慢查询优化

查看执行计划：

sql

explain
select *
from posts
where author_id = 1
order by created_at desc
limit 20;

查看真实执行：

sql

explain analyze
select *
from posts
where author_id = 1
order by created_at desc
limit 20;

关注点：

是否走索引扫描。
是否全表扫描。
估算行数和实际行数差异是否很大。
排序是否代价很高。
join 顺序是否合理。

优化思路：

明确慢查询对应的业务场景。
查看 SQL 是否取了不必要字段。
检查 where、join、order by 是否有合适索引。
分析执行计划，而不是凭感觉加索引。
必要时调整表结构、分页方式或缓存策略。

27. 锁和死锁

锁用于保护并发修改的数据一致性。

显式行锁：

sql

begin;

select *
from products
where id = 1
for update;

update products
set stock = stock - 1
where id = 1 and stock > 0;

commit;

死锁通常发生在两个事务以不同顺序持有对方需要的锁。

减少死锁：

多个事务按相同顺序访问资源。
事务尽量短。
不在事务中做慢网络请求。
对高并发热点数据使用更清晰的扣减策略。

第六篇：生产实践

28. 用户、角色和权限

创建角色：

sql

create role app_user login password 'secret';

授权：

sql

grant connect on database appdb to app_user;
grant usage on schema public to app_user;
grant select, insert, update, delete on all tables in schema public to app_user;

原则：

应用账号不要使用超级用户。
开发、测试、生产使用不同账号和数据库。
只授予应用需要的最小权限。
数据库密码不要提交到 Git。

29. 备份与恢复

逻辑备份：

bash

pg_dump "postgresql://app:secret@localhost:5432/appdb" > backup.sql

恢复：

bash

psql "postgresql://app:secret@localhost:5432/appdb" < backup.sql

生产环境要考虑：

定时备份。
备份文件加密。
异地保存。
定期演练恢复。
明确 RPO 和 RTO。

只做备份不做恢复演练，等于没有真正验证备份可用。

30. 迁移与版本管理

数据库结构变化应该版本化，而不是手动在生产库上随意改。

常见工具：

Prisma Migrate
Flyway
Liquibase
Knex migrations
TypeORM migrations

迁移文件应进入 Git。团队协作中，数据库结构变更需要像代码一样评审。

迁移注意：

大表加字段、建索引可能锁表或耗时。
删除字段前先确认应用不再读取。
拆分危险变更，分阶段发布。
生产变更前在备份或影子库验证。

31. Node.js/Prisma 项目接入

Node.js 原生驱动常用 pg：

import pg from "pg";

const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
});

const result = await pool.query("select * from users where id = $1", [id]);

注意使用参数化查询，避免 SQL 注入。

Prisma ORM 则通过模型和类型安全 Client 操作数据库：

const user = await prisma.user.findUnique({
  where: { id: 1 },
});

选择建议：

想精细控制 SQL：直接用 pg 或 query builder。
想提升 CRUD 效率和类型安全：使用 Prisma。
复杂报表和性能关键查询：即使用 ORM，也应能写原生 SQL 并看执行计划。

32. 常见误区和学习路线

误区一：会写 ORM 就等于会数据库。

纠正：ORM 只是工具。表设计、索引、事务和 SQL 才是数据库能力的核心。

误区二：查询慢就加索引。

纠正：先看执行计划，再判断是索引、SQL、数据量、分页、join 还是统计信息问题。

误区三：所有数据都放 JSONB 更灵活。

纠正：核心业务字段应结构化建模。JSONB 适合扩展属性，不适合替代表设计。

误区四：事务会降低性能，所以少用。

纠正：关键一致性场景必须使用事务。真正要避免的是长事务和不必要的大事务。

学习路线：

学会基本 SQL：select、insert、update、delete。
掌握 join、group by、having、子查询。
学会表设计：主键、外键、唯一约束、关系建模。
理解事务和隔离级别。
学会建立和分析索引。
能阅读 explain analyze。
学会备份、权限和迁移。
在真实项目中结合 Prisma 或 Node.js 驱动实践。

实战练习项目

项目一：博客系统

users
posts
comments
tags
post_tags

练习点：

一对多、多对多
分页查询
文章搜索
标签筛选
评论数量统计

项目二：订单系统

users
products
orders
order_items
payments

练习点：

事务
库存扣减
金额字段设计
订单快照
状态流转

项目三：后台审计系统

users
roles
permissions
audit_logs

练习点：

权限模型
JSONB 存审计上下文
时间范围查询
索引优化
数据归档

快速记忆清单

PostgreSQL 是关系型数据库，SQL 和表设计是核心。
主键保证记录身份，外键保证关系一致。
应用层校验负责体验，数据库约束负责底线。
金额不要用浮点数，时间优先用 timestamptz。
事务保证一组操作要么都成功，要么都失败。
索引能提升读性能，但会增加写入成本。
慢查询要看 explain analyze，不要凭感觉优化。
生产库账号遵循最小权限。
数据库迁移必须版本化、可回滚、可审查。

PostgreSQL 系统学习文档 ​

目录 ​

第一篇：数据库基础 ​

第二篇：SQL 基础 ​

第三篇：表设计与数据建模 ​

第四篇：进阶查询 ​

第五篇：事务、索引与性能 ​

第六篇：生产实践 ​

第一篇：数据库基础 ​

1. PostgreSQL 是什么 ​

2. 关系型数据库核心概念 ​

3. 数据库、模式、表、行、列 ​

4. 安装与连接 ​

5. psql 基础命令 ​

第二篇：SQL 基础 ​

6. 数据类型 ​

7. 创建表 ​

8. 插入、查询、更新、删除 ​

9. 条件、排序、分页 ​

10. 聚合和分组 ​

11. 多表关联 ​

第三篇：表设计与数据建模 ​

12. 主键、外键和唯一约束 ​

13. NOT NULL、CHECK 和 DEFAULT ​

14. 一对一、一对多、多对多 ​

15. 范式和反范式 ​

16. 命名规范和字段设计 ​

第四篇：进阶查询 ​

17. 子查询 ​

18. CTE ​

19. 窗口函数 ​

20. JSON/JSONB ​

21. 视图和物化视图 ​

第五篇：事务、索引与性能 ​

22. 事务 ACID ​

23. 隔离级别和并发问题 ​

24. MVCC 基础 ​

25. 索引原理和类型 ​

26. EXPLAIN 与慢查询优化 ​

27. 锁和死锁 ​

第六篇：生产实践 ​

28. 用户、角色和权限 ​

29. 备份与恢复 ​

30. 迁移与版本管理 ​

31. Node.js/Prisma 项目接入 ​

32. 常见误区和学习路线 ​

实战练习项目 ​

快速记忆清单 ​

PostgreSQL 系统学习文档

目录

第一篇：数据库基础

第二篇：SQL 基础

第三篇：表设计与数据建模

第四篇：进阶查询

第五篇：事务、索引与性能

第六篇：生产实践

第一篇：数据库基础

1. PostgreSQL 是什么

2. 关系型数据库核心概念

3. 数据库、模式、表、行、列

4. 安装与连接

5. psql 基础命令

第二篇：SQL 基础

6. 数据类型

7. 创建表

8. 插入、查询、更新、删除

9. 条件、排序、分页

10. 聚合和分组

11. 多表关联

第三篇：表设计与数据建模

12. 主键、外键和唯一约束

13. NOT NULL、CHECK 和 DEFAULT

14. 一对一、一对多、多对多

15. 范式和反范式

16. 命名规范和字段设计

第四篇：进阶查询

17. 子查询

18. CTE

19. 窗口函数

20. JSON/JSONB

21. 视图和物化视图

第五篇：事务、索引与性能

22. 事务 ACID

23. 隔离级别和并发问题

24. MVCC 基础

25. 索引原理和类型

26. EXPLAIN 与慢查询优化

27. 锁和死锁

第六篇：生产实践

28. 用户、角色和权限

29. 备份与恢复

30. 迁移与版本管理

31. Node.js/Prisma 项目接入

32. 常见误区和学习路线

实战练习项目

快速记忆清单