PostgreSQL入门实战：从安装配置到索引与性能调优

PostgreSQL 是目前世界上最流行的开源关系型数据库之一。它以「功能强大」著称：支持 ACID 事务、MVCC 并发控制、JSON/JSONB 类型、全文检索、GIS 地理信息处理，还提供了 MVCC 之外的大量扩展能力。MySQL 擅长简单读写和生态成熟度，而 PostgreSQL 在复杂查询、地理数据、事务一致性这些场景里往往更胜一筹。

本文从零开始，带你完成一次完整的 PostgreSQL 实践：装起来、建库建表、写入数据、跑查询、做索引优化，最后再聊聊备份和那些容易踩的坑。

一、安装与初始化

1.1 Docker 方式（推荐用于开发环境）

Docker 方式最省事，不用在宿主机上处理依赖和端口冲突：

docker run -d --name pg-demo \
  -e POSTGRES_PASSWORD=your_password \
  -e POSTGRES_DB=appdb \
  -p 5432:5432 \
  postgres:16-alpine

几个参数要记牢：

• POSTGRES_PASSWORD：初始化超级用户 postgres 的密码，不设置会直接启动失败
• POSTGRES_DB：初始化时自动创建的数据库，省掉手动建库
• -p 5432:5432：把容器端口映射到宿主机，Postgres 默认端口就是 5432

容器启动后可以用日志确认初始化是否完成：

docker logs pg-demo | grep "database system is ready"

出现两行 ready to accept connections 表示初始化成功（第一次启动会临时启动再重启一次，这是正常的）。

1.2 连接与基础管理

用 psql 连接：

docker exec -it pg-demo psql -U postgres -d appdb

连上之后有两个常用元命令：

\l        -- 列出所有数据库
\dt       -- 列出当前库的所有表
\q        -- 退出

psql 支持用 \timing on 打开执行耗时统计，调优时非常有用。

二、psql 基础操作

2.1 建表

PostgreSQL 的建表语法和 MySQL 类似，但类型体系更细致：

CREATE TABLE users (
    id          BIGSERIAL PRIMARY KEY,
    username    VARCHAR(50) NOT NULL UNIQUE,
    email       VARCHAR(255) NOT NULL,
    age         SMALLINT,
    balance     NUMERIC(12, 2) DEFAULT 0,
    profile     JSONB,
    is_active   BOOLEAN DEFAULT TRUE,
    created_at  TIMESTAMPTZ DEFAULT NOW(),
    updated_at  TIMESTAMPTZ DEFAULT NOW()
);

这里有几个 PostgreSQL 特有的点：

• BIGSERIAL 是自增主键，比 MySQL 的 AUTO_INCREMENT 更简洁，不用单独建序列
• NUMERIC 是精确数值类型，做金额计算必须用它，绝对不要用 FLOAT 存钱
• TIMESTAMPTZ 是带时区的时间戳，PostgreSQL 内部统一按 UTC 存储，展示时再转本地时区
• JSONB 是二进制 JSON，比 JSON 存储更紧凑、查询更快，还支持 GIN 索引

2.2 写入与查询

INSERT INTO users (username, email, age, balance, profile)
VALUES ('zhangsan', 'zhangsan@example.com', 28, 1250.50, '{"city": "Beijing", "tags": ["vip"]}'),
       ('lisi',     'lisi@example.com',     34,  980.00, '{"city": "Shanghai"}');

SELECT id, username, age, balance FROM users WHERE age >= 25 ORDER BY balance DESC;

2.3 JSONB 查询（PostgreSQL 的杀手锏）

JSONB 字段可以直接用类 SQL 语法查询，不用像 MySQL 那样写 JSON_EXTRACT：

-- 提取字段
SELECT username, profile->>'city' AS city FROM users;

-- 按 JSONB 内容过滤
SELECT username FROM users WHERE profile @> '{"city": "Beijing"}';

-- 给 JSONB 建 GIN 索引后查询才快
CREATE INDEX idx_users_profile ON users USING GIN (profile);

->> 是取文本值，-> 是取 JSON 类型，两者返回类型不同，写 SQL 时要注意。

三、索引与查询优化

3.1 索引类型怎么选

3.2 复合索引的列顺序

复合索引遵循「最左前缀」原则。如果查询条件是 WHERE city = 'Beijing' AND age > 25，那么索引应该把选择性更高的列放前面：

CREATE INDEX idx_users_city_age ON users (city, age);

注意：只要最左边那一列没被用上，后面几列就完全用不到索引。所以 age 单独查时这个复合索引帮不上忙，需要单独建 idx_users_age。

3.3 用 EXPLAIN ANALYZE 看执行计划

