PostgreSQL 从入门到进阶

本文覆盖 PostgreSQL 的连接与工具、数据类型、表设计与约束、查询、索引与执行计划、事务与并发、权限、备份恢复与常用配置，可作为日常开发与运维的参考。

• 环境：PostgreSQL 16（大部分语法 13+ 通用）
• 客户端：psql、任意支持 PG 的 GUI（DBeaver、DataGrip）或 Node 驱动 pg

一、PostgreSQL 的特点与定位

PostgreSQL 是开源关系型数据库，特点：

• 严格遵循 SQL 标准，同时提供大量扩展（JSONB、数组、全文检索、地理类型）。
• 完整 ACID 与多版本并发控制（MVCC），读写互不阻塞。
• 支持复杂查询：窗口函数、CTE、递归查询、GROUPING SETS。
• 可扩展：自定义类型、函数、操作符、索引方法（GIN、GiST、BRIN）。
• 许可证宽松（PostgreSQL License）。

适用场景：业务主数据、需要事务与约束的系统、复杂报表查询。

不适合：超大规模写入的时序数据（考虑 TimescaleDB 或专用时序库）、纯文档型数据（MongoDB 更灵活）。

---

二、安装、启动与连接

2.1 Docker（推荐用于本地）

docker run -d --name pg \
  -e POSTGRES_USER=app \
  -e POSTGRES_PASSWORD=secret \
  -e POSTGRES_DB=appdb \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql/data \
  postgres:16-alpine

连接串：

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

2.2 包管理安装（Ubuntu）

sudo apt install -y postgresql-16
sudo systemctl enable --now postgresql
sudo -u postgres psql            # 切换到 postgres 系统用户

2.3 远程访问配置

postgresql.conf：

listen_addresses = '*'
port = 5432

pg_hba.conf：

# 允许内网 md5 认证
host    all    all    10.0.0.0/8    scram-sha-256

修改后执行 SELECT pg_reload_conf(); 或重启。生产环境不要对公网开放 5432。

---

三、psql 常用元命令

命令	作用
\l	列出数据库
\c dbname	切换数据库
\dt	列出表
\d table	查看表结构、索引、约束
\di	列出索引
\df	列出函数
\dn	列出 schema
\du	列出角色
\x	切换横向/纵向显示（宽表常用）
\timing	显示 SQL 执行时间
\i file.sql	执行 SQL 文件
\copy	客户端侧导入导出 CSV
\e	打开编辑器编辑 SQL
\q	退出

执行脚本：

psql "postgres://app:secret@localhost:5432/appdb" -f schema.sql
psql "postgres://app:secret@localhost:5432/appdb" -c "select version();"

导入导出 CSV：

psql -c "\copy users(name,email) from 'users.csv' csv header"
psql -c "\copy (select id,name from users) to 'out.csv' csv header"

---

四、数据类型

4.1 数值

类型	范围/精度	用途
smallint	2 字节	小范围整数
integer / int	4 字节	常用整数
bigint	8 字节	大 ID、计数
numeric(p, s)	精确小数	金额（必须用这个）
real / double precision	浮点	科学计算，不要用于金额

CREATE TABLE accounts (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  balance numeric(14, 2) NOT NULL DEFAULT 0 CHECK (balance >= 0)
);

GENERATED ALWAYS AS IDENTITY 是 SQL 标准的自增写法，优于 serial（后者是 PostgreSQL 的历史写法）。

4.2 字符

类型	说明
text	变长，无长度限制（推荐）
varchar(n)	变长，带长度约束
char(n)	定长，补空格（很少用）

PostgreSQL 中 text 与 varchar 性能相同，长度约束优先用 CHECK 或业务层校验；需要数据库层限制时用 varchar(n)。

4.3 时间

类型	说明
timestamp	不带时区，容易出错
timestamptz	带时区，按 UTC 存储（推荐）
date	日期
time / timetz	时间
interval	时间间隔

CREATE TABLE events (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  created_at timestamptz NOT NULL DEFAULT now(),
  duration interval
);

一律使用 timestamptz。timestamp 不记录时区，服务器时区一变，历史数据的含义就变了。

4.4 布尔、UUID、JSON、数组、枚举

CREATE TABLE samples (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  enabled boolean NOT NULL DEFAULT true,
  payload jsonb NOT NULL DEFAULT '{}'::jsonb,
  tags text[] NOT NULL DEFAULT '{}',
  status text NOT NULL DEFAULT 'draft'
    CHECK (status IN ('draft', 'published', 'archived'))
);

