PostgreSQL：世界上最先进的开源关系型数据库
·
PostgreSQL：世界上最先进的开源关系型数据库
一、前言
在数据库的世界中，有一款产品以其强大的功能、卓越的稳定性和开放的生态屹立了三十余年，它就是 PostgreSQL（常被简称为 PG）。无论是初创公司还是世界500强企业，无论是传统的事务处理还是前沿的地理信息分析，PostgreSQL 都能胜任。
本文将从历史背景、核心特性、架构原理、应用场景、与 MySQL 的对比，以及实战入门等方面，带你全面了解 PostgreSQL。
二、历史与发展
2.1 起源
PostgreSQL 的历史可以追溯到 1986 年，由加州大学伯克利分校的 Michael Stonebraker 教授领导的 POSTGRES 项目发展而来。
| 时间 | 里程碑 |
|---|---|
| 1986 | POSTGRES 项目启动（伯克利大学） |
| 1995 | 添加 SQL 语言支持，更名为 Postgres95 |
| 1996 | 正式更名为 PostgreSQL，版本号从 6.0 开始 |
| 2005 | PostgreSQL 8.0 发布，原生支持 Windows |
| 2017 | PostgreSQL 10 发布，引入逻辑复制、声明式分区 |
| 2022 | PostgreSQL 15 发布，增强排序与压缩性能 |
| 2024 | PostgreSQL 17 发布，持续提升性能与开发体验 |
Michael Stonebraker 教授因其在数据库领域的杰出贡献，于 2014 年获得了图灵奖。
2.2 社区与许可
- 许可证：PostgreSQL License（类似 MIT/BSD），极其宽松，允许商用、修改、分发，无需开源。
- 社区驱动：没有单一商业公司控制，由全球数百名核心开发者和数千名贡献者共同维护。
- 发布节奏：每年发布一个大版本，每个大版本维护 5 年。
三、核心特性
3.1 完整的 ACID 事务支持
PostgreSQL 严格遵循 ACID（原子性、一致性、隔离性、持久性）原则，支持四种事务隔离级别：
- Read Uncommitted（实际表现等同于 Read Committed）
- Read Committed（默认）
- Repeatable Read
- Serializable
其中 Serializable 隔离级别采用了先进的 SSI（Serializable Snapshot Isolation） 算法，能够在保证可序列化的同时尽量减少锁冲突。
3.2 丰富的数据类型
这是 PostgreSQL 最引以为傲的特性之一：
┌─────────────────────────────────────────────────────┐
│ PostgreSQL 数据类型全景图 │
├─────────────┬───────────────────────────────────────┤
│ 基础类型 │ INTEGER, BIGINT, NUMERIC, REAL, │
│ │ DOUBLE PRECISION, SERIAL, BOOLEAN, │
│ │ CHAR, VARCHAR, TEXT, BYTEA, DATE, │
│ │ TIMESTAMP, INTERVAL, UUID │
├─────────────┼───────────────────────────────────────┤
│ 结构化类型 │ ARRAY, COMPOSITE TYPE, RANGE, │
│ │ DOMAIN │
├─────────────┼───────────────────────────────────────┤
│ 文档类型 │ JSON, JSONB, XML, HSTORE │
├─────────────┼───────────────────────────────────────┤
│ 几何/地理 │ POINT, LINE, POLYGON, CIRCLE, │
│ │ PostGIS 扩展（地理空间数据） │
├─────────────┼───────────────────────────────────────┤
│ 网络类型 │ INET, CIDR, MACADDR │
├─────────────┼───────────────────────────────────────┤
│ 全文检索 │ TSVECTOR, TSQUERY │
├─────────────┼───────────────────────────────────────┤
│ 其他 │ BIT, MONEY, PG_LSN, ENUM 等 │
└─────────────┴───────────────────────────────────────┘
3.3 强大的 JSON/JSONB 支持
PostgreSQL 可以同时作为关系型数据库和文档型数据库使用：
-- 创建包含 JSONB 字段的表
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
attrs JSONB
);
-- 插入 JSON 数据
INSERT INTO products (name, attrs) VALUES
('iPhone 15', '{"color": "black", "storage": 256, "5G": true}'),
('MacBook Pro', '{"color": "silver", "ram": 16, "cpu": "M3"}');
-- 查询 JSON 字段
SELECT name, attrs->>'color' AS color
FROM products
WHERE attrs @> '{"5G": true}';
-- 在 JSONB 上创建 GIN 索引
CREATE INDEX idx_product_attrs ON products USING GIN (attrs);
3.4 高级索引类型
PostgreSQL 支持多种索引类型，远超大多数数据库：
| 索引类型 | 适用场景 | 示例 |
|---|---|---|
| B-Tree | 等值、范围查询（默认） | CREATE INDEX ON t(col) |
| Hash | 纯等值查询 | CREATE INDEX ON t USING HASH(col) |
| GiST | 几何数据、范围类型、全文检索 | PostGIS 空间索引 |
| SP-GiST | 不平衡数据结构（四叉树、KD树） | 电话号码前缀查询 |
| GIN | 全文检索、JSONB、数组 | JSONB 包含查询 |
| BRIN | 大表中物理排序良好的列 | 时序数据的时间戳列 |
| Bloom | 多列等值查询组合 | 需要 bloom 扩展 |
-- 部分索引（Partial Index）—— 只索引满足条件的行
CREATE INDEX idx_active_users ON users (email)
WHERE is_active = true;
-- 表达式索引
CREATE INDEX idx_lower_email ON users (LOWER(email));
-- 覆盖索引（INCLUDE）
CREATE INDEX idx_order_customer ON orders (customer_id)
INCLUDE (order_date, total_amount);
3.5 MVCC（多版本并发控制）
PostgreSQL 使用 MVCC 机制来实现高并发读写：
事务1 (读操作) 事务2 (写操作)
│ │
│ SELECT * FROM accounts │
│ WHERE id = 1; │
│ → 看到旧版本 (balance=1000) │
│ │ UPDATE accounts
│ │ SET balance = 500
│ │ WHERE id = 1;
│ │
│ SELECT * FROM accounts │
│ WHERE id = 1; │
│ → 仍然看到 balance=1000 │ COMMIT;
│ │
│ 读操作不会被写操作阻塞！ │
关键点：
- 读不阻塞写，写不阻塞读
- 每行数据有
xmin（创建事务ID）和xmax（删除事务ID）隐藏列 - 需要定期执行 VACUUM 清理旧版本元组（autovacuum 自动执行）
3.6 窗口函数与高级 SQL
-- 窗口函数：计算每个部门的薪资排名
SELECT
name,
department,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees;
-- CTE（公共表表达式）递归查询：组织架构树
WITH RECURSIVE org_tree AS (
-- 基础查询：找到 CEO
SELECT id, name, manager_id, 1 AS level
FROM employees WHERE manager_id IS NULL
UNION ALL
-- 递归查询：找到下属
SELECT e.id, e.name, e.manager_id, t.level + 1
FROM employees e
JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree ORDER BY level, name;
-- LATERAL JOIN：每个类别取最新3条记录
SELECT c.name, recent.*
FROM categories c
CROSS JOIN LATERAL (
SELECT * FROM articles a
WHERE a.category_id = c.id
ORDER BY created_at DESC
LIMIT 3
) recent;
3.7 扩展机制（Extension）
PostgreSQL 被称为"最具扩展性的数据库"，其扩展生态极为丰富：
| 扩展 | 功能 |
|---|---|
| PostGIS | 地理空间数据处理（GIS 领域事实标准） |
| pg_trgm | 模糊字符串匹配与相似度搜索 |
| pgvector | 向量相似度搜索（AI/LLM 嵌入存储） |
| TimescaleDB | 时序数据处理 |
| Citus | 分布式水平扩展 |
| pg_stat_statements | SQL 性能分析 |
| pgcrypto | 加密函数 |
| hstore | 键值对存储 |
| pg_partman | 自动分区管理 |
-- 安装扩展非常简单
CREATE EXTENSION IF NOT EXISTS pgvector;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- 使用 pgvector 进行向量相似度搜索（AI 应用）
CREATE TABLE documents (
id SERIAL PRIMARY KEY,
content TEXT,
embedding VECTOR(1536) -- OpenAI embedding 维度
);
-- 创建向量索引
CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);
-- 查找最相似的5个文档
SELECT content, embedding <=> '[0.1, 0.2, ...]'::vector AS distance
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 5;
四、架构原理
4.1 进程架构
PostgreSQL 采用经典的多进程架构（而非多线程）：
客户端连接
┌─────────┐
│Client 1 │
│Client 2 │
│Client 3 │
└────┬────┘
│
▼
┌─────────────────┐
│ Postmaster │ ← 主进程（监听连接，fork子进程）
│ (PID 1) │
└───────┬─────────┘
│ fork
┌─────────────┼──────────────────┐
│ │ │
▼ ▼ ▼
┌───────────┐ ┌───────────┐ ┌─────────────┐
│ Backend 1 │ │ Backend 2 │ │ Backend 3 │ ← 每个连接一个进程
└───────────┘ └───────────┘ └─────────────┘
后台辅助进程：
┌──────────────────────────────────────────┐
│ • Background Writer (脏页写入) │
│ • WAL Writer (WAL 日志写入) │
│ • Checkpointer (检查点) │
│ • Autovacuum Launcher (自动清理) │
│ • Stats Collector (统计信息收集) │
│ • Logical Replication Launcher │
└──────────────────────────────────────────┘
4.2 存储架构
共享内存 (Shared Memory)
┌─────────────────────────────────────────┐
│ ┌──────────────┐ ┌────────────────┐ │
│ │ Shared Buffer │ │ WAL Buffer │ │
│ │ Pool │ │ │ │
│ │ (数据页缓存) │ │ (预写日志缓存) │ │
│ └──────┬───────┘ └───────┬────────┘ │
│ │ │ │
└─────────┼──────────────────┼─────────────┘
│ │
▼ ▼
┌──────────────┐ ┌──────────────┐
│ 数据文件 │ │ WAL 文件 │
│ (base/) │ │ (pg_wal/) │
│ │ │ │
│ 以 8KB 页为 │ │ 预写日志 │
│ 单位存储 │ │ 保证持久性 │
└──────────────┘ └──────────────┘
关键概念：
- Shared Buffers：数据页在内存中的缓存，通常设置为系统内存的 25%
- WAL（Write-Ahead Logging）：所有修改先写入 WAL 日志，再写入数据文件，保证崩溃恢复
- VACUUM：清理 MVCC 产生的死元组，回收空间
4.3 WAL 与崩溃恢复
写入流程
┌──────────────────────────────────┐
│ 1. 修改 Shared Buffer 中的数据页 │
│ 2. 写入 WAL Buffer │
│ 3. COMMIT 时将 WAL Buffer │
│ 刷入磁盘 (WAL 文件) │
│ 4. 后台异步将脏页写入数据文件 │
└──────────────────────────────────┘
崩溃恢复：
系统重启 → 读取 WAL 日志 → 重放未持久化的操作 → 数据一致
五、高可用与复制
5.1 复制方式概览
┌───────────────────────────────────────────────────┐
│ PostgreSQL 复制架构 │
├──────────────┬────────────────────────────────────┤
│ 流式复制 │ 基于 WAL 的物理复制（最常用） │
│ (Streaming) │ 主库 → 从库，支持同步/异步 │
├──────────────┼────────────────────────────────────┤
│ 逻辑复制 │ 基于逻辑变更的复制 │
│ (Logical) │ 可选择性复制部分表，支持跨大版本 │
├──────────────┼────────────────────────────────────┤
│ 基于触发器 │ Slony, Londiste 等第三方工具 │
└──────────────┴────────────────────────────────────┘
5.2 流式复制配置示例
# 主库 postgresql.conf
wal_level = replica
max_wal_senders = 10
synchronous_standby_names = 'standby1'
# 主库 pg_hba.conf
host replication replicator 192.168.1.0/24 md5
# 从库初始化
pg_basebackup -h 主库IP -D /var/lib/postgresql/data -U replicator -P -R
# -R 参数会自动创建 standby.signal 和配置连接信息
5.3 常见高可用方案
| 方案 | 特点 |
|---|---|
| Patroni | 基于 etcd/ZooKeeper 的自动故障转移（最流行） |
| repmgr | 轻量级复制管理与故障转移 |
| pgpool-II | 连接池 + 读写分离 + 负载均衡 |
| PgBouncer | 轻量级连接池 |
| Citus | 分布式扩展，水平分片 |
六、PostgreSQL vs MySQL
这是开发者最常问的问题之一：
| 对比维度 | PostgreSQL | MySQL |
|---|---|---|
| 定位 | 功能完备的企业级数据库 | 轻量快速的 Web 数据库 |
| SQL 标准 | 高度遵循 SQL 标准 | 有较多非标准行为 |
| 数据类型 | 极其丰富（JSONB、数组、范围、网络等） | 较为基础 |
| JSON 支持 | JSONB 二进制存储 + GIN 索引，性能优秀 | JSON 支持相对较弱 |
| 索引类型 | B-Tree, Hash, GiST, SP-GiST, GIN, BRIN | B-Tree, Hash, 全文索引, R-Tree |
| 全文检索 | 内置支持，多语言分词 | 基础支持 |
| 地理空间 | PostGIS（行业标准） | 基础 GIS 支持 |
| MVCC 实现 | 多版本存储在原表中 | InnoDB 使用 undo log |
| 复制 | 流式复制 + 逻辑复制 | 基于 binlog 的复制（更成熟） |
| 分区表 | 声明式分区（PG 10+） | 分区支持较早 |
| 存储引擎 | 单一引擎（可通过扩展添加） | 多引擎（InnoDB, MyISAM 等） |
| 扩展性 | 极强（自定义类型、函数、索引、语言） | 相对有限 |
| 社区 | 社区驱动，纯开源 | Oracle 公司主导 |
| 学习曲线 | 中等偏高 | 较低 |
| 生态工具 | pgAdmin, DBeaver 等 | MySQL Workbench, phpMyAdmin 等 |
选择建议
选 PostgreSQL 的场景：
- 需要复杂查询、窗口函数、CTE
- 数据类型复杂（JSON、地理空间、数组）
- 对数据完整性要求极高
- 需要强大的扩展能力（向量搜索、时序数据等）
- 数据仓库 / OLAP 分析
选 MySQL 的场景：
- 简单的 CRUD 应用
- 团队对 MySQL 更熟悉
- 需要成熟的主从复制生态
- 读多写少的 Web 应用
七、应用场景
7.1 典型使用者
┌─────────────────────────────────────────────┐
│ 谁在使用 PostgreSQL？ │
├─────────────────────────────────────────────┤
│ 🍎 Apple - 核心基础设施 │
│ 📸 Instagram - 社交媒体后端 │
│ 🎵 Spotify - 音乐推荐系统 │
│ 🔴 Reddit - 社区平台 │
│ 🛫 FlightAware - 航班追踪 │
│ 🌐 Cloudflare - 网络基础设施 │
│ 💬 Discord - 消息存储 │
│ 🐙 GitLab - 代码托管平台 │
│ 📦 Shopify - 电商平台 │
│ 🏛️ 众多政府机构、金融机构、科研机构 │
└─────────────────────────────────────────────┘
7.2 典型场景
| 场景 | 关键特性 | 相关扩展 |
|---|---|---|
| Web 应用 | JSONB、全文检索、高并发 | pg_trgm |
| 地理信息系统（GIS） | 空间数据类型、空间索引 | PostGIS |
| 金融系统 | ACID、精确数值、序列化隔离 | 内置 |
| 物联网/时序数据 | 分区表、BRIN 索引 | TimescaleDB |
| AI/向量搜索 | 向量数据类型、ANN 索引 | pgvector |
| 数据仓库 | 并行查询、窗口函数、物化视图 | cstore_fdw, Citus |
| 微服务 | 逻辑复制、LISTEN/NOTIFY | 内置 |
八、实战入门
8.1 安装
Ubuntu/Debian：
sudo apt update
sudo apt install postgresql postgresql-contrib
# 启动服务
sudo systemctl start postgresql
sudo systemctl enable postgresql
# 切换到 postgres 用户
sudo -u postgres psql
macOS（Homebrew）：
brew install postgresql@16
brew services start postgresql@16
psql postgres
Docker：
docker run -d \
--name my-postgres \
-e POSTGRES_PASSWORD=mysecretpassword \
-e POSTGRES_DB=mydb \
-p 5432:5432 \
-v pgdata:/var/lib/postgresql/data \
postgres:16
8.2 基础操作
-- 创建数据库和用户
CREATE USER myapp WITH PASSWORD 'secure_password';
CREATE DATABASE mydb OWNER myapp;
-- 连接到数据库
\c mydb
-- 创建表
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
profile JSONB DEFAULT '{}',
tags TEXT[] DEFAULT '{}',
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
-- 创建更新时间触发器
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER set_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_modified_column();
-- 插入数据
INSERT INTO users (username, email, profile, tags) VALUES
('zhangsan', 'zhang@example.com', '{"age": 28, "city": "北京"}', ARRAY['developer', 'gamer']),
('lisi', 'li@example.com', '{"age": 32, "city": "上海"}', ARRAY['designer']),
('wangwu', 'wang@example.com', '{"age": 25, "city": "北京"}', ARRAY['developer', 'reader']);
-- 查询：JSON 字段 + 数组操作
SELECT username, profile->>'city' AS city
FROM users
WHERE profile->>'city' = '北京'
AND 'developer' = ANY(tags);
-- 聚合查询
SELECT
profile->>'city' AS city,
COUNT(*) AS user_count,
ARRAY_AGG(username) AS usernames
FROM users
GROUP BY profile->>'city';
8.3 常用 psql 命令
\l -- 列出所有数据库
\c dbname -- 切换数据库
\dt -- 列出所有表
\d tablename -- 查看表结构
\di -- 列出所有索引
\df -- 列出所有函数
\du -- 列出所有用户/角色
\timing -- 开启/关闭查询计时
\x -- 切换扩展显示模式
\q -- 退出
8.4 性能优化要点
配置调优（postgresql.conf）
# 内存相关
shared_buffers = '4GB' # 系统内存的 25%
effective_cache_size = '12GB' # 系统内存的 75%
work_mem = '256MB' # 排序/哈希操作内存
maintenance_work_mem = '1GB' # VACUUM/CREATE INDEX 内存
# WAL 相关
wal_buffers = '64MB'
checkpoint_completion_target = 0.9
max_wal_size = '4GB'
# 查询优化
random_page_cost = 1.1 # SSD 磁盘建议设为 1.1
effective_io_concurrency = 200 # SSD 磁盘建议 200
# 并行查询
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
查询分析
-- 使用 EXPLAIN ANALYZE 分析查询计划
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM users WHERE profile->>'city' = '北京';
-- 输出示例：
-- Seq Scan on users (cost=0.00..1.04 rows=1 width=200) (actual time=0.015..0.016 rows=2 loops=1)
-- Filter: ((profile ->> 'city'::text) = '北京'::text)
-- Buffers: shared hit=1
-- Planning Time: 0.085 ms
-- Execution Time: 0.033 ms
-- 创建索引优化
CREATE INDEX idx_users_city ON users ((profile->>'city'));
-- 查看慢查询（需要安装 pg_stat_statements）
CREATE EXTENSION pg_stat_statements;
SELECT
query,
calls,
mean_exec_time AS avg_time_ms,
total_exec_time AS total_time_ms,
rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
九、PostgreSQL 在 AI 时代的新角色
随着大语言模型（LLM）和 AI 应用的爆发，PostgreSQL 凭借 pgvector 扩展成为了 向量数据库 的热门选择：
-- AI 应用：文档语义搜索
CREATE EXTENSION vector;
CREATE TABLE documents (
id SERIAL PRIMARY KEY,
title TEXT,
content TEXT,
embedding VECTOR(1536) -- 存储 OpenAI text-embedding-ada-002 向量
);
-- 创建 HNSW 索引（高性能近似最近邻搜索）
CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops);
-- 语义搜索：找到与查询最相似的文档
SELECT title, content,
1 - (embedding <=> $1::vector) AS similarity
FROM documents
ORDER BY embedding <=> $1::vector
LIMIT 5;
为什么选择 PG + pgvector 而不是专用向量数据库？
- 减少架构复杂度：不需要额外维护一个向量数据库
- 混合查询：可以同时进行向量搜索 + 传统 SQL 过滤
- 事务保证：向量数据与业务数据在同一事务中
- 成熟的运维生态：备份、复制、监控等工具齐全
十、学习资源
官方资源
- 官方文档：https://www.postgresql.org/docs/ （质量极高，强烈推荐）
- 官方 Wiki：https://wiki.postgresql.org/
推荐书籍
- 《PostgreSQL 实战》
- 《PostgreSQL 修炼之道》
- “PostgreSQL: Up and Running” — Regina Obe
- “The Art of PostgreSQL” — Dimitri Fontaine
在线资源
- PGTune：https://pgtune.leopard.in.ua/ （根据硬件自动生成配置）
- pgexercises：https://pgexercises.com/ （SQL 练习）
- Postgres Weekly：每周 PG 新闻邮件
十一、总结
┌─────────────────────────────────────────────────────────┐
│ 为什么选择 PostgreSQL？ │
├─────────────────────────────────────────────────────────┤
│ │
│ ✅ 真正的开源，无厂商锁定 │
│ ✅ 功能最完备的关系型数据库 │
│ ✅ 严格的数据完整性和 ACID 保证 │
│ ✅ 强大的扩展生态（PostGIS、pgvector、TimescaleDB...） │
│ ✅ 优秀的 JSON 支持，兼顾关系型与文档型 │
│ ✅ 持续演进，社区活跃，每年一个大版本 │
│ ✅ 在 AI 时代焕发新生（pgvector） │
│ ✅ 从小项目到超大规模均可胜任 │
│ │
│ 一句话总结： │
│ PostgreSQL 是一把"瑞士军刀"， │
│ 能解决你 90% 的数据存储与处理需求。 │
│ │
└─────────────────────────────────────────────────────────┘
PostgreSQL 不仅仅是一个数据库，它是一个数据平台。无论你是初学者还是资深工程师，深入学习 PostgreSQL 都将是一项值得的长期投资。
后记
2026年5月18日于上海，在claude opus 4.6辅助下完成。
AtomGit 是由开放原子开源基金会联合 CSDN 等生态伙伴共同推出的新一代开源与人工智能协作平台。平台坚持“开放、中立、公益”的理念，把代码托管、模型共享、数据集托管、智能体开发体验和算力服务整合在一起，为开发者提供从开发、训练到部署的一站式体验。
更多推荐
5
0- 0
已为社区贡献6条内容
所有评论(0)