这是 PostgreSQL 调优最重要的工具：

EXPLAIN ANALYZE
SELECT username, email FROM users WHERE city = 'Beijing' AND age > 25;

输出里重点看三件事：

• 执行类型：Seq Scan（全表扫描）在大表上通常是问题，Index Scan 才走索引
• cost 与 actual time：估算值和实际值差得很远（通常差 10 倍以上）说明统计信息过期，需要 ANALYZE 表
• rows：预估行数偏差大同样指向统计信息问题

统计信息自动更新有时候不及时，可以手动执行：

ANALYZE users;
VACUUM FULL ANALYZE users;  -- 回收死元组并重写表

3.4 部分索引与表达式索引

只给热点数据建索引，能省下大量空间：

-- 只索引活跃用户
CREATE INDEX idx_users_active ON users (created_at) WHERE is_active = TRUE;

对表达式建索引：

-- 查询 WHERE lower(email) = 'a@b.com' 时用得上
CREATE UNIQUE INDEX idx_users_email_lower ON users (lower(email));

四、备份与恢复

4.1 逻辑备份

# 备份单个库
pg_dump -U postgres -d appdb -F c -f appdb.dump

# 备份并压缩
pg_dump -U postgres -d appdb -F c -f appdb.dump.gz

# 恢复
pg_restore -U postgres -d appdb -c appdb.dump

pg_dump 的三种格式区别：

• 自定义格式 -F c：可以指定表、schema 恢复，还支持并行压缩，日常首选
• 目录格式 -F d：类似自定义格式，但恢复时能并行，最快
• 纯文本 -F p：人可读，但恢复慢，只在需要人工检查内容时用

4.2 物理备份与时间点恢复

生产环境要开启 WAL 归档，才能做到 PITR（时间点恢复）：

# 打开归档模式（改完需重启）
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/archive/%f && cp %p /var/lib/postgresql/archive/%f'

配合 pg_basebackup 做基础备份：

pg_basebackup -D /backup/base -U postgres -Fp -Xs -P

恢复时指定 recovery_target_time，就能把数据库恢复到误删数据之前的那个时刻。

五、几个最容易踩的坑

坑 1：事务里忘了提交

PostgreSQL 的事务隔离行为和 MySQL 略有差异。开着事务做了一堆 UPDATE，结果因为某一步报错没有 COMMIT，整批操作全部回滚，而客户端看起来「没报错」。

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 忘了 COMMIT，客户端一断开事务自动回滚

排查时先看 pg_stat_activity 里有没有长期挂着的 idle in transaction：

SELECT pid, state, query FROM pg_stat_activity WHERE state LIKE 'idle in transaction';

坑 2：序列和表数据不同步

用 pg_dump 恢复数据后，如果序列没同步，下一次插入就会撞主键冲突：

SELECT setval('users_id_seq', (SELECT MAX(id) FROM users));

或者用 TRUNCATE ... RESTART IDENTITY 直接重置。

坑 3：长事务把表撑大

PostgreSQL 的 MVCC 靠死元组实现。事务开太久，旧版本行不能被回收，表和索引会持续膨胀。生产上要配置 idle_in_transaction_session_timeout，强制杀掉空闲事务：

idle_in_transaction_session_timeout = 60s

坑 4：表数量爆炸导致性能下降

PostgreSQL 在执行 SQL 时要遍历系统目录来解析表名，表一多（几千张以上），规划时间会明显变慢。

SELECT count(*) FROM pg_class WHERE relkind = 'r';

超过几千张就要考虑分库，或者用 pg_prewarm 预热系统目录。

六、总结与最佳实践

1. 金额一律用 NUMERIC，不要用 FLOAT 或 REAL
2. 时间一律用 TIMESTAMPTZ，避免跨时区时区混乱
3. JSON 数据用 JSONB，并配 GIN 索引，不要用 JSON
4. 上线前先 EXPLAIN ANALYZE，重点消灭大表上的 Seq Scan
5. 定期 ANALYZE，统计信息过期是「计划突然变慢」的头号原因
6. 给热点查询建部分索引，比全表索引省空间得多
7. 监控长事务，idle in transaction 是线上事故常见源头
8. 备份用自定义格式 + WAL 归档，才能做到任意时间点恢复
9. 表数量控制在几千张以内，超了就该分库了

PostgreSQL 的学习曲线不算陡，但它的设计哲学和 MySQL 有明显差异：更强调数据完整性、类型严谨和扩展能力。把这篇文章里的命令亲手敲一遍，你就能建立起对 PostgreSQL 的基本体感，后面再深入分区表、逻辑复制、流式复制这些进阶话题就有地基了。