jsonb 与 json 的区别：

类型	存储	索引	建议
json	原文	不支持 GIN	仅需保存原文时
jsonb	解析后的二进制	支持 GIN	默认选择

JSONB 常用操作：

-- 取值（-> 返回 jsonb，->> 返回 text）
SELECT payload -> 'user' -> 'name' AS name_json,
       payload ->> 'user' AS user_text
FROM samples;

-- 包含与存在
SELECT * FROM samples WHERE payload @> '{"type":"order"}';
SELECT * FROM samples WHERE payload ? 'traceId';

-- 更新
UPDATE samples SET payload = jsonb_set(payload, '{status}', '"done"') WHERE id = '...';

枚举类型：

CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'closed');

枚举修改成本高（加值可以，删值困难），业务状态建议用 text + CHECK，便于演进。

---

五、表设计与约束

CREATE TABLE users (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email varchar(160) NOT NULL,
  name varchar(64) NOT NULL,
  status text NOT NULL DEFAULT 'active'
    CHECK (status IN ('active', 'disabled')),
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT users_email_unique UNIQUE (email)
);

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id uuid NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  amount numeric(14, 2) NOT NULL CHECK (amount > 0),
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);

常用约束：

约束	写法	说明
主键	PRIMARY KEY	唯一且非空，自动建索引
唯一	UNIQUE (col)	允许一个 NULL
外键	REFERENCES t (id)	可选 ON DELETE CASCADE/SET NULL/RESTRICT
非空	NOT NULL
检查	CHECK (amount > 0)	业务规则下沉到数据库
排他	EXCLUDE USING gist	时间段不重叠等场景

外键的代价：每次写入都要检查父表并加锁。高并发写入的表可以去掉外键，改由应用保证一致性，但要有补偿机制。

自增主键与 UUID 的选择：

主键	优点	缺点
自增 bigint	索引小、写入顺序友好	暴露业务量、分库分表麻烦
uuid	分布式生成、不泄露数量	索引大、随机写入导致页分裂（可用 uuidv7 改善）

---

六、增删改查

-- 插入
INSERT INTO users (email, name) VALUES ('a@example.com', '张三');

-- 批量插入
INSERT INTO users (email, name) VALUES
  ('b@example.com', '李四'),
  ('c@example.com', '王五');

-- 冲突时更新（UPSERT）
INSERT INTO users (email, name)
VALUES ('a@example.com', '张三丰')
ON CONFLICT (email) DO UPDATE
SET name = EXCLUDED.name, updated_at = now()
RETURNING id, name;

-- 查询
SELECT id, name, created_at
FROM users
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

-- 更新
UPDATE users SET name = '李四', updated_at = now() WHERE email = 'b@example.com';

-- 删除
DELETE FROM users WHERE id = '...';

-- 返回受影响的行
UPDATE orders SET amount = amount * 1.1 WHERE created_at >= now() - interval '7 days'
RETURNING id, amount;

RETURNING 是 PostgreSQL 的实用特性：插入/更新/删除都能直接返回结果，省一次查询。

6.1 分页

-- 浅分页
SELECT * FROM users ORDER BY created_at DESC LIMIT 20 OFFSET 40;

-- 深分页：用游标（keyset pagination），避免 OFFSET 越大越慢
SELECT * FROM users
WHERE created_at < '2026-01-01'
ORDER BY created_at DESC
LIMIT 20;

OFFSET 需要扫描并丢弃前面的行，深分页时性能急剧下降。

6.2 聚合

SELECT status, count(*) AS total, avg(amount)::numeric(10,2) AS avg_amount
FROM orders
GROUP BY status
HAVING count(*) > 10
ORDER BY total DESC;

6.3 条件表达式

SELECT
  id,
  CASE WHEN amount > 1000 THEN '大额'
       WHEN amount > 100 THEN '中额'
       ELSE '小额' END AS level,
  COALESCE(remark, '无备注') AS remark,
  NULLIF(name, '') AS name_clean
FROM orders;

---

七、查询进阶：JOIN、CTE、窗口函数

7.1 JOIN

-- 内连接：只保留匹配的行
SELECT u.name, o.id, o.amount
FROM users u
JOIN orders o ON o.user_id = u.id;

