PostgreSQL 实战：从入门到高可用的完整指南

PostgreSQL 以功能全面、扩展性强、严格的数据一致性著称，被业界誉为"最先进的开源关系数据库"。从 JSON 支持到全文检索，从物化视图到逻辑复制，PG 几乎覆盖了所有数据需求。本文从核心特性讲起，完整走一遍从部署到高可用的实战路径。

一、为什么选择 PostgreSQL

相比其他关系型数据库，PG 的核心优势：

1. 功能纵深：CTE、窗口函数、部分索引、数组/JSONB 类型、GIN/GiST 索引；
2. 扩展生态：PostGIS（地理空间）、TimescaleDB（时序）、pgvector（向量检索）；
3. 数据完整性：ACID 严格实现、外键、检查约束、排他约束；
4. 高可用方案成熟：流复制 + Patroni 是生产标配。

二、基础使用：表、索引与 JSONB

2.1 常用 DDL 与数据类型

CREATE TABLE articles (
    id          BIGSERIAL PRIMARY KEY,          -- 自增
    title       TEXT NOT NULL,
    content     TEXT,
    category    VARCHAR(50),
    tags        TEXT[] DEFAULT '{}',            -- 数组类型
    meta        JSONB DEFAULT '{}'::jsonb,      -- 半结构化
    view_count  INTEGER NOT NULL DEFAULT 0,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- 部分索引：只对满足条件的行为建索引，节省空间
CREATE INDEX idx_articles_published ON articles (created_at)
WHERE category = 'backend';

2.2 JSONB 的妙用

-- 半结构化数据直接查询
SELECT * FROM articles WHERE meta->>'is_top' = 'true';

-- GIN 索引加速 JSONB 查询
CREATE INDEX idx_articles_meta ON articles USING GIN (meta);

JSONB vs 关系表：字段不确定、频繁增删的元数据适合 JSONB；有稳定查询模式、需要强约束的业务字段应该用列。

三、高级查询能力

3.1 CTE 与递归查询

-- 树形结构递归查询（分类层级）
WITH RECURSIVE tree AS (
    SELECT id, name, parent_id FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id
    FROM categories c
    JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;

3.2 窗口函数

-- 每篇文章在其分类内的阅读量排名
SELECT title, category, view_count,
       RANK() OVER (PARTITION BY category ORDER BY view_count DESC) AS rank
FROM articles;

窗口函数在排行榜、同比环比、去重取 TopN 场景中不可或缺。

3.3 UPSERT

INSERT INTO user_stats (user_id, view_count)
VALUES (1, 1)
ON CONFLICT (user_id)
DO UPDATE SET view_count = user_stats.view_count + EXCLUDED.view_count;

ON CONFLICT 是并发安全的高频更新利器，比"先查再改"少一个竞态窗口。

四、并发控制：MVCC 与事务隔离

PG 通过 MVCC 让读写互不阻塞：读事务看到的是快照，写事务不阻塞读。

-- 四种隔离级别
BEGIN ISOLATION LEVEL READ COMMITTED;      -- 默认
BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN ISOLATION LEVEL SERIALIZABLE;

-- 事务级锁（行锁）
SELECT * FROM inventory WHERE id = 1 FOR UPDATE;

实践提醒：长事务会放大 MVCC 开销与膨胀（bloat），生产环境务必监控 long-running queries 并设置 idle_in_transaction_session_timeout。

五、备份与恢复

# 逻辑备份（支持选表/选库）
pg_dump -U app -h localhost app_db -Fc -f app_db.dump

# 并行物理备份
pg_basebackup -D /backup/base -h db-primary -U replica -Xs -P

# 恢复
pg_restore -U app -d app_db app_db.dump

备份要点：物理备份（pg_basebackup）用于快速恢复全库，逻辑备份（pg_dump）灵活选表；WAL 归档提供按时间点恢复（PITR）能力。

六、主从复制与高可用：Patroni 方案

6.1 流复制搭建

# 主库 postgresql.conf
wal_level = replica
max_wal_senders = 5
hot_standby = on

# 从库：从主库 basebackup 后配置
pg_basebackup -h db-primary -U replica -D /var/lib/pgsql/data -Xs -P
echo "primary_conninfo = 'host=db-primary user=replica'" >> data/postgresql.conf
touch data/standby.signal

6.2 Patroni：自动化故障转移

Patroni 基于 etcd/Consul 维护集群状态，实现"主库故障自动提升从库"：

# patroni.yml
scope: pg-cluster
name: pg-01
restapi:
  listen: 0.0.0.0:8008
etcd:
  hosts: etcd-1:2379,etcd-2:2379
bootstrap:
  dcs:
    ttl: 30
    postgresql:
      parameters:
        wal_level: replica
postgresql:
  listen: 0.0.0.0:5432
  data_dir: /data/pg

patronictl list 查看集群状态，patronictl switchover 手动切换。配合 HAProxy 或服务发现，应用无感知地完成主从切换。

七、性能调优速查

| 配置项 | 建议值 | 说明 | |--------|--------|------| | shared_buffers | 内存的 25% | 共享缓存池 | | effective_cache_size | 内存的 60~70% | 供优化器估算 | | work_mem | 4~16MB | 排序/哈希内存，过高易爆内存 | | max_connections | 按需，配合连接池 | 每个连接有内存开销 | | checkpoint 相关 | 适度放大 | 减少写放大与 IO 尖刺 |

-- 常用慢查询定位
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;

结语

PostgreSQL 的魅力在于"用一个库解决绝大多数问题"：关系数据、JSON、全文检索、地理信息、时序数据都能在一套体系内优雅共存。从 JSONB 到窗口函数，从流复制到 Patroni，PG 的能力纵深让它在功能与性能之间提供了难得的平衡。建议新项目认真评估 PG——它很可能比你想象得更能打。