-- 左连接：保留左表全部，右表缺失为 NULL
SELECT u.id, u.name, count(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

-- 反连接：没有订单的用户
SELECT u.* FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

NOT EXISTS 通常优于 NOT IN：后者遇到 NULL 会返回空结果集。

7.2 CTE（WITH）

WITH recent AS (
  SELECT * FROM orders WHERE created_at > now() - interval '30 days'
),
stats AS (
  SELECT user_id, count(*) AS cnt, sum(amount) AS total
  FROM recent
  GROUP BY user_id
)
SELECT u.name, s.cnt, s.total
FROM stats s
JOIN users u ON u.id = s.user_id
ORDER BY s.total DESC
LIMIT 10;

递归 CTE（树形结构）：

WITH RECURSIVE tree AS (
  SELECT id, parent_id, name, 1 AS depth
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name, t.depth + 1
  FROM categories c
  JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree ORDER BY depth, name;

7.3 窗口函数

-- 每个用户最近 3 笔订单
SELECT *
FROM (
  SELECT o.*,
         row_number() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders o
) t
WHERE rn <= 3;

-- 累计金额与环比
SELECT created_at::date AS day,
       sum(amount) AS daily,
       sum(sum(amount)) OVER (ORDER BY created_at::date) AS cumulative,
       lag(sum(amount)) OVER (ORDER BY created_at::date) AS prev_day
FROM orders
GROUP BY 1
ORDER BY 1;

常用窗口函数：row_number、rank、dense_rank、lag、lead、sum() over、avg() over。

---

八、索引原理与使用

8.1 索引类型

类型	适用	说明
B-tree	等值、范围、排序	默认类型，绝大多数场景
GIN	数组、JSONB、全文检索	多值索引
GiST	几何、范围、模糊匹配	通用搜索树
BRIN	超大表、按顺序写入	体积小，适合时间序列
Hash	仅等值	很少用，B-tree 更通用

CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_users_status_created ON users (status, created_at DESC);
CREATE UNIQUE INDEX idx_users_email_unique ON users (lower(email));

-- 部分索引：只索引满足条件的行
CREATE INDEX idx_orders_unpaid ON orders (created_at) WHERE status = 'pending';

-- 表达式索引
CREATE INDEX idx_users_name_lower ON users (lower(name));

-- JSONB
CREATE INDEX idx_samples_payload ON samples USING gin (payload);

-- 数组
CREATE INDEX idx_samples_tags ON samples USING gin (tags);

8.2 什么时候索引不生效

• 列上使用函数或运算：WHERE lower(email) = 'a@b.com'，应建表达式索引。
• 隐式类型转换：WHERE phone = 13800000000（列为 text），会走不了索引。
• LIKE '%abc' 前缀模糊：B-tree 不适用，需要 pg_trgm + GIN。
• 返回行数占比过高：全表扫描更快，优化器会放弃索引。
• 统计信息过期：ANALYZE 后再看。

8.3 索引的代价

• 占用磁盘与内存。
• 每次写入都要维护索引，写多的表索引越少越好。
• 重复索引会浪费资源（如 (a) 与 (a, b) 同时存在时前者可能多余）。

---

九、执行计划与优化

EXPLAIN SELECT * FROM orders WHERE user_id = '...' ORDER BY created_at DESC LIMIT 10;
EXPLAIN ANALYZE SELECT ...;   -- 实际执行并给出真实耗时与行数
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;  -- 附带缓存命中情况

阅读要点：

节点	含义
Seq Scan	全表扫描
Index Scan	通过索引取行再回表
Index Only Scan	覆盖索引，不回表
Bitmap Index Scan	先建位图再取行，适合返回较多行
Nested Loop	小表驱动大表，适合小结果集
Hash Join	大表连接，内存不足会落盘
Sort / HashAggregate	排序与聚合，注意 work_mem

判断标准：

• 预估行数与实际行数差距大 → 统计信息不准，执行 ANALYZE。
• rows=1 但 actual rows=100000 → 计划错误，常见于函数或参数化查询。
• 出现 external merge Disk → 内存不足，调大 work_mem 或减少排序量。

常见优化手段：加索引、改写查询（避免子查询嵌套过深）、减少 SELECT *、分页改游标、拆分大事务。

---

十、事务、隔离级别与锁

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- 出错时 ROLLBACK;

隔离级别：

级别	脏读	不可重复读	幻读
Read Uncommitted	允许（PG 实际等同 Read Committed）	允许	允许
Read Committed（默认）	否	允许	允许
Repeatable Read	否	否	允许（PG 实际避免）
Serializable	否	否	否

PostgreSQL 的默认级别是 READ COMMITTED，语句级快照。需要事务内多次读取一致时用 REPEATABLE READ。

BEGIN ISOLATION LEVEL REPEATABLE READ;

行锁与死锁：

SELECT * FROM accounts WHERE id = 1 FOR UPDATE;         -- 排他锁
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;  -- 拿不到立即报错

死锁的典型原因：两个事务以相反顺序更新同一批行。处理方式：固定加锁顺序，或缩短事务、避免在事务中做远程调用。

查看阻塞：

SELECT pid, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE state <> 'idle' ORDER BY query_start;

---

十一、视图、函数与触发器

-- 视图
CREATE VIEW active_users AS
SELECT id, name, created_at FROM users WHERE status = 'active';

-- 物化视图（缓存结果，需要手动刷新）
CREATE MATERIALIZED VIEW daily_stats AS
SELECT created_at::date AS day, count(*) AS cnt, sum(amount) AS total
FROM orders GROUP BY 1;

REFRESH MATERIALIZED VIEW daily_stats;

-- 函数
CREATE OR REPLACE FUNCTION order_total(p_user_id uuid)
RETURNS numeric AS $$
  SELECT COALESCE(sum(amount), 0) FROM orders WHERE user_id = p_user_id;
$$ LANGUAGE sql STABLE;

-- 触发器：自动维护 updated_at
CREATE OR REPLACE FUNCTION touch_updated_at() RETURNS trigger AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_users_touch
BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION touch_updated_at();

触发器会隐藏副作用、增加排查难度，业务逻辑尽量放在应用层；updated_at 这类通用维护适合用触发器。

---

十二、用户与权限

-- 创建角色与数据库
CREATE ROLE app_user LOGIN PASSWORD 'secret';
CREATE DATABASE appdb OWNER app_user;

-- 只读账号
CREATE ROLE readonly LOGIN PASSWORD 'secret';
GRANT CONNECT ON DATABASE appdb TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

-- 回收
REVOKE DELETE ON orders FROM app_user;

原则：应用账号只授予必要的库表权限；迁移账号与运行账号分离；禁止应用使用超级用户。

---

十三、备份与恢复

# 逻辑备份（单库）
pg_dump -Fc -h localhost -U app -d appdb -f appdb.dump

# 恢复
pg_restore -h localhost -U app -d appdb_new -c appdb.dump

# 纯 SQL 导出（跨版本兼容性好）
pg_dump -h localhost -U app -d appdb -f appdb.sql

# 全库备份
pg_dumpall -h localhost -U postgres -f all.sql

备份策略建议：

• 每日全量 + 持续归档（WAL）用于时间点恢复（PITR）。
• 备份文件放在另一台机器或对象存储，不要和数据库同盘。
• 定期做恢复演练，未验证的备份不可信。

---

十四、常用配置参数

配置文件位置：/etc/postgresql/16/main/postgresql.conf（包管理）或容器内 /var/lib/postgresql/data/postgresql.conf。

参数	说明	建议
max_connections	最大连接数	与应用连接池总和匹配，过大反而消耗内存
shared_buffers	共享内存缓冲	内存的 25% 左右
work_mem	单次排序/哈希内存	从 4MB 起，按慢查询调整
maintenance_work_mem	维护操作内存	256MB 左右
effective_cache_size	优化器假设的可用缓存	内存的 50%~75%
wal_level	WAL 级别	归档/复制需要 replica 或 logical
max_wal_size	WAL 上限	影响检查点频率
timezone	服务器时区	Asia/Shanghai 或 UTC（需与应用统一）
log_min_duration_statement	慢查询阈值	生产建议 200ms~1s
shared_preload_libraries	预加载扩展	pg_stat_statements

查看与修改：

SHOW max_connections;
SHOW timezone;

ALTER SYSTEM SET log_min_duration_statement = '500ms';
SELECT pg_reload_conf();

启用慢查询统计：

ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
-- 重启后
CREATE EXTENSION pg_stat_statements;
SELECT query, calls, mean_exec_time
FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;

---

十五、维护：VACUUM 与统计信息

PostgreSQL 的 MVCC 会保留旧版本行，需要 VACUUM 回收：

VACUUM (VERBOSE, ANALYZE) users;
VACUUM FULL users;         -- 会锁表，慎用
ANALYZE users;             -- 更新统计信息

autovacuum 默认开启，但在大表或高更新表上可能跟不上：

• 监控 pg_stat_user_tables 的 n_dead_tup。
• 对大表单独调 autovacuum_vacuum_scale_factor。
• 长事务会阻止清理，检查 pg_stat_activity 中的 xact_start。

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

---

十六、常见错误与排查

错误	原因	处理
password authentication failed	密码错或 pg_hba.conf 不匹配	检查认证方式与用户
FATAL: no pg_hba.conf entry	客户端 IP 未授权	增加 host all all <网段> scram-sha-256
too many connections	连接数耗尽	加连接池、调 max_connections
canceling statement due to statement timeout	语句超时	优化查询或调整 statement_timeout
deadlock detected	加锁顺序冲突	固定顺序、缩短事务
could not serialize access due to concurrent update	Serializable 冲突	应用层重试
relation does not exist	搜 search_path 或 schema 不对	指定 schema 或调整 search_path
查询慢但索引存在	统计信息过期、类型不匹配、函数包裹列	ANALYZE、EXPLAIN ANALYZE 定位
时间差 8 小时	字段用了 timestamp 或时区配置不一致	用 timestamptz，统一 timezone
磁盘突然占满	WAL 堆积、日志、临时文件	检查 pg_wal 大小与归档状态

---

十七、FAQ

Q1：serial 和 identity 用哪个？

identity 是 SQL 标准写法，serial 是历史遗留（本质是序列 + 默认值）。新表用 GENERATED ALWAYS AS IDENTITY。

Q2：varchar 和 text 有性能差异吗？

没有。长度约束用 CHECK 或 varchar(n) 表达业务规则即可。

Q3：为什么金额不能用 float？

浮点数无法精确表示十进制小数，累加会出现 0.1 + 0.2 ≠ 0.3 的问题。金额必须用 numeric。

Q4：json 还是 jsonb？

默认 jsonb：支持索引与操作符。只有需要保留原始格式（键顺序、重复键）时才用 json。

Q5：COUNT(*) 很慢怎么办？

PG 的 count(*) 需要扫描，大表会慢。可用近似统计、维护计数表，或只统计必要条件的数据。

Q6：DELETE 后磁盘空间为什么没释放？

MVCC 保留旧行，需要 VACUUM 回收；VACUUM FULL 可立即回收但会锁表。物理空间最终由文件复用而非缩小。

Q7：主键用 UUID 有什么问题？

随机 UUID 导致 B-tree 页分裂，写入性能下降、索引变大。可用 uuidv7（时间有序）或 gen_random_uuid() + 定期重建索引。

Q8：为什么加了索引还是不生效？

检查是否对列做了函数运算、是否类型不匹配、返回行数是否过多；用 EXPLAIN ANALYZE 看实际计划。

Q9：外键要不要用？

核心数据建议保留外键，能防止脏数据；高并发写入可去掉并用应用层校验 + 对账补偿。

Q10：NULL 和空字符串怎么选？

NULL 表示"未知/不适用"，空字符串表示"确认为空"。二者不要混用；比较时注意 NULL 不参与等值判断（用 IS NULL）。

---

附录：SQL 速查

-- 库与表
CREATE DATABASE appdb;
DROP DATABASE appdb;
\c appdb
CREATE TABLE t (...);
ALTER TABLE t ADD COLUMN c text;
ALTER TABLE t DROP COLUMN c;
TRUNCATE t;
DROP TABLE t;

-- 查询骨架
SELECT [DISTINCT] cols
FROM t
JOIN u ON ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...
LIMIT n OFFSET m;

-- 常用函数
now()  current_date  age(ts)  date_trunc('day', ts)
coalesce(a,b)  nullif(a,b)  greatest/least
lower/upper/trim  length  substring  concat_ws
jsonb_build_object  jsonb_array_elements  to_jsonb
array_agg  string_agg  count  sum  avg  min  max

-- 事务
BEGIN; ... COMMIT; / ROLLBACK;
SAVEPOINT s; ROLLBACK TO s;

-- 索引
CREATE INDEX idx ON t (col);
CREATE INDEX idx ON t (col) WHERE cond;
CREATE INDEX idx ON t USING gin (jsonb_col);
DROP INDEX idx;

-- 查看
SELECT * FROM pg_stat_activity;
SELECT * FROM pg_stat_user_tables;
SELECT * FROM pg_indexes WHERE tablename = 't';